Sorting group based on running total field in crystal report

Question

I am having a report that designed to display the net sales and purchases amount of a given item within a given store. The report having 2 groups the first one is on the store and the second one is on the item and it looks like the example below:

Store: store_name [Group Header]
Item : item_name [Group Header]

[Qty] [DocNum] [DocType] [Detail]
[500] [ 8 ] [ S ]
[200] [ 3 ] [ P ]

Total Sales 500 [Group Footer]
Total Purchases 200

[Group Footer]

The totals fields are a running total fields not a summary field. All what I need is to sort the items within the store group according to either the higher sales or purchases amount and such sorting will be done through a sorting option represented by a boolean parameter value. If the parameter value is false the item with the higher sales amount will be first in order then the next item in amount etc. If the parameter value is true then the item with the higher purchases amount will be first in order then the next item in amount etc. Unfortunately, I cannot use the Group Sort option under Report menu as I do not have summary based on the group and therefore the Group Sort option is disabled and even if it is not it will sort according to a summary fields not on the running total fields. Does anyone has an idea about how to achieve such sort?!!!

What I have tried:

I tried to achieve the result through the database using the order by clause however that won't work correctly as I expected

Answer 1

There are 2 ways how to achieve that:

• Simpler one is to make 2 different reports, and depending of that boolean value call different report. Not the best solution because on every change of report you have to change them both etc...
• "better way" to do it to sort you data before calling your report, so when you use grouping in report you group by your columns with options "in original order" (picture below). Your data will be sorted how you sorted it before calling report and you use only 1 report for both conditions.

So do it what is easier to you, both ways works but require different approaches than you have now. There is possible 3rd way with underlined logic and lots of if-then-else logic, but you would need a lot of conditions and you could be messy sometimes.

Hope it helps you

Answer 2

Instead of manipulating the running totals.. manipulate the quantity part so that if quantity is changed running total will automatically changes.

Try below approach and let me know.

Insert one more group (In your case it is 3rd group) with Quantity and place the quantity in detail and write your running total.

Now for group 3 go to change group and select option use Formula as group sort order

When formula window is opened write below code.

if paramter="false"
then crDescendingOrder
else crAscendingOrder

Let me know how it goes.

Answer 3

Firstly thanks to every one tried to help or gave me an idea :). Finally I had to make the changes in the stored procedure through sending an additional parameter to the it to specify the sort option. I did it through putting my select within a common table expression then I select from that table net sales and net purchases grouping by store and item and then according to the parameter value I choose either the net sales field or the net purchases field for ordering and within the report I have choose the order option as the original order. It works fine and I know that solution from the beginning but I do not want to turn to it directly as a first solution to avoid slow query execution.