How to Create SSRS Report with using SQL Query or SQL Procedure MS Dynamics Ax 2012 R2

There are different ways to create SSRS report in MS Dynamics Ax 2012, mostly depending upon different type of data source used for creating report. There are several ways to retrieve data for reports. One of them is to use the Report Data Provider Class or RDP Class. To process the SQL Query or Procedure, a processing class is needed. This class will get a SQL Query string as a parameter and process the string to get a desired Result Set. Though the result set is not at all like data set, get from creating data query class in business logic.

What is the need to process SQL Query or Procedure instead of X++?

Firstly, sometimes the query becomes so complex, this becomes a difficult for novice or moderate programmer to make and test the query. It is easier to make and test SQL Queries in the Management Studio for SQL Server.

Secondly, the SQL Server processes query faster than the metadata query of X++.

Please consider the following Query.

SELECT V.*, INVENTDIM.INVENTCOLORID, INVENTDIM.INVENTSIZEID, INVENTDIM.INVENTSTYLEID, INVENTTABLE.NAMEALIAS FROM (

SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, sum(fQtyO) AS fQtyO, SUM(fAmtO) AS fAmtO, sum(fQTYR) AS fQtyR, sum(fAmtR) as fAmtR, sum(fQtyI) as fQtyI, sum(fAmtI) as fAmtI

FROM

(

SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, sum(QTY) AS fQtyO, sum(COSTAMOUNTPOSTED) AS fAmtO, 0 AS fQTYR, 0 AS fAmtR, 0 AS fQtyI, 0 AS fAmtI

FROM         TRI_INVENTDIMTRANSORIGINVIEW

WHERE     (DATEPHYSICAL > CONVERT(DATETIME, '2010-04-01 00:00:00', 102)) AND (DATEPHYSICAL < CONVERT(DATETIME, '2015-04-01 00:00:00', 102))

GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID

HAVING      (INVENTLOCATIONID = N'Main Store - BLB')

UNION

SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, 0 AS fQtyO, 0 AS fAmtO, sum(QTY) AS fQTYR, sum(COSTAMOUNTPOSTED) AS fAmtR, 0 AS fQtyI, 0 AS fAmtI

FROM         TRI_INVENTDIMTRANSORIGINVIEW AS TRI_INVENTDIMTRANSORIGINVIEW_1

WHERE     (DATEPHYSICAL >= CONVERT(DATETIME, '2015-04-01 00:00:00', 102)) AND (DATEPHYSICAL <= CONVERT(DATETIME, '2015-04-30 00:00:00', 102)) AND qty>0

GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID

HAVING      (INVENTLOCATIONID = N'Main Store - BLB')

UNION

SELECT     INVENTDIMID, INVENTLOCATIONID, ITEMID, 0 AS fQtyO, 0 AS fAmtO, 0 AS fQTYR, 0 AS fAmtR, sum(QTY) AS fQTYI, sum(COSTAMOUNTPOSTED) AS fAmtI

FROM         TRI_INVENTDIMTRANSORIGINVIEW AS TRI_INVENTDIMTRANSORIGINVIEW_1

WHERE     (DATEPHYSICAL >= CONVERT(DATETIME, '2015-04-01 00:00:00', 102)) AND (DATEPHYSICAL <= CONVERT(DATETIME, '2015-04-30 00:00:00', 102))  and qty<0

GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID

HAVING      (INVENTLOCATIONID = N'Main Store - BLB')

) T

GROUP BY INVENTDIMID, INVENTLOCATIONID, ITEMID

) V

LEFT OUTER JOIN

INVENTDIM ON V.INVENTDIMID  = INVENTDIM.INVENTDIMID

LEFT OUTER JOIN

INVENTTABLE ON V.ITEMID   = INVENTTABLE.ITEMID

ORDER BY V.ITEMID

To process 171 GB of Data it takes 1 Second in SQL Server Management Studio. This is not in the case of processing X++ Query.

Thirdly, it is a bit popular among the Dot Net programmer and SSRS Report Developers.

Anyhow, the SQL Reporting Services solution for Microsoft Dynamics AX 2012 includes a data source type called Report Data Provider (RDP) which can be used to build reports that have data from an X++ class as the source. Custom business logic to be rendered in reports using predefined X++ classes. Also, can bind parameter elements to Report Definition Language (RDL) expressions using the expression editor.

For further detail can visit my blog on How to Create SSRS Report with Report Data Provider (RDP) Class in MS Dynamics Ax 2012 R2.

In this walk-through we will create, publish a report in the development server, then we will export, import and publish the report on the live server.

To do this the following set of tasks will be needed:

1.    Create a Shared Project

