How to sort groups by derived formula

[Former Member]

My report contains two groups. First grouping is by department and second group is by pay_code (for example, OT, OT1, OT5 are overtime paycodes and RG, RG1, RG5 are regular hour paycodes). Each group at the department level contains a running total of overtime and regulartime wages (wage_amount is a database field for each record). I've inserted a formula in the department group footer: runningTotalOvertimeWages/runningTotalRegularTimeWages.

There are about 70 department groups however I only want to show the 10 departments that have the highest overtime rate (result of formula described above.) I've read the docs for the Group Sort Expert however this option is not enabled because I'm using running totals and not summaries. Using a summary instead of a running total doesn't produce expected results (because second group is defined conditionally based on value of paycode.)

Is there another approach I can use?

[Former Member]

Hey Patricia,

I think you can sort on a formula. What I mean is, try counting/summing with a formula instead of the runniing totals and see if that works and then try the Top N in Group sort.

The Panda

Edited by: pandabear on Jun 20, 2008 4:45 PM

[Former Member]

The record layout doesn't allow use of the Summary feature. For example, my source records are in this format:

[DEPARTMENT] [EMPLOYEE] [PAYCODE] [AMOUNT]

Each record represents one day wages of speficied pay code. PAYCODE values can be regular, overtime, or others. My query returns records sorted by DEPARTMENT and PAYCODE. The running totals are needed to sum amounts of type overtime or regular (where running total gets reset on Group2 (when paycode changes). If I use the Summary feature Crystal sums ALL amounts. I need to isolate sum of regular and overtime amounts in order to calculate the formula defined as totalOvertimeWages/totalRegularWages.

[Former Member]

Correction, my query returns only records of regular or overtime PAYCODE (total of six PAYCODES).

[Former Member]

Hi Patricia,

I agree...the summary feature is has too many holes in it.

-What I mean is, can you add (sum...) the groups' overtime in a formula like: @sumOT numbervar holdT:= holdT + table.ot.

Running Total is good because it gives you some nice options and a place to set formulas.

-If none of this is good for you, then I would go to the query

(actually, this is the best solution anyway).

Each db has it's own way of saying "Give me the top n of records". Here's a few ways of doing it:

http://blogs.claritycon.com/blogs/satish_vemula/archive/2006/02/15/220.aspx...so get the top 10 groups in the query.

...then you can group by sum(ot) in the query. This will give you the 10 groups and the sums (in whatever order you like).

Please forgive me if I'm off track with what you're saying. Not trying to frustate...your second posting did make things a little clearer.

Hope this helps,

The Panda

I just wanted to add that in my "DB2" world, I might use a "With" statement

to create a temp table with the original query, do all the calculations with the

"with" table, and output everything preprocessed to the report. This is great for charts and such where placement is so limiting in Crystal(...all the calculations are "below me", yet I want to show the chart "above me", etc.)

Edited by: pandabear on Jun 23, 2008 3:37 PM

[Former Member]

@sumOT numbervar holdT:= holdT + table.ot

I used this formula to compute totals for both the overtime and regular hours.

This enabled the group sort function however Crystal only enables the sort by either SumOT or SumRegular, and not the formula I used to compute SumOT/SumRegular.

Instead I used a SQL inline view, performed all the grouping, sorting, and computing of the ratio on the server and returned the ranked list to Crystal. The disadvantage of this approach? The SQL does not return detail (rows). Workaround involves providing two reports: an unranked, unsorted detail report for ALL departments that allows drill-down to row data, and a summary report that provides high-level top10 summarized view.

Thanks for your tips.

[Former Member]

Hardly seems very fulfilling does it? I like the query solution for the main report. As much as I don't like to use them, will a sub report, linked to the main report, give you the details you need...all in one report ? Wish someone smarter would come along and chime in.

The Panda