If you don't have a solid knowledge of MDX, never fear--Microsoft's Office Excel Add-in for SQL Server Analysis Services generates MDX for you. The add-in also provides additional features for building Excel pivot tables that access Analysis Services cubes.
First, download and install the add-in (http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en). After you launch Microsoft Excel, a Cube Analysis menu item and a new toolbar will be available. When you choose to build a report, you'll open the Build Report window, which presents several layout options. To generate an MDX statement that you can copy and paste into Reporting Services without modifications, choose Column Header Report. Then, insert two Row Header segments. Switch to the data pane and add a measure to the column and a different dimension to each row. Just as in Reporting Services, it's important to put only measures on the column. Don't place a high level of a dimension in the rows and expect to be able to drill down because the MDX won't open dimension levels. Instead, choose the lowest-level items in the Browse/Search window and put only those items in the row. The MDX will generate the flattened results that Reporting Services is expecting to see.
Once you've set up the report, you can see the MDX statement by clicking the Cube Analysis menu and choosing Show MDX. The dialog box that appears has the MDX statement, which you can copy from the box and paste into the query window in Reporting Services.
