How do i count only those records that are being shown based on a parameter in cognos and not the entire data based on a condition?

Question

Okay so i have a data sheet which has Purchase Order data in it with dates included now i have created a new report with a date prompt page where the user chooses the start and end dates and based on that we will show the PO's which fall in between that date. Also i want to count PO's based on their individual statuses. However the count is showing the entire thing. Like suppose there are 10 approved PO's and based on the date selected by the user there is only 2 approved PO, so i need the count to show it as 2 and not 10 which is coming right now.

I am a cognos newbie

score 2 · Answer 1 · answered May 12 '20 at 14:06

2

You may just be able to get away with count([Status] for [PO]) or simply group on [PO] and use the summary of count.

answered May 12 '20 at 14:06

Daniel Wagemann

741
4
6

how do i add the query condition of counting within the entered date window in the parameter prompted? I have created 2 parameters, one for capturing the start date and other for end date so i have a range of date now entered by the user. – Ranit Sen May 12 '20 at 14:18
If you are not filtering the query via a detail filter you could wrap this in an if then else if( [Date] between ?StartDate? and ?EndDate?) then (count([Status] for [PO]) ) else (0) – Daniel Wagemann May 12 '20 at 18:54

score 0 · Answer 2 · answered May 10 '20 at 07:47

0

Create a query item (Approved Flag) with the expression:

if ([PO] = 'Approved') then 1 else 0

Create a query item (Approved POs)

total([Approved Flag] for report)

answered May 10 '20 at 07:47

Tripanosomagambiense

154
1
10

score 0 · Accepted Answer · answered May 14 '20 at 03:04

0

THE ANSWER WAS SIMPLE. THANKS TO THE CLUES PROVIDED IN SYNTAX BY DANIEL WAGERMANN, I SIMPLY DEFINED 2 PARAMETERS TO CAPTURE THE START AND END DATES

COUNT ( IF([STATUS]='APPR' AND [DATE] BETWEEN ?STARTDATE? AND ?ENDDATE?) THEN (1) ELSE NULL FOR REPORT)

I HAD ISSUES WITH BRACKETS HENCE WAS GETTING ERROR BUT NOW IT IS WORKING FINE.

answered May 14 '20 at 03:04

Ranit Sen

11
2

maybe give an upvote to DANIEL WAGERMANN as a thank you? – VAI Jason May 18 '20 at 13:40
Hi Jason i did that but it does not registers it says this "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." Which is why i mentioned in the answer his name as he gave me the clue to the solution. – Ranit Sen May 18 '20 at 15:31

How do i count only those records that are being shown based on a parameter in cognos and not the entire data based on a condition?

3 Answers3