CrossTab: Static columns and rows

I have the following problem:

I search in a database for 3 different types of information about 3 genes (gene1, gene2, gene3) and count their occurences.
I want to generate a CrossTab and want to keep always all rows (genes, e.g. gene1, gene2, gene3) and columns (e.g. info1, info2, info3) from my database search.

xxxx|info1|info2|info3
gene1|100|2|0
gene2|0|0|0
gene3|10|20|0

At the moment only that columns (or rows) are displayed that have at least one cell (summary field) that has a count greater than 0.

xxxx|info1|info2
gene1|100|2
gene3|10|20

How can I enforce the display of all columns (info) and rows (genes) from my search whether the cells have values greater than 0 or not?

Find more posts tagged with

Comments

mwilliams

Create a scripted dataSet or flat file dataSet that has all the dimensions you want to include and join your returned data with this dataSet. If you need an example, show me what your data looks like in your dataSet and let me know your BIRT version.

Dulli

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80318" data-time="1311177155" data-date="20 July 2011 - 08:52 AM">
Create a scripted dataSet or flat file dataSet that has all the dimensions you want to include and join your returned data with this dataSet. If you need an example, show me what your data looks like in your dataSet and let me know your BIRT version. </blockquote>
 
I use Eclipse 3.6.1/BIRT 2.6. I am not quite sure what is meant by JOIN. So I can provide an example. 
 
The columns are always the same. The Dataset SYSTEMS looks like: 
System1 
System2 
System3 
System4 
 
The rows dependent on the search. Lets say I search for the genes ABC, DEF, GHI and XYZ. I can generate a dataset GENE like: 
ABC 
DEF 
GHI 
XYZ 
 
My SQL statement returns a dataset FOUND_ENTRIES like: 
ABC;System1;2.1 
ABC;System1;2.3 
ABC;System1;3.4 
ABC;System2;1.9 
ABC;System3;2.1 
DEF;System1;10.5 
DEF;System1;2.5 
DEF;System3;3.4 
DEF;System3;1.9 
DEF;System3;2.1 
GHI;System2;10.5 
GHI;System3;2.5 
GHI;System3;3.4 
GHI;System3;2.1 
 
The value in the 3rd column does not matter. I only want to count the occurences, not the sum of column 3, i.e. COUNT for the aggregation cell. 
In this example there is no hit for System4 and Gene XYZ, but I want to display the whole Crosstab - with all genes and systems and a "zero" in the aggregation cell when there are no found entries. 
 
Thanks a lot !

mwilliams

Take a look at this example. I use two dataSets that outline the possible genes and possible systems. I then join the actual data with these two dataSets and use computed columns to "mesh" the data together. I also gave the original data a new value of 1 and the added rows a value of 0. This way the measure can give a correct number by using the sum. Using the count would have been off since the new rows were added. Let me know if you have questions.

Dulli

Thank you for this solution. The idea with the two FULL OUTER JOINs/Computed Columns and the use of SUM instead of COUNT is exactly the solution I can use for several reports I have created.

mwilliams

Glad to help. There are other ways to do this as well, so if you run into issues with this, let me know. Otherwise, let us know whenever you have questions!