Implementing Your Ledger Split Dimension Entity

This post contains a view for Contoso that does something that is very important if you want to do some SQL reporting where you have to filter on some financial dimension. I frequently get asked to show x type of transactions just for BusinessUnit 22 or store 089 or site 13 and so on and so on. The easiest way to filter on this to split the DisplayValue string into its various parts based on your ledger account delimiter. There are several good articles on Google on how to do this. However, this can be slow, potentially very slow because you would have to read every transaction, split out the values then check to see if its the one you want. if not, move onto the next one. This eats up CPU time pointlessly and is a major annoyance for me because i'm rather unpatient, especially with anything SQL. So, how can we address this? This view is the first step.  First, lets cover the stuff that can just be copied/pasted

 

CREATE view [dbo].[vw_ledgerSplitDimensionEntity] as
SELECT *
FROM   (SELECT da.NAME,
               entityinstance,
               dalvv.valuecombinationrecid,
               dalvv.[partition]
        FROM   dimensionattributelevelvalueview dalvv
               JOIN dimensionattribute da
                 ON da.recid = dalvv.dimensionattribute
                    AND da.[partition] = dalvv.[partition]) dimension
       PIVOT (Max(entityinstance)
             FOR NAME IN (	STUFF GOES HERE)) piv; 


GO

 

First, this is just getting a few values from some tables and views from AX. The values that are being fetched are essentially a list of financial dimension values. The PIVOT statement below the SELECT is critical. You can learn about PIVOT here. The elevator speech version is it takes a list and rotates it so rather than A, B, C as 3 rows in a list top to bottom, its 3 columns in a list left to right.

In the "STUFF GOES HERE" section is where we want to focus. Here we will want to place a list of all of our financial dimension names that we have seperated by a comma. The easiest way to get this info is to just copy the names from General Ledger > Setup > Financial Dimensions > Financial Dimensions. If you have Financial Dimension "Site", "Department" and "CostCenter" then you will replace "STUFF GOES HERE" with "Site, Department, CostCenter". Pay attention that site, department and CostCenter in the PIVOT aren't quoted or anything like that. Odd for SQL but how PIVOT is designed.

If you have any questions, please let me know. Also, it would be a good idea to get VM with Contoso data because that data set has at least one basic data element, in a general sense, for all the different functional areas in AX with multiple configurations. You can get this on PartnerSource, CustomerSource and LCS (I think).