Alias rows in a crosstab

[oneilltnc]

I'm using CR 9 with SQL Server 2000. I need to build a benchmark report that will be sent to a client and will display the total number of calls they took for the past 12 months in comparision with other clients we have. The only problem is that we cannot release the names of the other clients, we can only show them as Company A, Company B,etc. So my cross tab ends up looking something like...

Company 1 25 8 11
Company 2 47 33 22
Company 3 17 21 55

I thought about just suppressing the company field and putting a text field in place of it, but the number of rows can vary. Has anyone ever had to do something similiar this?

 

[synapsevampire]

You might create a reference table on the database to use as a lookup table, something like:

Company Alias
IBM Company1
HP Company2

And join by the company.

If the client isn't going to use the actual Crystal report, then you can build a formula to do the same:

If {table.company} = "IBM" then
"Company 1"
else
If {table.company} = "HP" then
"Company 2"

Another cheat might be to use the company ID if you have one as that shouldn't mean anything to them.

And finally, you might create some code to alter the name, such as adding some value to each characters ASCII equivalence.

-k