SQl Query for Financial Dimensions in Ax 2012

Hi Friends,

SQL Query for financial dimensions are often a common requirement we face.

The problem with dimensions is that there are multiple rows of a single default dimensions. Below is the query which returns a single coluumn for the Dimension

stuff((select '-'+[Displayvalue] from DIMENSIONATTRIBUTEVALUESETITEM
inner join DIMENSIONATTRIBUTEVALUESET on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET = DIMENSIONATTRIBUTEVALUESET.RECID where DIMENSIONATTRIBUTEVALUESET.RECID = SQT.DEFAULTDIMENSION FOR XML Path('')),1,1,'') AS DimDESCRIPTION


Now another point is that we often need to select one dimensions out of list of dimensions given here is the solution for it

( select [Displayvalue] from DIMENSIONATTRIBUTEVALUESETITEM
inner join DimensionAttributeValue on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DimensionAttributeValue.RECID
inner join DIMENSIONATTRIBUTE on DimensionAttributeValue.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID AND DIMENSIONATTRIBUTE.NAME = 'CostCenter'
inner join DIMENSIONATTRIBUTEVALUESET on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET = DIMENSIONATTRIBUTEVALUESET.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = SQT.DEFAULTDIMENSION ) AS DimDESCRIPTION

We can pass values to Financial Dimensions and get the required output.

Now a simple SQL Query is shown below

SELECT CASE WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 0 THEN 'Created' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 1 THEN 'Created' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 2 THEN
'Confirmed' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 3 THEN 'Created' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 4 THEN 'Created' END AS 'STATUS',
CASE WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 0 THEN 'Created' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 1 THEN 'Sent' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 2 THEN
'Confirmed' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 3 THEN 'Lost' WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 4 THEN 'Cancelled' END AS 'SubSTATUS',
month(SALESQUOTATIONTABLE.CREATEDDATETIME) AS 'Month', year(SALESQUOTATIONTABLE.CREATEDDATETIME) AS Expr1, SALESQUOTATIONTABLE.CURRENCYCODE,
SUM(SALESQUOTATIONLINE.LINEAMOUNT) AS 'Amount in Currency',
case when SALESQUOTATIONTABLE.CURRENCYCODE = 'AED' then SUM(SALESQUOTATIONLINE.LINEAMOUNT)
when SALESQUOTATIONTABLE.CURRENCYCODE <> 'AED' then SUM(SALESQUOTATIONLINE.LINEAMOUNT) *
((SELECT TOP (1) EXCHANGERATE.EXCHANGERATE/100
FROM EXCHANGERATE INNER JOIN
EXCHANGERATECURRENCYPAIR AS ERCP ON ERCP.RECID = EXCHANGERATE.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATE.VALIDFROM =

(SELECT MAX(EXCHRATES_5.VALIDFROM) AS fromdate
FROM EXCHANGERATE AS EXCHRATES_5 INNER JOIN
EXCHANGERATECURRENCYPAIR ON EXCHANGERATECURRENCYPAIR.RECID = EXCHRATES_5.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATECURRENCYPAIR.FROMCURRENCYCODE = 'AED')AND (EXCHANGERATECURRENCYPAIR.TOCURRENCYCODE = SALESQUOTATIONTABLE.CURRENCYCODE)
) )))
end AS 'Amount in Local Currency',

case when SALESQUOTATIONTABLE.CURRENCYCODE = 'USD' then SUM(SALESQUOTATIONLINE.LINEAMOUNT)
when SALESQUOTATIONTABLE.CURRENCYCODE <> 'USD' then (SUM(SALESQUOTATIONLINE.LINEAMOUNT) *
((SELECT TOP (1) EXCHANGERATE.EXCHANGERATE/100
FROM EXCHANGERATE INNER JOIN
EXCHANGERATECURRENCYPAIR AS ERCP ON ERCP.RECID = EXCHANGERATE.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATE.VALIDFROM =

(SELECT MAX(EXCHRATES_5.VALIDFROM) AS fromdate
FROM EXCHANGERATE AS EXCHRATES_5 INNER JOIN
EXCHANGERATECURRENCYPAIR ON EXCHANGERATECURRENCYPAIR.RECID = EXCHRATES_5.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATECURRENCYPAIR.FROMCURRENCYCODE = 'USD')AND (EXCHANGERATECURRENCYPAIR.TOCURRENCYCODE = SALESQUOTATIONTABLE.CURRENCYCODE)
) )))) end AS 'Amount in USD',



SALESQUOTATIONTABLE.DEFAULTDIMENSION,

