grouping and totaling inside of groups? 1

[kenndot]

I have this situation:

In the db, there is a field that holds one of about 7 possible entries, they are not particularly relevant, but just for understanding, let's say that they are: sun, moon, stars, planets, comets and asteroids.

Now, on the report, it has to be shown differently. I need to show some totals as follows: sun, moon and stars all need to be added together to form one total and planets and comets another and asteroids another.

There is another field which holds values that I need to group all of this by: let's say Earth, Mars and Jupiter

So, finally; I need to group everything by Earth, Mars and Jupiter
then within that grouping I need to total those other fields, BUT they need to be totalled separately within each of the groups and keeping in mind that the fields that I am totalling do NOT have the same name as what is shown in the db, so I need to have a condition in there somewhere I just can't seem to get it right to have it do everything dynamically.

What I have tried was to group and then drill down, and that part worked, but I could only get it to work for one of the totals. So, then I tried to basically hard code the information and use the Running Totals function for each total that I need, but that's not exactly very good design, so I'm asking all of you.

If I haven't provided enough or the right info, let me know.

 

[kenhamady]

This should get you close. When you want to lump several values into one group, you create a formula ( for now called category) and group on the formula:

if {field} in ['sun', 'moon' , 'stars' ]
then "Category 1"
else "Category 2"


Group on your primary field {planetName}
then group on {@category} formula above,
then group on the {field} if needed.


Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)