Complicated Manual Crosstab 1

[oanion]

I am trying to create a crosstab that will have a dyamic number of column names (Bank) and a dynamic number of rows (Client). These rows and columns will be generated depending on a parameter. For example, if a specific parameter is chosen, then the crosstab should look like the following. Please note, that the row names and bank names will change because it will dependant on the parameter.

CitiBank Household Metris ……
Rate | Rank Rate | Rank Rate | Rank
Client 1 10.5 | 1 11.5 | 1
Client 2 1.5 | 5 2.5 | 5
Client 3 09.5 | 2 10.5 | 2
Client 4 05.5 | 4 06.5 | 4
Client 5 08.5 | 3 04.5 | 3
Client…….

I know I need to create a manual crosstab, however, I don’t know where to begin.

Please help

 

[lbass]

The parameters don't really make it complicated. You would just set the record selection formula to:

{table.bank} = {?banks} and
{table.client} = {?clients}

Then use {table.bank} as your column field and {table.client} as your row field. The bigger issue is whether "rank" is some sort of calculated field. If it is already a field in your database, along with {table.rate}, then you should be able to use an inserted crosstab, although depending upon your CR version, you might not be able to get the horizontal display of summaries. If rank is based on a topN sort, then you would have to use a manual crosstab.

-LB

 

[oanion]

Thanks for your reponse Ibass. However, the solution you provided will not throughly assist me. Currently I'm using SQL 2000 and CR 10. I know I need to use a manual cross tab because the rate and rank are both calucations, not numbers coming directly from a database. If I use the crosstab wizard, Crystal automatically wants to summarize data. I do not want to do this. Any asstiance from any manual crosstab experts would be greatly appreaciated

 

[lbass]

Okay, first add the record selection formula I suggested in my first post. Then group on {table.client} and then create a series of formulas:

//{@col1}:
if ubound({?banks}) >= 1 then
if {table.bank} = {?bank}[1] then {@rate}

//{@col2}:
if ubound({?banks}) >= 2 then
if {table.bank} = {?bank}[2] then {@rate} //etc.

You would create similar formulas for {@rank}. Then you would insert summaries (probably a maximum) on each formula at the group level and then suppress the details.

To get the appropriate headings, create additional formulas:

//{@col2heading}:
if ubound({?banks}) >= 2 then
{?bank}[2]

Replace the column headings "ColN" with the corresponding formula.

Of course, this solution doesn't address how you are calculating rank, but you haven't indicated that you have any problems with doing that.

-LB

 

[oanion]

What if the number of column heading changes depending on the parameter? The number of columns will not always be the same, therefore I think I would need column formulas that will generate a infinite number of heading.

 

[lbass]

Are you planning on an infinite number of columns? You would create formulas per my suggestions above up to the maximum number of columns you expect (or some number slightly larger than the maximum). Because of the ubound clause, column formulas based on a number that exceeds the actual number of parameter results would not appear on the report. Once you have decided on a number of columns to create, you could also create a formula like:

if ubound({?bank}) > 15 then "Number of parameter selections exceed number of columns" else ""//assuming 15 columns

Place this as an alert in your report header.

-LB

 

[oanion]

Ibass. Thanks for your guidance. I've tried to use the forumulas you listed above and Crystal is looking for an array value with the ubound function. How should I go about setting up the array the formulas need to work?

 

[lbass]

It sounds like you didn't set up your {?bank} parameter to allow multiple values. If you go into the parameter setup screen and check that, then the formula should work.

-LB

 

[oanion]

Thanks Ibass. You're always right on the money