Cross tab with calculated columns

[Former Member]

Hi expert,

I would know if is possible to create the report in attached image with 1 crosstab with Crystal report for enterprise.

I select a date in a prompt and customer needs:

accounts in rows

first column:  total value previous 2 year respect to prompt selection

second column: total value previous year respect to prompt selection

third column: YTD -1 value respect to prompt selection

fourth column: YTD value respect to prompt selection

Thanks

Samuele

[abhilash_kumar · Accepted solution]

Hi Samuele,

You won't be able to do this using a Crosstab unless you can get those columns as part of a single field.

In any case, you really don't need a crosstab for this structure. Here's what you need to do:

1) Create a Record Selection Formula so that the report returns data for two full years. Go to the Data tab > formulas and use this code:

Year({Date_Field}) IN [Year({?Date_Prompt})-2 to Year({?Date_Prompt})]

Note: Due to the Date conversions involved in the formula above, this formula will not convert to where clause. Since you're using CR for Enterprise, I'm assuming you're reporting against an SAP Datasource? If yes, then you should try to filter the records at the backend and ensure CR always receives a filtered resultset for better performance.

2) Insert a Group on the field you wish to show as the 'Row'

3) Create a formula called 'Prev2' with this code:

If year({Date_field}) = year({?Date_Prompt})-2 then {Measure_field}

4) Create a formula called 'Prev1' with this code:

If year({Date_field}) = year({?Date_Prompt})-1 then {Measure_field}

5) Create a formula called 'YTDPrev' with this code:

If ({Date_field}) IN [cdate(Year({?Date_Prompt})-1,01,01) TO cdate(Year({?Date_Prompt})-1,Month(currentdate),Day(currentdate))] then

{Measure_field}

6) Create a formula called 'YTD' with this code:

If ({Date_field}) IN [cdate(Year({?Date_Prompt}),01,01) TO cdate(Year({?Date_Prompt}),Month(currentdate),Day(currentdate))] then {Measure_field}

7) Go to the Insert tab > Total > Choose 'Sum of' in the first drop-down > Choose 'Prev2' in the second drop-down > Choose 'at all group levels' in the third > Under 'Location', choose 'above'

😎 Repeat step 7 for all the formula fields you created above

9) Right-click anywhere on the Details Section and Click 'Hide'

10) Right-click anywhere on the Group Footer Section and Click 'Hide'

11) Align the summary fields on the Group Header beside each other. Insert a Text Object on top of each Summary to label them.

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash. I have tried your suggestion and It works. Now I have this problem: I need to calculate measures based on account aggregation, in a crosstab is easy , but I don't know how caculate in different group. In attached image you could find the structure of the report

[abhilash_kumar · Accepted solution]

Hi Samuele,

Glad the suggestion works.

Please post that as a new discussion per SCN's rules.

Also, make sure you close this thread before creating the new discussion.

-Abhilash