Create Date Array 1

[kezdawg]

I will begin by describing what i want to achieve and then the methodology that i think i could use to achieve it (any other solutions gratefully accepted). Using CR 11.

User will put in a date range which runs the report.

ie 1 Jan 2007 - 31 Dec 2007.

I then want to run the report pulling in all patient attendance dates between that date range. Easy enough.

However what i want to also do is provide in the footnote an exception report that will highlight by quarter any data that is missing between that date range.

Using example above;

Clinic has submitted data for all dates in 2005 except for January , February , March, July, August, Sept(these clinics input their results on a quarterly basis)

Therefore my exception footnote will highlight Q1, Q2 2007 as having missing data.

I was thinking that i could turn the date range parameter into an array of Year and Quarters which i could then run against the attendance dates.

Is that feasible or is there another way of doing this ?

 

[kezdawg]

If I do need to a date range array, any help in how to build this would be much appreciated.

 

[lbass]

I think you can just create a formula like this for the detail section:

whileprintingrecords;
numbervar qtr1;
numbervar qtr2;
numbervar qtr3;
numbervar qtr4;
select datepart("q",{table.date})
case 1 : qtr1 := qtr1 + 1
case 2 : qtr2 := qtr2 + 1
case 3 : qtr3 := qtr3 + 1
case 4 : qtr4 := qtr4 + 1

Then in the report footer, use a formula like this:

whileprintingrecords;
numbervar qtr1;
numbervar qtr2;
numbervar qtr3;
numbervar qtr4;
stringvar except;
if
(
minimum({?daterange}) <= date(year(minimum({?daterange})),3,31) and
maximum({?daterange}) >= date(year(maximum({?daterange})),1,1)
) and
qtr1 = 0 then
except := except + "Qtr1, ";
if
(
minimum({?daterange}) <= date(year(minimum({?daterange})),6,30) and
maximum({?daterange}) >= date(year(maximum({?daterange})),4,1)
) and
qtr2 = 0 then
except := except + "Qtr2, ";
if
(
minimum({?daterange}) <= date(year(minimum({?daterange})),9,30) and
maximum({?daterange}) >= date(year(maximum({?daterange})),7,1)
) and
qtr3 = 0 then
except := except + "Qtr3, ";
if
(
minimum({?daterange}) <= date(year(minimum({?daterange})),12,31) and
maximum({?daterange}) >= date(year(maximum({?daterange})),10,1)
) and
qtr4 = 0 then
except := except + "Qtr4, ";

if len(except) > 2 then
"Exceptions: "+left(except, len(except)-2);

You would use a record selection formula like this:

{table.date} = {?daterange}

-LB

 

[kezdawg]

Lbass once again your a lifesaver. I was really overcomplicating my solution and spent a very frustrating day trying to find a solution. You definitely helped me have a bright start to my monday.

Thanks a million,

Kieran