2.    Create a Table

3.    Create a SQL Utility Class

4.    Test the SQL Utility Class through a JOB

5.    Create a Data Contract Class

6.    Create a Report Data Provider (RDP) Class

7.    Create a reporting project in Visual Studio

8.    Create a report

9.    Build and Publish the Report

10. Export the Project from the Development and Testing Server

11. Import the Project into the Live Server

12. Re-Compile the Visual Studio Project and Publish the report with proper administrator rights to the Live Server.

Pre-Requisites

To complete this example, the followings are required:

·         Microsoft Dynamics AX with sample data

·         Microsoft Visual Studio 2010

STEP 1 - Create a Shared Project

Creating a Shared Project help in moving the reports from Development and Testing Environment to the Live servers. For this example, a shared project is defined within the Microsoft Dynamics AX development environment (AOT – can be opened by Pressing CTRL+D while running the MS Dynamics Client Application, and having proper credential).

The following procedure explains how to create a shared project.

To create a shared project

1.    Open Microsoft Dynamics AX.

2.    In the AOT, right-click the Queries node, and then click New Query.

3.    Select Project Menu Item from View Menu Group or press CTRL+SHIFT+P.

4.    Expand the Shared Folder

5.    Right Click and add a New Project, name it as zz_SNMSingleSourceVendor

STEP 2 – Create a Table

The table will hold data from the business logic class temporarily to render the data to the

SSRS report. As the approximate count of row will not be more than 1000 so an InMemory

Table Type could be used in this case.

To create a temporary Table

1.    In the AOT, Expand the Data Dictionary node, then right-click on the Table node, and then click New Table.

2.    Name the Table as zz_SNMSingleSourceVendorTmp

3.    In the TableType put InMemory Value from the dropdown.

4.       Add required Fields, by either creating as a new field or by dragging the Field name from tables which contains field (from where data is called in the business logic class)

5.       Create two fields fName having type string of 60 character, and fItem having type string 250 character.

6.       Add the newly created table to the project, by dragging the table into the project window.

STEP 3 – Create a SQL Utility Class

The SQL Utility Class will get a string as a parameter and process the string so as to get a Result Set. Please note that ResulSet is a Class in itself.

The ResultSet class provides access to a table of data generated by executing a Statement. Has different methods like cancelTimeOut, close, next, etc. The String could be a validated SQL Query Statement or name of a Procedure with Parameters.

To create a SQL Utility Class

1.    In the AOT, right-click the Classes node, and then click New Class.

2.    Name the Class as zz_SNMSQLUtility. Please note that do not create name of any class with first character as _, as it will not be processed by C# compiler while making the Visual Studio Project.

3.       Now create two methods, classDeclaration, executeQuery, as shown below.

No need to create the executeUpdate.

server static ResultSet executeQuery(str sql, Connection connection = new Connection())

{

    SqlStatementExecutePermission   sqlPerm;

    Statement                       statement;

    ResultSet                       resultSet;

    ;

    sqlPerm = new SqlStatementExecutePermission(sql);

    sqlPerm.assert();

    statement = connection.createStatement();

    resultSet = statement.executeQuery(sql);

    CodeAccessPermission::revertAssert();

    return resultSet;

}

STEP 4 – Test the SQL Utility Class through a JOB

The SQL Utility Class could be tested by creating a JOB and then running the JOB.

To create a JOB

1.    In the AOT, right-click the Jobs node, and then click New Job.

2.    Paste the following codes to test.

Listing of the Codes

static void Job44(Args _args)

{

    ResultSet       testRS;

    ;

    testRS = zz_SNMSQLUtility::executeQuery("SELECT SALESPOOLID, NAME FROM SALESPOOL");

    while(testRS.next())

    {

        info(testRS.getString(2));

    }

}

The same could be tested for a SQL Stored Procedure. The following Stored Procedure does the same as done in the SQL Query above.

STEP 5 – Create a Data Contract Class

The Data Contract Class will define the parameters, class for the parameters, and validation.

To create a Data Contract Class

1.    In the AOT, right-click the Classes node, and then click New Class.

2.    Name the Class as zz_SNMSingleSourceVendorContract. Please note that do not create name of any class with first character as _, as it will not be processed by C# compiler while making the Visual Studio Project.

3.    Now create three methods, classDeclaration, parmFromDate, parmToDate, as shown below.

Listing of the Codes

class zz_SNMSingleSourceVendorContract

{

    DatePhysical               FromDate;

    DatePhysical               ToDate;

}

[

    DataMemberAttribute('FromDate'),

    SysOperationLabelAttribute('From Date :'),

    SysOperationDisplayOrderAttribute('1')

]

