Base Report on % of subreport

[shelby55]

Hello

I am using Crystal 9.0 accessing an Access 97 database.

I hope I am explaining this well but it's kind of complicated. What I want to do is base a formula in a report on the % of cases fitting a description.

All cases are grouped into "like" buckets called CMGs which is the group the report is based on. The hospital also uses pathways for certain cases (not based on CMGs) so pathway entry for a case could be yes or no.

I have a field indicating "% pathway" but I only want the denominator to include CMGs that have at least 20% where pathway is either yes or no (and numerator is where pathway = "Y").

Right now I'm running a query in Access to determine % of each CMG and then hard coding a field in Crystal to use as a denominator. How can I do this via formula or subreport?

Thanks.

 

[lbass]

Create a formula {@Yes}:

if {table.pathway} = "Y" then 1

Insert a group on {table.CMG}. Then create a formula like this:

whileprintingrecords;
numbervar denom;
if sum({@Yes},{table.CMG})/count({table.CMG},{table.CMG}) >= .2 then
denom := denom + 1;

Then in the report footer, you can reference the variable for your %pathway formula:

whileprintingrecords;
numbervar denom;
{table.number}%denom

This assumes you are expecting the denominator to be a count of CMGs that meet your 20% criteria.

-LB

 

[shelby55]

Hi LB

Thanks for the speedy reply.

I understand your concept which makes sense but I want to be able to use the denominator in various lines. And I actually lied because I don't want to always have to group to CMG.

For instance:

Grouped by Qtr of Year
Qtr # cases # of cases with pathway=Y pathway %
1 300 150 150/200

In my example above the 200 represents the "eligible" cases for pathway which are ones that have either "yes" or "no" in the pathway field. Some have a blank in that field so they aren't eligible.

The theory behind this is that I don't want to under report % pathway by using all cases as denominator because if some aren't even eligible it's not correct.

Does this make sense and is it possible? Thanks.

 

[shelby55]

HI

I wanted to clarify something further: because the pathway isn't decided upon due to CMG I need to determine "eligibiity" of a specific CMG by the % of cases that are noted as pathway = yes or no. So if 20% of a CMG has pathway = yes or no then I want all of that CMG included in the denominator including the cases for that CMG that don't have any pathway indicator.

As I mentioned earlier, right now I'm running a list and finding out % of cases that are 20% or more based on total cases and those with pathway yes/no and hard coding the denominator as "if CMG in (001, 002, etc) then +1 else 0" but I wish these CMGs to be selected via formula.

Please don't hesitate to indicate if you don't think this is possible.

 

[lbass]

I can't quite follow this. Please try to provide some sample data that shows the detail level as well as the group at which you want to perform this calculation. If you need to determine the percentage per CMG, you would need to do a group on this (or possibly a SQL expression).

And what is a "CMG"?

-LB

 

[shelby55]

HI LB

Sorry it's confusing.

A CMG stands for "case mix group" and it's a way to group a case so that they can compare against themselves. Each patient has a CMG value.

chart disch date cmg pathindi
1234 Feb 9 2009 208 Y
4568 Feb 22 2009 200 N
7890 Feb 28 2009 208
5555 Mar 5 2009 196
6666 Mar 7 2009 198 Y
8888 Mar 9 2009 208

In the instance of CMG 208 only one case has pathway on it so depending on total volume of cases with yes or no then it may or may not be in denominator.

Right now I only report on # of pathways of yes for the group of CMGs that have 20% or more of pathway = yes or no. The denominator is then the yes and no but only for the specified.

 

[lbass]

Your sample data didn't show the group level where you would be doing the calculation or what you would expect the result to be for that sample data. It might also be helpful to see the content of the formula calculation for which you are trying to create the denominator.

My earlier formula should work as long as your "blanks" are in fact nulls. What happened when you tried my earlier formula? In what way was the result incorrect? I just don't have enough information--still. Sorry I lost track of this one.

-LB

 

[shelby55]

Hi LB

