To Hide Null Column (Heading) in CrossTab

ATirumalas · 2011-09-15T06:51:26+00:00

Hi, I am facing a problem with cross-tab display. Step 1) Say My Data Table is as follows: create table nulltest ( XAxis varchar(10), GroupName varchar(10), Total int(10) ); Containing the following Data: Insert into nulltest(XAxis,…

ATirumalas

Hi,

I am facing a problem with cross-tab display.

Step 1)
Say My Data Table is as follows:
create table nulltest
(
XAxis varchar(10),
GroupName varchar(10),
Total int(10)
);

Containing the following Data:
Insert into nulltest(XAxis, GroupName, Total) values
('XA','A', 40 ),
('XA', 'B', 50),
('XA', 'C', 10),
('XB', 'A', 20),
('XB', 'B', 60),
('XB', 'C', 40),
('XC', NULL, NULL);

ScreenShort: Data_SS.png

2) I have to create a crosstab with XAxis (XA/XB/XC) as row-headers, GroupName as column-headers and Total as actual values in the cross tab.
So in the Report Design, I create a cross tab Grouping XAxis & GroupName, and Total as Summary Field.

3) I create a Crosstab, since The XAxis contains XC with corresponding NULL values an additional collumn with NULL is created. I here presume that BIRT cross tab does not consider NULL as a missing value, but a value by itself and hence displays it in a new collumn with header name as NULL.
Please look at the ReportOutput_SS.png screenshot attached for more info.

4) My aim is to remove/hide the column with NULL header. (P.S. The XC should be displayed in the crosstab)
In order to achieve this, I started a script with following code, but I am unable to find a way to hide this collumn. (Just coloured Yellow to spot the NULL collumn). Please help.

function onCreateCell( cellInst, reportContext )
{
if(cellInst.getCellType() == "header")
{
if(cellInst.getDataValue("GroupName") == "")
{
cellInst.getStyle().setBackgroundColor("#FFFF00");
}
}
}

File Attached: CrossTab_Script.txt

Find more posts tagged with

Comments

ATirumalas

mwilliams

It looks like your files did not get attached. Can you please re-attach them? Thanks!

mwilliams

Nevermind, I found that you posted a second time with the files. The easiest way to get rid of this is to assign a value of A, B, or C to the empty group in a computed column and use the computed column in the crosstab. This will get rid of the empty column.

ATirumalas

Thanks for your response.
Unfortunately, our requirement is something different and does not satisfy the above solution.

The data which I have provided is the way in which the data is returned from different Tables. The XAxis value is fetched from one table and calculations are done on other tables. When there are no records in other tables for the corresponding XAxis value, it will return NULL for GroupName & Total columns.

The reason for not able to assign GroupNames to "XC" row is because, it makes it a compulsion to provide all available GroupNames to all the XAxis Values, which is not required. For instance there is a GroupName "D" but none of the current required XAxis values have corresponding records to "D", then the whole column in the crosstab will be displayed as NULL, which is a requirement that "When no corresponding values are present for the whole column, then the whole column should not be displayed". Hence instead of XC-A, XC-B, XC-C, XC-D combinations being returned, we want only one row with XC as XAxis and NULL for rest.

Or Alternatively, how to hide the whole column when there are no data values present for all rows assuming that we consider all combinations of available data (12 ROWS)
XA-A, XA-B, XA-C, XA-D
XB-A, XB-B, XB-C, XB-D
XC-A, XC-B, XC-C, XC-D

Actual dataset:
XAxis, GroupName, Total
('XA','A', 40 ),
('XA', 'B', 50),
('XA', 'C', 10),
('XB', 'A', 20),
('XB', 'B', 60),
('XB', 'C', 40),
('XC', NULL, NULL);

mwilliams

What is your BIRT version?

ATirumalas

2.6.2

mwilliams

With your data from above, this can be done as I described earlier. See the attached design. The problem you run into here is if the first row of your dataSet has a null value for the group. In this situation, you wouldn't have a value that's in the dataSet to replace it with stored in your variable.

In the example, a variable is assigned a value from the GroupName field if it's not null. This value is then used in a computed column to fill the null values. The total field is given a value of 0 to not change the sums in the crosstab. Creating a scripted dataSet from the original dataSet's data would be one way to solve the issue in the case where the first line of your dataSet has a null group value.

Hope this helps.

ATirumalas

Hi,
Many Thanks for the response.

I have done something similar in my work-around implementation.

Instead of implementing in the tool, I modified the data in the similar way by replacing the NULL values with one of the existing non-null values of GroupName.
Many Thanks once again for your response.

Cheers!!

ATirumalas

The reason I was not happy with that implementation being that, I was expecting the tool to handle NULL values and not to display them. But in this case the tool is considering "NULL" as a value by itself. I also got a TED raised #43950. Currently it speaks only about Chart Legend containing no name with colour box for such NULL data, I requested them to add cross-tab issue to the TED as well.

Cheers!!

mwilliams

Thanks for the update!