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.

 

Start SQL Report Builder 2.0

Launch SQL Report Builder 2.0

image image image

 

Create a Data Source

In the Report Data pane on the left,  select New > Data Source

The Data Source Properties dialog will launch

image

Change the Name field to “DemoDynamicsAXOLAP”

image

Click on Use a connection embedded in my report

The dialog will change its contents.

image image

For Select a connection type, expand the dropdown and select Microsoft SQL Server Analysis Services

image

Under Connection string, click the Build button

The Connection Properties dialog will launch

image

For server name, enter the name of the machine running the SSAS service (in this example “isotopex”)

image

In the Connect to a database area, in the Select or enter a database name, click the dropdown and select “Dynamics AX”

image

In the Connect to a database area, click the Advanced button

The Advanced Properties dialog will launch

image

Under the Security section of properties, set the Integrated Security property to SSPI

image

Click OK to dismiss the Advanced Properties dialog

image

You’ll be back at the Connection Properties dialog

image

Click the Test Connection button

image

The Test results dialog should say “Test connection succeeded”. Click OK to dismiss the dialog.

image

You’ll be back at the Connection Properties dialog

Click OK to dismiss the Connection Properties dialog

image
You’ll be back at the Data Source Properties dialog

Click OK

image

The Data Source Properties dialog will disappear and you will see “DemoDynamicsAXOLAP”
as a node in the Report Data pane on the left.

image

Create a Dataset

In the Report Data pane on the left,  select New > Dataset

The Dataset Properties dialog will launch

image

Change the Name field to “DemoDatasetAXBank”

image

Set the Data source field to the one you previously created (“DemoDynamicsAXOLAP”)

image

Under Query click the Query Designer button

The Query Designer dialog will launch.

image image

Select the General Ledger Cube by clicking on the ellipsis button in the upper left.

image image image

Expand the Bank account descriptions dimension

Drag the “By Bank Account ID – Name” dimension into the dimensions are at the top of the dialog.

image image

Unexpand the Bank account descriptions dimension

image image

expand the Measures node

image image

Expand the Bank transactions node

image image

Drag “Master Amount – Bank” to the big empty area

image image image

Unexpand the Measures node

image image

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

image image image

Click OK to close the Query Designer

image

You’ll be back at the Dataset Properties dialog. Click OK to close the dialog

image

And now you’ll see on the left that the Dataset called “DemoDatasetAXBank” is listed inside the node for “DemoDynamicsAXOLAP”

image

In the design of the report, click on the “Table or Matrix” icon

image

The New Table or Matrix dialog will launch

Select DemoDataAXBank and click Next

image image

The Arrange Fields page will appear

image

In the Arrange Fields page, drag Master_Amount__Bank into the Values box

image image image

Drag Bank_name into the Row groups box

image image image

Drag Bank_accounts into the Row groups box under the Bank name

image image image

Click Next

The Choose the layout page will appear

image

Click Next

image

The Choose a style page will appear.

snap1703

Click Finish

image

The dialog will go away and you’ll see the design has been modifies

image

Click Run on the ribbon

image

And you’ll see the report execute and render in a moment

image

Play with the report

Expand the Bank of China Operating Account

image image

Expand the Bank of Europe Operating Account

image image

Expand the Bank of USA Operating Account

image image

Expand the Bank of USA Payroll account

image image

 

 

Save the Report

 

Click on the Save icon. It’ will save the RDL file in you My Documents folder.

image image

Deployment

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.