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.

[Former Member]

Hi av,

If year is only for 2013 or in future will you have some more data for Year dim. i.e 2014 ,2015,.......

If above is the case then that you are trying is not correct. Try to create a filter on measure instead of year as below.

Var_Measuretest = Not ISnull(Measure) and apply this filter on report block.

If the report is static then you can extend above formula as

Var_Test  = If (Not ISnull(Measure)  and Year <> "2013") then "Y" Else "N"

Apply above filter on your report block.

Cheers,

Suresh Babu Aluri.

[Former Member]

Hi,

you should use like this create a variable if (table1.id=table2.id and table2,year<> 2013 ) then y else n end; place in a variable A

then you got result like this

1 X 2013

2 y 0

then place variable a <>0 then u got the result what you need . but you first merge the dimensions id .if they are coming from two different queries

[Former Member]

Hi,

When you are pulling Id,Name and Year column into report, Does it showing like below

Id     Name     Year

1     x            2011

1     x            2012

1     x            2013

2     y            2001

2     y            2002

3     z            2004

If your requirement is not to show ids which have revenure for current year then you can use below formula to acheive it-

Craete variable

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

Let me know If it works.

Regards,

Anuj