XML/A underwent a major overhaul in the 9.3.1 timeframe to be more compliant with the Microsoft flavor of XML/A. This happened before the Oracle merger when Hyperion and Microsoft were getting a bit cozy and there was some cross-breeding that allowed Hyperion Reporting tools to connect to SQL Server Analysis Services ("SSAS") and allowed SSRS to connect to Essbase. For this to happen, you must have at least Essbase and APS 9.3.1 and SQL Server 2005 SP2 or higher; I am using Essbase/APS 11.1.1 and SQL Server 2005 SP2.
To begin, select Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio to start the development environment. The SSRS development environment is actually a version of Visual Studio 2005 so, for those of you new to this, welcome to the Microsoft .NET programming experience! After Microsoft Visual Studio starts, select File, New Project from the menu to display the New Project dialog. Select the Report Server Project template and give the new project a name and directory.
Press the OK button to continue and the new project will be created. In the Solution Explorer, select Shared Data Sources, right mouse click and select Add New Data Source.
In the Shared Data Source dialog, add a Name and select Hyperion Essbase from the Type dropdown.
Press the Edit button to configure the connection. Fill in the relevent properties on the dialog. If you have filled everything in properly, you should get a list of applications to choose from in the appropriate dropdown.
Next, select the Reports node in the Solution Explorer, right mouse click and click on Add New Report. This will launch the Report Wizard.
Hit the Next button to process past the wizard introduction step to the Select Data Source step. Select the Shared Data Source you just created.
Hit the Next button to proceed to the Query Builder.
I found that when I dragged any members not in the Measures dimension into the grid, Essbase threw an error "OLAP_error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query". It seems to me to be related to outlines enabled with non-unique member names but I don't know for sure; perhaps there is an option somewhere that I missed. Note that the Query Builder does have an Auto Execute button that is turned on by default. I ended up turning off Auto Execute in my Query Builder.
In any case, make sure you select enough members/hierarchies/etc that you can have different dimensions to define the rows, columns and data (details) areas of a matrix report.
Hit OK to close the Query Builder and the MDX query string will be displayed in the wizard.
Hit the Next button and select the report type.
In my example, I added the Measures dimension members to the 'Details', the Years hierarchy to the Columns and the Market hierarchy to the Rows.
Hit the Finish button, give the report a Name and save it.
At this point, I was ready to test it and see data in the SSRS report. Guess what? The error I saw earlier came back to bite me. It seems the new provider changed enough in 9.3.1 and higher to cause a problem. Microsoft has fixed the issue in SQL Server 2005 SP3. The following line is found in the fixed buglist for SP3 at http://support.microsoft.com/kb/955706:
FIX: Error message when you use the new Essbase provider name: "OLAP error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query"
OK, I will upgrade to the new service pack and see what happens.