Cross-tab not working properly. 1

[zemp]

CR 10

Cross-tab placed in Report Header.

I have been trying to get the following data set into a cross-tab for display. The table structure is a follows.

Month Drug Dental Vision Fees Total
----- ---- ------ ------ ---- -----
09-2006 100.00 110.00 120.00 60.00 390.00
10-2006 110.00 120.00 130.00 60.00 420.00
...

I need to get the data to look like this (with YTD totals added on the right).

09-2006 10-2006 ... YTD
-----------------------------------------
Drug |100.00 110.00
Dental |110.00 120.00
Vision |120.00 130.00
Fees | 60.00 60.00
-----------------------------------------
Total |390.00 420.00

So far I have been unsuccessful. I can get the months in the columns and the other fields in the rows. However the grouping must be wrong because I get something similar to this.

09-2006 10-2006 ...
-----------------------------------------
Drug |100.00 0.00
|110.00 0.00
|120.00 0.00
| 60.00 0.00
-----------------------------------------
Dental |100.00 0.00
|110.00 0.00
|120.00 0.00
| 60.00 0.00
-----------------------------------------
Vision |100.00 0.00
|110.00 0.00
|120.00 0.00
| 60.00 0.00
-----------------------------------------
Fees |100.00 0.00
|110.00 0.00
|120.00 0.00
| 60.00 0.00
-----------------------------------------
Drug |0.00 110.00
|0.00 120.00
|0.00 130.00
|0.00 60.00
-----------------------------------------
Dental |0.00 110.00
|0.00 120.00
|0.00 130.00
|0.00 60.00
-----------------------------------------
...

Can anyone see what I am doing wrong? Can you point me in the right direction?

zemp

 

[lbass]

If you look at your original data, it is already displayed in crosstab format, as if there were a field maybe called type used as the column, and it sounds like if this were a crosstab, you'd want to pivot it. The problem is that there isn't such a field as type. To do the crosstab, you could use a command that used a union statement to put the health, vision, dental, and fees fields all in one field. Something like:

Select 'Dental' as type, table.`dental`, table.`month`, table.`amt`
From table
union all
Select 'Vision' as type, table.`vision`, table.`month`, table.`amt`
From table
union all
Select 'Drugs' as type, table.`drug`, table.`month`, table.`amt`
From table
union all
Select 'Fees' as type, table.`fees`, table.`month`, table.`amt`
From table

Then you can use {command.type} as your column field in a crosstab, with {command.month} as your row field, and {command.amt} as your summary field.

-LB

 

[zemp]

Thanks LB. I used your suggestion to basically change the data structure and I am about 98% there.

A couple of questions.

Can I individually format a row or cell within the crosstab?
Can I change the grid lines based on data in the row?

If so can anyone point me in the right direction?

zemp

 

[lbass]

I don't think there is a way to conditionally change the grid lines. You can manually remove selected lines in the customize style tab.

You can format rows and cells individually, but you'd have to be more specific about what you wanted to do. You can use the currentfieldvalue or gridrowcolumnvalue() functions to do this.

-LB

 

[zemp]

With formatting the rows. I have one row of data that is whole numbers (count of people) and the rest of the rows are dollar ammounts. I would like the dollar amounts formatted as currency and the numbers not. It is always the same row (row number 8) that is the numbers, rows 1-7 and 9 are currency.

I have removed the inside row lines, as requested by the customer, but I need a line between row 6 and 7 (row 7 contains totals data)

zemp

 

[lbass]

As far as the line, in CR XI, there is the option "Draw grand total line only"--not sure if this is available in CR 10. As far as the row formatting, you should be able to handle this simply by clicking on the $ sign in the toolbar or not, assuming these are all separate summaries. Otherwise, if somehow this is all the same summary, then, you could select the inner cell->format field->number->customize->currency symbol tab->enable currency symbol->x+2 and enter:

if gridrowcolumnvalue("table.rowfield") = "Row8Value" then
crNoCurrencySymbol else
crFixedCurrencySymbol

You would place your crosstab rowfield in the parens, replacing the curly brackets with quotes and identify the particular row 8 value in the quotes.

-LB