Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Group and Sum using date ranges Post Reply Post New Topic
Author Message
Kevinbwdsb
Newbie
Newbie


Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
Quote Kevinbwdsb Replybullet Topic: Group and Sum using date ranges
    Posted: 10 Feb 2017 at 9:31am
Hi I am in need of a way to count the number of received vs closed calls in a specific date range. When I group on the received date and then add a summary the number is right for received but when I add a summary for the number of closed calls - the number is always equal to or very close to those received - running a SQL script the numbers or closed is completely wrong. I tried grouping on closed as well but then grouping gets messed up for closed. I also need to add a total for remaining Open that includes the weeks prior. I can run three separate reports and get the right numbers, but when I try to combine the formulas and get it all on one report every total is wrong. I've tried a cross tab but it is ugly and the totals are still wrong, running totals don't work in headers and putting everything in the footer didn't seem to work. I have to limited the calls to between the range (parameter) of beginning to End for Rec'd and Closed but remaining open can include calls prior to the beginning date if that makes sense.
Any help would be appreciated...

I want it to look like:
February               Received     Closed   Open
Week Beginning Feb 5      150          129     75
               Feb 12     100          105     70
               Feb 19     160          130    100
               etc
March
Week Beginning March 5 Etc Etc
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Feb 2017 at 2:52am
what does your data set look like?
IP IP Logged
Kevinbwdsb
Newbie
Newbie


Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
Quote Kevinbwdsb Replybullet Posted: 14 Feb 2017 at 5:24am
Hi DBlank,
   My data set is in SQL - what exactly do you need to know - I am using HEAT Call logging to collect the data
There are fields for Date Received, Date Closed etc. Dates are stored as 'yyyy-mm-dd' and I am using formulas to ensure the first day of the week is Monday not Sunday and then converting to text.

Thanks
Kevin
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Feb 2017 at 3:01am
Is your data set one row with multiple date columns, like RecievedDate, OpenDate, CloseDate

or is it one row with one date field and another field that identifies what the date is for?
IP IP Logged
Kevinbwdsb
Newbie
Newbie


Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
Quote Kevinbwdsb Replybullet Posted: 15 Feb 2017 at 3:31am
Hi DBlank,
Data set is row with multiple date columns.

Thanks
Kevin
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Feb 2017 at 3:44am
The issue is each row gets grouped into one group and the remaining dates may need to be another group. Can you write a stored proc or the like to convert this into the data set that wold support your output need?
Basically you can do 3 select statements and union them together, somehting like:
select pkid, ReceivedDate as [ReportDate], 'Received' as [ReportType]
from table where ReceivedDate is not null
UNION
select pkid, OpenDate as [ReportDate], 'Opened' as [ReportType]
from table where OpenDate is not null
UNION
select pkid, CloseDate as [ReportDate], 'Closed' as [ReportType]
from table where CloseDate is not null

Thsi data set allows you to group on the singular Date field and then do counts (crosstab column group) by the 'ReportType' field.
IP IP Logged
Kevinbwdsb
Newbie
Newbie


Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
Quote Kevinbwdsb Replybullet Posted: 15 Feb 2017 at 5:14am
Hi DBlank,
    I will talk to my DBA about doing this.
Thanks
Kevin
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Feb 2017 at 5:45am
Note you can also use a Crystal Command to do this inside Crystal if needed. I would also make sure this approach, or the selected fields, support all your report requirements.I was only focused on what you posted about.

Edited by DBlank - 15 Feb 2017 at 5:45am
IP IP Logged
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.031 seconds.