Split long text in SSRS reports on two pages

By default text in tables on Dynamics AX reports like SalesQuotation are kept together. If the text does not fit on the actual page, the complete line starts on the next page. This wastes lot of space and produces unnecessary many pages.

image

To split text in the SalesQuotation follow these steps:

  1. Start Visual Studio as Admin and load the SalesQuotation report from the Application Explorer
  2. Open the report design
  3. Add a new line under the line that contains [ItemId], [Name], [DlvDate] etc.image
  4. Assign the same fields in the new line, but don’t assign the [Name] field
    image
  5. Select the new line and set the property KeepTogether to False
  6. Drag&Drop a textfield form the toolbox on an empty space in the report and name it textboxName
    image
  7. Drag&Drop the textfield in the empty column in the new line.
  8. Make sure the textfield “textboxName” is still selected and open the properties window. Set the field [Name] as value for the textfield
    image
  9. Change the Font and Size to fit the other fields, by default its Segoe UI 8pt
  10. Finally mark the old line above and set the property Hidden to True
  11. Deploy the report

image

Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page

image

View Sales Data on Map in Excel 2013

Excel provides great BI features for end users and professionals. Loading, transforming and presenting data can easily be done with PowerView and PowerMap. This is an example to visualize Dynamics AX sales data.

Dynamics AX sales data in PowerMap

Prerequisites

Provide Data via ODataFeed

  1. Open Dynamics AX 2012 R2 development workspace
  2. Create a new Query called ERPCustInvoiceJour
  3. Add the CustInvoiceJour as datasource, set the Fields Dynamic property to false
  4. Add InvoiceAmount, InvoiceDate and CustGroup to the field list
  5. Add the LogisticsPostalAddress
  6. Add the CountryRegionId and City to the field list
  7. Set the Relations property to No
  8. Add a new relation manually, clear the Field and Related Field property but select InvoicePostalAddress_FK

image

In Dynamics AX Application workspace go to Organisation Administration > Setup > Document Management > Document Data Sources. Create a new record for module “Sales and Marketing”, type Query Reference for ERPCustInvoiceJour query. Enable the data source.

image

Open the ODataFeed in a browser, depending on the server name and AOS Port it should look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPCustInvoiceJour

image

Enable PlugIns

  1. Open Excel and go to File > Options > Add Ins > COM Add-ins > Go…
  2. Enable PowerPivot, PowerView and PowerMap

