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

SSRS Report values of Company Heading , Page Numbers, Date And Time Printing and Row Numbers

Hi Friends,

Today we will use simple ways to print on the textbox

Company Information
=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.PostDataMethodEvaluation(Microsoft.Dynamics.Framework.Reports.DataMethodUtility.
UpdateAxContextPartition(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, Parameters!AX_RenderingCulture.Value, Parameters!AX_PartitionKey.Value),Microsoft.Dynamics.Framework.Reports.DataMethodUtility.GetFullCompanyNameForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value))

Page Numbers On the Header Section

=System.String.Format(Labels!@SYS182566, "" & Globals!PageNumber & "", "" & Globals!TotalPages & "")

Date And Time Printing on the Header Section

=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.PostDataMethodEvaluation(Microsoft.Dynamics.Framework.Reports.DataMethodUtility.UpdateAxContextPartition(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, Parameters!AX_RenderingCulture.Value, Parameters!AX_PartitionKey.Value),Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "d", Parameters!AX_RenderingCulture.Value)) & vbCrLf & Microsoft.Dynamics.Framework.Reports.DataMethodUtility.PostDataMethodEvaluation(Microsoft.Dynamics.Framework.Reports.DataMethodUtility.UpdateAxContextPartition(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, Parameters!AX_RenderingCulture.Value, Parameters!AX_PartitionKey.Value),Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "t", Parameters!AX_RenderingCulture.Value))

To Print the row numbers in the Grid

=RowNumber(Nothing)

Vivek Chirumamilla

C# code to use in Ax for fetching records

Hi Friends,

Here is the C# sharp code to login and retreive records in Ax

using System;
using System.Data;
using System.Security.Permissions;
using Microsoft.Dynamics.Framework.Reports;
using System.Data.SqlClient;

public partial class test
{

[DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
public static System.Data.DataTable DataMethod1()
{

System.Data.DataTable customers2 = new DataTable();
string queryString = "SELECT * FROM dbo.CustTable";

SqlConnection connection = new SqlConnection();
connection.ConnectionString =
"Data Source=@server;" +
"Initial Catalog=@DB;" +
"Integrated Security=SSPI;";

SqlCommand cmd = new SqlCommand(queryString, connection);

connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

return dt;


}

}




Vivek Chirumamilla

Financial Dimension values in Ax 2012

Hi Friends ,

In Ax Financial Dimensions are widely used. In Ax 2012 these dimensions are not at all individual fields, all these Financial dimensions are combined into a single field Default Dimension. This default dimension carry reference to a set of Financial dimension.

The question is how do we get single value of the dimension or all the set of values using this financial dimensions. Here is the sample code which gives the values of financial dimensions.

public static SysDim DefaultDimensionValue(DimensionDefault _dimensionDefault, Description _nameofDimension)
{

DimensionAttributeValueSetStorage dimensionStorage;
Counter i;
SysDim nameofDimension;


// Get dimension storage
if (_dimensionDefault)
{
dimensionStorage = DimensionAttributeValueSetStorage::find(_dimensionDefault);

for (i=1 ; i<= dimensionStorage.elements() ; i++)
{
if (DimensionAttribute::find(dimensionStorage.getAttributeByIndex(i)).Name == _nameofDimension)
{
nameofDimension= dimensionStorage.getDisplayValueByIndex(i);
break;
}
else
{
nameofDimension= '';
}
}
}

return nameofDimension;
}

The above sample code gives values dimension value if you have default dimesion and name of Dimension. If you want all the values just tweak this code a little bit.

Vivek Chirumamilla

Address System in Ax 2012 R3

Hi Friends,

Ax 2012 has a complex Address when compared to the previous versions of Ax.

In this address is not directly linked to the Customer or Vendor directly.

Customer or Vendor has Party which is linked to the DirParty.

DirParty Contains the Primary postal address reference to Logistics Location RecId which is reflected in LogisticsPostalAddress.

In short Dirparty contains the value of Location for searching address in LogisticsPostalAddress.

Vivek Chirumamilla