Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: CR xi with ClearQuest Post Reply Post New Topic
Page  of 2 Next >>
Author Message
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Topic: CR xi with ClearQuest
    Posted: 25 Aug 2011 at 4:49am

Hi! I'm new to this and am running into a problem.  I have two fields review_date and closed_date. I want to count the number of records in each  with a value between the CQ query-generated date range (for which I have a min and max of {ClearQuest.action_history_timestamp} on the report header).

Any ideas or help is greatly appreciated! 
~Randi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Aug 2011 at 6:34am
you mean you have a runtime parameter (set to allow a range) and you want to count the records that fall in that range?
You can do this in a number of ways.
First, you can create a formula field to make a 1 or zero based on your conditions and then sum the formula field (note that this options does not work if you have duplicate data you need to exlcude)
sample:
if {table.review_date} in Minimum({?dateparam}) to Maximum({?dateparam}) then 1 else 0
2. you can make variable formulas that conditionally count items
3. you can use Running Totals that conditionally count


Edited by DBlank - 25 Aug 2011 at 7:03am
IP IP Logged
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Posted: 25 Aug 2011 at 8:19am
The date range is being set in a ClearQuest query, so I'm not sure how to get that date range into Crystal as a field. And I don't want to have to modify the report template every week to accommodate the new date range.
~Randi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Aug 2011 at 8:27am

I think I understand your process so maybe this?

for reviews:
if {table.review_date} in Minimum({ClearQuest.action_history_timestamp}) to Maximum({ClearQuest.action_history_timestamp}) then 1 else 0
 
 
for closed:
if {table.close_date} in Minimum({ClearQuest.action_history_timestamp}) to Maximum({ClearQuest.action_history_timestamp}) then 1 else 0


Edited by DBlank - 25 Aug 2011 at 8:29am
IP IP Logged
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Posted: 30 Aug 2011 at 4:26am
OK. I tried that. My process was flawed.  I've created two new fields in CQ - Review_Date and Closed_Date.  This helps get the right records. Now I'm trying to suppress duplicates in my count.  My query in CQ shows 56 unique records that fit the criteria of having a Closed_date or Review_date that falls within my date range. In Crystal, my reports shows 393 records - the records have been in the state for more than one day within the timeperiod, so they are being reported for each day.
 
I've been looking through the forum and see the {field}=previous{field}, but I'm not sure how to apply it (within different details sections?).
~Randi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Aug 2011 at 4:33am
do you have a unique identifier (primarykey) field per instance?
IP IP Logged
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Posted: 30 Aug 2011 at 4:47am
Yes. The ClearQuest.id is unique for each record. However one record may have both a closed_date and review_date that fall within the specified date range.
~Randi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Aug 2011 at 4:55am

you can use a running total

name = whatever
field to summarize=ClearQuest.id
summary type = distinctcount
evaluate = use a formula
{table.review_date} in Minimum({ClearQuest.action_history_timestamp}) to Maximum({ClearQuest.action_history_timestamp})
or
{table.close_date} in Minimum({ClearQuest.action_history_timestamp}) to Maximum({ClearQuest.action_history_timestamp})
reset=never (for a grand total - you can reset on group level if you want group totals)
place in the report footer (or group footer for group totals)
 
You can also use a variable formula if you prefer.
IP IP Logged
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Posted: 30 Aug 2011 at 4:57am
I'll give that a try. Thanks!
~Randi
IP IP Logged
bullagirl
Newbie
Newbie
Avatar

Joined: 24 Aug 2011
Online Status: Offline
Posts: 12
Quote bullagirl Replybullet Posted: 30 Aug 2011 at 5:19am

This is what I have:

{ClearQuest.closed_date} in Last7Days
OR
{ClearQuest.review_date} in last7days
with all of settings you suggest above.
 
When I run the report, the total is 2 (and the record count still shows 393 in the corner).
Confused
~Randi
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.