SQL server Reporting Services (SSRS) Architecture for AX 2012

1. User requesta a report :

A menu item in the Microsoft Dynamics AX client may be bound to a report for Reporting Services. After a user clicks the menu item, a parameters form is displayed to the user. The user enters parameters to filter the data that is displayed on the report.
The Microsoft Dynamics AX client then requests the report from an instance of Reporting Services. The request includes the parameters that the user entered.

2.Reporting Services receives the request and requests the report data from the Microsoft Dynamics AX server.

Reporting Services receives the request and examines the report. The report is stored as an .rdl file. The .rdl file indicates the report’s data source. The data source may be a Microsoft Dynamics AX query, a report data provider class, or an external data source that is accessed through report data methods.
If a Microsoft Dynamics AX data source is used for the report, Reporting Services uses the Microsoft Dynamics AX data extension to retrieve the data.

Reporting Services then requests metadata about the data source from Microsoft Dynamics AX. Then Reporting Services requests the data for the report.

3.The Microsoft Dynamics AX server receives the request and sends the report data back to Reporting Services.
The Microsoft Dynamics AX services examine the query in the Application Object Tree (AOT) to return the requested metadata. The services also run the query to generate the data for the report.

Microsoft Dynamics AX then returns the metadata and data to Reporting Services.

4.Reporting Services renders the report and sends it to the Microsoft Dynamics AX client.
The Microsoft Dynamics AX customization extension formats the report. The customization extension uses metadata to provide automatic formatting of data and can affect the positioning and layout of elements on the report.
Reporting Services then renders the report into a visual representation and sends that representation to the Microsoft Dynamics AX client.

5.The report is displayed to the user.
The Microsoft Dynamics AX client displays the report to the user in the report viewer control.

Stages of Reports in Microsoft Dynamics AX 2012

Report definition

The report definition file (.rdl) stored on a report server. 

Compiled report and intermediate report format

The report that uses evaluated expressions, parameters and parameter properties evaluated.

Snapshot or Report History

A snapshot is the set of report data at a specific point in time plus the intermediate format that 

contains report layout information.

Processed report

A fully processed report that contains both data and layout information.

Rendered report

A fully processed report is sent to a report renderer to combine the data and layout on each page of the targeted rendering format. Rendering extensions are customizable and extensible.

Exported report

An exported report is a fully paged report saved in a specific file format. Export formats depend on installed rendering extensions and can be customized. By default, export formats include Excel, Word, XML, PDF, TIFF, and CSV.

Type of Reports in Microsoft Dynamics AX 2012

Drilldown reports

Drilldown reports must retrieve all possible data that can be shown in the report. For reports that use large amounts of data, consider drillthrough reports instead.

Subreports

A subreport is a report item that you add to a report as a layout element. A subreport points to a different report and displays inside the body of a main report as an subreport instance.

Main/detail reports and drillthrough reports

A main/detail report solution includes a main report that displays summary information with hyperlinks to one or more reports that display detailed information.

Linked reports

A linked report is a report server item that contains a pointer to the report definition but has its own set of report properties and settings. These include security, parameters, location, subscriptions, and schedules.

History reports

Report history is a collection of report snapshots. You can use report history to maintain a record of a report over time.

Cached reports

A cached report is a saved copy of a compiled report and report data. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time that is required to retrieve large report datasets.

Snapshots

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time.

Model reports and Clickthrough reports

(i) Report model :A report model is a user-friendly description of an underlying database, with pre-established data relationships and auto-generated queries. Report models can be used as data sources for reports created in Report Designer and Report Builder.

(ii)Clickthrough report : A clickthrough report is a report that displays related data from a report model when you click the interactive data contained in the model-based report. Clickthrough reports are autogenerated.

Saved reports

A saved report is a report definition (.rdl) file. A report definition can be saved locally or uploaded to a report server.

Published reports

