On my data item, under Expression Definition how do I include a Boolean 'and' operator similar to that in a where clause in SQL. Basically, My goal is to get a count of specific records in a table by referencing two fields existent in that table.
Currently I have this: (it works)
Count([Needles].[Cases].[Case No.])
I wish to add criteria to my count, so that I am counting records where caseType = 'WC' AND [Date Opened] >= 2015-04-15
In SQL it would look like this:
select count(*) from cases where caseType = 'WC' AND [Date Opened] >= 2015-04-15
Thanks in advance for your help. A little stuck here!
Abdel
Hello Abdel,
Create a data item with expression as such:
CASE
WHEN CaseType='WC' AND [Date Opened]>=2015-04-15
THEN [Needles].[Cases].[Case No.]
ELSE NULL
END
Then set the aggregate property of this data item to Count.
ALTERNATIVELY, the data item expression could be..
CASE
WHEN CaseType='WC' AND [Date Opened]>=2015-04-15
THEN 1
ELSE 0
END
Then set the aggregate property of this data item to Total.
Hope it works for you!!
-Cognos810
Worked like a charm! :)
I am just curious. Alternatively, could I have inserted my SQL statement somewhere and used it instead? Just asking for general knowledge. No need for detail as I have already resolved my problem
A
Yes and No :)
If you have to simply display the count somewhere on the report page, as a standalone number OR in conjunction with all columns in the projection list of your SQL query, then you could use the SQL object in report studio to put in your SQL. Its not a good practice to use direct SQL though.
If you already have a query container like a list, crosstab, chart etc. coming from one query that is package based, then inserting an item from another query(fed by the sql object containing your SQL statement), its not as straightforward and may involve performing joins/master-detail etc.
-Cognos810
Understood!