Skip to Content

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 Blog

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

/wp-content/uploads/2014/12/query_602917.jpgg

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

/wp-content/uploads/2014/12/report1_602960.jpg

And this is Report2

/wp-content/uploads/2014/12/report2_602961.jpg

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.

/wp-content/uploads/2014/12/reportfilter_602962.jpg

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

.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Neil Mitchell-Goodson

    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

    This mechanism also works in at least XI3.1SP5 and higher, can’t comment on 4.x.

    HTH

    NMG

    (0) 
    1. Rogerio Plank Post author

      hi Neil, thanks for your feedback.

      THis sample was done with 4.1 SP3. I would love to share it but, apparently, i could not upload a wid

      CHeers,

      Rogerio

      (0) 
      1. Neil Mitchell-Goodson

        No worries, I actually did a lot of testing on this in SP3 and could never make it work (I participated a lot in the first thread you reference); however, upon upgrading to SP5 I was able to get the mechanism working quite succesfully. I think it’s worth noting as even though this thread is titled towards WEBI 4.x, many of the tips are backwards compatible,and some of us – sadly – are stuck on 3.x 🙁

        NMG

        (0) 
    1. Tom Francois

      I think it is because the prompt text & starting accolade “Year In List {” is 14 characters long, so the values that you want to read out start from the 15th position.

      (0) 

Leave a Reply