Dynamics NAVAX

In my last blog I did a demonstration of using an SQL query to build a PowerBI report for the general ledger transactions. I hard coded the RecId for the financial dimensions. It might be cumbersome to build the query manual. So here I wrote a job you could run to get the query. It will spit out an infolog which you should be able to copy and paste. static void navax_generateGLQuery(Args _args) {     DimensionAttribute  dimensionAttribute;     str                 sqlStatement, dimNameStr, dimMaxStr, name;     sqlStatement =     @'         SELECT         GeneralJournalCube.*         %1         FROM GeneralJournalCube INNER JOIN         (             SELECT             DAVC.RecId as CombinationId             %2             FROM             DIMENSIONATTRIBUTEVALUECOMBINATION DAVC INNER JOIN             DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON             DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION INNER JOIN             DIMENSIONATTRIBUTELEVELVALUE AS DAVL ON

With the recent release of Power BI desktop it has been an exciting week to play with new toys. I decided to do a quick general ledger cube using Power BI desktop. I put together some SQL scripts to resolve the dimensions. 1. Open up PowerView Desktop and select Get data and select SQL as the source. 2. Paste the following query I mashed up to get the GL transactions with the 3 financial dimensions. The RecIds I have there are based on Contoso database. In my next blog I will try to explain how to get the RecIds. SELECT     GeneralJournalCube.*,     OLAPBUSINESSUNIT,     OLAPCOSTCENTER,     OLAPDEPARTMENT FROM GeneralJournalCube INNER JOIN (     SELECT         DAVC.RecId as CombinationId ,         MAX(CASE WHEN DA.RECID = 22565421195 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPBUSINESSUNIT ,         MAX(CASE WHEN DA.RECID = 22565421197 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPCOSTCENTER ,         MAX(CASE WHEN DA.RECID = 22565421196 THEN DAV.ENTITYINSTANCE ELSE NULL END)