Need help summarizing data in report header

[Former Member]

Hi all,

I am only a basic user of Crystal Reports and I need to do something that I do not know how to do. Any help would be appreciated.

I get my data from a single stored procedure. The data consists of a row for each order. An invoice can contain multiple orders. Some of the fields in the data are Order number, Invoice number, Invoice Date, Order Gross, Order Net, and Credit.

My report shows details for each order grouped by Customer then invoice number.

What I want to do is to place some summary information in the report header so that an overview is available up front. I wish to place a grid with a row for each invoice and columns containing the sum of the gross charges, net charges, credits, and a calculated value of the margin for that invoice.

I looked at using a cross tab, which I know nothing about, but it didn't look like I would be able to use that.

Can someone please offer some suggestions on how I might accomplish this?

Thanks,

David

[Former Member · Accepted solution]

To summarize data in the header, I have used the following formula, it uses WhilePrintingRecords; This means the data will be printed after all calculations are done.

In your case, you will have to create a formula, called something like Summary_init

Global NumberVar Array InvoiceTotal := 0;
Global NumberVar countInvoiceNumber := 0;

In your group fouter, based on the invoices, create a formula called Summary_line

Global NumberVar Array InvoiceTotal;
Global NumberVar countInvoiceNumber;

// Formula for adding statistics number to array
(
    countInvoiceNumber:= countInvoiceNumber + 1;
    Redim Preserve InvoiceTotal[countInvoiceNumber];
    InvoiceTotal[countInvoiceNumber] := SUM({field1.amount},{Group.invoice_number});
)

Place the following formula in your report header

WhilePrintingRecords;

Global NumberVar Array InvoiceTotal;
Global NumberVar countInvoiceNumber;

Local StringVar TotalHeader := "";
Local NumberVar i;

For i := 1 To InvoiceTotal Step 1 Do

(

   TotalHeader := TotalHeader + "Invoice" + ToText(countInvoiceNumber,0,"") + " = " + ToText(InvoiceTotal<i>,2) + chrw(13)

);

TotalHeader 

Good luck with it, I use it to read out lines for specific codes that need to be printed in the header, you can also modify these formula's to work with strings/dates etc.

With kind regards,

Cyrus

[Former Member]

Under the "Insert" menu, select "Summary".

Select the field you wish to aggregate. Next, select "Sum". Unless you're using groups, the only option is to place the summary field in the "Report Footer".

Once the summary field is placed in the Report Footer, simply move it to the top of your document.

[Former Member]

Jeff, thank you for responding.

I need to clarify something here. The summary information that I want to include at the top of the report is not summary information for the whole report. It is basically a summary of the Group 2 data. The report is grouped by customer then by invoice. I need to list data for each invoice in the summary. It is basically a replication of the data that is included in each of the Group 2 footers.

I do this frequently in other reports using a chart. That is displaying summary data for groups. However, the data I need to display this time does not lend itself to a chart format.

I know I could accomplish this with a sub report but since I already have all the data it seems a waste to have a sub report need to re-read it all.

Any other ideas would be greatly appreciated.

[Former Member]

Still follow the instructions above. Since you are using groups, the Insert Summary will have a dropdown that allows you to put the summary in the group footer. Put it there, then drag it to the group header.

HTH,

Carl

[Former Member]

Ok, I think I know what you're trying to do. Personally, I'd go with your plan of a subreport. I know it sounds redundant, but it's the quickest and easiest method.

Another alternative would be to move the summary to the group header and hide the details. This will give an initial summary view with the ability to drill into the Group 2 data.

[Former Member]

If this is a report that's gonna be run a lot, I'd stay away from the subreport. They are S-L-O-W. Summaries in headers are no problem (once you know how to do it).

HTH,

Carl

[Former Member]

Since you want to summarize data based on a group but locate it in the report header, you should create a formula with the group-based summary function in it. Then put the formula into the RH section.

Fuskie

Who believes you can only directly insert a group summary into a group header/footer...

[Former Member]

They are S-L-O-W

Generally. But here it will be cached. Should be OK.

I think the objective is to iterate the group totals on the report header. Pretty sure a sub report's the best bet on that one if you need to see the details at the same time. If you don't need the details, moving the summary to the group header and hiding the details with drill down works too.

As for Fuskie's suggestion, it will net the same result as a total in the report footer (only in the report header). Again, it won't iterate the group totals.

[Former Member]

Thank you everyone for your responses.

I was able to accomplish what I needed using a sub report. However, I was not able to accomplish it otherwise. If a summation field, or a formula field is created, when it is placed in the report header section it results in a single value being placed in the report. What I need is to have the summary information for each group 2 appear in the report header. Something like this:

RH

Invoice 1 total is $546.54

Invoice 2 total is $645.43

...

G1 Header - Customer A

G2 Header - Invoice 1

Detail Transaction 1 total $200.00

Detail Transaction 2 total $346.54

G2 Footer - Total for Invoice 1 is $546.54

G2 Header - Invoice 2

Detail Transaction 3 total $150.00

Detail Transaction 4 total $50.00

Detail Transaction 5 total $445.43

G2 Footer - Total for Invoice 2 is $645.43

G1 Footer - Total for Customer A is $ 1191.97

...

As I said, I was able to do this using a sub report. If there is another way though, I would be very interested in learning it.

Thanks again everyone.

David

[Former Member]

Jeff, you sure you can't say something like

@forumula
sum(item, GroupName)

and put it in the report header? Even if you put WhileReadingRecords in it?

Fuskie

Who admittedly has not tried this and based on OP's latest post it wouldn't have addressed his requirements...

[Former Member]

Actually, since you only need to show invoice number and total dollars, a cross tab would work here quite nicely, I think... Put the invoice number in the Rows, and the amount in the Summarized Fields in the Group Expert. Enable row totals (if not already enabled).

HTH,

Carl

[Former Member]

I like that. OP would have to do some formatting work to remove all the lines and tighten up the spacing but this could work.

Fuskie

Who is an old dog who likes learning new tricks...

[Former Member]

Well, back to the cross tab. This is something that I will have to experiment with as I have never been exposed to them other than the little time I took to look at them before starting this thread.

As far as only needing the invoice totals, well I need a few more fields but they are simply sums of other columns so that would be no more difficult than the invoice total. As I stated in my original post, I will need to include one field that is calculated from two of the other summed fields. I'm not sure if that will pose any additional problems or not.

I will play with this and will post my results if I am successful. If anyone has any suggested resources on cross tabs please post links to them. I'm always happy to learn something new.

Thank you all for your help.

David