Data Link Request

4.15K viewsGeneral Discussion
0

I have models with many data links with I use SQL to extract data using the most recent closed month. Line two in the code. Most hits against datasets have the same method being used (15 data sets in general). It is a pain in the #%@ to edit these one at a time every month. Please add a pipe from a selection cube. (or tell me how to do it)

Sample Code as follows

DECLARE @MO int
SET @MO = 5

SELECT
D.””Company””,
D.””Short_MO””,
D.””FiscalMonthNumber””,
D.””type””,
D.””value””,
D.””YTD/YTG””,

Case
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” <= 2013 Then D.””YearName””+’ Act’
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” <= @MO Then ‘2014 Act/Fcst’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” > @MO Then ‘2014 Act/Fcst’
When D.””VersionKey”” = ‘PLANFINAL’ AND D.””YearName”” = 2014 Then ‘2014 Bud’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2015 Then ‘2015 Plan’
END as [Year],
Case
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” <= 2013 Then ‘Act/FCST’
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” <= @MO Then ‘Act/Forecast’
When D.””VersionKey”” = ‘PLANFINAL’ AND D.””YearName”” = 2014 Then ‘BUD’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” > @MO Then ‘Act/Forecast’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2015 Then ‘Plan’
END as [Version]

FROM
“”dbo””.””13-budget”” as “”D””

Where
D.””VersionKey”” in (‘ACT’, ‘FCST01’, ‘PLANFINAL’)
AND
( Case
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” <= 2013 Then D.””YearName””
When D.””VersionKey”” = ‘Act’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” <= @MO Then ‘2014’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2014 AND D.””FiscalMonthNumber”” > @MO Then ‘2014’
When D.””VersionKey”” = ‘PLANFINAL’ AND D.””YearName”” = 2014 Then ‘2014’
When D.””VersionKey”” = ‘FCST01’ AND D.””YearName”” = 2015 Then ‘2015’
END is not null
)
AND D.””YearName”” > 2011
AND D.””YearName”” < 2015

Latest Questions

Qloud Losing Formatting 4 Answers | 0 Votes
Meditation on timelines 3 Answers | 0 Votes