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

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