The reason the original format won't work is because your percent isn't based on all entries of pathway (i.e. yes or no) plus your sum value for the pathway cases is per the group of CMG but I wish to group by fiscal quarter.

chart disch date cmg pathindi
1234 Feb 9 2009 208 Y
4568 Feb 22 2009 200 N
7890 Feb 28 2009 208
5555 Mar 5 2009 196
6666 Mar 7 2009 198 Y
8888 Mar 9 2009 208
9999 Mar 15 2009 208 N

Grouped:
Qtr Total Cases % Pathway
Q4 7 33.3% (which is 2/6)

Rationale: 2 cases where an eligible CMG is "yes" and the denominator is 7 indicating eligible cases. CMG 208 had 2 cases with either yes or no and the other 2 are null but 2/4 is 50% so this CMG is included in numerator and denominator. CMG 200 is 100% for pathway eligibility as is CMG 198 but CMG 196 isn't because there isn't a Y or N.

Does that help? Thanks so much!

Shelby

 

[VKZIMM]

I am not sure what you are looking for, but if you create a subreport which calculates the value you want, you can use Shared Variables to pass the information to the main report so that it can use the value in formulas.
Shared Numbervar count

You must call you subreport before you use your shared variable, probably in the group header.

 

[shelby55]

Hi

Thanks for your suggestion. I have thought about using a subreport but I still need to determine how to come up with the denominator value in order to do so.

 

[lbass]

Can you confirm that your blanks are really nulls?

-LB

 

[shelby55]

Yes they are truly blanks and not nulls.

 

[lbass]

Try converting the blanks to nulls by using a formula like this {@pathindi}:

if isnull({table.pathindi}) or
trim({table.pathindi}) = "" then
{@null} else
{table.pathindi}

...where {@null} is a new formula that you open and close without entering anything.

Then use an adjusted version of my earlier formula:

whileprintingrecords;
numbervar denom;
if sum({@Yes},{table.CMG})/count({@pathindi},{table.CMG}) >= .2 then
denom := denom + 1;

...and then follow the earlier instructions.

-LB

 

[shelby55]

Hi LB

I'm sorry but you are still not understanding: the 20% isn't dependent on yes it is dependent on yes or no.

And why is the whileprintingrecords formula based on the group of CMG when that isn't a grouping in the report? I need to report by fiscal year, not by CMG.

I've tried your earlier formula and played with different versions of it but it is not working.

Thanks anyway.

 

[lbass]

shelby,

I think I understand now. Since you don't want to group by CMG, one solution might be to use a command that incorporates subselects, like this:

select table.`chart`,
(
select count(A.`CMG`)
from table A
where A.`CMG` = table.`CMG` and
{fn quarter(A.`disch date`)} = {fn quarter(table.`disch date`)}
) as cntbyqtr,
(
select count(A.`pathindi`)
from table A
where A.`CMG` = table.`CMG` and
{fn quarter(A.`disch date`)} = {fn quarter(table.`disch date`)} and
A.`pathindi` in ('Y','N')
) as cntYNbyqtr
from table

Link to table on the chart field. Then you could create a formula {@meetscrit}:

if {command.cntYNbyqtr}/{command.cntbyqtr} >= .2 then 1

Then create a second formula {@Yes}:

if {table.pathway} = "Y" then 1

Then {@pathway%} is:

sum({@Yes},{table.dischg date},"quarterly")%sum({@meetscrit},{table.dischg date},"quarterly")

-LB

 

[shelby55]

Hi LB

This is way above me: how do I go about making a command that subselects?

Thanks.

 

[lbass]

I'm sorry, but I've already shown you as specifically as I am able. If you are asking about where you do this, go to database->database expert->your datasource->add command (above the table lists). You would then replace the field names and "table" with your actual names. Leave the alias "A" table name as is.

-LB

 

[shelby55]

HI LB

Thanks very much but there is no "add command" in my datasource area. There is "repository" which has "commands" but nowhere to actually add a command.

I guess I'll have to figure another way - thanks anyway.

Shelby