Daily average report

[Former Member]

I need to design a Crystal report that shows the average number of scheduled appointments per day in the next 4 weeks.

(This will be a rolling weekly report.)

The fields I am working with are "ApptDateTime" and "PatientNumber"  (Our database has no "ApptDate" field, so I usually work around this by using a parameter)

The results should look like this:

WEEK of	Mon	Tues	Wed	Thurs	Friday	AVERAGE# Appts
Aug 4	2	2	2	2	2	2
Aug 11	4	2	4	3	4	3
Aug 18	5	2	5	2	1	3

Any help would be greatly appreciated!

[abhilash_kumar · Accepted solution]

Hi,

A crosstab, as Brian suggests, is the ideal way to go about building such reports. Do note however, that it will skip any rows/columns that doesn't have any data in the database.

You don't want a crosstab that doesn't show the Weekday just because there aren't any appointments for that day for the next few weeks (very rare situation in my opinion).

It is still worth covering up for the not-so-obvious. You'll need to build a table in the database with a DateTime column that list all dates for perhaps the next two or more years and LEFT JOIN it to the existing ApptDateTime field. Throughout the report, wherever you've referenced the ApptDateTime field, replace that with the DateTime field from the new table.

Once you have the above sorted out, you have some more work before the Crosstab is ready. Here's what you need to do (Steps work only for CR 2008 or higher):

1) Add the record selection formula so that it gives the weekends a miss:

Not(datepart('w',{DateTimeField}) IN [1,7])

2) Create a formula called 'DayName' with this code:

datepart('w',{DateTimeField})

3) Insert the crosstab. Use the DateTime field as the Row and set it to the Print 'For each week' by clicking the options button

4) Use the 'DayName' field as the Column > Click Group Options > Options tab > Click 'Customize Group Name field' > Click the formula button beside 'Use a formula' and use this code:

WeekDayName(datepart('w',{DateTimeField}))

5) Use the PatientNumber field as the Summarized Field and change the Summary to 'Count'

6) Refresh the report and while in the Preview Mode, right-click the 'Total' Column > Select Column Grand Totals > Check 'Suppress Grand Totals'

7) Right-click column header for 'Friday' and select Calculated Member > Insert Column. A blank column with zero values is inserted

😎 Right-click the blank header cell > Calculated Member > Edit Header Formula and type this:

"Average # Appts"

9) Next, Right-click one of the zero value cells in the newly added column > Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;   

local numbervar avg;   

local numbervar cnt;  

for i := 0 to CurrentColumnIndex-1 do   

(   

    avg := avg + (GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);   

    cnt := cnt + 1;   

);   

avg/cnt;

Let me know how this goes.

-Abhilash

[Former Member · Accepted solution]

Regarding your first instructions:

"It is still worth covering up for the not-so-obvious. You'll need to build a table in the database with a DateTime column that list all dates for perhaps the next two or more years and LEFT JOIN it to the existing ApptDateTime field. Throughout the report, wherever you've referenced the ApptDateTime field, replace that with the DateTime field from the new table."

We use a third-party source who manages the database and do not have authorization to add or edit tables, only view the data.  Is there a workaround I could do?

Thanks.

[abhilash_kumar · Accepted solution]

Yes, Create an Excel Sheet with the list of dates and Join that to the existing table instead.

This will slow down the report (to a huge extent or to some extent depending on the data).

Also have a look at all the techniques in this excellent Blog from Jamie:

-Abhilash

[Former Member · Accepted solution]

Yes, I was thinking I would have to create something like an Excel sheet.  Thanks.

Just to clarify:  The reason to do this is to avoid missing columns (day of the week) if there are no appointments that day (which does happen here occasionally)?

If so, is there some way to just have it print a "0" for the day instead of having to create a new table?  Or am I on the wrong track...

Thanks again.

[Former Member · Accepted solution]

After building an Excel sheet with the dates and doing the outer Join, I am getting NO data in the date column now (even after changing all my original fields to the new datetime field in the Excel spreadsheet.)

I even tried every possible variation of the outer Join, to no avail.

I think it may be because our database (which I cannot change) ONLY has a timedate field instead of a date field.

The Excel sheet I created only has dates (no times), and I don't think it would be practical to create an Excel sheet listing every minute of the day for the next 2 years!

Anything else I can do?

Thanks again.

[abhilash_kumar · Accepted solution]

This exercise is being done only to avoid a situation where you might not see a 'day of the week' being shown on the crosstab *because there aren't any appointments for the weeks (rows of the crosstab) reported.

E.g: The column 'Friday' is missing because there aren't any appointments in the week of Aug 4, Aug 11 and Aug 18.

When you LEFT OUTER JOIN to a table that has all the dates, you should see the missing column - all with zeroes!

How about you write a SQL Query that converts the 'DateTime' to 'Date' so that both the fields being joined on are the same?

P.S: If you're confident that every weekday will have an appt in some week or the other, you have nothing to worry about.

-Abhilash

[Former Member · Accepted solution]

Success!  It is working perfectly!  Thank you for your excellent help!

I created a SQL query to convert the DateTimes to Dates.  Then, by following Abhilash's steps, I designed the rest of the report.

At that point, all I needed to do was tweak appearance, etc.

I am learning a lot!  Thanks again!!

P.S.  One minor mistake in step 9:  - a Missing parenthesis after CurrentSummary Index

But, as soon as I added that, no problems!

[abhilash_kumar · Accepted solution]

Glad it works!

Please don't forget to close the thread.

Have a great day.

-Abhilash

[former_member292966]

Hi,

Off the top of my head the most direct way for this report would be with a Crosstab.

Your row would be based on ApptDateTime grouping by week.  The columns would be based on ApptDateTime and grouped by Day.  Use  PatientNumber and do a count as the summary.

The final column you can do an average of the row.

Good luck,

Brian