How to sort groups based on calculated field?

[Former Member]

Hi all,

I have a quesion. I have a report with 3 groups. Here's how it looks like when it first run (Group1 level):

M1 M2 M3 M4 ... M13 CY PY Diff Diff%

2 5 5 8 5 25 20 5 20%

4 5 6 5 5 25 30 -5 -20%

1 2 15%

I need to sort these groups by Diff%. When I go to Report - > Group Sort Expert and select "All" based on... it gives me some other formulas in drill down but not that one that I need. How to add formula I need to that drop down list that CR offers you to sort group based on?

I'm using CR XI R2.

Thanks

[Former Member · Accepted solution]

Hi Vitaly,

The Diff % must have been calculated on a particular field,so it will give higher % for a higher value numerator and a lower value for a lower value numerator.

So, one option is to sort it by the field in the numerator.Other option is to introduce a "WhileReadingRecords" command in the fromula which you want to see.

Thanks

[Former Member · Accepted solution]

Prathamesh, thanks for you reply.

First statement that num will be grater if Dif% is grater not true in my case because both num and denum are formula not a particular field.

Also, I can't use WhileReadingRecords - it says Formula must be evaluated later than you trying to do it. I've tryed to push all my formulas to Pass 1 but I can't do this. I need to calculate Diff% in Pass 2. Looks like there's no way in my case to do this easy. I'm thinking to add one more column in my db....

[Former Member · Accepted solution]

Then, are these 2 formulas visible? If yes so you may try to sort on the one, in the num.

[Former Member · Accepted solution]

None of these formulas are visible in "based on" drill down. I can see just basic summary field there that I can't use...

[Former Member · Accepted solution]

Ok, I was able to create summary of Diff column that's visible in "based on" drill down for group sorting but I need to sort on DIFF% that's Diff/CY.

What I did it's create new formula Diff_for_Sorting and put CY-PY there and then place summary of this formula to the Group Header. But as I said it not sorted correctly because of I need to sort based on Diff/CY and CY is not static for each group. I can't do CY-PY/CY in my formula because it will do this Sum(CY-PC/PY) as summary but I need to do this Sum(CY-PC)/Sum(CY) to get correct data. Is there any way to resolve this?

[Former Member · Accepted solution]

Hey, did anyone figure this out? I also need to be able to sort on a ratio item that is a formula,

but the formula is not listed in the Group Sort... any idea?

[Former Member · Accepted solution]

Problem here is that you can't use any formula to sort group based on it. It should be straight aggregation from database field. So in my case I ended to do all calcalation that I need in stored procedure and return precalculated ratio to my report. Not the best way but it resolve my problem.

[Former Member · Accepted solution]

Hi. Thanks. Yes, but I need the ratio at the group level as well:

Group1 20 10 2%

-

---

Det1 4 2 2%

Det2 10 4 2.5%

Det3 6 4 1.5%

You see what I mean?

[Former Member · Accepted solution]

Yea, I see what you mean. I had exectly the same situation. As I said I ended calculate all ration in the sp and return it to report already precalculated for EACH level of grouping. In this case I can create aggregation function on this precalculated field and use it to sort group based on it. I understand that this is kind of wierd solution but it was only one solution that I could find for this case.