SubReport

[brxmas]

Crystal 9.0
Connection ODBC

Hopefully someone can help me....Thanks in advance. I have two questions.

1) I have a main report which is grouped by the following:

Sales Rep#
Account #
@category (which includes the following):

IF {mc_ad_cost_view.obj_code} in ["15", "20", "50"] THEN
"Adjustments"

ELSE

IF {mc_ad_cost_view.obj_code} = "200" THEN
"Color"
ELSE

IF {mc_ad_cost_view.obj_code} = "270" THEN
"We Print Color"
ELSE

IF {mc_ad_cost_view.obj_code} = "485" THEN
"Booth"
ELSE

IF {mc_ad_cost_view.obj_code} = "900" THEN
"Internet Up Sell"
ELSE

IF {mc_ad_insertion.prod_code}= "MBDY" THEN
"Liners"
ELSE

IF {mc_ad_insertion.prod_code}= "MBMD" THEN
"Display"
ELSE

IF {mc_ad_insertion.prod_code}= "MBEX" and
dayofweek({mc_ad_insertion.insertion_date})=4 THEN
"MVP (Wed) Liners"

The main report shows the SalesRep #, Account, Revenue for each category for the Current Year and Last Year

Sales Rep#
Account Name & Account #
@category

Example:

Sales Rep# 001
GM Plan Works --- 8001352
CY $ LY $
Adjustments 542 447
Color 17 5
Liners 3 1
Booth 25 13

At the end of each Sales Rep I want the total of each
@category, see below:

SalesRep #001
Adjustments 942 857
Color 45 52
Liners 22 14
Booth 124 173

At the present time I have a subreport, which takes a while to process which gives me all this information. But, I would like to avoid using a subreport is possible. I've tried creating another group, but it doesn't work.

2) After I schedule this report to run and save as a .pdf file is there a way to move each SalesRep# to their specific folder (Rep001, Rep003, etc.) So, Reps can only access their folder???? There is a page break after each SalesRep#.

Any help would be greatly appreciated.

 

[lbass]

I think you can insert a crosstab into the sales rep group footer where you use {@category} for your row field, and {table.date} as your column field. After adding the date as a column, and while it is highlighted, go into the group options and choose "print on change of year". As long as you have the date range for current year and last year in your record selection formula, this should work. Then add sum of revenue as the summary field.

I can't answer your second question, but I'm sure someone else can.

-LB

 

[brxmas]

ok, that works....But, now I have Inches for CY and LY, so I need to show those columns on the Crystal Report as well. It puts 2004 2005. Is there anyway I can modify the Column headings?


$CY $LY Inches CY Inches LY
Adjustments
Color

I really appreciate your help. I never thought of inserting a crosstab.

 

[lbass]

For this display, I think it would be simplest if you added a second crosstab, using year as the column field, inches as the summary and category as the row field. For each crosstab you could create a column label:

//{@Revenue}:
whilereadingrecords;
"Revenue"

//{@Inches}:
whilereadingrecords;
"Inches"

Add each of these to their respective crosstabs as another column field and then drag each into the topmost column position while in the crosstab expert. Then go to the customize style tab, select the label formula, and check "suppress subtotals".

Finally, you will want to suppress the row labels in the Inches crosstab and align it (overlap it) with the first crosstab. If you don't need the gridlines, go to the customize style tab->format gridlines->and uncheck "show gridlines".

-LB

 

[brxmas]

LB, thanks using crosstab reports instead of subreports works perfect.

Thanks.

BC