Excluding filter or calculation

[chumby]

I have attempted to locate an existing topic for my question, and haven't found one.  It could be that my question is so incredibly basic and dimwitted, that it has never been registered.  Nevertheless, after trying to search here and beyond with any wording I could think of to find the answer, I've been unsuccessful, and I figured I would just go to the pros.

I'm working in a relational model.  I do not have access to the databases or SQL, and must rely upon what's available in Report Studio itself.

Within my data, I have a unique identifier that is a case ID.  I have another column that gives me action codes.

What I'm trying to do is exclude any case ID that matches code '71' in the action codes.

The problem that I'm running into is the action codes field has many other codes in it as well, and if I just filter out the '71''s, it still leaves the rest.  What I need to do is make it so that any case ID that has even one action code of 71 not appear in my report results at all.

I've tried creating a calculated field to identify the case ID's that have a '71' action code

If ([Action Code] = '71') Then ([Case ID]) Else ('0')

and then attempt to filter out the case ID field off of that result, but nothing I do seems to work (using the NOT function, <> syntax, etc).  The best I can do is get the report to just not show the rows where the action code is 71 but it leaves the rest that I don't want. 

I realize that the answer is likely ridiculously simple, and I fully expect that it will make me feel really foolish when its given.  However, hunting around Google and countless links trying to find the answer is making me feel fairly dumb already.

I appreciate any help you can offer.

[MFGF]

I have attempted to locate an existing topic for my question, and haven't found one.  It could be that my question is so incredibly basic and dimwitted, that it has never been registered.  Nevertheless, after trying to search here and beyond with any wording I could think of to find the answer, I've been unsuccessful, and I figured I would just go to the pros.

I'm working in a relational model.  I do not have access to the databases or SQL, and must rely upon what's available in Report Studio itself.

Within my data, I have a unique identifier that is a case ID.  I have another column that gives me action codes.

What I'm trying to do is exclude any case ID that matches code '71' in the action codes.

The problem that I'm running into is the action codes field has many other codes in it as well, and if I just filter out the '71''s, it still leaves the rest.  What I need to do is make it so that any case ID that has even one action code of 71 not appear in my report results at all.

I've tried creating a calculated field to identify the case ID's that have a '71' action code

If ([Action Code] = '71') Then ([Case ID]) Else ('0')

and then attempt to filter out the case ID field off of that result, but nothing I do seems to work (using the NOT function, <> syntax, etc).  The best I can do is get the report to just not show the rows where the action code is 71 but it leaves the rest that I don't want. 

I realize that the answer is likely ridiculously simple, and I fully expect that it will make me feel really foolish when its given.  However, hunting around Google and countless links trying to find the answer is making me feel fairly dumb already.

I appreciate any help you can offer.

Hi,

How about checking for the maximum value of your calculated field for each distinct Case ID? If there are no Action Code 71 entries for a Case ID, the maximum value of the calculation for the Case ID will be '0', so you could perhaps define your filter as:

maximum([Your calculated item] for [Case ID]) = '0'

Does this work for you?

MF.

[chumby]

When I applied that filer, it returned the highest number case, and all instances where that had a  '0', which isn't quite that I need either.

I'm probably not explaining things well enough, so here's a very simplified example of the data itself

Case ID   Action Code   Calculated Field
1202       1                   0
1202       20                 0
1202       71                 1202
1203       1                   0
1203       20                 0
1203       54                 0

The calculated field is doing what it should, and returning the Case ID if the action code is '71'.  But I need to be able to remove any case ID that has even one action code '71' (along with the rest of the rows of other action codes too).  In this example, case 1203 would show up in my report, but I wouldn't want 1202 to appear at all.

Thanks again for your help

[adam_mc]

I don't want to put words into the Great Muppet's mouth, but I think he was trying to have you do something like taking your calculation
[Calc1] = If ([Action Code] = '71') Then ([Case ID]) Else ('0')

Then the calculation, [Calc2] =  maximum([Calc1] for [Case ID]) as this should make your data look like below with both new calculations shown:

Case ID   Action Code   Calc1    Calc2
1202       1                   0          71
1202       20                 0          71
1202       71                 71        71
1203       1                   0          0
1203       20                 0          0
1203       54                 0          0

Then, you would have the filter where [Calc2] = 0

MFGF, just did it one step instead of two!

Hope this helps,
Adam.

[MFGF]

I don't want to put words into the Great Muppet's mouth, but I think he was trying to have you do something like taking your calculation
[Calc1] = If ([Action Code] = '71') Then ([Case ID]) Else ('0')

Then the calculation, [Calc2] =  maximum([Calc1] for [Case ID]) as this should make your data look like below with both new calculations shown:

Case ID   Action Code   Calc1    Calc2
1202       1                   0          71
1202       20                 0          71
1202       71                 71        71
1203       1                   0          0
1203       20                 0          0
1203       54                 0          0

Then, you would have the filter where [Calc2] = 0

MFGF, just did it one step instead of two!

Hope this helps,
Adam.

Yep, exactly this. :) Thanks Adam!

[chumby]

Forgive my naivite.

That did work a treat.  Thank you both very much for your help!