Question about crosstabs

Hi,

I have a couple of questions about crosstabs:

1.) I have a data cube which brings the details about the types of cars and quantity of cars that were sold from a factory (say I get 20 types of cars, month sold, the total amount and total quantity for each car per month in my data cube). Then I filter (top 10) cars using the crosstabs filters and display them in my report. Now I want the summation (of prices and quantities) of all the remaining 10 cars and display them as the 11th row in my report. How can this be done?

2.) Suppose I have two groups displayed in the first column of my crosstab, now I want to include headers for each group so that it is just displayed once on the first row. How can this be done? (rownum etc. seems to be not working for crosstabs)

Thanks,

Find more posts tagged with

Comments

mwilliams

Hi anir,

For #1, you might be able to turn on the grand total row and delete the aggregations within it and put in your own aggregations to display what you're wanting in this row.

For #2, Can you explain a little more of what you're talking about? Are you talking about headers for the row dimensions? If so, you could put the headers in a grid in the designated row header cell in the top left of the crosstab. If you mean something else, let me know.

anir

Ford 2 2 4 8

Jaguar 1 2 3 6

Porche 1 1 3 5

Others 4 4 6 14

Grand total 8 9 16 33

Where 'Others' should be calculated in the crosstab logic and not in database logic. For displaying the top 3 i am using crosstab filters and want to group all the rest into 'Others'.

2.) Do we have something as row.__rownum with which I can check the rownum of crosstab? If I can do that, then I want to place a label just on top of 'Ford' and set its visibitlity to false for rows other than the first row. I understand that I can do that by placing a grid in the row heading area. But in my actual report I have multiple groups in the rows and the alignment of the headings is becoming an issue if I am placing them in the row header area in a grid.

Hope I am somewhat clear this time

Thanks,

mwilliams

I'll have to think about #1 some more to see if I can think of a way to do that. For #2, you should be able to create your own variable in the initialize script set to 0. Then, in the visibility area for your label you're wanting to place, you should be able to check against this variable. If it's 0, increment and show the variable. If it's greater than 0, then hide it. Hopefully that solves your second question. Let me know.

anir

Hi Michael,

Solution for #2 seems good. Also have another question :

3.) i want to highlight alternate rows with different colours. How can this be done?

Thanks,

mwilliams

anir, 
 
One way to get every other row highlighted in a crosstab can be seen in this devShare post. I think there are multiple ways in script to achieve this as well, but this should help. Let me know if you have any questions. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/825-crosstab-highlight-using-script/#description'>Crosstab highlight using script - Designs & Code - BIRT Exchange</a>

anir

Much helpful. I am still held up by #1

Thanks,

mwilliams

anir, 
 
For #1, you're going to have to figure out a way to make a new column in your dataSet that has a value of the vehicle manufacturer for the top 3 and a value of "other" for all other manufacturers. I don't think there's any way to do this otherwise, since you can't use a table as a source for a cube and there isn't a top N & others option for a crosstab. This is something you could request as an enhancement for future versions. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/bug-reporting/'>Report Bugs - BIRT Exchange</a>