Last post Aug 01, 2014 03:18 AM by jjkk
Jul 28, 2014 10:05 AM|Paulious83|LINK
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.
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
Aug 01, 2014 03:18 AM|jjkk|LINK
you can refer to the article: