Skip to Content

sorting using a formula field

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Aug 21, 2015 at 07:58 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.