Variable using in Report filter.

[system]

I need to filter rows using Report Filter using variable, variable is defined as measure. As soon as I put variable in report filter no data showing up, all data disappearing, following is my variable.

Variable is =(Count([Item]) In ([Deal Key]))

---

rajpal (BOB member since 2010-05-07)

[system]

I see no one is replying , is there problem with my posting?

My question is can use Measure variable in Report Filter?

thank you.

---

rajpal (BOB member since 2010-05-07)

[system]

Hi rajpal, not even 1 hour is over from the time you posted. It takes time for people to respond. Please be patient.

However to answer your question, yes, measures can be used in filters, but not with the objects that use aggregate function if I am right.

Edit: Check this FAQ.

FAQ: Reporter

Why can’t I filter on a Report Aggregate? Answered by Dave Rathbun: It’s done that way to prevent an infinite loop. You can filter on the report level, you just can’t filter on an aggregate. That’s because the filters define the aggregate, not the other way around. Process: Return data to the cube Apply filter Display data Calculate aggregates If you tried to apply a filter after displaying the aggregates, you would loop back to step 2 and have to recalculate your aggregate. At which point…

---

Jansi (BOB member since 2008-05-12)

[system]

thank you, Jansi for responding…

So are thinking I can’t use Count in variable?

Following is my data : And I want to show only 1300 deal, any idea how should I do?

Deal: item: count
1200 222 2
1200 223 2
1300 222 1

thank you.

---

rajpal (BOB member since 2010-05-07)

[system]

Can’t you try ranking?

---

Jansi (BOB member since 2008-05-12)

[system]

I don’t have Measure object to create Rank !

---

rajpal (BOB member since 2010-05-07)

[system]

Is the ‘Deal’ 1300 fixed or how do you filter which deal to show

---

zigzag13 (BOB member since 2008-08-20)

[system]

Item Id is fixed 222, 223.

---

rajpal (BOB member since 2010-05-07)

[system]

here is my problem: I want to show record if

1. item 1038 Date is not null and 1039 item date is null (example: entity 49082 I want to show)

2. item 1038 & 1039 is not null then I don’t want to show (example: entity 81522300 & 81522400)

3. in this report I am looking for only two items (1038,1039).

how should I eliminate (81522300 & 81522400)

entity Item Date
49082 1,038 9/14/11
49082 1,039
49083 1,038 9/20/11
49083 1,039
81522300 1,038 9/19/11
81522300 1,039 9/20/11
81522400 1,038 9/29/11
81522400 1,039 9/30/11[list][/list]

---

rajpal (BOB member since 2010-05-07)

[system]

Hi,
One possible solution is

1. Create another data provider DP2 with two objects Entity, Date and do a override sql (modify SQL as per your requirement) as below and run the query.
   SELECT Entity, ‘No’ FROM dbo.table1 WHERE [Date] IS NULL

2. Goto Merged Dimension, select Entity from DP1 and Entity from DP2 and select Merge.

3. Select objects Entity, Item, Date from DP1 and goto Table properties and check box Display->Avoid Duplicate Row Aggregation.

4. Click the Table and Click Filter icon from the tool bar, drop [DP2].[Date ]object on the filter pane and select value from the list ‘No’ and click ok.

5. The table should display only 49082 and 49083 rows only.

The idea is to have second data provider that tells which Entity rows should show. By merging two data providers you get to know which row to display along with the required data.

Hope this helps.

---

zigzag13 (BOB member since 2008-08-20)