Hi All,
I am currently having a frustrating problem where I need to display Open SR's & Closed SR's on a chart. All works fine when I just do the query expression based on status (when status = 'NEW', or when status = 'CLOSED' for example). As shown below.
(http://i.imgur.com/twAW45v.png)
However, doing the queryn in this way meant that cognos was duplicating records and counting them twice. The only way I've found to stop this from happening is by adding in a second check as follows.
"status = 'NEW' AND extract(month,[changedate]) = extract(month,[reportdate])"
Adding this second part causes the query to return the right values, however for some reason stops it from displaying on the chart. As shown below.
(http://i.imgur.com/j2eTyuw.png)
However I know the data is right, since I displayed in just a singleton query. I suspect it's clearly a problem with the date(s) but not sure how I can solve it, while still keeping the date check in the Query Expression. If anyone has any insight into this problem it would be greatly appreaciated. I might not be able to access Cognos again until tomorrow morning however.
Thanks,
Will.
Hi,
When you refer to "adding in a second check" can you elaborate? Are you doing this in a query calc or in a filter? If a calc, can you tell us the entire syntax you are using?
Cheers!
MF.
OK Sorry about the confusion.
My original Query Calc (the working one) is as follows:
"case when [status] NOT IN ('RESOLVED','CLOSED') then [ticketid] else null end"
Then the second Query Calc I made, which I was referring to as adding a 'second check' is as follows:
"case when [status] NOT IN ('RESOLVED','CLOSED') AND extract(month,[changedate]) = extract(month,[reportdate]) then [ticketid] else null end"
There is a Count Distinct aggregate function applied to the data item, as I do not want to count the same [ticketid] more than once. I've also just noticed the error in the screenshot does not happen when a Count aggregate function is applied, only with Count Distinct.
Hopefully this gives you some more insight!
Regards,
Will