sort on a summarized field

[safari7391]

Crystal Reports XI

How do I sort on a summarized field?

I have a formula that sums all the acdcalls for every supervisor - {@CallsHandled}. But Crystal will not let me use the {@CallsHandled} formula unless I have a group for {@Supervisor_Name}. And if I have a group on the {@Supervisor_Name} it will sort by the {@Supervisor_Name} group first.

{@CallsHandled}
sum({Daily_Metrics.Sum_acdcalls},{@Supervisor_Name})

{@Supervisor_Name}
if isnull({Daily_Metrics.Sprvr_Name}) then "Other"
else {Daily_Metrics.Sprvr_Name}

 

[synapsevampire]

Try Report->Group Sort Expert->Top N and set the Where N is to 100 or so to allow for all of them, and choose the {Daily_Metrics.Sum_acdcalls} as the field to sum.

-k

 

[safari7391]

Group Sort Expert is greyed out. I have no groups.

 

[safari7391]

This is the way I am sorting. I am using a parameter to sort the different summarized fields listed below. But the field are not working unless I group by the {@Supervisor_Name} formula. And I do not want to group by the {@Supervisor_Name} formula, because the fisrt sort will be by {@Supervisor_Name} formula instead of one of the fields below.


{@Sort}
//Output 1st Sorting criteria
if count({?Sort_Criteria}) >=1 then
(
select {?Sort_Criteria}[1]
case "Calls Handled": {@_Calls_Handled_Supervisor}
case "Average Calls Per Hour": {@_Average_Calls_Per_Hour_Supervisor}
case "Average Handled Time":{@_Avg_Handle_Time_Supervisor}
case "Average Talk Time": {@_Avg_Talk_Time_Supervisor}
case "Average Call Work Time": {@_Avg_Call_Work_Time_Supervisor}
case "Total Hold Time": {@_Sum_Hold_Time_Supervisor}
case "Aux Time":{@_Sum_Aux_Time_Supervisor}
case "Aux Time %": {@_Aux%_Supervisor}
default: 0000
)

 

[lbass]

You have to insert a group on {@Supervisor_Name} or else your other formulas will not work. Because you want to sort on summaries, you will need to use topN/group sort (which overrides the group sort order). But, in order to use topN/group sort, you must be able to insert a summary on the field to be used for the sort. If your sort formula results were all intended to be summed, you could change the formula result in each case to the field you wanted summed. Then you would be able to place the formula in the detail section, insert a sum, and {@sort} would appear as a topN/group sort option.

But, with mixed summaries as you are showing, you wouldn't be able to do that. One way you could solve this is by using "add command" as your datasource, where you define the individual summaries in the SQL query directly as in:

(select sum(AKA.`Sum_acdcalls`) from Daily_Metrics AKA where
{fn ifnull(AKA.`Sprvr_Name`,'Other')} = {fn ifnull(Daily_Metrics.`Sprvr_Name`,'Other')})

Then you would use the resulting expressions in your formula instead of the summary formulas. You would then be able to insert a maximum on {@sort} to use in the topN/group sort, since a maximum of each expression would be the same value as the expression itself.

-LB

 

[safari7391]

Can you please go into more detail about the "add command" and "sql", this is kind of new to me. The first formula is the...

(select sum(AKA.`Sum_acdcalls`) from Daily_Metrics AKA where
{fn ifnull(AKA.`Sprvr_Name`,'Other')} = {fn ifnull(Daily_Metrics.`Sprvr_Name`,'Other')})

Not sure about all the AKAs.

Please explain how I would start the "add command" and begin with the first formula.

 

[lbass]

Before trying this in the "add command" area, see if it will be accepted in the SQL expression formula area (field explorer->SQL expression). I'm not sure whether 9.0 allowed these types of SQL expression, but if it does, it would be simpler for you.

The "AKA" is my name for an alias table, so leave that as is, but make sure the field and table names in the above expression are correct. The syntax/punctuation for SQL expressions can differ based on your datasource, as can the functions that are available, so if the above doesn't work, I won't be able to be of much further help. To check how your database uses punctuation, you could go to "Show SQL query" and observe how the punctuation appears there.

-LB

 

[safari7391]

I am using Crystal Reports XI and I do not see the SQL Expression in that version. Although I do have a copy of Crystal Reports 9, and it has the SQL Expression in it. Should I down grade the report to version 9?

 

[lbass]

Sorry, I must have been thinking of a different poster when I assumed you were using 9.0.

I don't have access to my CR XI right now, so am not sure if you can do this, but, in your current report, go to the "Show SQL Query" and see if you can copy it. Then open a blank report and select "Add command" as your datasource. Then see if you can paste the query there. Otherwise, you will have to write it from scratch. Observe the punctuation conventions used in the (pasted) query, and then insert the expression I specified earlier in the Select area, so it would look something like:

Select
Daily_Metrics.`Sprvr_Name`, Daily_Metrics.`Sum_acdcalls`, ((select sum(AKA.`Sum_acdcalls`) from Daily_Metrics AKA where
{fn ifnull(AKA.`Sprvr_Name`,'Other')} = {fn ifnull(Daily_Metrics.`Sprvr_Name`,'Other')}) as 'Calls Handled'
From
`Daily_Metrics` Daily_Metrics
//etc.

Unfortunately, I'm also not sure whether the {fn ifnull()} function can be used directly in a SQL query or whether it needs to be stated differently. Maybe someone else can clarify, since I can't test it right now (it does work in the SQL expression area for certain databases).

To do the averages, you would have to create separate expressions for the sum and for the count and then create a formula that divides the sum by the count and then use that in your master formula.

-LB

 

[safari7391]

Also how can I pass 3 parameters from a access database to crystal reports and use them as a dynamic parameter. I have already passed the 3 parameters from the access database to the crystal report and able to see the 3 parameters under the paramater section in crystal report. Just do not know how to create a dynamic parameter from those 3 database parameters.

 

[lbass]

If this is unrelated to the current problem, please start a new post. Otherwise, if you are saying you are not sure how to incorporate parameters in the add command object, you do have the option of creating parameters in the add command area. Then I think you place the cursor in the query where you want it to appear and doubleclick.

-LB