Percentage Calculations based on grouped calculated fields. RRS feed

  • Question

  • User2141921786 posted


    I'm using Visual Studio 2012 Shell and have a sub-report with one dataset(referrals) and various paprameters passed to it, within this dataset I calculate fields (Mth1 -> mth12) based on some date logic. Then group the details by SRO_Type. This produces two rows of numbers but I wish to have a third row that calculates the percentage of SRO's compared to NON_SRO's.  I assume this has to be done in SQL rather than calculated fields as I only have one row visible during designing because the grouping creates the two rows at run time.

    Thanks in advance.

    [Referrals] DataSet

    Declare @year1 as varchar(4) Declare @year2 as varchar(4) set @year1 = left(@ContractYear,4) set @year2 = cast(right(@ContractYear,4) as int)

    SELECT tblReportsProgramStarts.referralDate as Referrals,tblReportsProgramStarts.teamID, tblPlussTeams.teamName, CASE WHEN tblWCReferral.refType <> '4' THEN 'NON_SRO' ELSE 'SRO' END as SRO_Type

    FROM tblReportsProgramStarts join tblPlussTeams on tblPlussTeams.teamID = tblReportsProgramStarts.teamID join tblWCHeader on tblWCHeader.WcID = tblReportsProgramStarts.wcID join tblWCReferral on tblWCHeader.WcRefID = tblWCReferral.WcRefID WHERE tblWCReferral.dateOfreferral  BETWEEN @year1+'-10-25' AND @year2+'-10-24' order by Referrals, teamID

    Monday, July 28, 2014 10:05 AM

All replies