public DatePhysical parmFromDate(DatePhysical _fromDate = FromDate)

{

    FromDate = _fromDate;

    return FromDate;

}

[

    DataMemberAttribute('ToDate'),

    SysOperationLabelAttribute('To Date :'),

    SysOperationDisplayOrderAttribute('2')

]

public DatePhysical parmToDate(DatePhysical _toDate = ToDate)

{

    ToDate = _toDate;

    return ToDate;

}

public boolean validate()

{

    if (!this.parmFromDate() || !this.parmToDate())

    {

        // The From date field and the To date field must be filled in.

        return checkFailed("@SYS95151");

    }

    if (this.parmToDate() && this.parmFromDate() > this.parmToDate())

    {

        // From date must be before To date

        return checkFailed("@SYS91020");

    }

    return true;

}

STEP 6 – Create a Data Report Provider (RDP) Class

The Report Data Provider (RDP) Class will get the parameters and process the data with the help of a business logic class. The result of the class will be stored into a temporary table (zz_SNMSingleSourceVendorTmp).

To create a Report Data Provider (RDP) Class

1.    In the AOT, right-click the Classes node, and then click New Class.

2.    Name the Class as zz_SNMSingleSourceVendorDP.

3.       Now create four methods, classDeclaration, get_SNMSingleSourceVendorTmp, getReportParameters, processReport as shown below. Please note the methods getReportParameters and processReport as mandatory and having specific role to render the data for SSRS, so please include these two methods and name should be as mentioned.

  

Listing of the Codes

[

   SRSReportParameterAttribute(classstr(zz_SNMSingleSourceVendorContract

))

]

public class zz_SNMSingleSourceVendorDP extends SRSReportDataProviderBase

{

    zz_SNMSingleSourceVendorTmp     SNMSingleSourceVendorTmp;

    FromDate                        fromDate;

    ToDate                          toDate;

}

[

    SRSReportDataSetAttribute(tablestr(zz_SNMSingleSourceVendorTmp))

]

public zz_SNMSingleSourceVendorTmp get_zz_SNMSingleSourceVendorTmp()

{

    select SNMSingleSourceVendorTmp;

    return SNMSingleSourceVendorTmp;

}

private void getReportParameters()

{

    zz_SNMSingleSourceVendorContract contract = this.parmDataContract();

    if (contract)

    {

       fromDate           = contract.parmFromDate();

       toDate             = contract.parmToDate();

    }

}

[SysEntryPointAttribute]

public void processReport()

{

    ResultSet       RS;

    str             bDate, eDate;

    str             cVendAccount, cName, cItemID, cItem;

    str             cVendAccount1, cName1, cItemID1, cItem1;

    int             nSingle;

    Query                               query = this.parmQuery();

    QueryRun                            queryRun;

    this.getReportParameters();

    bDate =date2str(fromDate,321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);

    eDate =date2str(toDate,321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);

    RS = zz_SNMSQLUtility::executeQuery("SELECT DISTINCT PURCHLINE.VENDACCOUNT, P.NAME AS fName, PURCHLINE.ITEMID, "+

         "          ECORESPRODUCT.SEARCHNAME+' - '+INVENTDIM.INVENTSIZEID+' - '+INVENTDIM.INVENTCOLORID+' - '+INVENTDIM.INVENTSTYLEID+' - '+INVENTDIM.CONFIGID AS fItem "+

         "FROM      PURCHLINE INNER JOIN "+

         "          INVENTDIM ON PURCHLINE.INVENTDIMID = INVENTDIM.INVENTDIMID INNER JOIN "+

         "          ECORESPRODUCT ON PURCHLINE.ITEMID = ECORESPRODUCT.DISPLAYPRODUCTNUMBER INNER JOIN "+

         "(SELECT   VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME "+

         "FROM      VENDTABLE LEFT OUTER JOIN "+

         "          DIRPARTYTABLE ON VENDTABLE.PARTY = DIRPARTYTABLE.RECID "+

         ") P ON PURCHLINE.VENDACCOUNT=p.ACCOUNTNUM "+

         "WHERE PURCHLINE.MODIFIEDDATETIME>='"+ bDate+"' AND PURCHLINE.MODIFIEDDATETIME<='"+ eDate+"' "+

         "ORDER BY fItem, fName"

         );

    RS.next();

    cVendAccount = RS.getString(1);

    cName = RS.getString(2);

    cItemId = RS.getString(3);

    cItem = RS.getString(4);

    while(RS.next())

    {

            cVendAccount1 = RS.getString(1);

            cName1 = RS.getString(2);

            cItemId1 = RS.getString(3);

            cItem1 = RS.getString(4);

            if (cItemID != cItemID1)

                {

                    SNMSingleSourceVendorTmp.VendAccount = cVendAccount;

                    SNMSingleSourceVendorTmp.fName = cName;

                    SNMSingleSourceVendorTmp.ItemId = cItemId;

                    SNMSingleSourceVendorTmp.fItem = cItem;

                    SNMSingleSourceVendorTmp.insert();

                    SNMSingleSourceVendorTmp.clear();

                    cVendAccount = cVendAccount1;

                    cName = cName1;

                    cItemId = cItemId1;

                    cItem = cItem1;

        }

    }

}