A published report is a report server item that you publish to a report server from a Reporting Services tool.

  

Upgraded reports

An upgraded report is a published report definition that is converted to a newer schema when a report server is upgraded from one version of Reporting Services to a later version. The original report definition is preserved. The report is upgraded in memory, compiled, and the compiled version is saved internally.

X++ CODE TO READ WORD DOCUMENT into AX 2012

Below is the job to read text from Word document to AX 2012

static void ReadFromWord(Args _args)

{     
    str         document = "D:\\Demo Edition.doc"; //path were the word document is situated

    COM         wordApplication;

    COM         wordDocuments;

    COM         wordDoc;

    COM         range;

    TextBuffer  txtBuff = new TextBuffer();

    // Create instance of Word application

    wordApplication = new COM("Word.Application");

    // Get documents property

    wordDocuments = wordApplication.Documents();

    // Add document that you want to read

    wordDoc = wordDocuments.add(document);

    range = wordDoc.range();

    txtBuff .setText(range.text());

    // to replace carriage return with newline char

    txtBuff .replace('\r', '\n');   

   

    info(txtBuff .getText());
}

Debug Ax Code in Visual Studio in Microsoft Dynamics AX 2012

The steps involved in in debugging AX code through Visual Studio :

Step 1: 

Make sure AOS Service runs on account which you logged in. 

Step 2:

Open visual studio and click on AX Application explorer (Shortcut Ctrl + D)AOT opens and you can browse nodes as you used to do in Dynamics AX.Open the object which you want to debug and insert breakpoint.

Step 3:

Then in Visual studio, Click on Debug->Attach process

Note: As shown in above image, First make sure two check boxes at bottom are marked.Then only you can find and able to select AX32Serv.exe (AOS)  process and click on attach button.

Step 4:

You done with setting up visual studio to debug code running in CIL.
Now restrart process in Dynamics AX which you want to debug and you can find Visual studio debugger opened for you.

List of paper sizes in Microsoft Dynamics AX 2012

Below is the job to get the list of paper sizes in Dynamics AX    

Virtual Company in Microsoft Dynamics AX 2012

Dynamics Ax stores data as per company in tables. But there might be occasions when you want to share data across companies, like country, state, zip codes data. This sharing of data is achieved by creating a virtual company and storing data in this virtual company. 

The only purpose of virtual company is to share data across companies, you cannot log into this virtual company.

There is a property on Ax tables called "SaveDataPerCompany", you can use this property to save data globally in Ax. To share data set this property to "No".

Virtual Company setup:

Step 1: Create Table Collection

Decide the tables you want to share and create a table collection for these functionally related tables. For example; if you want to share Global Address Book across companies then you can utilize the existing table collection "DirPartyCollection".

Note: To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection

Create a virtual company that will hold the shared data for normal companies.
Note: You should be Ax administrator.

1. Go to Administration -- Setup -- Virtual company accounts, and create a virtual company.


2. Decide which companies needs to share data and attach those normal companies with this virtual company.


3. Attach the table collection with this virtual company.

Your Ax client will re-start and you are done with setting up the virtual company account.

Note: Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

Import data from Excel into AX in Microsoft Dynamics AX 2012

static void mynamescopyfromexcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
testTable  mynames;

int row = 1 ;

str lastname;
str firstname;
int salary;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = @"C:\Location\Name.xlsx";
 
try
{
    workbooks.open(filename);
}
catch (Exception::Error)
{
    throw error("File cannot be opened.");
}

    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

do
{
    row++;
    firstname = cells.item(row, 1).value().bStr();
    lastname = cells.item(row, 2).value().bStr();
    mynames.clear();
    mynames.Fname = firstname;
    mynames.Lname = lastname;
    mynames.insert();

    info("Data inserted succesfully");

    type = cells.item(row+1, 1).value().variantType();
}

while (type != COMVariantType::VT_EMPTY);
application.quit();
}