query based on start date and end date parameters. calculate: zscore, year totals, percent change, weighted average, averages, standard deviation, and an acceptable range for the data. used for trend analysis ssrs reports.

1trendanalysisSSRS

SELECT        IBRS, IncYear, COUNT(IBRS) AS countibrs
FROM            v_WIBRS
WHERE        (TheDate >= @startdate) AND (TheDate <= @enddate) OR
                         (TheDate >= DATEADD(yy, - 1, @startdate)) AND (TheDate <= DATEADD(yy, - 1, @enddate)) OR
                         (TheDate >= DATEADD(yy, - 2, @startdate)) AND (TheDate <= DATEADD(yy, - 2, @enddate)) OR
                         (TheDate >= DATEADD(yy, - 3, @startdate)) AND (TheDate <= DATEADD(yy, - 3, @enddate)) OR
                         (TheDate >= DATEADD(yy, - 4, @startdate)) AND (TheDate <= DATEADD(yy, - 4, @enddate)) OR
                         (TheDate >= DATEADD(yy, - 5, @startdate)) AND (TheDate <= DATEADD(yy, - 5, @enddate))
GROUP BY IBRS, IncYear

5yearaveragessrs

=AVG(iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-5,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-4,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-3,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-2,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-1,Parameters!startdate.Value))), Fields!countibrs.Value,NOTHING))))))

acceptedrangessrs

=round((Sum(Fields!countibrs.Value) / CountDistinct(Fields!IncYear.Value)) - StDevP(Fields!countibrs.Value)) & "-" & round((Sum(Fields!countibrs.Value) / CountDistinct(Fields!IncYear.Value)) + StDevP(Fields!countibrs.Value))

allyearsssrs

=iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-5,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-4,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-3,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-2,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-1,Parameters!startdate.Value))), Fields!countibrs.Value,"")))))

percentchangessrs

=(ReportItems!Textbox70.value - ReportItems!Textbox60.value) / ReportItems!Textbox60.value

stddeviationssrs

=stdevp(iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-5,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-4,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-3,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-2,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-1,Parameters!startdate.Value))), Fields!countibrs.Value,NOTHING))))))

weightedaveragessrs

=SUM(iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-5,Parameters!startdate.Value))), Fields!countibrs.Value,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-4,Parameters!startdate.Value))), Fields!countibrs.Value*2,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-3,Parameters!startdate.Value))), Fields!countibrs.Value*3,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-2,Parameters!startdate.Value))), Fields!countibrs.Value*4,iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-1,Parameters!startdate.Value))), Fields!countibrs.Value*5,NOTHING))))))/1

yearssrs

=iif(Fields!incyear.Value = year((DateAdd(dateinterval.year,-5,Parameters!startdate.Value))), Fields!countibrs.Value,0)

zscoressrs

=(ReportItems!Textbox70.value - ReportItems!Textbox60.value) / ReportItems!Textbox46.value