Add both the classes into the project

STEP 7 – Create a reporting project in Visual Studio

To create a Report, run Microsoft Visual Studio 2010. Then create a reporting project, you can choose from two project templates: Visual Basic and Visual C#. Most popular is Visual C# template.

To create a reporting project

1.      Open Microsoft Visual Studio 2010.

2.      On the File menu, point to New, and then click Project. The New Project dialog box displays.

3.      In the Project Types pane, click the Visual C# node, and in the Templates pane, click Dynamics AX Reporting Project.

4.      In the Name box, type _rptSNMSingleSourceVendor, and in the Location box, type a location.

5.      Click OK.

A reporting project contains a model by default. A model is a file with the .moxl file name extension.

STEP 8 – Create a reporting project in Visual Studio

After creation of reporting project, ready to create the report. The following procedure explains how to create the report.

To create a report

1.      In Model Editor, right-click the Report1 node, and then click Rename.

2.      Type zz_SNMSingleSourceVendor as the name.

3.      Expand the zz_SNMSingleSourceVendor node if it is not already expanded.

4.      Right-click the Datasets node, and then click Add Dataset.

5.      Select the node for the data set.

6.      In the Properties window, specify the following values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Report Data Provider
Default Layout	Table
Name	zz_SNMSingleSourceVendorDP
Query	Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the zz_SNMMrirDP class, and then select the all fields by selecting the All check box.

 Create an AutoDesign Report. This is a quick way to see that everything designed so far is working well or not.

To create an AutoDesign Report, drag the DataSet named ds on to the Designs Node. It will create an AutoDesign1 report with all the fields as columns in the report.

Before Running the report, change some properties related to presentation of the report, as shown below:

 

Run the report by providing the parameters.

After satisfaction the data fetched by the DataSet is as per the requirement, then create a Precision Design Report.

To create a Precision Design Report:

1.    Add a Precision Design, by right clicking on the Design node.

2.    Name the Design as zz_rptSNMSingleSourceVendorReport.

3.    Design a SSRS Report.

4.    To repeat the header on each page, click on the right side of the Column Group at the bottom, and select the Advance Mode of designing.

Put True as value to the Property RepeatOnNewPage.

Test the Report designed.

STEP 9 – Build and Publish the Report

Click on the Build Menu Option and Select Build option so as to build the Report Project. Then after successful build, click on the Build Menu Option and select Deploy Option to deploy the report so that can be called from the Ax Client.

Create a Menu Item so that user can access the Report by selecting it. To create a MenuItem, expand the Menu Items Node, and then Right Click on the Output Node, select New Menu Item option. Then put values to the newly created Menu Item as shown below:

Property	Value
Name	zz_SNMSingleSourceVendorReport
Label	Single Source Vendor Report
ObjectType	SSRSReport
Object	zz_rptSNMSingleSourceVendor
ReportDesign	zz_SNMSingleSourceReport

Drag the Newly Created Menu Item Option on to the desired location on the Menu Node.

Re-Run Dynamic Ax, it will now load the Menu Option on the Client. Run the Report by providing proper parameters.

Dynamic Ax, See the header is visible on the Next Page.

Add the Report, the Visual Studio Project and the Menu Item into the Project.

STEP 10 – Export the Project from the Development and Testing Server

Right click on the project created, and select option Export, provide a suitable name and export.

Copy the Exported project file to a desired location, from where it could be imported into live server.

STEP 11 – Import the Project into the Live Server

For Client Access drag the imported Menu Item on to a Menu Node.

Test the Report, by providing desired parameters.

It will show an Error, this is due to non-deployment of the SSRS report on to the SSRS Server.

STEP 12 – Re-Compile the Visual Studio Project and Publish the report with proper administrator rights to the Live Server

There are several ways to publish a report, one of them could be by copying the Visual Studio Project File from the Development Location to a location having Administrative access of the Live Server.

Make sure that the user from where the Visual Studio is operating has stuffiest rights to publish the report on the Live Server. To Test you can view the AOT on the Visual Studio Tool Window.

Then open the project.

Then Build and Publish the Project.