Skip to Content
0
Mar 10, 2021 at 10:04 PM

what is the best way to create a data source for CR with this SQL query

18 Views

I was given this query and I have to find a way to make it into a data source in Crystal Reports. The syntax is correct when it is running as a query. I get the expected results.

However I can't seem to get the syntax right to create a view or a stored procedure. I haven't created views or stored procedures with variables before.

_________________________________________________________________

EM Depreciation Forecast for a 10 year fiscal year period Requested by user base FILTER CRITERA - include inactive equipment (these are included by default since we are not filtering them out) - include disposed assets (these are included by default since we are not filtering them out) - do not include equipment with no assets (the join to [table] eliminates these rows) - do not include assets with no schedule (the join to [table] eliminates these rows --REPORT PARAMETERS DECLARE @EMCo tinyint = 28 DECLARE @FYStart smallint = 2021 --starting fiscal year; must be 4 digit year DECLARE @BegGLAcct varchar(20) = '' DECLARE @EndGLAcct varchar(20) = '' DECLARE @BegCat varchar(10) = '' DECLARE @EndCat varchar(10) = '' DECLARE @BegEquip varchar(10) = '' DECLARE @EndEquip varchar(10) = '' --REPORT FORMULAS DECLARE @LastDeprDt date --last month depreciated; is found in [defined field] select @LastDeprDt = DeprLstMnthCalc from EMCO WHERE EMCo = @EMCo --determine fiscal years; we create 11 so we have an ending date for the 10th year --these can be used as column headers on the report DECLARE @FYYr1 smallint = @FYStart DECLARE @FYYr2 smallint = @FYYr1 + 1 DECLARE @FYYr3 smallint = @FYYr2 + 1 DECLARE @FYYr4 smallint = @FYYr3 + 1 DECLARE @FYYr5 smallint = @FYYr4 + 1 DECLARE @FYYr6 smallint = @FYYr5 + 1 DECLARE @FYYr7 smallint = @FYYr6 + 1 DECLARE @FYYr8 smallint = @FYYr7 + 1 DECLARE @FYYr9 smallint = @FYYr8 + 1 DECLARE @FYYr10 smallint = @FYYr9 + 1 DECLARE @FYYr11 smallint = @FYYr10 + 1 --determine actual FY beginning dates based on Fiscal years DECLARE @FYBegDt1 date = datefromparts(@FYYr1 -1, 10, 1) DECLARE @FYBegDt2 date = datefromparts(@FYYr2 -1, 10, 1) DECLARE @FYBegDt3 date = datefromparts(@FYYr3 -1, 10, 1) DECLARE @FYBegDt4 date = datefromparts(@FYYr4 -1, 10, 1) DECLARE @FYBegDt5 date = datefromparts(@FYYr5 -1, 10, 1) DECLARE @FYBegDt6 date = datefromparts(@FYYr6 -1, 10, 1) DECLARE @FYBegDt7 date = datefromparts(@FYYr7 -1, 10, 1) DECLARE @FYBegDt8 date = datefromparts(@FYYr8 -1, 10, 1) DECLARE @FYBegDt9 date = datefromparts(@FYYr9 -1, 10, 1) DECLARE @FYBegDt10 date = datefromparts(@FYYr10 -1, 10, 1) DECLARE @FYBegDt11 date = datefromparts(@FYYr11 -1, 10, 1) --uncomment to display formula values when testing query in editor select @EMCo as EMCo, @FYStart as FYStart, @LastDeprDt as LastDeprDt ,@FYYr1 as FYYr1, @FYBegDt1 as FYBegDt1 ,@FYYr2 as FYYr2, @FYBegDt2 as FYBegDt2 ,@FYYr3 as FYYr3, @FYBegDt3 as FYBegDt3 ,@FYYr4 as FYYr4, @FYBegDt4 as FYBegDt4 ,@FYYr5 as FYYr5, @FYBegDt5 as FYBegDt5 ,@FYYr6 as FYYr6, @FYBegDt6 as FYBegDt6 ,@FYYr7 as FYYr7, @FYBegDt7 as FYBegDt7 ,@FYYr8 as FYYr8, @FYBegDt8 as FYBegDt8 ,@FYYr9 as FYYr9, @FYBegDt9 as FYBegDt9 ,@FYYr10 as FYYr10, @FYBegDt10 as FYBegDt10 ,@FYYr11 as FYYr11, @FYBegDt11 as FYBegDt11 select dp.Equipment, dp.Asset, em.Manufacturer as Make, em.Model, em.ModelYr as Year ,em.[Description] as EquipDesc, em.Department, dp.PurchasePrice ,dp.DeprExpAcct, ac.[Description] as DeprExpAcctDesc ,em.Category, cm.[Description] as CategoryDesc ,cast(dp.PurchasePrice / dp.NoMonthsToDepr as decimal(10,2)) as MonthlyDeprAmt ,ds.FYYr1Amt, ds.FYYr2Amt, ds.FYYr3Amt, ds.FYYr4Amt, ds.FYYr5Amt, ds.FYYr6Amt ,ds.FYYr7Amt, ds.FYYr8Amt, ds.FYYr9Amt, ds.FYYr10Amt, ds.FY10YearTotal from EMDP dp join EMEM em on em.EMCo = dp.EMCo and em.Equipment = dp.Equipment join EMDM dm on dm.EMCo = em.EMCo and dm.Department = em.Department left join GLAC ac on dp.GLCo = ac.GLCo and dp.DeprExpAcct = ac.GLAcct left join EMCM cm on em.EMCo = cm.EMCo and em.Category = cm.Category --this query sums up annual amounts by asset by fiscal year join ( select ds2.EMCo, ds2.Equipment, ds2.Asset ,sum(case when ds2.[Month] >= @FYBegDt1 and ds2.[Month] < @FYBegDt2 then dsm.Amount else 0 end) as FYYr1Amt ,sum(case when ds2.[Month] >= @FYBegDt2 and ds2.[Month] < @FYBegDt3 then dsm.Amount else 0 end) as FYYr2Amt ,sum(case when ds2.[Month] >= @FYBegDt3 and ds2.[Month] < @FYBegDt4 then dsm.Amount else 0 end) as FYYr3Amt ,sum(case when ds2.[Month] >= @FYBegDt4 and ds2.[Month] < @FYBegDt5 then dsm.Amount else 0 end) as FYYr4Amt ,sum(case when ds2.[Month] >= @FYBegDt5 and ds2.[Month] < @FYBegDt6 then dsm.Amount else 0 end) as FYYr5Amt ,sum(case when ds2.[Month] >= @FYBegDt6 and ds2.[Month] < @FYBegDt7 then dsm.Amount else 0 end) as FYYr6Amt ,sum(case when ds2.[Month] >= @FYBegDt7 and ds2.[Month] < @FYBegDt8 then dsm.Amount else 0 end) as FYYr7Amt ,sum(case when ds2.[Month] >= @FYBegDt8 and ds2.[Month] < @FYBegDt9 then dsm.Amount else 0 end) as FYYr8Amt ,sum(case when ds2.[Month] >= @FYBegDt9 and ds2.[Month] < @FYBegDt10 then dsm.Amount else 0 end) as FYYr9Amt ,sum(case when ds2.[Month] >= @FYBegDt10 and ds2.[Month] < @FYBegDt11 then dsm.Amount else 0 end) as FYYr10Amt ,sum(case when ds2.[Month] >= @FYBegDt1 and ds2.[Month] < @FYBegDt11 then dsm.Amount else 0 end) as FY10YearTotal from EMDS ds2 --this query determines monthly amount by asset join ( select EMCo, Equipment, Asset, [Month], sum(case when [Month] <= @LastDeprDt then AmtTaken else AmtToTake end) as Amount from EMDS where EMCo = @EMCo group by EMCo, Equipment, Asset, [Month] ) dsm on ds2.EMCo = dsm.EMCo and ds2.Equipment = dsm.Equipment and ds2.Asset = dsm.Asset and ds2.[Month] = dsm.[Month] where ds2.EMCo = @EMCo and ds2.[Month] >= @FYBegDt1 --we only want assets who have remaining months to depreciate based on chosen FY group by ds2.EMCo, ds2.Equipment, ds2.Asset ) ds on dp.EMCo = ds.EMCo and dp.Equipment = ds.Equipment and dp.Asset = ds.Asset where dp.EMCo = @EMCo -- and dp.DeprExpAcct between @BegGLAcct and @EndGLAcct -- and em.Category between @BegCat and @EndCat -- and em.Equipment between @BegEquip and @EndEquip ORDER BY dp.DeprExpAcct, dp.Equipment, dp.Asset