How to count the number of reports in a Webi document

[Verakso]

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.

The question

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.

That was until I asked the question on the Business Objects Board better known as BOB, the worlds foremost resource for Business Object related questions and answers.

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.

The answer

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.

1. you take the full length of the report summery


2. make a new string where you remove the occurrences of “*** Filter “ - which is 11 characters (including the last space)


3. 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())

like this:



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.