Filter if both results return (new user)

[trasmith]

Hi,

I am relatively new to Cognos 11 and I am working on a report to show when preventative maintenance is due. Each unit has an A-pm and a B-pm which are dependent upon each other. Meaning that if the B-pm is due and completed it fulfills the A-pm and the cycle starts over.

Currently my report shows me all pm's due.
When a unit has the A-pm and B-pm due due for a unit I only want to show the B-pm as being due.
Only in this situation do I want to hide the A-pm, at all other times when due it should show.

I am not sure how to accomplish this. I've attached an screen print of my report.

[Lynn]

Hi,

I am relatively new to Cognos 11 and I am working on a report to show when preventative maintenance is due. Each unit has an A-pm and a B-pm which are dependent upon each other. Meaning that if the B-pm is due and completed it fulfills the A-pm and the cycle starts over.

Currently my report shows me all pm's due.
When a unit has the A-pm and B-pm due due for a unit I only want to show the B-pm as being due.
Only in this situation do I want to hide the A-pm, at all other times when due it should show.

I am not sure how to accomplish this. I've attached an screen print of my report.

The above should return a count of 1 or 0 if one of the two PM services are due or if none of the PM services are due. It should return 2 when both are due. Create a query item with this expression called [PM Count] to test.

Next, create a filter and set it to after auto aggregation. Use your count in conjunction with the description to remove the A PMs where both are due.

Code Select Expand


[PM Count] = 2 and [repair component description] like 'TRACTOR A PM'


Hope this helps or at least gives you additional ideas toward solving the problem.
Cheers.

[trasmith]

Hi Lynn,

Thank you for  your reply. I don't see the expression for "PM Count" listed, only the expression for the the filter. Can you post please?

[trasmith]

Hi does anyone else have any ideas? I still need help.

[trasmith]

I added a data item with the below formula.

case
when [COMP CODE] in ('000-004', '000-002')
then 1
else 0
end

[dougp]

I think what Lynn is saying is to create a separate query containing [UNITNUMBER] (for convenience, name it BPMUNIT) where [COMP CODE] = '000-004'.  Left join that query to the current query on [BPMUNIT] = [UNITNUMBER].  Include everything from your current query plus the [BPMUNIT] from the new query.  In the resulting query, filter out records where [COMP CODE] = '000-002' and [BPMUNIT] = [UNITNUMBER].  (or [COMP CODE] = '000-002' and [BPMUNIT] not null)

[Lynn]

Hi Lynn,

Thank you for  your reply. I don't see the expression for "PM Count" listed, only the expression for the the filter. Can you post please?

Sorry....I was getting errors back from Cognoise when I tried to post and copy/pasted only a partial response.

This expression should count up all the PM comp codes for each unit.  Using your sample data, this expression should return a count of 1 on rows 4 through 7 (unit 91882) and a count of 2 on rows 6 through 10 (unit 91886).  Your after auto aggregation filter would then exclude row 6 because the count is 2 and the description is an A PM service (note I mistakenly omitted the % in the expression in my earlier post).

Code Select Expand


count (
   case 
      when [repair component description] like 'TRACTOR A PM%'
           or [repair component description] like 'TRACTOR B PM%'
       then [comp code]
     else null
     end
for [unitnumber] )


Code Select Expand


[PM Count] = 2 and [repair component description] like 'TRACTOR A PM%'


[trasmith]

Thank you. I'll give it a go and let everyone know.

[trasmith]

I modified the PM Count and this is what I have now which seems like it should work with the filter and give me the results I need however when I apply the filter I get an error (see attached)

Data Item PM Count
count(
case
when [Due ?] = 'DUE'
and [REPAIR COMPONENT DESCRIPTION] like 'TRACTOR A PM%'
or [REPAIR COMPONENT DESCRIPTION] like 'TRACTOR B PM%'
then [REPAIR COMPONENT DESCRIPTION]
else null
end
for [UNITNUMBER])

Detail Filter
[PM Count] = 2 and [REPAIR COMPONENT DESCRIPTION] not like 'TRACTOR A PM%'