Compare current year data to previous year data in crosstab

[Former Member]

Hi,

I’m trying to create crosstab in crystal reports that includes year to date(YTD) data and last year (LYTD) data.

For example, for date range: start date=04/01/2018 and end date 05/31/2018 I need to be able to get the result below:

……………… Apr 2018……….May 2018……….YTD……….LYTD(Apr2017+May2017)

Orders………..25……………………..15…………… ..40………………..…36...………

products………30…………………….40………………70……………………64...………..

Any advice and suggestions will be greatly appreciated.

Thanks

[abhilash_kumar · Accepted solution]

Hi Alisia,

There are too many steps involved but here you go:

1. Setup a report with a crosstab that shows data for just Apr and May 2018. I believe you already have this setup as needed?

Insert a "Report Header b" section and move the crosstab to this section. The "Report Header a" section will be used to hold the Subreport.

2. Right-click the "Total" column header > Select Calculated Member > Insert Child.

3. A Blank Column should be inserted as the first column of the crosstab. Right-click the blank white space to the top left of the Crosstab > Advanced Calculations > Calculated Member.

Highlight the value ("Please enter a description") under "Calculated Members" > Look for the "Properties" box to the right. Select "Insert Evaluation" value as "After" and click OK.

This should move the new blank column to the end of the crosstab.

4. Right-click the blank column header cell of this new column > Calculated Member > Edit ColumnValue formula and use this code:

DateTime(1890,01,01,00,00,00)

5. Right-click the column header cell again > Calculated Member > Edit Header formula > Type in the following:

"YTD"

6. Right-click one of zero values in this new column > Calculated Member > Edit Calculation Formula > Use this code:

GetTotalValueFor("Date Field Used as Column")

Replace "Date Field Used as Column" with the database field you've used in the Crosstab's column. Notice how the field is enclosed in double-quotes and NOT the usual curly braces "{ }"

7. Repeat Steps 2 and 3 so that a new blank column is inserted to the end. This will be used to show LYTD.

8. After you insert this new column, repeat Step 4 and use the below ColumnValue code:

DateTime(1990,01,01,00,00,00)

9. Repeat Step 5 for this new column and name its Header as:

"LYTD"

10. Save an additional copy of this report and name it "Subreport". So you now have two copies of the same report.

11. Open the Original Report.

Go to Insert Subreport > Select "Choose an existing report" > Click "Browse" and open the report named "Subreport" > Click OK and place it on the "Report Header a".

12. Double-click the Subreport so you enter its Design Mode.

13. Modify its Selection Formula so that it always returns data for the Previous Year based on the Date Range selected.

14. Suppress ALL sections EXCEPT the one that has the crosstab.

15. Right-click the Row Labeled "Total" > Select Row Grand Totals > Click Suppress Grand Totals.

16. Right-click one of the values under the "Total" Column > Common tab > Click the formula button beside "Suppress" and use this code:

shared stringvar array s; 
shared numbervar x := x + 1; 
redim preserve s[x]; s[x] := 
totext(CurrentFieldValue); 
True;

17. Right-click the blank white space to the top left of the crosstab > Crosstab Expert > Customize Style > Format GridLines > Uncheck "Show GridLines"

18. Right-click one of the Row Values > Format Field > Select "Suppress"

Right-click one of the Summary Values > Format Field > Select "Suppress"

Right-click one of the Column values > Format Field > Select Suppress

Right-click the "Total" label > Format Field > Suppress

You should now have a blank report

19. Go back to the Main Report > Right-click one of the values under the "LYTD" column > Format Field > Click the formula button beside "Display String" > Use this code:

shared stringvar array s; 
If Year(GridRowColumnValue("Date Field used as Column")) = 1900 then //Replace "Date Field Used as Column" with the correct database field and enclose in double quotes like this example
s[CurrentRowIndex] else 
totext(CurrentFieldValue); 

Go to the Section Expert > Highlight "Report Header a" > Click "Underlay following sections"

That's it!

Do Note that both the Main Report and the Subreport should return the same rows in the same order for this to correctly work.

Let me know how this goes.

-Abhilash

[Former Member · Accepted solution]

Thank you Abhilash very much!!!

[Former Member]

Firstly you need to control data selected, assuming you want to use an end date parameter rather than current date

Create Parameter {?EndDate} as a date type

in select Statement

(({YourdateField} >= date(year({?EndDate}),1,1) and {YourdateField} <= {?EndDate}) // this years data

OR

({YourdateField} >= date(year({?EndDate})-1,1,1) and {YourdateField} <=dateadd("y",-1, {?EndDate})) // this years data

check syntax for dateadd to subtract a year, do not have Crystal on this machine. If you want to use other filter conditions you need to use all brackets defined above.

For Cross Table create formula @Month

If year({YourdateField}) = Year(currentdate)-1 then 'LYTD'

else

totext(Month({YourdateField}),"00",0,"")&"/"&Totext(Year({YourdateField}),0,"") // this will format 05/2018. You can not use May/2018 as data will not sort correctly

Use this formula as you column header in CT

Ian