Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

How to Consume a SQL Query or Stored Procedure within the Dynamics AX 2009 SSRS Reporting Engine

How to Consume a SQL Query or Stored Procedure within the Dynamics AX 2009 SSRS Reporting Engine

Rate This
  • Comments 1

NOTE:  This is not a recommended process as these steps will NOT enforce the security that is set up within Dynamics AX 2009.

 

There are times when creating reports, it is desired to
'mine' data out of the Dynamics AX 2009 database.  For performance reasons, a SQL query may be
desired.  The following shows how this can be accomplished.

The first step is to add a new datasource to the report library.  This is done by right clicking
on the report library in the Solution Explorer pane, and choose Add >> New Item… from the menu that appears. 
The object type is Report Data Source.  Give the datasource a meaningful name, and click the Add button.  Your new, custom datasource should now appear in the Solution Explorer.

Next, you will want to change a few properties of this new datasource.  The Provider should be SQL,
and the Connection String property is the ADO .NET connection string value.  The format is as follows:

Server=<server name>;Database=<Dynamics AX 2009 Database Name>;Integrated Security=SSPI

<Server Name> and <Database Name> will need to be modified to match your current setup.

The next step is to get the SQL Query or Stored Procedure tied into the report.  This is done by
going into the Model View in Visual Studio.  Add a new report to the library, and then view that report in the Model Editor.  Right click on the Datasets node
in the Model Editor, and choose New Dataset. Now, open the properties of the new Dataset.  The Data Source property should be set to the
NEW Report Data Source that was created above, NOT the Dynamics AX that defaults into this property.  The Data
Source Type should be either Stored Procedure or Query (this will allow the
selection of a stored procedure or access to the query editor in the next step).  Choose a Default Layout and
meaningful Name for the Dataset, and move to the Query property. 

Click the button on the end of the Query property line.  This will bring up a box that will display
the Stored Procedures that exist in the Database selected in the Report Data
Source (if the Data Source Type property is set to Stored Procedure), or it
will bring up the query editor (if the Data Source Type is set to Query).

At this point, if the query has a list of fields returned by
the query, these fields will show up as fields in the dataset.  At that point, drag the fields (or dataset)
into the Designs node, and the fields will populate the AutoDesign.

NOTE: when deployed, you may get an error
stating that the AX_CompanyName parameter is missing a value.  This is created automatically by the
reporting engine.  To get around this error, you can code in any value for this by Expanding the Parameters node of
the report (in the Model Editor).  Locate the AX_CompanyName parameter.  In the
properties pane, set the Default Value to DAT.  The assumption is that the query (or stored procedure) will do whatever
is needed with the dataareaid column, so this value is simply supplied to
provide a value to the parameter, even though the parameter is not used by the query.

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Nice! Thanks for sharing

Page 1 of 1 (1 items)