Cannot filter data using report filters or input controls

[Former Member]

Hi Every one,

I need help creating variables to filter data in report level

I have two tables in my data base

A(Summary) left outer join B(Revenue )

Table A

id name

1 x

2 y

3 z

Table b

id year

1 2011

1 2012

1 2013

2 2001

2 2002

3 2004

Now I want to build report(all filtering must happen in report level) exactly like table A except that I want the Id's who don't have revenue in 2013

Report should look like

id name

2 y

3 z

I created a variable a= if year<>2013 then "y" and "n" but when I apply a filter on the table/ or create an input control that doesn't seem to work.

Please help me find a solution to this.

Thanks in advance

[former_member189638 · Accepted solution]

Try using ForEach function.

=If([Year] ForEach([Id]) <>2013) THEN "Y" ELSE "N"

[Former Member · Accepted solution]

Hi Rakesh,

Thanks for replying.

I created the variable and when id and variable are dragged into the report i get #multivalue error

example

id   variable

1     #multivalue

also when I try to apply a filter on the table using this variable..it doesn't seem to work(no data displayed when selecting either "Y" or "N" as defined in the formula for the variable)

[former_member189638 · Accepted solution]

But may I know why are you creating a flag??

Create 2 queries in a single Webi Report

First query will have ID and Year. In this query, use the Year prompt filter not equal to 2013. So the output of the query will contain 2 and 3

Create a second query with ID and Name. In this use the ID prompt and select option "Result from another query". Select the ID from Query 1 here... 2 and 3 from Query 1 will be passed to this query.