Forum Discussion

cogburnml's avatar
cogburnml
New Contributor II
3 years ago

Suppress NoData Tabs in Book Using Loop

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 w...
Books
db_pdx's avatar
db_pdx
Valued Contributor
3 years ago

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