Cross Tab Report, Date Formula for 1

[Jonathan212]

I am comparing surgical cases done for each surgeon for the time period "1/1/06 to 8/31/06" with cases they have done in the time period "1/1/07 to 8/31/07". I am grouping by surgeon name and use a cross tab report.

I understand there are various date grouping options (e.g. by date, by quarter, by half year, etc.) but I would like to do this for the first 8 months of calendar year 2006 and compare to the first 8 months in 2007. Right now I am doing this be re-running the report twice (with different date ranges) and then manually comparing case totlas to arrive at a variance.

Is there a way to do this, perhaps via formula, by running the report only once, with the date ranges "1/1/06 to 8/31/06" and 1/1/07 to 8/31/07".

I am using Crystal Reports 10.
Thanks in advance for any suggestions. Jonathan

 

[lbass]

If you don't have row inflation and if you are using a record selection formula that just selects for those two periods, then you could create a formula like this:

//{@2006}:
if year({table.date}) = 2006 then 1

//{@2007}:
if year({table.date}) = 2007 then 1

Add these as summaries in your crosstab, using SUM as the summary, and use doctor as the row field.

Then create a formula to act as a holder {@0}:
whilereadingrecords;
0

Add this as your third summary. Then select sum of {@2006} in the crosstab->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar yr2006 := currentfieldvalue;
false

Repeat for sum of {@2007}, changing the variable name to yr2007. Then select {@0} and right click->format field->common tab->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar yr2006;
numbervar yr2007;
totext((yr2007-yr2006)%yr2006,2)+"%"//2 for 2 decimals

This assumes that you want a percentage change as your result.

If your cases appear multiple times per doctors, then the solution would be slightly different.

-LB

 

[Jonathan212]

Thanks LB.
Your first sentence is a big clue to me. I don't have row inflation, but I don't believe I have record selection that just selects for those two time periods - I was not aware I could do that.

If my selection formula was as follows:

{pcmCase.status} = 3 and
{pcmORroom.orName} startswith ["A", "H", "O"] and
{pcmCase.surgDate} in DateTime (2006, 01, 01, 00, 00, 00) to DateTime (2006, 08, 31, 00, 00, 00) and
in DateTime (2007, 01, 01, 00, 00, 00) to DateTime (2007, 08, 31, 00, 00, 00)

Status of 3 just means the case was completed and A, H and O are names of operating rooms names.

When I run the report using the above selection formula, I get no results. Am I missing some syntax such as a [ or ( in various places?

I can certainly do a range of 1/1/06 to 8/31/07 but I did not wish to include cases done from 9/1/06 to 12/31/06. Perhaps if I do exactly as you recommended in your response it would accomplish it - but I did not want to proceed without this first clarification.

Jonathan

 

[lbass]

That should be:

{pcmCase.status} = 3 and
{pcmORroom.orName} startswith ["A", "H", "O"] and
[red]([/red]
{pcmCase.surgDate} in DateTime (2006, 01, 01, 00, 00, 00) to DateTime (2006, 08, 31, 00, 00, 00) [red]or[/red]
{pcmCase.surgDate} in DateTime (2007, 01, 01, 00, 00, 00) to DateTime (2007, 08, 31, 00, 00, 00)
[red])[/red]

-LB

 

[Jonathan212]

I am almost there - with your expert guidance and solutions.

For the {@0} and the entry in the common tab - display string, which I have as:

whileprintingrecords;
numbervar yr2006;
numbervar yr2007;
totext((yr2007-yr2006)%yr2006,2)+"%"

I am getting a "display by zero" error message.

Any thoughts on that?

 

[lbass]

Sorry, I should have accounted for that. Change it to:

whileprintingrecords;
numbervar yr2006;
numbervar yr2007;
if yr2006 <> 0 then
totext((yr2007-yr2006)%yr2006,2)+"%" else
"--"

Or if you want it to be blank when yr2006 is zero, leave off the else clause.

-LB

 

[Jonathan212]

Thank you, thank you for your time and extraordinary effort. You are a gem. You are an expert. Much appreciated for your patience.