I tried searching to see if this is covered already, but it is kind of difficult to look up as I'm not even sure how to word it. My question is basically if there is a way to filter out records based on a single data item. For each customer I have, there are multiple records of different types. My user is requesting I remove ALL records if at least one record falls under a certain type. I can't use the filter [DataItem] <> 'Type' because all that does is remove that specific record, leaving the rest for that customer.
I believe my data is using a relational package, but not entirely sure.
For example below, I am looking to remove ALL IDs '123' and '456' because at least one record of each is a type A.
Type ID Name
A 123 Taylor
B 123 Taylor
C 123 Taylor
B 789 Mike
D 789 Mike
C 369 Troy
A 456 Ryan
A 456 Ryan
D 456 Ryan
Desired Outcome:
Type ID Name
B 789 Mike
D 789 Mike
C 369 Troy
Any suggestions or ideas would be greatly appreciated! Thank you in advance.
Quote from: norrisar on 28 May 2014 09:54:01 AM
I believe my data is using a relational package, but not entirely sure.
If you only see query subjects and query items in your source pane rather than dimensions with members and levels then it is relational. Another way to check is to see how the data items are referenced. Right click on an item in the source pane and take a look a the properties.
Relational has three: [Namespace].[Query Subject].[Query Item]
Dimensional has four: [Namespace].[Dimension].[Hierarchy].[Level]
Assuming it is relational, then try creating a query calculation to count up the type A items by customer. Drag a query calculation into the query and use an expression similar to below. Call it something like CountTypeA.
Set the aggregation method to none. Drag the item into your layout to verify that it is returning 1 or 0 as you would expect. If so, drag it into the detail filter area and make the condition [CountTypeA] = 0 and set the application property for the filter to After Auto Aggregation.
count ( distinct
case
when [YourPackage].[YourQuerySubject].[Type] = 'A'
then [YourPackage].[YourQuerySubject].[Type]
else null
end
for [CustomerID]
)
I don't always do things the easiest possible way so perhaps others might chime in with an alternate approach.
Quote from: Lynn on 28 May 2014 10:54:26 AM
If you only see query subjects and query items in your source pane rather than dimensions with members and levels then it is relational. Another way to check is to see how the data items are referenced. Right click on an item in the source pane and take a look a the properties.
Thanks for the clarification; my data IS in fact relational. I did the method you described, and it seemed to have worked. I'm sending a test copy to my user now to make sure that is what she is looking for. Thank you for your help!
If anyone else comes by this and has another way, I'm always open to learn other methods.