Remove rows or columns with no data

[PhilipFaure]

Hi

Id can’t seem to figure out how to remove rows with no data within a “Cross Tab” table.

In Reporting, in the “Outline” panel, I first loaded in a query into my data sets. I then created a “Data Cube” to import this data query. I added a “Cross Tab” to the report and dropped the “Data Cube” in the “Cross Tab”.

The table gets populated, with rangers as the rows, and the number of patrols/distance patrolled, etc. as the columns. However, there are a lot of rangers that do not have any patrols and I’d like to remove these empty rows (see example image from report).

How can I format the Cross Tab so that it only populates rows with data?

[PhilipFaure]

I did notice that in the properties there is a “Empty Rows/Columns” menu, but I can’t seem to figure out how to enable it. Please see the attached image.

[LiliSadikin]

Hi Philip

To remove the empty row, you should add computed column into your data set and useexpression to SUM data from one row.

this an example

Then, on your report layout, add a filter to exclude your empty row.

Regard
Lili Sadikin

[LiliSadikin]

and here the result

[PhilipFaure]

Hi Lili

Thanks for the response - this works perfectly!

[Jackie]

Hey Lili,

Thanks for your response! I’ve already tried this approach in a few reports, and it works well.

However, I’m now facing a slightly different situation.

I have a table that shows the calculation of river discharge when there are one or two branches:

It works fine when there are two branches. But when we only have one, I’d like to hide the rows for “Volumen 2 (l)”, “Tiempo promedio 2 (s)”, and “Caudal 2 (l/s)”. I’m a bit confused about how to apply a computed column or filter to make that happen.

Thanks in advance for your help.
Best
Jackie

[LiliSadikin]

Hi @Jackie

You should implement visibility setting with script

Click on row (not on cell) of Volumen 1 (l), then clik on Visibility, write a script
row[“Volumen1”]>0

Click on row (not on cell) of Volumen 2 (l), then clik on Visibility, write a script
row[“Volumen2”]>0

Click on row (not on cell) of Tiempo promedio 1 (l), then clik on Visibility, write a script
row[“Tiempo promedio1”]>0

Click on row (not on cell) of Tiempo promedio 2 (s), then clik on Visibility, write a script
row[“Tiempo promedio2”]>0

Click on row (not on cell) of Caudal 1 (l/s), then clik on Visibility, write a script
row[“Caudal1”]>!null

Click on row (not on cell) of Caudal 2 (l/s), then clik on Visibility, write a script
row[“Caudal2”]=!null

If your data is aggregation, you should use greater than 0

but if your data is not aggregation, you can use “is not null” or =!null

Regard

Lili Sadikin

[Jackie]

Hi Lili,

Thanks so much for your response and guidance!

I tried the expression you suggested, but unfortunately it didn’t work as expected. It might be because the variable is empty?

I tried adding a ! before the variable (e.g. !row[“Tiempo promedio 2”]), and that actually worked for hiding the row when the field is empty.

Thanks again for your help!

Best,
Jackie

[LiliSadikin]

@Jackie

I tried the expression you suggested, but unfortunately it didn’t work as expected. It might be because the variable is empty?

be careful with your script, try use another data to check your script.

In JavaScript, the expression !row["Tiempo promedio 2"] is using the logical NOT operator (!) to evaluate the truthiness of the value found at the key "Tiempo promedio 2" in the row object.

Here’s a breakdown of what this means:

1. Accessing the Property: row["Tiempo promedio 2"] accesses the value associated with the key "Tiempo promedio 2" in the row object. This is a way to retrieve a property value using bracket notation.

2. Logical NOT Operator: The ! operator negates the truthiness of the value. In JavaScript, values are considered “truthy” or “falsy”. The following values are considered “falsy”:

• false
• 0
• "" (empty string)
• null
• undefined
• NaN

If the value of row["Tiempo promedio 2"] is any of these falsy values, !row["Tiempo promedio 2"] will evaluate to true. If it is a truthy value (any value that is not falsy), the expression will evaluate to false.

In summary, !row["Tiempo promedio 2"] checks if the value of "Tiempo promedio 2" in the row object is falsy and returns true if it is, or false if it is not.

[LiliSadikin]

My bad with wrong script, try this script for every row below:

Click on row (not on cell) of Volumen 1 (l), then clik on Visibility, write a script
row[“Volumen1”]==null

Click on row (not on cell) of Volumen 2 (l), then clik on Visibility, write a script
row[“Volumen2”]==null

Click on row (not on cell) of Tiempo promedio 1 (l), then clik on Visibility, write a script
row[“Tiempo promedio1”]==null

Click on row (not on cell) of Tiempo promedio 2 (s), then clik on Visibility, write a script
row[“Tiempo promedio2”]==null

Click on row (not on cell) of Caudal 1 (l/s), then clik on Visibility, write a script
row[“Caudal1”]==null

Click on row (not on cell) of Caudal 2 (l/s), then clik on Visstrong textibility, write a script
row[“Caudal2”]==null

Regard

Lili Sadikin

[Jackie]

@LiliSadikin

==null

Thank you very much Lili. I understand it better now and it works out perfectly

Jackie

[Jackie]

Hey Lili,

I was wondering how can we make this work out, for a grid element?
For example, I have a grid with many elements:

I would like to set a condition to erase the whole grid if there is no data for this section, but i’m not sure which one is the best approach since the grid seems to not recognized the elements of the tables.

Jackie

[LiliSadikin]

try this

Hiding grids/tables in BIRT whith no results from datasource

[LiliSadikin]

Or try this

Place your grid as part of your table (need to add header row on your table, and place your grid)

Table Properties > Visibility > Hide Element, Expression =

row.__rownum == -1