Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Counting records between dates Post Reply Post New Topic
Author Message
barry.ein
Newbie
Newbie
Avatar

Joined: 18 Feb 2015
Location: Israel
Online Status: Offline
Posts: 12
Quote barry.ein Replybullet Topic: Counting records between dates
    Posted: 22 Aug 2018 at 3:35am
Hi all,

I am creating a chart where the "On Change Of" is set to a command that gives me all dates between a few month period (the data is shown for each month).

For the show values I want to count how many records where opened between the first date of the command and each specific end of month.

Thanks,

Barry
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Aug 2018 at 10:39am
On change of use the date field
in Order option set it to 'for each month'
Show values, set a count (or distinct count) of a primary key field in your data set
IP IP Logged
barry.ein
Newbie
Newbie
Avatar

Joined: 18 Feb 2015
Location: Israel
Online Status: Offline
Posts: 12
Quote barry.ein Replybullet Posted: 22 Aug 2018 at 10:36pm
I created a command that is used as the date field for "On Change Of" and changed it to show data for each month:
WITH barry AS
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 11, 0) DateValue
UNION ALL
SELECT DateValue + 1 FROM barry WHERE DateValue + 1 < (SELECT CURRENT_TIMESTAMP))
SELECT DateValue FROM barry OPTION (MAXRECURSION 0)
======================================================
Then I created 11 formulas as below for each month (in each formula the month number is 1-11:
if {PR.date_opened} > minimum({Command.DateValue})
and {PR.date_opened} < dateadd("m",1,DateSerial(year(minimum({Command.DateValue})),month(minimum({Command.DateValue})),1)-1)
then 1 else 0
==================================================
The problems are:
1.How can I combine the 11 formulas into one so I can
put is in the chart's Show Value(s) area?
2.I can't summarize the formulas i created
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.015 seconds.