| Author |
Message |
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
   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 Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
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 Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
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 Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
Vimal Nair
Newbie
Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
|
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 Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Vimal Nair
Newbie
Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
|
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 Logged |
|
|
|
