Dynamics AX 2009: Creating an SSRS 2008 report that gets data from an AX Cube

转载：http://blogs.msdn.com/saveenr/archive/2008/11/05/dynamics-ax-2009-creating-an-ssrs-2008-report-that-gets-data-from-an-ax-cube.aspx

Dynamics AX 2009: Creating an SSRS 2008 report that gets data from an AX Cube

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

 

Create a Data Source

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.

You'll be back at the Connection Properties dialog

Click OK to dismiss the Connection Properties dialog

You'll be back at the Data Source Properties dialog

Click OK

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

Create a Dataset

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

Click Next

The Choose the layout page will appear

Click Next

The Choose a style page will appear.

Click Finish

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

Play with the report

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

 

 

Save the Report

 

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

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.