( select [Displayvalue] from DIMENSIONATTRIBUTEVALUESETITEM --where DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
inner join DimensionAttributeValue on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DimensionAttributeValue.RECID
inner join DIMENSIONATTRIBUTE on DimensionAttributeValue.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID AND DIMENSIONATTRIBUTE.NAME = 'LineOfBusiness'
inner join DIMENSIONATTRIBUTEVALUESET on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET = DIMENSIONATTRIBUTEVALUESET.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = SALESQUOTATIONTABLE.DEFAULTDIMENSION ) AS DimDESCRIPTION
FROM SALESQUOTATIONTABLE LEFT OUTER JOIN
SALESQUOTATIONLINE ON SALESQUOTATIONTABLE.QUOTATIONID = SALESQUOTATIONLINE.QUOTATIONID
WHERE /* (SALESQUOTATIONTABLE.CREATEDDATETIME BETWEEN @fromDate AND @toDate) AND *//*month(SALESQUOTATIONTABLE.CREATEDDATETIME) > 1 and */(SALESQUOTATIONTABLE.DATAAREAID = 'dat') AND (SALESQUOTATIONTABLE.QUOTATIONSTATUS <> 2) AND
(SALESQUOTATIONLINE.DATAAREAID = 'dat')
GROUP BY SALESQUOTATIONTABLE.QUOTATIONSTATUS, year(SALESQUOTATIONTABLE.CREATEDDATETIME),month(SALESQUOTATIONTABLE.CREATEDDATETIME), SALESQUOTATIONTABLE.CURRENCYCODE,
SALESQUOTATIONTABLE.DEFAULTDIMENSION

UNION
SELECT 'Confirmed' AS 'STATUS', 'Confirmed' AS 'SubSTATUS', month(SQT.CONFIRMDATE) AS 'Month',year(SQT.CONFIRMDATE) AS 'Year', SQT.CURRENCYCODE,
SUM(SQNL.LINEAMOUNT) AS 'Amount in Currency',
case when SQT.CURRENCYCODE = 'AED' then SUM(SQNL.LINEAMOUNT)
when SQT.CURRENCYCODE <> 'AED' then SUM(SQNL.LINEAMOUNT) *
((SELECT TOP (1) EXCHANGERATE.EXCHANGERATE/100
FROM EXCHANGERATE INNER JOIN
EXCHANGERATECURRENCYPAIR AS ERCP ON ERCP.RECID = EXCHANGERATE.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATE.VALIDFROM =

(SELECT MAX(EXCHRATES_5.VALIDFROM) AS fromdate
FROM EXCHANGERATE AS EXCHRATES_5 INNER JOIN
EXCHANGERATECURRENCYPAIR ON EXCHANGERATECURRENCYPAIR.RECID = EXCHRATES_5.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATECURRENCYPAIR.FROMCURRENCYCODE = 'AED')AND (EXCHANGERATECURRENCYPAIR.TOCURRENCYCODE = SQT.CURRENCYCODE)
) )))
end AS 'Amount in Local Currency',

case when SQT.CURRENCYCODE = 'USD' then SUM(SQNL.LINEAMOUNT)
when SQT.CURRENCYCODE <> 'USD' then (SUM(SQNL.LINEAMOUNT) *
((SELECT TOP (1) EXCHANGERATE.EXCHANGERATE/100
FROM EXCHANGERATE INNER JOIN
EXCHANGERATECURRENCYPAIR AS ERCP ON ERCP.RECID = EXCHANGERATE.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATE.VALIDFROM =

(SELECT MAX(EXCHRATES_5.VALIDFROM) AS fromdate
FROM EXCHANGERATE AS EXCHRATES_5 INNER JOIN
EXCHANGERATECURRENCYPAIR ON EXCHANGERATECURRENCYPAIR.RECID = EXCHRATES_5.EXCHANGERATECURRENCYPAIR
WHERE (EXCHANGERATECURRENCYPAIR.FROMCURRENCYCODE = 'USD')AND (EXCHANGERATECURRENCYPAIR.TOCURRENCYCODE = SQT.CURRENCYCODE)
) )))) end AS 'Amount in USD',


SQT.DEFAULTDIMENSION,
( select [Displayvalue] from DIMENSIONATTRIBUTEVALUESETITEM --where DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
inner join DimensionAttributeValue on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DimensionAttributeValue.RECID
inner join DIMENSIONATTRIBUTE on DimensionAttributeValue.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID AND DIMENSIONATTRIBUTE.NAME = 'LineOfBusiness'
inner join DIMENSIONATTRIBUTEVALUESET on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET = DIMENSIONATTRIBUTEVALUESET.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = SQT.DEFAULTDIMENSION ) AS DimDESCRIPTION
FROM SALESQUOTATIONTABLE AS SQT LEFT OUTER JOIN
SALESQUOTATIONLINE AS SQNL ON SQT.QUOTATIONID = SQNL.QUOTATIONID
WHERE /*(SQT.CONFIRMDATE BETWEEN @fromDate AND @toDate) AND*/(SQT.DATAAREAID = 'dat') AND (SQT.QUOTATIONSTATUS = 2) AND (SQNL.DATAAREAID = 'dat')
GROUP BY SQT.QUOTATIONSTATUS, month(SQT.CONFIRMDATE),year(SQT.CONFIRMDATE), SQT.CURRENCYCODE, SQT.DEFAULTDIMENSION



Vivek Chirumamilla