CR xi with ClearQuest
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14191
Printed Date: 28 Apr 2024 at 7:14pm
Topic: CR xi with ClearQuest
Posted By: bullagirl
Subject: CR xi with ClearQuest
Date 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 |
Replies:
Posted By: DBlank
Date 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
|
Posted By: bullagirl
Date 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 |
Posted By: DBlank
Date 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
|
Posted By: bullagirl
Date 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 |
Posted By: DBlank
Date Posted: 30 Aug 2011 at 4:33am
| do you have a unique identifier (primarykey) field per instance? |
Posted By: bullagirl
Date 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 |
Posted By: DBlank
Date 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.
|
Posted By: bullagirl
Date Posted: 30 Aug 2011 at 4:57am
|
I'll give that a try. Thanks! ------------- ~Randi |
Posted By: bullagirl
Date 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).
 ------------- ~Randi |
Posted By: DBlank
Date Posted: 30 Aug 2011 at 7:34am
| try in the evaluate formula change pick list option to 'Default values for Null' |
Posted By: bullagirl
Date Posted: 30 Aug 2011 at 8:08am
|
still no ------------- ~Randi |
Posted By: DBlank
Date Posted: 30 Aug 2011 at 10:47am
|
for clarity sake you want a distinct count of the clearquest.id for records that only have a close date or a review date between 8/23/2011 and 8/30/2011.
correct?
|
Posted By: bullagirl
Date Posted: 31 Aug 2011 at 3:34am
|
I need a unique count for records that have a review date between the range OR a closed date between the range. There may be records that would be identified in both groups. My query shows 32 with Review and 25 with Closed - when run separately. I just can't get those same numbers to appear on the report. The report is showing 32 with Review and 2 with Closed (incidentally, there are 2 records within Review that have a closed date within the range, so I think that's where the 2 is coming from). ------------- ~Randi |
Posted By: DBlank
Date Posted: 31 Aug 2011 at 3:58am
|
So if you alter your RT to test fot counting only reviewdates what value do we get...
name = whatever
field to summarize=ClearQuest.id
summary type = distinctcount
evaluate = use a formula
{ClearQuest.review_date} in last7days
reset=never
place in the report footer
If 0 (or empty) look on your detial rows where you know that one of those dates exists and look to make sure there is a value that is a non NULL for the ClearQuest.id on that same detial row.
What does this test give us?
|
Posted By: DBlank
Date Posted: 31 Aug 2011 at 3:59am
| you can also place the RT on the detail section and you can see it change (add or stay the same) per row to know which rows aer being counted and which are being ignored. This can help you determine what is working as intended and what is not. |
Posted By: bullagirl
Date Posted: 31 Aug 2011 at 7:28am
|
Thanks. I'll try that next! ------------- ~Randi |