How to propagate values used in an Input Control to other Reports

[Former Member]

Using the values applied in a Input control among reports

Hi,

Would like to share a method for using the values applied to na Input Control in a Report (let´s say Report1) in another Report (Report2).

This is not entirely my idea, I try to improve the discussion in here

http://www.forumtopics.com/busobj/viewtopic.php?t=161592&postdays=0&postorder=asc&start=0

and in Dave's blg

Web Intelligence - Input Control that affects all tabs | David Lai's Business Intelligence ...

Let´s construct our report based on a query over e-fashion like this :

g

I will assemble Report1 as a cross-table containig [Line], [Year] and [Sales revenue] .

I´ll also create na Input Control over [Year].

That´s how my report looks like

And this is Report2

The whole idea is, when I alter the input control in Report1, Report2. Should be filtered by the same values.

To do so  lets first take a look at ReportFunctionSummary(). This function returns a string containing all filters applies on each report in a Document, so in our report, this is how ReportFilterSummary() will appear when I set the years 2004 and 2005 in the Input Control.

What we need to do is thentext between the brackets, which is the list of the values used by the Input Control.

I will use the following formula to separate the string list of values . Create a measure variable [list_of_values]

[list_of_values] = =Substr(ReportFilterSummary();Pos(ReportFilterSummary();"Year In List {")+14;Pos(ReportFilterSummary();"}")-Pos(ReportFilterSummary();"Year In List {")-14)

The blue part of the formula is where the string of the filtered Year begins and the red part is the Length of the string list.Then, that’s how I filter  Report 2 based on the string list.

[tst] = =If(Pos([lista];[Year])>0;1;If(Pos(ReportFilterSummary();"}")=0;1))

The blue part is where I test for [Year] being in the string list. In the red part of the formula, I test for the existence of the [Year] filter. If there is no [Year] in ReportFilterSummary() it means that an All selection was made in the input control.

Filter Report 2 by [tst] = 1 and that’s it.

There are somethings that one have to keep in mind while implementing this solution :

1 – As the search for [Year] filter ([list_of_values] variable), it is dependent on the language used. So, if you have an environment where you can have more than language selected, you must pay attention to that;

2 – This approach works , not only for the In List operator, but altering the variable you cant test for the other operators;

3 – Up to BOXI 3.1 SP3 this approach didn’t work because the Report had to be refreshed in order to it work. Apparently, in BOXI 4.1 SP3, this restriction was taken away

Cheers,

Rogerio

.