Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: SQL question Post Reply Post New Topic
<< Prev Page  of 2
Author Message
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet 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.
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet 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.
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
Vimal Nair
Newbie
Newbie
Avatar

Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
Quote Vimal Nair Replybullet 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
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 
IP IP Logged
Vimal Nair
Newbie
Newbie
Avatar

Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
Quote Vimal Nair Replybullet 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
IP IP Logged
<< Prev Page  of 2
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.