Sort on Variance Formula

[LHSCNaved]

Hi All,

SQL2005/CRXI.

I am calculating Laboratory Test Per patient for the year
2008 and 2009 by dividing (Number of Patient/Number of Lab Test Volume) and then calculating Variance like following.

Lab Test Per Patient for 2008 (@TPP2008)

if DistinctCount({@2008Case}, {@GroupOne}) > 0 then
Count ({@2008Case}, {@GroupOne})/DistinctCount({@2008Case}, {@GroupOne})
else
0

Lab Test Per Patient for 2009 (@TPP2009)

if DistinctCount({@2009Case}, {@GroupOne}) > 0 then
Count ({@2009Case}, {@GroupOne})/DistinctCount({@2009Case}, {@GroupOne})
else
0


Variance Calculation

if {@TPP2008} > 0 then
(({@TPP2009}-{@TPP2008})/{@TPP2008})*100
else
0


I would like to sort my report based the above mention Variance Calculation formula as it does not allow me to insert summary based on that formula and also it is not available Group Sort Expert.

Any help in this regard will be appreciated.

Thanks

Naved Altaf

 

[Madawc]

Crystal won't let you sort using summary values, values derived from more than one detail line.

You can use TopN to order the groups

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You would have to be able to convert your summaries to SQL expressions in order to be able to do a sort. I can't tell how complicated this would be because you haven't shown the content of your nested formulas, and I can't see what tables/fields might be involved.

-LB

 

[LHSCNaved]

Thanks for your email

SQL2005/CRXI

I am using the following view.

SELECT dbo.DIRADCactus.DG_ENCOUNTER AS Encounter, dbo.DIRAD.VisitType, dbo.DIRAD.OrderPhysician, dbo.DIRAD.PhysicanID, dbo.DIRAD.Orderable,
dbo.DIRAD.OrderableCount, dbo.DIRAD.ActivityType, dbo.DIRAD.Client, dbo.DIRADCactus.CMGPlusCode, dbo.DIRADCactus.CMGPlusDescription,
dbo.DIRADCactus.MRDrName, dbo.DIRADCactus.MRDrSrvCode, dbo.DIRADCactus.MRDrSrvDescription, dbo.DIRADCactus.MRDxCode,
dbo.DIRADCactus.MRDxDescription, dbo.DIRADCactus.AGE, dbo.DIRADCactus.LOS, DimDatesOrder.FiscalYear AS OrderFiscalYear,
DimDatesOrder.FiscalQtr AS OrderFQtr, dbo.DIRAD.OrderDate, dbo.DIRADCactus.DISDATE, dbo.DimDates.FiscalYear AS DiscFiscalYEar,
dbo.DimDates.FiscalQtr AS DiscFQtr, dbo.DIRADCactus.RIW, dbo.DIRADCactus.ELOS, dbo.DIRADCactus.Service
FROM dbo.DimDates INNER JOIN
dbo.DIRADCactus ON dbo.DimDates.FullDateConv = dbo.DIRADCactus.DisDateKey INNER JOIN
dbo.DIRAD INNER JOIN
dbo.DimDates AS DimDatesOrder ON dbo.DIRAD.OrderDateKey = DimDatesOrder.FullDateConv ON
dbo.DIRADCactus.DG_ENCOUNTER = dbo.DIRAD.Encounter


And then use the following formula in CRXI to identify Cases for the Fiscal Years.

@Case2008
if {viewDIRAD.DiscFiscalYEar} = "2007-08" then {viewDIRAD.Encounter}
else
{@NullValue0708}

@Case2009
if {viewDIRAD.DiscFiscalYEar} = "2008-09" then {viewDIRAD.Encounter}
else
{@NullValue0809}

Distinct Count gives me the number patients and counts gives the number of Lab Tests.
Later on use the formula to get total for Fiscal Years and variance like mention earlier.


Lab Test Per Patient for 2008 (@TPP2008)

if DistinctCount({@2008Case}, {@GroupOne}) > 0 then
Count ({@2008Case}, {@GroupOne})/DistinctCount({@2008Case}, {@GroupOne})
else
0

Lab Test Per Patient for 2009 (@TPP2009)

if DistinctCount({@2009Case}, {@GroupOne}) > 0 then
Count ({@2009Case}, {@GroupOne})/DistinctCount({@2009Case}, {@GroupOne})
else
0


Variance Calculation

if {@TPP2008} > 0 then
(({@TPP2009}-{@TPP2008})/{@TPP2008})*100
else
0

Kindly let me know if this is the feedback you were looking was.



Thanks and Regards

Naved Altaf

 

[lbass]

You still didn't show the content of your nested formulas, but I think you should just create the summaries as subqueries within your view. Then you can build them into formulas, and insert a summary on the formula percent formula. The group sort is ONLY activated when a summary is inserted, and you can't insert summaries on summaries, so you need to return the summaries as fields within the view.

-LB

 

[LHSCNaved]

Thanks Lbass,

I create the summaries in the view or Store Procedure and will move accordingly.

Have A Nice Day.