crosstab stumper for days, fake empty columns

[mwhalen]

I need to achieve the following:
***********************************************************
AA_Item New Retro BB_Item New Retro
mbr 1 50 13
mbr 2 75 80
mbr 3 43 22

***********************************************************

New and Retro aren't fields. They are blank columns on my report that I will export to excel, send to a client and the client will fill in the new $ and then the retro $. There is no set calculation to these numbers that I can derive from a formula. Each mbr could have any dynamic number of item columns whether it be an AA and BB or unlimited. This is why I'm thinking I need a crosstab.
HOWEVER, I do I display 2 fake dummy columns side by side for each item?

ANY direction you can give me would be MOST helpful as this is driving me crazy!

 

[chelseatech]

Use a conditional formula for each column and then summarys of those formulas. You can easily leave a gap for those columns you want the user to fill in.

This is sometimes called the "Manual Crosstab" technique, so do a Tek-Tips search on that to see more details

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

What version are you using? Assuming you have a version that allows horizontal displays of summaries, you could add two additional summaries and then suppress them. I think this would maintain the spaces, but cannot test it right now.

-LB

 

[mwhalen]

thank you for the suggestion to do a search on manual crosstabs. When I say I have been stumped for days, it also includes days of searching on this site. From the comments I see about manual crosstabs, it looks as though those are only good when you have a fixed number of variables in your columns. Each member may have 2 item types or they may have 20. I need to display all item types with a column called New and a column called Retro after each item type.

I'm using version 10. If I play with the crosstab long enough I can get 2 columns after each item type, HOWEVER, the column headings do not appear on the same line side by side. This has to be exported to Excel.

Thanks so far for looking into this and I look forward to continued feedback!

 

[lbass]

I'm fairly certain a horizontal display is possible in 10.0. You might want to check in the customize style tab, or I'll take a look tomorrow to see where it is in 11.0.

-LB

 

[chelseatech]

Version 10 has horizontal placement, so add some extra columns and labels, and use the suggestion above with suppressed field.s

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[mwhalen]

Okay, I got it to display horizontally the multiple items. THANK YOU THAT WAS AWESOME!
Now I have other non multiple fields that I also want to show beside "mbr" such as name and addess. How do I achieve:

Key Name Address AA_Item BB_Item
mbr 1 bob CAN 50 20
mbr 2 ted USA 75 30
mbr 3 sue GBR 43 22

 

[lbass]

I would concatenate the three fields and use that formula as the row field instead of the member number, as in:

{table.key}+" "+{table.name}+" "+{table.address}

Then you will have to stretch the row label to accommodate the longest instance.

-LB

 

[mwhalen]

I had thought of that solution but how can I possibly know the longest field on 50,000 records? Also, I can see myself running into some problems in a length limit on a text field.

I'm willing to give the manual crosstab a try but can you confirm that is only good for a fixed number of columns?

 

[lbass]

If you right click on your strings, you will see the character limit for each field. You could also create a formula:

len({@concatenatedfields})

and then insert a maximum on it to see what the maximum length is. Or eyeball it. You can drag the label field down so that it wraps, too.

The issue with a manual crosstab is that you have to create a formula for each column value, so it's a matter of how many you are willing to write. Also, an inserted crosstab will allow for virtual pages off to the right, but you can't do that with a manual crosstab.

If you use the concatenation, which I think makes the most sense, it is possible to also align the elements of the formula, as long as you are willing to use a non-proportional font--if that is a factor in your decision.

-LB

 

[mwhalen]

thanks. This is a report that needs to be maintenance free as it will be posted on the web for our clients to log in and use. We have new clients added to our database all the time so I have about 4 fields I need to add to the mbr name. I would be hesitant to use the concatenation.

 

[lbass]

Actually, on second thought, if these three fields have only one value per mbr, then you should be able to add them as additional rows. Then go to the customize style tab in the crosstab expert and select each field and check "suppress subtotals."

This should work--sorry for not realizing it earlier.

-LB

 

[mwhalen]

I don't have my suppress subtotals option - it's greyed out. Just to clarify along my row I need the following to show:

mbr number, name, address, social security number

Mbr number obviously is my unique field. In playing around with it, if I group on mbr number then the suppress subtotals box is enabled but still greyed out for the other ones.

 

[lbass]

You have to add mbr number, name, address, social security number as separate row fields in that order. In the customize style tab, first highlight mbr number and check "suppress subtotals". Then highlight name->suppress subtotals, and then repeat for address. Only the last row should have suppress subtotals grayed out.

-LB

 

[mwhalen]

Ok, I'm really close - that worked - I can't express how INVALUABLE your help has been! Thank you so much!

Now I just have to add fake column headings for mbr number, name, address, and social security number above my row so they line up with the real column headings. I'll give it a try and test exporting it to excel.

Thank you again! It's a good exercise in that this type of thing is how you learn. Have an awesome day!