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.
Quote from: trasmith on 15 Oct 2018 02:29:03 PM
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.
[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.
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?
Hi does anyone else have any ideas? I still need help.
I added a data item with the below formula.
case
when [COMP CODE] in ('000-004', '000-002')
then 1
else 0
end
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)
Quote from: trasmith on 16 Oct 2018 08:04:56 AM
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).
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] )
[PM Count] = 2 and [repair component description] like 'TRACTOR A PM%'
Thank you. I'll give it a go and let everyone know.
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%'