image

          Create Map

        In Excel go to DATA > From other sources > Data Feed > provide the URL from Dynamics AX data feed. Load the data in Excel. go to INSERT > Map. Set the LogisticsPostalAddress_City as Geography field and click next.

      image

      Leave the Type as Column. Set the CustInvoiceJour_InvoiceAmount as Height for the Column. Set the CustInvoiceJour_CustGroup as Category and CustInvoiceJour_InvoiceDate as Time.

      image

      Run the map time line and watch where and when sales takes place. Watch the implementation in this short video

      Create a PowerPivot BI Application for AX Project Postings (Update)

      This is an update on the first posting how to create a PowerPivot Application for Dynamics AX. These are the changes and updates:

      • Excel 2013
      • XML Data Feeds instead of SQL
      • Timeline Filter instead of calculated fields

        Create and Publish Data Access Services

        You can publish Dynamics AX queries as open data feed. In contrast to direct SQL access it gives you more control on what is published because the builtin Dynamics AX security layer is used. Furthermore the data is delivered as XML and can easily be processed by other applications  Create three queries using these datasources and fields:

        Datasource            
        ProjTable ProjId Name        
        ProjJournalTrans ProjId CategoryId Txt Qty Transdate Worker
        HCMWoker join
        DirPerson
        Person RecId from HCMWorker Name      

      HcmWorker join DirPerson

      Go to Organization Administration > Setup > Document Management > Document data sources. For each of the queries create a new entry using Module “Project” and Type “Query Reference”. Choose your query from the dropdown and activate them.

      Document Data Sources

      You can check your services by opening the ODataService URL in your Browser. Depending on the name of the Server and Ports it might look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService. You should see three available data services.

      ODataFeed collections

      You can load the data by adding /COLLECTION_NAME to the URL. For example http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPProjTable to view the data from the ERPProjTable collection based on the ERPProjTable query.

      Projtable ODataFeed

      Build the Data Model

      PowerPivot and PowerView reports are builtin with Excel 2013. However, these addins need to be activated manually. Go to File > Options > Addins > Dropdown COM Addins. Activate PowerPivot and PowerView.

      Active PowerPivot and PowerView

      Go to PowerPivot Tab > Manage > Get External Data From Data Service. In the wizard copy the data service URL (without collection name) and click next. Select the data feed collections and finish the import

      Import Dynamics AX ODataFeed in Excel

      Switch to the Diagram View using the small button on the right bottom. Create the relations between the tables by drag and dropping the related fields. ProjTable.ProjId on ProjJournalTrans.ProjId and ProjJournalTrans.Worker on HcmWorker.RecId

      PowerPivot 2013 Relations

      Switch back to Excel. From the PowerPivot tab, select Calculated Fields and create 2 new calculated fields. One for the Qty sum and another sum of postings on project Holiday and Illness.

      • sum(ERPProjJournalTrans[ProjJournalTrans_Qty])
      • CALCULATE(sum(ERPProjJournalTrans[ProjJournalTrans_Qty]);
        ERPProjTable[ProjTable_ProjId] = "50002" || ERPProjTable[ProjTable_ProjId] = "50003")

       image

      Create a KPI to visualize Absence vs. Productive

      In the PowerPivot Tab, create a new KPI. Use the C_Absence as base value and the C_Total as Target value. Change the color scheme to Gree-Yellow-Red and set the sliders to 5% and 10%.

      image

      Go to Insert Tab, from the Tables Group select PowerPivot Table. In the Dialog select “External Datasource”. Switch to Tables and use Workbook Data Model.

      image

      Select the ERPWorker and drag the DirPers_Name field in the rows. Select the ERPProjJournalTrans and drag the C_Total calculated field in the Values box. Expand the C_Absence KPI. Drag the Value(C_Absence) and the Status in the values box.

      image

      Go to the Analyze Tab (only visible if the pivot table is selected), from the group Filter, create a new Timeline Filter and select the ProjJournalTrans_TransDate field. Place the Filter above the table (or wherever your want)

      image

      Create a PowerPivot BI Application for AX Project Postings

      PowerPivot is a great extension for Excel to realize BI applications in very short. For Excel 2010 download and install the free PowerPivot extension. PowerPivot ist built in with Excel 2013. To activate PowerPivot go to File > Options > Addins > Choose COM Addins > activate PowerPivot.

      PowerPivot Tab in Excel 2010

      Load Data

      Open PowerPivot Window > External Data > SQL Server. Provide Credentials to access Dynamics AX database. Select PROJTABLE, PROJJOURNALTRANS, HCMWORKER and DIRPARTYTABLE. For each table open the preview and select the following fields.

      ProjTable ProjId Name DataAreaId        
      ProjJournalTrans ProjId CategoryId Txt Qty Transdate Worker DataAreaId
      HcmWorker Person RecId          
      DirPartytable Name RecId          

      In the import wizard set a filter on the DataAreaId for PROJTABLE and PROJJOURNALTRANS on CEU

      PowerPivot Data Import

      After the tables have been imported, create relations between these tables. Back in the PowerPivot window > Relations > Manage Relations create these relations.

      • ProjJournalTrans[PROJID] to ProjTable[PROJID]
      • ProjJournaltrans[WORKER] to HcmWorker[RECID]
      • HcmWorker[PERSON] to DirPartytable[RECID]
        Data Model in PowerPivot
        In the PowerPivot window select PROJJOURNALTRANS table and on the right side after the last column select the new empty column. Write =YEAR(‘PROJJOURNALTRANS’[TRANSDATE]) in the formula text field. Rename the new calculated field to YEAR. Add a second calculated column =MONTH(‘PROJJOURNALTRANS’[TRANSDATE]) and rename to MONTH

      Calculated Field in PowerPivot

      Create the first pivot table

      Close the PowerPivot windows and return to Excel. From the PowerPivot tab create new pivot table. Place the PROJJOURNALTRANS[QTY] field in the right lower Values box. Place PROJTABLE[NAME] and PROJJOURNALTRANS[CATEGORYID] in the rows box. Place PROJTABLE[NAME] and PROJJOURNALTRANS[CATEGORYID] in the vertical slicer box. Place PROJJOURNALTRANS[YEAR] and PROJJOURNALTRANS[MONTH] in the columns box and horizontal slicer.

      Pivot Table based on Dynamics AX Data

      Create charts

      From the PowerPivot tab open the PowerPivot table dropdown, select 2 charts vertical and place them in a new sheet. For both charts place the PROJJOURNALTRANS[QTY] in the lower right values box. For the first diagram place the PROJTABLE[NAME] field in the columns box. In the second diagram place the DIRPARTYTABLE[NAME] in the rows box. Now there are two diagrams, postings per employee and postings per project. Place PROJTABLE[NAME], PROJJOURNALTRANS[CATEGORYID] and DIRPARTYTABLE[NAME] in the vertical slicer. Place PROJJOURNALTRANS[YEAR] and PROJJOURNALTRANS[MONTH] in the horizontal slicer.

      Dynamics AX Project Postings per Project and per Employee

      Connect Slicer to other sheets

      Select the YEAR slicer, from the context menu open the pivot table connections. Connect the slicer to the other pivot table created earlier. By connecting the slicer a selection made on one sheet also effects the other sheet.

      PowerPivot Slicer

      Create Measures and define a KPI

      From the PowerPivot tab, PowerPivot table dropdown create a new simple pivot table on a new sheet. Place the DIRPARTYTABLE[NAME] field in the rows box. Place the PROJJOURNALTRANS[YEAR] in the horizontal slicer On the fieldlist select the PROJJOURNALTRANS table and create a new measure from the PowerPivot tab. Call the measure M_TOTAL and use the formula  =sum(PROJJOURNALTRANS[QTY])

      PowerPivot Measure posted Qty

      Create a second measure on the PROJJOURNALTRANS table called M_ABSENCE. This will be the sum of all postings made on project HOLIDAY (PROJID 50002) and ILLNESS (PROJID 50003). Use the formula
      =CALCULATE(sum([QTY]);PROJJOURNALTRANS[PROJID] = "50002" || PROJJOURNALTRANS[PROJID] = "50003") .

      PowerPivot Measure posted Qty on Holiday or Illness

      On the fieldlist, select the measure M_ABSENCE in the PROJJOURNALTRANS table. From the PowerPivot tab, create a new Key Performance Indicator (KPI) based on the M_ABSENCE Measure. Select the M_TOTAL as target. Choose green > yellow > red color schema. Define green to be from 0 – 5 percent, yellow from 5 to 10 percent and red to all above.

      PowerPivot KPI

      The pivot table with KPI should look like this

      PowerPivot table with KPI