complicated crosstab situation

[KelleyO]

I am using Crystal 8.0 which connects to my db via ODBC. I have a field with multiple values separated by commas. What I want to end up with is a cross tab.

data:
record1: product1, product2, product3
record2: product3, product4, product5, product6
record3: product1, product6
record4: product7
record5: product5, product2

5 records
7 distinct products
12 products total

my desired result:

product1 2
product2 2
product3 2
product4 1
product5 2
product6 2
product7 1

total 12

I've used the split function to get the count of 12 products in another part of the report. I also have local variables for each of the 7 products.

In the crosstab row formula, I have if-then-else statements set up as follows:

if varproduct1=1 then "product1" else
if varproduct2=1 then "product2" else
if varproduct3=1 then "product3" else
if varproduct4=1 then "product4" else
if varproduct5=1 then "product5" else
if varproduct6=1 then "product6" else
if varproduct7=1 then "product7" else
"unknownproduct"

what I'm actually getting:

product1 2
product2
product3 1
product4
product5 1
product6
product7 1
unknownproduct 7

total 12

This is my problem. When Crystal finds product1 in the record, it then goes to the end. It doesn't CONTINUE reading the record to see if that record satisfies any of the other ifs. I know, that's how if-then-else statements work.

How can I get Crystal to read the record multiple times so that it will count all 12 of the products and put them in the correct line of the cross tab? I have a feeling an array is involved somewhere, but I don’t know exactly how. Can anyone help me?

 

[kenhamady]

Cross-tabs won't work here unless you can get the data to be one record per product. Because a cross-tab can't count one record in more than one cell.

If you have a finite number of products you could write a conditional formula for each product. Seven formulas like this:

if Product1 in {field}
then 1
else 0

And then 7 totals, one for each formula.
And then a grand total that adds them all up.

However, if you have an unlimited number of products this won't be practical.

You could (theoreticaly) build 2 arrays, one to track the products as they are encountered, and another to run totals of each product. This would require a pretty sophisticated use of Crystal variables, and would be more than I could describe in TT postings. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[KelleyO]

Ken,

Thanks for the lightning fast response! I do have a finite number of products (about 50) and I've already done the first thing you suggested and it works fairly well. The reason I wanted a cross tab was so that I could break out the product requests into calendar quarters across the top and products down the side.

Like I said, I had a feeling that arrays would be involved. While this IS a very complex situation, I think it's worth doing. If it's not appropriate to this forum, let me know. Thanks.

 

[kenhamady]

Kelley,

Even with arrays you won't get to use Cross-tabs. There is NO way to get cross-tabs to include a record in more than one cell. It is a shame that they put multiple products in one record because that greatly limits your ability to process the data.

You could create 4 formulas per product (instead of 1), one for each quarter. And then total each. Or you could create 4 running totals for each product.

Or you could do an array, but you would still need 200 formulas to display the result of the array. The advantage of the array is that it doesn't require you to hard-code the products. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HowardHammerman]

Ken is right.
You will have to re-organize your data outside of Crystal.
You can write a fairly simple FoxPro, Dbase, or VB program
(my favorites) to do that.

Check out Data Junction. This is a user-friendly data manipulation program that lets you create macros to do many things.

Personally, I would create a foxpro program that read in the ASCII file into one database with one large text field then process that table through a DO loop looking for the
commas and writing records for each product.

Once you have a table with one record per product along with info about the product, the Crystal report is easy. Crystal Reports training, consulting, books, training material, software, and on-site support. Scheduled training in 8 cities.

http://www.hammerman.com

800-783-2269

 

[it1234]

Ken and Howard are right. There is NO WAY to use the cross tab in this case. If you still prefer to use the cross tab to get the advantages of it, the best way is to write a stored procedure to return one row for each product and count and use it in the report istead of reading directly from the table.

 

[KelleyO]

Thanks for all the responses, even though they weren't what I had wanted to hear.