Sorting based on Formula results

[Former Member]

I have built a report that is constructed like this

Main Report

Group one = Sales Agents Location

Group two = Sales Agents Names

Group two Footer A

I have subreport linked to the Sales Agents ID that totals their tours taken.

Group two Footer B

I have a subreport that is linked to the Sales Agents ID that totals their Sales Made

I have a formula that divides Tours by Sales to get their ratio.

I need to SORT each agent by highest ratio however my Group Expert is grayed out. Is their another method?

Edited by: Holly Elliott on Sep 19, 2008 9:07 PM

[Former Member · Accepted solution]

Because of when Crystal will process the Group Sorting, you can only sort on subtotal summaries that are solely calculated by data in the main report.

It sounds like your subtotals are dependent on totals from the subreports, which won't be processed until after the Group Expert.

Take a look at the topic "Multi pass reporting" in the CR Help for more information on this subject.

[Former Member · Accepted solution]

So is the bottom line that what I'm trying to do can't be done?

[Former Member · Accepted solution]

Not unless you can get those totals calculated in the main report.

You may be able to use a SQL Command to get the complete dataset without having to use subreports for the totals. If you can get the totals you need into the main report dataset, then you may be able to pull off the sort.

[Former Member · Accepted solution]

use shared variables to pass the values from the subreport to the main. then use that in your totals and you should be able to sort

[Former Member · Accepted solution]

Sharon:

You can't group sort off of a variable from a subreport because the group sorting is processed in Pre-Pass #2 and subreports are processed in Pass #2. Sorting is also processed before subreports in Pass #1.

Brian

[Former Member · Accepted solution]

Using the SQL Command thought process would this be something I could inside of Crystal or I would have to do on the database side of things?

[Former Member · Accepted solution]

Hey Holly,

Just put your query in the "Command" table.

Database Expert >Current Connections > Add Command