Crosstab, SubReport or Other Approach?

[darran6]

Hi,

I'm using CR XI Developer.

I'm producing reports for a sales meeting next week and need to achieve the following:

This report is to show top 10 product lines per sales rep along with number of units sold.

The sales manager wants a table looking like:

Rep Name 1 Rep Name 2 Rep Name 3 etc

Line 1 Line 1 Line 1
Units 1 Units 1 Units 1

Line 2 Line 2 Line 2
Units 2 Units 2 Units 2

etc
etc
Other Ln Other Ln Other Ln
Other U Other U Other U

Totals...

The problem I have is that Line 1 for Rep 1 is probably not the same as Line 1 for Rep 2 - if I use a cross-tab then the Lines would be the same for all reps which is not what the manager wants.

I also tried producing a cross-tab to use as a subreport limiting sales to a single rep and then inserting a subreport in a parent report for each rep, but Crystal doesn't let me specify different parameters for each subreport.

Each line is made up of many products which are grouped by Crystal.

Any help/suggestions would be greatly appreciated...

Darran

 

[IdoMillet]

CrossTabs support TopN sorting (just click in the top-left empty cell to select the CrossTab and go to Report, TopN Sort Group Expert).

- Ido

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

http://www.MilletSoftware.com

 

[darran6]

Ido,

I already know about the Top N group sort and have used it in the past.

The problem I have is showing different Product Lines for each sales rep - a standard cross tab will give me the same lines for each rep which is not what the manager wants.

Darran

 

[IdoMillet]

Sounds like you need a side-by-side subreports.

- Ido

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

http://www.MilletSoftware.com

 

[Madawc]

Use a formula field - Rep & Line. Crosstab on that. This would work in 10 and so should be fine for you.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[darran6]

Sorry Madawc, but I don't think I fully understand.

I created a formula combining rep and line, but can't see how I can use that to get a cross tab grid as per the above.

What exactly are you intending to be in the row and column sections (and I assume sum of units in the summary)?

Darran

 

[darran6]

Ido,

The problem with that is each sub-report would have to be maintained individually and set to produce data for one rep at a time - I have done a similar thing with placing cross-tabs side by side for a similar effect but then I only had 4 columns of data to product whereas in this instance I have 15!

I was after a more maintainable, elegant solution!

Cheers,
Darran

 

[IdoMillet]

You can address the same need by using a TopN Chart showing 10 horizontal bars for each Sales Rep.

- Ido

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

http://www.MilletSoftware.com

 

[lbass]

How many reps are you working with?

-LB

 

[darran6]

LB,

I've checked with the sales manager and there will be 9 reps at the meeting.

 

[darran6]

Ido,

I hadn't thought of using a graph, but unfortunately this would take up too much page space - the sales manager definitely wants a summary table so all info can be seen at a glance.

 

[IdoMillet]

A horizontal (not vertical!) bars chart would serve your purpose and would fit into a single page. By controling bar colors, it would also be easier to see that Product A was 1st under one rep and 3rd under another.

- Ido

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

http://www.MilletSoftware.com

 

[darran6]

Ido,

I've played around with this but can't see how I can possibly show the data in any meaningful fashion.

I tried a horizontal bar chart (standard as well as stacked types) and showed sum of units sold on change of rep and product line, with the top 10 group sort on product line.

It didn't help that there are over 27 different product lines shown on the report, nor that the sales manager's figures were substantially higher than the other reps due to some large manufacturer accounts she has.

I've ended up using multiple cross tabs - formatted one cross tab using specified ordering to pick one rep at a time and display just one column. Then copied, pasted, aligned and changed the rep to produce a perfect looking table (I just hope I don't need to change this report too often).

Thanks for your help,
Darran

 

[lbass]

Why not insert a separate crosstab for each sales rep in the report footer, where you use the product as the row field and then use a group sort on each which is set to top10.

-LB

 

[lbass]

To do the separate crosstabs, create a formula like the following for each sales rep:

if {table.salesrep} = 1 then {table.sales}

Use the formula as your summary field, and use the products as your row field, with no column field. Then do a topN sort on the crosstab. You would need to manually change the "Total" label to "Sales Rep #1". Repeat for each salesrep.

-LB

 

[darran6]

LB,

I hadn't thought of doing it that way - not sure which involves less work.

I used product as the row field, rep as the column with the specified ordering as above (after applying a topN). I created a function to show product code and name and summarised on this (nth most frequent : n=1) and unit sales.

A couple of text boxes on top of the crosstabs finishes off the table look and feel. I've put an edited version of the report at:

http://www.smithcorner.co.uk/report.jpg

Thanks for all the suggestions,
Darran