Creating SSRS report using ‘Query’ and ‘Auto Design’

In this example we will create a SSRS report that shows all the records in ‘VendTable’

1. Create a new query.

     a) Create a new query “SSRS_VendTable” in the AOT and add “VendTable” as the data source and rename it to “VendTable_DS”.

s1

     b) Now set the “Dynamic” property of ‘Fields’ to “Yes” (in order to include all the fields from the table).

s2

You can manually select the fields from the table “VendTable” and drag and drop them to the data source “VendTable_DS”.

     c) Set the range of the query.

The main purpose of setting a range is to filter the data that is to be displayed on the report. Let set field “AccountNum” as range

s3

2. Create a new report project: The next step is to create a SSRS report that will use this query to display the records from “VendTable”.

     a) Launch Visual Studio, click on “File” menu and select “New >> Project”. It will open the following dialog box:

s4

Here select the option “Report Model” under “Microsoft Dynamics AX” and specify the name of the project “SSRS_VendTable” and press “OK”. This will create a new SSRS project.

     b) Add “Report” to the newly created project by referring the screenshot below.

s5

     c) Add the dataset to the newly added report.

s6

Rename the dataset to “VendTable”.

     d) Right click on the dataset “VendTable” and select properties.  When you select the property “Query” a dialog box will pop up.

This dialog box will list all the queries in AOT. Select the query “SSRS_VendTable” and click on “Next”.

s7

The next dialog box will list all the fields in the query “SSRS_VendTable”. Click on “All the fields” and select “OK”. You can manually select the fields that you want to include in this report.

s8

     e) Add “Design” to the report.

In SSRS reports there are two kinds of designs “Auto Design” and “Precision Design”. We generally use “Precision Design” when the report is complex in nature.

So continuing with our example, simply select the dataset “VendTable” and drag and drop it on the “Design” node, it will create a new “Auto Design”.

 s9

Change the name of the design to “Design”.  Here we need to set an important property Layout Template set it to “ReportLayoutStyleTemplate”.

3. Our report is now complete. In order to view the report right click on report and select “Preview”

s10

 You will get the following screen:

s11

In the step “1.c” we specified “AccountNum” as the query range. So click on “Select” and specify the range of the query. Now click on “Report” you will be able to view the report:

s12

Written By: Shikha Satija (Dynamics AX Team, Cipher Dynamics) 

Install Data Migration (Import\Export) Framework for Microsoft Dynamics AX

The Microsoft Dynamics AX 2012 Data Import/Export Framework is an extension that helps you export data and import it into Microsoft Dynamics AX. Examples of the data that you can import include master data, open stock, and balances.

Architecture:

1

Install DMF step by step:

 1.  Download the installation package from Microsoft Information source\Partner source for the Data Import/Export Framework, DataImportExportFramework.zip, and extract it to a local folder & execute the Set up, right-click Setup.exe, and click Run as administrator.

2

 2. The installation process is split into 3 main components :

a)       A component must be installed on a server running MS SQL Integration Services.

b)       A component must be installed on a server running AOT.

c)       A component must be installed on a workstation having the Dynamics AX client

3

 3. The wizard, the installer will check if you have the minimal requirement.

4

  4. Specify a user account to run the Data Import/Export Framework service. Enter the user account of AOS service.

5

 5. Click Next & the following components will be installed

6

 6. After all of the components have been installed. Now Import the Model file from a command line using AXUTIL. The modal file is located inside the folder of the client component.

Note that there is a matching folder in “C:\Program Files\ Microsoft Dynamics AX 2012 Data Import Export Framework Client Component” for each supported version of Dynamics AX.

  • AX 2012 -> Folder 2012.
  • AX 2012 Feature Pack -> Folder 2012 FP.
  • AX 2012 R2 -> Folder 2012 R2.

7

 For Dynamics AX 2012 R2 execute in command prompt: axutil import /file:”C:\Program Files\Microsoft Dynamics AX 2012 Data Import Export   Framework Client Component\2012 R2\Model\DataImportExportFramework.axmodel”

8

9

 7. After the model files have been imported, Restart the AOS Service

