How to ReCap Product codes

[SQLLady]

I am a beginning user of Crystal Reports X . I want to add a page to a report Titled 'RE-CAP' and group all the product codes and amounts( in ascending code order) in a summary fashion ie.

Code Amount
30 $1,011.82
32 45.68

How do I group the codes and dollar amounts? thanks in advance.

 

[synapsevampire]

Try selecting Insert->Crosstab and place it in the report footer.

In the Rows place the Code, and in the summarized field the field you want summed (or whatever aggregate function, you can use Change Summary to change which aggregate function to use).

You may need to right click the Cross-Tab and select Cross-Tab Expert to do this.

-k

 

[SQLLady]

crosstabs do not work for me in this case.

 

[synapsevampire]

Since you stated that you want to add a page, I inferred that this meant you want a summary at the end of the report, but I guess I should bite the bullet adn just suggest that you state basic technical information, including explicitly stating what and where you need it:

Database connectivity used
Example data
Expected output
Where is the summary to be provided

I'm also curious why a cross-tab in the report footer would work, it will supply exactly what you requested.

-k

 

[SQLLady]

Sorry , as I stated before the Manager wants a RECAP in the following format:

product codes and amounts( in ascending code order) in a summary fashion i. e.

Code Amount
---- ------
30 $1,011.82
32 45.56 etc
The first part was done by the previous DBA, I only have to add the RECAP Sheet.

Thanks

 

[synapsevampire]

I guess you prefer not to answer my questions, hope that works out for you. I ask for example data, and wher eyou want to do so, and you paste in your expected output and ignore other questions.

Not to worry, there are many knowledgable, patient sorts around here that will probably coax it out of you.

-k

 

[lbass]

Did you try inserting the crosstab as SV suggested? If so, why didn't it work for you? What results did you get? Without further info, I agree with him that this would be the simplest solution.

-LB

 

[SQLLady]

Here are the answers to your questions:

Database connectivity used - ODBC, connecting to a stored procedure which pulls the data into a temporary table via product codes.

examples - product codes, dollar amounts by Audit #
I do not have a copy of the report here at home to scan it in for you.

The expected output is what I stated before. Its a "summary" of all the product codes and there dollar amounts that total the report $21,000.
The summary is to be provided at the end of the report - last page.

 

[lbass]

Try inserting the crosstab in the report footer and then report back with your results.

-LB

 

[IdoMillet]

I don't see why a Crosstab can't work for you but, as an alternative, you can insert an unlinked subreport that simply groups by code, and shows the total. Use the Report, Group Sort Expert... menu to sort the subreport groups by their totals.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[SQLLady]

Thanks IBass & IdoMillet for responding to my post. The cross-tab did not work for me becuase I got too many rows and I do not know how to just get 2 columns of data.

The subreport will work If you could tell me know to eliminate each individual product code and dollar amount.I just need:

Produce code Amount
------------ ---------
30 $1011.21
32 45.68 etc.

I want to group all the dollar amounts for Product code 30 and the total amount equal to $1011.21 etc.

thanks in advance.

 

[lbass]

Please try the crosstab again. Use Product code as the rowfield, and sum of amount as the summary.

-LB

 

[SQLLady]

I recreated the crosstab, but I get the data for each product code spread across the page with the total amount column at the end. How do I get the crosstab with just 2 columns? Is there a way to edit a crosstab?

thanks in advance.

 

[lbass]

Put {table.productcode} in the ROW area (to the left), not the column area (at the top). Leave the column field blank.

-LB

 

[SQLLady]

is there a way to edit a crosstab in PREVIEW mode?

 

[lbass]

Depends upon what you are editing. To get back into the crosstab expert in order to change the row/column fields, right click on the upper left hand corner of the crosstab->crosstab expert.

-LB

 

[SQLLady]

IBass, you are the best. I will continue working with.

Do you know of any good Crystal Reports 10 training CDs? I need something to train myself(cannot afford instructor led classes).

Again, thanks a lot for your help.

 

[lbass]

The George Peck books are good, e.g, The Complete Reference: Crystal Reports X (not sure what the latest number is). Also, the Help files for CR are quite good. Reading posts and FAQs on this site has been very helpful to me, and also just plain practice and exploration. I test out a lot of my solutions using the Xtreme database that comes with CR.

-LB

 

[SQLLady]

My manager now wants the description after the code in the report example:

Product Code Amount

30 State Sales Tax $498.32
707 Special Function 685.43 etc

How do I get this info on the same line in a crosstab?

thanks in advance.

 

[lbass]

Add it as a second rowfield.

-LB