Data Link Request

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
2.46K 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

0

I [i:ftuozmm5]think[/i:ftuozmm5] you can use an expression in your SQL to point to a cell in a Quantrix matrix. The expression syntax is:

{Matrix*Name::itemName}

Going off the memory banks here but give it a shot.