Grouping on a Report and Sum Total 1

[psvialli]

I will try and explain what i am trying to get done !

I have 2 tables Company & Invoices

On my company table I have Company name, Total Sales, and total Certs. (Total sales is nothing to do with Invoice total)

On my Invoice I am holding Details and Invoice cost.

I have joined the tables so I now have the following

Co_pk Invo_pk Name Total Sales Total Certs Details Invoice cost
1 1 Company1 100.00 500 Details1 20.00
1 2 Company1 100.00 500 Details2 30.00
1 3 Company1 100.00 500 Details3 50.00
2 4 Company2 50.00 300 Details1 100.00
3 5 Company3 120.00 250 Details1 65.00
3 6 Company3 120.00 250 Details2 85.00
Etc…
So on my report I am trying to show the following(have grouped by CO_PK :-

Company 1 100.00 500

Details1 20.00
Details2 30.00
Details3 50.00

Company2 50.00 300
Details1 100.00

Company3 120.00 250
Details1 65.00
Details2 85.00

-------------------------------------------------------------------------------

Total SAles 270.00
Total Certs 1050
Total Invoce Cost 350.00


I have this all working on my report but cannot seem to generate the correct totals at the bottom, it keeps adding up all the total sales,certs etc..without the grouping so I get 590 for total Sales and not the 270 in the total no matter what i do with the SUM grouping..

Can this be done on the report?

Thanks
Paul

 

[Stella740pl]

Paul,

Can this be done on the report?

If I understand you correctly, of course. In several ways, as a matter of fact.

First of all, did you create the groups in your report also, not only in your cursor?
If so, then CO_PK (if you wish to show it) should go in the group header band, the details - in the detail band, and you can put your summary in the group footer band. You just
put your SALES, CERTS, and COST as expressions in the appropriate fields of the group footer, and in their properties click the "Calculations" button. Select "Sum" there. That should do it.

This is the most straight forward way. In some cases, you might need to introduce report variables.

Try this and come back to tell how it worked out.

 

[psvialli]

Hi Stella740pl,

I have a group co_pk on my report, The Cursor is not Grouped, as i cannot group by Co_PK as i need to show all the Invoices that relate to that company.

I have tried what you have suggested above but i still get the Total sale , cets for all the companys not the grouped ones , have even tried setting the Reset after co_pk.

Any suggestions how to solve this ?

Thanks

Paul

 

[TamarGranor]

First, the grouping Stella mentioned is in the report, not the query. Grouping in a report is a way of giving yourself additional header and footer bands for sections within the report. In your case, you'd group on the customer pk (look on the report menu for the grouping option) and put the customer name and customer-level info in the group header, then the invoice info in the detail band.

If you want all the totals at the end, for the stuff you're totalling at the group level (total sales and total certs), you have two basic choices:

1) Compute the totals ahead of time and simply plug the right fields/variables into the report.

2) Use several report variables to do the work. The key is that you need two report variables to track whether you're starting a new customer or continuing a current one, so that you only add to the group-level totals once for each customer. I'm doing this off the top of my head, but it's roughly like this, making sure to define these in this order:

Variable name: LastCust
Initial value: <anything>
Value to store: CurCust
Calculate: None
Reset: End of report

Variable name: CurCust
Initial Value: <something that can't occur as a customer PK>
Value to store: co_pk
Calculate: none
Reset: End of report

Then, you need one report variable for each of the things you want to calculate. For example, for total sales, something like this:

Variable name: GrandSales
Initial value: 0
Value to store: IIF(LastCust=CurCust, 0, Total_Sales)
Calculate: Sum
Reset: End of report

What happens here is that the LastCust variable runs one record behind the CurCust variable. When they're the same, you know that the two records are in the same group. When they're different, you're starting a new group, so you should add the Total Sales (or whatever) value to the running total.

In the Report Summary, you place the GrandSales variable.

Tamar

 

[Stella740pl]

Can you explain something, please?

Is CO_PK a company ID?

You say you don't want to add up everything in one total.
Do you mean that you want to print totals for each
company/CO_PK separately?

Do you want them at the end of each group or on the bottom of the whole report? If so, then why, in your example of what you want to get you show only one set of totals?


"have even tried setting the Reset after co_pk"
Reset of what? If you are talking about reset of your field totals, then yes, it has to be reset on the group expression, which is CO_PK.

Is your cursor sorted in any way? How?

Where, exactly, in what band, are you trying to set up totals? It should be after each group, in the group footer band.

On the other hand, if you are trying to put all the totals for all the companies, at the end of the whole report, in the summary band, then the approach above wouldn't work.

If you want separate totals for every single company, but at the end of the whole report, then you need to keep 3 separate report variables to accumulate the totals for every single company you have. Not the simplest way to create a report (but not impossible).

Please clarify which one you are trying to do and in which band. Depending on this, an approach can be selected.

 

[psvialli]

Many thanks Tamar,

First for the explanation and then for the answer, it works Great and I understand why as well!

Many thanks

Paul