How to Summarize the same data set multiple times

[fmrock]

Hey everyone,

Just was wondering if anyone has any ideas on how I would summarize a dataset 4 Times.

I am pulling data for Charges, Payments, Adjustments for 3 Hospitals in our system.

We want to have 4 different summary, but all are the same.

The summaries would be

Yesterday
Week to Date
Month to Date
Year to Date

I know I could create a sub reports, but I would like to be able to pull the data back for the whole year and do the 4 summaries with the one dataset.

Is this possible? Any help would be great.

BTW.. I am using CR9.

 

[lbass]

You can either use running totals or conditional formulas. If you don't have duplicate data, try conditional formulas like this:

//{@yesterdaycharge}:
if {table.date} = currentdate-1 then {table.chg}

//{@weektodatecharge}:
if {table.date} in weektodatefromsun then {table.chg}

//{@monthtodatecharge}:
if {table.date} in monthtodate then {table.chg}

//{@yeartodatecharge}:
if {table.date} in yeartodate then {table.chg}

Repeat for the other two fields. Place these in the detail section and then insert sums on them at group and/or grand total levels.

-LB

 

[fmrock]

lbass,

I have done that and got the data I want. However, the way the client wants the data is more like


12/8/2010
Hospital-Charges-Payments-Adjustments-Net A/R
(3 hospitals data here)

WTD
Hospital-Charges-Payments-Adjustments-Net A/R
(3 hospitals data here)

MTD
Hospital-Charges-Payments-Adjustments-Net A/R
(3 hospitals data here)

YTD
Hospital-Charges-Payments-Adjustments-Net A/R
(3 hospitals data here)

They would like these 4 reports all together, so I was trying to avoid creating subreports and pulling the query the over and over as it take about 30 minutes to pull the data once.

 

[lbass]

Maybe you could talk them into grouping on hospital, and then showing the results horizontally in the group header or footer?

Otherwise, you need to use subreports--unless you want to create summary formulas per hospital per field. You can't group on these time frames, as one record will only appear in one group.

Another approach would be to use a command where you added the dataset four times using union alls, and limiting the time frames in the where clause. This would probably be faster than the subreport approach.

I'm wondering why the report is so slow to begin with. Have you optimized the SQL query so that all selection criteria are appearing in the query?

-LB