This is a re-post of an old entry I made years ago in a company blog. That blog is no longer active, so I have re-posted it here to maintain an archive of my own old blog entries
Here the other day, I needed a way to count the number of reports in a Web Intelligence document.
Just to clarify some semantics here: A Web Intelligence document is a collection of one or more reports also known as tabs.
So I asked my colleagues and around, but it seems that it was not possible – there is no built-in function in Web Intelligence that let you do that. There is a function that calculates the number of data providers, but that did not apply in this case.
In the beginning, people here also here thought it could not be done, that was until Marek Chladny wrote a simple, but brilliant answer that solves the question.
Now, Web Intelligence did not have a built-in function, but there is a another function that can be used ReportFilterSummery()
Now when you add this filter to a report it looks like this:
As you can see, all the reports is shown in the ReportFilterSummery() as
*** Filter on Report Name ***
Marek Chladny solutions was so simple, that it buggers me that I did not come up with this myself.
If you do some simple sting manipulation, and calculation, you can use ReportFilterSummery() to get the number of reports.
- you take the full length of the report summery
- make a new string where you remove the occurrences of “*** Filter “ – which is 11 characters (including the last space)
- deduct the new length from the original length, and divide with the length removed on each occurrence
So the formula for calculating the number of reports in a Web Intelligence document looks like this:
=(Length(ReportFilterSummary())-Length(Replace(ReportFilterSummary();"*** Filter ";"")))/11
I have modified Marek’s original answer to better fit, that this formula also works when my uses runs reports with Danish locale settings.
Marek has made nice blog post himself, where he explains in more detail how the formula works
My use of the formula
My original reason for asking the question was, that I needed a way to nicely format the footer of the reports.
In the center of the footer I would like to have the DocumentName() and the tab name (ReportName()), something like this:
But I only wanted the report name to be included, if there was more than one report in the Web Intelligence document, otherwise it would be redundant.
So, by the use of the information above, I created a new variable V Report Count
Then I created another variable for the document name footer V Document Footer using If
=If([V Report Count]>1; DocumentName()+" ("+ReportName()+")"; DocumentName())
To achieve the result I was looking for, so if the document contains more than one report, the footer looks like this:
An example of the report footer, when the document has more than one report
And if the document only contains one report, the footer looks like this:
An example of the report footer, when the document only contains one report.