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 bedesired. 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 clickingon 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 bygoing 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 nodein the Model Editor, and choose New Dataset. Now, open the properties of the new Dataset. The Data Source property should be set to theNEW Report Data Source that was created above, NOT the Dynamics AX that defaults into this property. The DataSource Type should be either Stored Procedure or Query (this will allow theselection of a stored procedure or access to the query editor in the next step). Choose a Default Layout andmeaningful 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 displaythe Stored Procedures that exist in the Database selected in the Report DataSource (if the Data Source Type property is set to Stored Procedure), or itwill 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 bythe 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 errorstating that the AX_CompanyName parameter is missing a value. This is created automatically by thereporting engine. To get around this error, you can code in any value for this by Expanding the Parameters node ofthe report (in the Model Editor). Locate the AX_CompanyName parameter. In theproperties pane, set the Default Value to DAT. The assumption is that the query (or stored procedure) will do whateveris needed with the dataareaid column, so this value is simply supplied toprovide a value to the parameter, even though the parameter is not used by the query.
Nice! Thanks for sharing