An interesting question was asked today on the BOB forum. Let me rephrase it slightly:
How can I count reports (some call them tabs) within a single WebI document?
It’s a simple question one would say – visually check the tabs, go and count them, one, two, three, four, five … forty two. Done. 🙂
Well, the answer is a bit challenging than that if you want to actually display that number in a report or to use it somewhere in a formula and you do not want to hardcode it.
There is no WebI function that would give us the answer to our question directly. So we need to use what is available and to be a bit creative.
What is nice about this function is that it gives us a list of all document reports. Sort of. And we just need to count them now. For that we will leverage the fact that every report name in the result of the ReportFilterSummary() function is prefixed with a constant text:
*** Filter on Report
Please note that the above text contains exactly 20 characters. We will use this information later.
Now, if we count the number of occurrences of the constant text within the function result then we get the answer to our question. But how to do that? By removing the text from the function result. Really? Really.
The length of the whole text of the ReportFilterSummary() is:
When we remove the constant text “*** Filter on Report” from the function result, the final text becomes shorter and its length is:
=Length(Replace(ReportFilterSummary();"*** Filter on Report";""))
Please note that we removed a text from another text by replacing it with an empty string. Therefore the Replace() function was used with the 3rd parameter being “” which means an empty string.
The difference between the two above lengths represents the length of all strings that were removed by the Replace() function. But we know that all what was removed was N occurrences of the “*** Filter on Report” string that has 20 characters. From there we can conclude that N is:
=(Length(ReportFilterSummary())-Length(Replace(ReportFilterSummary();"*** Filter on Report";"")))/20
So the above formula gives us the answer to our initial question – what’s the number of reports (tabs) within a single WebI document.
Three WebI functions were combined in a final formula that returns the number of reports in a WebI document:
• ReportFilterSummary() – provides a list of filters and their details applied to all document reports
• Replace() – replaces a part of a string with another string
• Length() – counts the number of characters in a string