10

 8. Start the Dynamics AX Client, a dialog box pops-up “Model store has been modified”, click Compile and synchronize and press OK.

11

 9. When the synchronization is completed, click Compile into .Net Framework CIL.

 10. If the dialog box does not open by itself, follow these steps:

    1. a) Compile the application from System administration > Periodic > Compile.

              b) Click System administration > Periodic > Database > SQL administration. On the Table actions menu, click Synchronize database.

 11. Compile into .NET CIL from System administration > Periodic > Compile.

 12. After the model has been compiled into .NET CIL, the Data Import/Export Framework button is added to the navigation pane.

12

13

Written By: Neha Gupta (Dynamics AX Team, Cipher Dynamics)

Walk through: Microsoft Office Add-in for Microsoft Dynamics AX

Excel and Word add-ins are used to view, analyze, and share information in Microsoft Dynamics AX. Bidirectional interoperability enables users to access and refresh data in Microsoft Dynamics AX without leaving Excel.

  • The Office Add-ins for Microsoft Dynamics AX 2012 allows business users to work with data in the tools they are familiar with i.e. MS Office.
  • The use of the Office Add-ins for Dynamics AX 2012 will allow users flexibility for tasks such as reporting, customized documents for different customers and more, all from within Microsoft Excel or Microsoft Word 2010.
  • Data import can also be done with Office Add-ins.

 Install the Office Add-Ins

In Microsoft Dynamics AX Setup, Select Components click Office Add-ins. When users select to install the Office add-ins, the Remote Desktop Services integration component is selected automatically. Click “Next” to complete the installation wizard.

AX_PB1_1

When users installs the Office Add-ins, a new Microsoft Dynamics AX contextual tab is created on the ribbon in Excel and Word. Users can then use the controls on this tab to create and refresh data in an Excel spreadsheet or a Word document. Options like, Add data (for selecting service/ Query), Add Table (here you can select Tables from AX). You will also find option for publishing the data to and from Dynamics AX.

AX_PB1_2

 How to work with Excel Add-in

Fetch the Existing Data

  1. Open an Excel spreadsheet.
  2. Click the “Connection” button, from the ribbon, and make sure we are in the company account and instance we want to work on. 

AX_PB1_33. Next click on “Add Data” >> “Add Tables” to select an existing table & update data in it.

Here select table for customer group i.e. “CustGroup”.

AX_PB1_4

4. Click on create worksheet; this will create a new worksheet for each of the selected tables.

AX_PB1_5

 5. Click on OK button, it will create a new worksheet i.e. shown below,

AX_PB1_6

6. Click on “Field chooser” to add or remove fields to the worksheet, from the table on the left side. This can be achieved using either double click or drag/drop of any field will add a column binding (Insert as Column) to the location in focus.

AX_PB1_7

 7. Now click on “Field chooser” again, this will hide the field’s selection pane & will enable the “Refresh All” button. Select “Refresh All” will update the worksheet with the existing data from the selected entity of Dynamics AX.

AX_PB1_8

 Customer group

AX_PB1_9

 Add/Modify the Existing data through Dynamics AX Add-In

  1.  Modify customer group table by adding two new rows & publish the content. 

AX_PB1_10

2. Click on “Publish Data” & select “Publish options”, set it up as per the requirement & Click OK.

AX_PB1_11

Publish Data

  • Publish interactively – synchronous data publish
  • Publish to folder – write to a file, and use AIF services to import the data.
  • Track changes – when enabled, read, update, and delete supported.  Disabled, created records only.
  • Order of publishing – use when dependencies between order of objects in service or tables.

3. Click the publish button to insert/update records in AX.

4. Once published, it adds a new worksheet “Dynamics AX Status” to track the status.

AX_PB1_12

5. Now , go to Dynamics AX, select the legal entity , in this case I am selecting CEU , also select Account Receivable>>Set up>> Customer group, and check for records that were inserted with the help of Excel Add-In

AX_PB1_13

Result: Records have been successfully updated in AX with the help of Excel Add-In.

Written By: Neha Gupta (Dynamics AX Team, Cipher Dynamics)