SQL question
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21220
Printed Date: 27 Apr 2024 at 11:49pm
Topic: SQL question
Posted By: Stircrazy08
Subject: SQL question
Date Posted: 11 Dec 2014 at 12:39pm
I have a report that i am writing. and i use a select for from and to dates on the invoicedate, and i also have a item select(between) which works.
for the date select the user inputs say 11/1/2014-11/30/2014.
and the report gives the qty/dollar sold for that range.
is there a way to create a column that will display the same data qty/dollar for 11/1/2013 - 11/30/2013.
if you create a SQL field will that go off the db table to pull the data no matter what date selection you do on the initial report select?
-------------
Peter F
|
Replies:
Posted By: z9962
Date Posted: 15 Dec 2014 at 3:17am
|
are you displaying your report details section? or just at a group level?
|
Posted By: Stircrazy08
Date Posted: 16 Dec 2014 at 10:09am
right now, i am only displaying group footer.
i am surpressing/no drill down on detail.
-------------
Peter F
|
Posted By: z9962
Date Posted: 16 Dec 2014 at 11:52pm
Do you have 1 parameter as a date range? or two parameters from and to?
If one as a range
you could try in your select criteria
(invoicedate in {?dateRange} or
invoicedate in DATEADD("yyyy",-1,minimum({?dateRange})) and DATEADD("yyyy",-1,maximum({?dateRange})))
this will then return records for current and previous year. you can then use a formula to show the correct data at the group level.
|
Posted By: Stircrazy08
Date Posted: 17 Dec 2014 at 10:31am
I have two parameters that i created. fromdate; todate.
very new to this, does this go in the SQL expression Fields or in the select expert under invoicedate??
Thanks for helping!!
-------------
Peter F
|
Posted By: Stircrazy08
Date Posted: 17 Dec 2014 at 10:46am
this is what i put in the select expert.
tells me you cant summarize the field:
?prompt from date
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,minimum({?PROMPT FROM DATE})) and
DATEADD("yyyy",-1,maximum({?PROMPT TO DATE})))
-------------
Peter F
|
Posted By: kevlray
Date Posted: 18 Dec 2014 at 4:52am
|
You cannot use maximum in the select expert. You might be able use it in a group select (never tried before though). The only option I can think of is a command or a stored procedure.
|
Posted By: z9962
Date Posted: 18 Dec 2014 at 9:37pm
you can use the min / max if the parameter was a range. Though as you are using two parameters your select expert will look like
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}))
|
Posted By: Stircrazy08
Date Posted: 19 Dec 2014 at 3:34am
Z9962,
i changed my fields and made it a date range like your previous post, first time using that. thanks.
so, my select is.
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?daterange} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,minimum({?DateRange})) and
DATEADD("yyyy",-1,maximum({?daterange})))
when i check the syntax, i get the error:
A Date-Time range is required here, and it highlights the following part of the statement.
DATEADD("yyyy",-1,minimum({?DateRange}))
my daterange parameter was created with type:DATE (Static)
Peter
-------------
Peter F
|
Posted By: z9962
Date Posted: 19 Dec 2014 at 3:51am
double check what i wrote...
in last example i did not use DateRange
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}))
|
Posted By: Stircrazy08
Date Posted: 19 Dec 2014 at 4:17am
That statement worked.
i want to create a field, to use in my report that has the total qty from a field {AR_InvoiceHistoryDetail.QuantityShipped}
would i create a field in Formula Fields?
i want to receive the sum({AR_InvoiceHistoryDetail.QuantityShipped}), for {AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}))
trying to compare total qty from 11/1/2014-11/30/2014 to 11/1/2013-11/30/2014.
i am extremely greatful for your responses!
this has been a big learning curve and i know i havent even scratched the surface. also if you know of any good computer based training or you tube posts that would help - small company needs to be free! :)
Peter
-------------
Peter F
|
Posted By: z9962
Date Posted: 19 Dec 2014 at 4:31am
I would create a formula to flag year.
formula year flag
IF {AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}) then "Previous Year" else "Current Year"
Then you can create a second formula
IF formula year flag= "Previous Year" then {AR_InvoiceHistoryDetail.QuantityShipped}
then you can sum this formula.
|
Posted By: Stircrazy08
Date Posted: 22 Dec 2014 at 4:05am
i tried creating the flag, but get 0.00 for the sum qty field i created.
PrevYearFlag formula
IF {AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PromptFromDAte}) to DATEADD("yyyy",-1,{?PromptToDate}) then "Previous Year" else "Current Year"
PrevYearQty formula
IF {@PrevYearFlag} = "Previous Year" then {AR_InvoiceHistoryDetail.QuantityShipped}
Created a PrevYearTotQty sum field.
field to summarize @PrevYearQty
type of summary: SUM
Evaluate - for each record
Reset- on change field: AR_InvoiceHistoryDetail.ItemCode
tried also to do Reset on Never, but still get 0.00
the result should bet 110 total qty for previous year, so i know the data is there.
-------------
Peter F
|
Posted By: z9962
Date Posted: 22 Dec 2014 at 11:10am
try adding PrevYearFlag formula to details section to make sure that this is working correctly.
The other thing, it looks like you are using a running total? if you right mouse click on the prevYearQty field and select Insert summary.
You may need a running total in the details section to work.
|
Posted By: Stircrazy08
Date Posted: 22 Dec 2014 at 11:28am
adding PrevYearFlag to detail section doesnt work.
i changed the wording in PrevYearQty formula to = "Current Year" to see if it would work, and i get the correct Total Qty in PrevYearTotQty sum field.
I am running the report thru Parameters, does the formula created above go off the db? or the records selected for the report from the parameters?
i did create the PrevYearTotQty field in the Running Total Fields Section, wasnt sure where else to do what i wanted as i want the total qty summed up for each item, the table has multiple qty records for the same item.
-------------
Peter F
|
Posted By: Vimal Nair
Date Posted: 28 Jan 2015 at 12:03am
Cant you try it at the sql level?
For eg:
Select
Case When {AR_InvoiceHistoryHeader.InvoiceDate} Between {?From_Date} And {?To_Date} Then Quantity End CurrentYearQuantity,
Case When {AR_InvoiceHistoryHeader.InvoiceDate} Between {?From_Date} And {?To_Date} Then Dollar End CurrentYearDollar,
Case When {AR_InvoiceHistoryHeader.InvoiceDate} Between add_months({?From_Date},-1) And add_months({?To_Date},-1) Then Quantity End LastYearQuantity,
Case When {AR_InvoiceHistoryHeader.InvoiceDate} Between add_months({?From_Date},-1) And add_months({?To_Date},-1) Then Dollar End LastYearDollar
From
Your_Table
Where Item_Code={AR_InvoiceHistoryDetail.ItemCode}
Group it inside the report and do the summary.
Hope this helps.
-------------
Born To Live
|
Posted By: Stircrazy08
Date Posted: 28 Jan 2015 at 4:49am
Vimal
thanks for the reply, I am a novice. not sure where you mean "at the SQL LeveL" ? do you mean the select expert?
also, when you say group it inside the report? do you mean group it by itemcode? in the Group Expert?
i like your case below, and would love to try it, just need to figure out where to put it.
Thanks very much for your input.
-------------
Peter F
|
Posted By: DBlank
Date Posted: 28 Jan 2015 at 9:27am
|
You may be over complicating this.
If you just want summarized data comparing like items across two years use a Cross Tab.
In teh cross tab add the item code as the "row field", the date as the 'Column, setting it to per year and the shipped quantity as a sum in the summarized field
|
Posted By: Vimal Nair
Date Posted: 28 Jan 2015 at 7:42pm
Dear Stir,
We deal with huge amounts of data which might be in millions and billions and the database we use is Oracle. So we don't use direct table in Crystal report. Either it will be a command sql query or Oracle Procedure or a Package Which is more efficient in pulling data and summaries and we work closely with SAP. We don't suggest Huge data summaries at report level.
If your data is not very huge, then better do it at report level. The query I mentioned above can be done at report level also with a slight syntax change.
-------------
Born To Live
|
|