Very soon you’ll be able to use Dynamics AX 2009 BI Components with SQL Server 2008. One benefit of SQL 2008 is that SSRS 2008 includes a new tool to design reports called SQL Report Builder 2.0. You can use Report Builder 2.0 to design ad-hoc reports against the OLTP database or reports against the OLAP Cubes. In this example, I’ll show you how to connect to the AX OLAP Cubes.
Launch SQL Report Builder 2.0
In the Report Data pane on the left, select New > Data Source
The Data Source Properties dialog will launch
Change the Name field to “DemoDynamicsAXOLAP”
Click on Use a connection embedded in my report
The dialog will change its contents.
For Select a connection type, expand the dropdown and select Microsoft SQL Server Analysis Services
Under Connection string, click the Build button
The Connection Properties dialog will launch
For server name, enter the name of the machine running the SSAS service (in this example “isotopex”)
In the Connect to a database area, in the Select or enter a database name, click the dropdown and select “Dynamics AX”
In the Connect to a database area, click the Advanced button
The Advanced Properties dialog will launch
Under the Security section of properties, set the Integrated Security property to SSPI
Click OK to dismiss the Advanced Properties dialog
You’ll be back at the Connection Properties dialog
Click the Test Connection button
The Test results dialog should say “Test connection succeeded”. Click OK to dismiss the dialog.
Click OK to dismiss the Connection Properties dialog
You’ll be back at the Data Source Properties dialog
The Data Source Properties dialog will disappear and you will see “DemoDynamicsAXOLAP” as a node in the Report Data pane on the left.
In the Report Data pane on the left, select New > Dataset
The Dataset Properties dialog will launch
Change the Name field to “DemoDatasetAXBank”
Set the Data source field to the one you previously created (“DemoDynamicsAXOLAP”)
Under Query click the Query Designer button
The Query Designer dialog will launch.
Select the General Ledger Cube by clicking on the ellipsis button in the upper left.
Expand the Bank account descriptions dimension
Drag the “By Bank Account ID – Name” dimension into the dimensions are at the top of the dialog.
Unexpand the Bank account descriptions dimension
expand the Measures node
Expand the Bank transactions node
Drag “Master Amount – Bank” to the big empty area
Unexpand the Measures node
Go back to the bank account descriptions node
and drag the “By Bank Accounts – Name” into the big empty area and drop it just in front of the Master Amount field
And now you’ll see the data
Click OK to close the Query Designer
You’ll be back at the Dataset Properties dialog. Click OK to close the dialog
And now you’ll see on the left that the Dataset called “DemoDatasetAXBank” is listed inside the node for “DemoDynamicsAXOLAP”
In the design of the report, click on the “Table or Matrix” icon
The New Table or Matrix dialog will launch
Select DemoDataAXBank and click Next
The Arrange Fields page will appear
In the Arrange Fields page, drag Master_Amount__Bank into the Values box
Drag Bank_name into the Row groups box
Drag Bank_accounts into the Row groups box under the Bank name
The Choose the layout page will appear
The Choose a style page will appear.
The dialog will go away and you’ll see the design has been modifies
Click Run on the ribbon
And you’ll see the report execute and render in a moment
Expand the Bank of China Operating Account
Expand the Bank of Europe Operating Account
Expand the Bank of USA Operating Account
Expand the Bank of USA Payroll account
Click on the Save icon. It’ will save the RDL file in you My Documents folder.
I’ll cover that in another post.
It will be simple: put the RDL file on the SSRS 2008 Server using the SSRS Reports Manager web site.