Replace a running total to show in GroupHeader

[Gus01]

Is there a way to replace a running total, to show the total in the groupHeader ?

Group Section
Group1
Group2
Group3

Total = summarize fld1
evaluate : on change of group Group3
Reset : on change of group Group2

Thanks for help !

 

[lbass]

What type of summary? In what group header to you want to use it? Can you provide some sample data and identify the fields and also the groups by the fields grouped on?

-LB

 

[Gus01]

Yes, for example :

Data =

Year City Client Invoice Price Product
---- ---- ------ ------- ----- -------
2008 City1 ClientA 001 120 Product1
2008 City1 ClientA 001 120 Product2

2008 City1 ClientA 002 100 Product1
2008 City1 ClientA 002 100 Product5

Report =

Group1 = Year+City+Client
Group2 = Invoice

2008 City1 ClientA Total Price = 220

invoice 001 Price = 120
Product1
Product2

invoice 002 Price = 100
Product1
Product5
The problem becomes because the price to sum is on the invoice and there are details record in an invoice (Invoice-Product relationship 1-n).
For performance reasons (huge data), I prefere to join the tables Invoice and Product in the database (join in CR is very slow, even using a subreport).

RunningTotal works, but not to show the total in the Group1Header :
sum(Price)
evaluate : on Change of group Group2
Reset : on change of group Group1

So I am looking for another way to do this summarize.

 

[lbass]

You could insert a crosstab that uses a formula that concatentates your two groups into one field, and then use that as a row field, and then use maximum of price as a summary. You could suppress the label and remove the grid. This would display correctly in the group header.

You could also create a SQL expression to get the price for the group header. You didn't indicate whether there was more than one table. If one table, it would look like this:

(
select max(`price`)
from Table A
where A.`year` = table.`year` and
A.`City` = table.`City` and
A.`Client` = table.`Client` and
A.`Invoice` = table.`Invoice`
)

You would have to adapt the punctuation to your datasource.

-LB

 

[Gus01]

Thank you for these ideas.

The data are pushed in the report as a dataset, and there are a lot of clients (not only one as showned in my sample). There is only one table in CR (the differents tables of the database are joined in the database, before the data are pushed in the report) and there are about 30 fields to summarize as described.

So, the report is more complex.

I am not sure that the crosstab does the right summarize.
At level Group2, the summary is Maximum(Price, Invoice), but
At level Group1, the summary is Sum(Maximum(Price, Invoice), Client)

Please, what do you mean by "create a SQL expression" ? Is it : create a new table in the CR report, joined with the main first table (named A in your sample) ?

 

[lbass]

Yes, I think the crosstab won't work--sorry. A SQL expression is a subquery that you can create in field explorer->SQL expression. If you can't see that in the field explorer, it's not an option for you. But you could potentially use a command instead. The "add command" feature is above the list of tables in the database expert. Let me know which is available to you.

-LB

 

[Gus01]

Unfortunately I think that I have not the "SQL expression" option available.
In the Database Expert, on the right, I just have the panel "Selected Tables", but no button, or contextual menus.

I use the release embedded in Microsoft Visual Studio (CR 10), which has not all the feature of the full release.

I have tried other unsuccessful ways with formulas and I think I need to add a subreport to select the data on invoice with no double due to the join with Product.
In spite of a second query on the database, and a join in the report ...

 

[lbass]

The SQL expression area is found in the field explorer, NOT in the database expert.

The "add command" feature is listed above your list of tables (to select FROM) on the left (not on the right).

Can you find either of these?

-LB

 

[Gus01]

These 2 feature are not available because the data source for the tables is ADO.NET (NewDataSet).
I can see "SQL expression" and "Add command" when I try with a new report and data source = Database Files.

In my report, the report doesn't create the SQL query to the database (as in case of data source = Database Files), but the data are pushed in the report by a program (ADO.NET dataset, which comes from different type of database Oracle, MySQL, ...).

I think a solution will be to create a new information on the data, to identify the products in an invoice, and then make a formula which return the invoice price only for the first product. A simple Summary (sum) at the client level, on this formula, summarize the right price for the client.

(sorry for my english ...)

 

[lbass]

You could either remove the Product table and then add it back in with a subreport linked to the invoice, or you could save the report as a subreport, add it to the group header, and display the running total only.

-LB

 

[Gus01]

ok thank you very much for these suggestions and any assistance you have given to my thoughts.

 

[lbass]

The problem with your idea is identifying the first record--this requires some kind of summary field, e.g.,

if {table.date} = maximum({table.date},{table.group}) then...

Or,

if {table.sequenceno} = minimum({table.sequenceno},{table.groupfield}) then ...

Neither of these can be summarized using an inserted summary--you would have to use a variable which could only be displayed in a group footer section.

-LB

 

[Gus01]

Yes I agree with you.
To know the first record, I think get information from the database (a sequence on the products of each invoice) to avoid having to use the min or max on the group , and then to use a summary inserted.

As you said, a better approach would effectively remove the Products table and insert a subreport.
However, I am worried about performance. Approximately 5000 to 10,000 invoices in relation to products in the report.
I do not know what will be the fastest:
- link Invoice-Products in the report using a subreport,
- Or do the join in the database (fast) and then calculate 30 inserted summaries in the group header Invoice?

 

[lbass]

I don't see how an inserted number is an option at all in this case.

-LB

 

[Gus01]

If I understood your question, I believe it is possible to aggregate a number
a) in the database with SQL Group By,
b) or in the report with a summary field for example.
It depends on how the data are transmitted to CR report. In my case, SQL queries are executed independently of the report, then the data from these queries are pushed into the report and subreports

For example, to know the total price of products in an invoice, I can choose :

solution a) report+subreport
SQL query 1 = invoice_no, sum (product_price) group by invoice_no -> main report
SQL query 2 = invoice_no, product_id -> subreport
and link report / subreport with invoice_no

solution b) only a main report + summary field
SQL query 1 = invoice_no, product_id, product_price -> main report
and summarize with summary field : sum(product_price, invoice_no) in GroupHearder Invoice

I think the solution b) is often faster (no joint report / subreport in CR report) when there is much data to link.

Was it the meaning of your comment ?

 

[lbass]

If you can return the summary directly to the report as a "field" in your database based on an already executed SQL query, then you should. That was the whole point of suggesting a SQL expression or a command--to accomplish that very thing. But the conventional insertion of a summary on a detail record won't work, as you know, because you had repeating values in the detail section, which was why you were using running totals in the first place.

-LB

 

[Gus01]

This is a good summary.
Thank you for helping me in my understanding, at the beginning when I asked the question, these functions were not so obvious to me.