Author Topic: Count records with Criteria  (Read 2321 times)

Offline Abdel

  • Full Member
  • ***
  • Join Date: Apr 2015
  • Posts: 19
  • Forum Citizenship: +0/-0
Count records with Criteria
« on: 02 Jun 2015 05:17:51 pm »
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


Offline cognos810

  • Community Leader
  • *****
  • Join Date: Aug 2014
  • Posts: 246
  • Forum Citizenship: +18/-0
Re: Count records with Criteria
« Reply #1 on: 02 Jun 2015 05:36:55 pm »
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


Offline Abdel

  • Full Member
  • ***
  • Join Date: Apr 2015
  • Posts: 19
  • Forum Citizenship: +0/-0
Re: Count records with Criteria
« Reply #2 on: 02 Jun 2015 06:07:30 pm »
Worked like a charm!   :)



« Last Edit: 02 Jun 2015 06:15:55 pm by Abdel »

Offline Abdel

  • Full Member
  • ***
  • Join Date: Apr 2015
  • Posts: 19
  • Forum Citizenship: +0/-0
Re: Count records with Criteria
« Reply #3 on: 02 Jun 2015 06:17:27 pm »
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

Offline cognos810

  • Community Leader
  • *****
  • Join Date: Aug 2014
  • Posts: 246
  • Forum Citizenship: +18/-0
Re: Count records with Criteria
« Reply #4 on: 03 Jun 2015 12:22:52 pm »
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

Offline Abdel

  • Full Member
  • ***
  • Join Date: Apr 2015
  • Posts: 19
  • Forum Citizenship: +0/-0
Re: Count records with Criteria
« Reply #5 on: 03 Jun 2015 12:58:12 pm »
Understood!

 


       
Twittear