Suppress NoData Tabs in Book Using Loop

cogburnml
New Contributor II

Is there a way to exclude a tab in a book if that tab has no data?

I have a cost center loop added to a book. The loop includes all cost centers, although I know some cost centers have no data. I want to make sure we aren't excluding any cost centers in the event an old cost center is used. I'm trying to ensure the report is complete. However, adding all cost centers to the loop results in many completely blank tabs. Is there a way to suppress these tabs or exclude them from the output?

3 REPLIES 3

db_pdx
Contributor III

Hi cogburnml: your request makes total sense and it feels like there should be an easy way to do this!  I can think of two ways (listed below), but I hope someone from the community has an easier solution, as my brain created a convoluted route to achieve this

Option 1 (More Difficult):

  • Your Loop Parameter is the Dashboard Parameter Type
  • Dashboard Parameter is a Bound List of cost centers that are not 0 (based on some form of account/scenario criteria)
  • Bound List is a Method Query that pulls from a Business Rule
  • Business Rule is an FDX-CubeView which returns the datatable of values which we can reference
  • The Cube View used in the FDX is a basic one with your Cost Center UD on the row with a base level expansion and, importantly, row suppression for NoData/Zeroes turned on
  • That should result in a list of cost centers that are not 0 which will then loop through your report(s)
  • (whew! talk about a process...)

I did a basic test of the above and it did work, but did not include much for parameterization of scenario/time/entity dimensions.  Obviously, it has many steps (Parameter, BR, CV) which increases risk that something doesn't work right.

Option 2 (Easier - but not quite working)

  • Your Loop parameter is a standard member list / expansion
  • First item inside the loop is an IF statement
  • Use XFCell() for the cost center (|Loop1Variable|) and a profit account (of your choosing) to compare against 0.  If the evaluate XFCell is not 0, then we run any reports placed below/inside the If statement.
    • Side note: IF Statement syntax is on p.584 (pdf p.621) of the Design and Reference Guide.

The problem with the above is that the If Statement does not properly compare a value to 0.  Meaning, (0 = 0) fails [does not evaluate to true], as does (0 <> 0) [incorrectly validates to true].  Interestingly, (Test = Test) evaluates correctly.  @ChristianW is this a bug with (0 = 0) not working in a Report Book IF Statement?

Like I mentioned, hopefully someone has an idea that is not so convoluted.

Cheers   -db

 

LisaS
New Contributor III

Does anyone have a working example of this? I'm having the same issue. The cube view in the book has many accounts so if any of those accounts have balances I need them to show in the book but I'm looking to exclude the tabs that are completely blank.

db_pdx
Contributor III

Hi LisaS: this question was recently asked again and I shared my current (working) solution.  You'll find it here:
https://community.onestreamsoftware.com/t5/Reporting/Report-Books-If-Statement/m-p/29095/highlight/t...