Dynamic filters

building reports desktopintelligence
1

I have seen several postings regarding complex filters but have a problem that perhaps takes it one step further.

I have a document that has all of the data on one report(tab). I would like additional reports (tabs) that look exactly the same, but are filtered for one value of a particular column. The hard part is that the filter needs to be dynamic - i.e. based on values that a user happens to bring back in his report. The user is not prompted for the values in the column I need to filter. The values that are displayed depend on other prompt responses.

For example:
The first tab has data for all regions that the user selected and all districts that apply to those regions. The next tab needs to have data only for the first district, the second tab for the second district, etc. The number of tabs will vary because the number of districts returned varies, but it is OK to have tabs with empty tables.

Of course, what would really be great is if the tab names could be dynamic to match the filters!!

Our environment is BO 5.0 and WebI 2.51 and usually Oracle tables for the database (developing in full-client but viewing reports with WebI).

Regards, Linda Caron
lcaron@wciconsulting.com
linda.m.caron@fritolay.com

Listserv Archives (BOB member since 2002-06-25)

2

May be you could achieve this using macros. You could write a macro that will execute after DocumentRefresh, which will browse thru’ your data provider and create one report tab per district value. Not sure if this would work in WebI environment. It should work in full client environment.

  • Vasan

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 00-02-07 20:59:19 EST, you write:

I have a document that has all of the data on one report(tab). I would
like
additional reports (tabs) that look exactly the same, but are filtered for one value of a particular column. The hard part is that the filter needs to be dynamic - i.e. based on values that a user happens to bring back in his report. The user is not prompted for the values in the column I need to filter. The values that are displayed depend on other prompt responses.

This is certainly possible with a script.

Using straight filters, however, would be a problem. You would need a filter that says something like: Show only the First Value on this report. Then the second tab would have a filter that says: Show only the Second Value on this report.

Since there is (to my knowledge) no way to determine the first, second, etc. values on a report, I don’t see how this would be possible using filters. And since Webi does not support scripting, I’m not sure that’s a viable answer for your situation either.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 00-02-11 17:22:05 EST, you write:

I have a document that has all of the data on one report(tab). I would
like
additional reports (tabs) that look exactly the same, but are filtered
for
one value of a particular column. The hard part is that the filter
needs
to
be dynamic - i.e. based on values that a user happens to bring back in
his
report. The user is not prompted for the values in the column I need to filter. The values that are displayed depend on other prompt responses.

This is certainly possible with a script.

Using straight filters, however, would be a problem. You would need a
filter
that says something like: Show only the First Value on this report. Then
the
second tab would have a filter that says: Show only the Second Value on
this
report.

A followup: if you had a prompt for the values that you wanted to filter on, then you could substring the user response and create filters to accomplish what you need.

Use the Pos() function to return the first “;” found in the user response string. Then use Substr() to remove just the first resort. Something like this:

=UserResponse(DataProvider() ,“Pick some resorts:”)

<Resort 1> =SubStr( ,1 ,(Pos( ,“;”)-1))

To return the second, third, fourth… etc. resorts you would have to do some manipulation on the Prompt Result to get something other than the first result from the prompt.

Then create a complex filter on each of five tabs (there are five resorts in Island Resorts) that compares the to <Resort 1>, and so on.

This does, however, require a prompt.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

5

I have a similar problem, I have created a variable based on the user response, ie

=UserResponse(“Query 1” ,“MainDealID”)

The table returns ALL quantities for ALL deal ids and I would like to apply a dynamic filter so that I get the quantity for the deal id returned in the user prompt above.

Any ideas… I have been trying all sorts off things and keep getting syntax errors :frowning:

BOJunkie (BOB member since 2005-07-13)