sorting using a formula field

[Former Member]

Dear Experts,

I am trying to sort my Crystal Reports for Enterprise report on a formula field. My report is as below.

*************************************************************************************************

cost center 1:

     account               $Change[in$]         %of Total Change[in %]

     salary                      500                      -25

     admin                      1000                    -50

     misc                        1500                    -75

     extra                        -5000                   250

                                   --------

                                    -2000

                                   --------

cost center 2 :

*************************************************************************************************

The "% of total change" is calculated as follows : individual value/sum for e.g. (500/-2000)*100= -25%. The other 2 columns are coming in from bex query. Now i need to sort the report on the 3rd column in a descending fashion so that the report should look like this

extra               -5000               250

salary               500                -25

admin               1000              -50

misc                 1500              -75

The problem is when i go to the sort tab, i do not see the formula variable which i used for the 3rd column.

CRE allows me to sort on the 2nd column but the sorting is correct only if the sum is positive. If the sum is negative as shown above, the sorting is not as expected.

Please help.

Palaniappan

[abhilash_kumar]

Hi Palaiappan,

You cannot, unfortunately, sort on formula fields that refer to aggregate functions.

You have a few workarounds:

1) Calculate the percentage in the BEx query

2) Use a Subreport. This BEx query would run twice with this approach and might slow down the report too.

3) Use a Crosstab.

- Add the 'Account' field as the 'Row'

- Add the '$Change' field as the First Summary Field and set its Summary function to 'Sum'

- Add the '$Change' field again and this time select 'Change Summary' > Check 'Show as percentage of'

- You should now be also able to sort the crosstab 'Row' based on the Percentage summary.

-Abhilash

[Former Member]

Abhilash,

Thank you for your response.

1. In bex i cannot use the ct sum function and olap access at the same time. If i uncheck olap access then query will not be available in crystal.

2. I tried this approach : create sub report->create shared variable(which is sum of the $ change) in sub report->pass that shared variable back to main report and use that for the denominator. Still doesn't work

3. Can you expand on this?

Regards,

Palaniappan

[abhilash_kumar]

You cannot sort on 'shared variables'.

This workaround involves more work and involves using an array passed as a 'prompt' inside the subreport. The subreport is used to 'display' the final layout.

Where exactly are you with the Crosstab approach? Did you try it?

-Abhilash