How do I use the totals of sub group in the main group...

[rahulroy08]

Hi,

I have a sub group in my main group. But when I print the totals I need to print the values or sub-totals(totals of sub-groups) again in the main group too.

How do I do that.

Here is how it looks...

Grp 1
  sub-grp1
          fld1 fld2 fld3
          ...
          ...
          ---------------
  sub-total1

  sub-grp2
          fld1 fld2 fld3
          ...
          ...
          ---------------
  subtotal2

(Grp-footer)
total
sub-total1 points (sub-total1)
sub-total2 points (sub-total2 * 2)
Total Points(stotal pts1 + stotal pts2)

This is how it looks...

So I need the values of sub-totals again in the main group to calculate the sub-total points. How do I use it. Please help.


All the suggestions are appreciated.



Thanks,



Rahul.

 

[synapsevampire]

Yeah, that's not how aggregates of groups work, of course.

Try inserting a crosstab into the outer group footer with the subgroup field as the row to act as your second pass at the data.

Should look similar.

-k

 

[rahulroy08]

I tried doing that but that doesnt work. Is there any other approach that you can suggest.


Thanks,

Rahul.

 

[synapsevampire]

Maybe, I'll need to know what it doen't work means.

If you place a crosstab in an outer groups footer, an d place the inner groups field as the row, and the field to perform an aggregate on in the field to summarize, it will do what you are asking for.

So I would guess that I misunderstand the requiements, or you misunderstand the solution offered.

If you could explain what happened with the crosstab I can address that.

-k

 

[Madawc]

Crosstab should be the answer, as synapsevampire says. What doesn't work?

If necessary you can put a subreport in the footer to do the whole job again with a different display.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[rahulroy08]

Hi

Let me be more clear with the problem

Grp 1
 
  sub-grp1
          fld1 fld2 fld3
          fld4 fld5 fld6 (all are diff fields on the dbase)
          fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
          -------------------------------------------------
sub-total1 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)

sub-grp2
          fld1 fld2 fld3
          fld4 fld5 fld6 (all are diff fields on the dbase)
          fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
          -------------------------------------------------
sub-total2 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)


(Grp-footer)
 
total
sub-total1-points (=sub-total1)
sub-total2-points (=sub-total2 * 2)
Total Points (=stotal-pts1 + stotal-pts2)

Hope this helps...

 

[rahulroy08]

Also I'm using Crystal Reports 8.5 and SQL Server 2005.

 

[synapsevampire]

OK, I don't understand why the crosstab doesn't work.

You show:

(fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)

Which looks tpo me like there are 3 different sums.

Then in the end where you are showing the TOTAL you show one field only.

So what is being shown there?

You can create a formula of {table.fld1}+{table.fld2}+{table.fld3}

And then use that formula in the crosstab if that helps.

Typically it's best to show example data and the expected output. Ypu've shown field names in an example of the output, I think.

-k

 

[rahulroy08]

yep. I tried the same thing. But the style of cross tab in CR 8.5 doesnt look good. With such a big report and such a huge crosstab...I just want my data in a single page...So was looking to accomodate it in a single page...I guess a sub-report as suggested 'Madwac' would do.. Let me try that and get back to you guys.

I sincerely appreciate everybody's suggestions and help.


Rahul.

 

[rahulroy08]

Finally, succeeded in completing my report as desired. I just had two groups under my sub-group, so decided to use Running totals for all the fields with the Fields to be evaluated to be the Sub-Report's sub-total fields...and It turned out to be perfect.


Anyways thanks guys for your help and support.


Rahul.

 

[synapsevampire]

Ahhh, hardcoding.

Something I generally don't suggest. If anotehr group is added to the database, your report won't work properly.

So the crosstab worked fine, you just didn't like the output.

Madawc's subreport idea is fine as well, this means you query the database twice, but it's a better solution than hardcoding.

There are other solutions as well, such as returning the subtotals using a SQL Expression and using an array to house the values for displaying at the end.

That would be faster as it's a single query, but complex to code.

-k

 

[angelana22]

Hello Rahul,

Can you explain how you did this report for I have the same problem.

Thanks

 

[rahulroy08]

Hi angelana,

I have a sub-group,whose field has just two values, Peak and off-Peak. So I used running totals for with the sub-group totals in the main group footer.And it worked for me.

But as discussed earlier, if the sub-group field has more values then I think a sub-report will give you better performance.

Hope this information helps.


Rahul.