No Parameter Pane when Reporting in 2005 against a 2000 AS Cube

SQL Server Reporting Services

Will Riley


I have this strange issue.

VS2005

SSRS2005

SSAS2000 cube based on data from SQL 2005 database

When i connect to the SSAS cube and create a dataset in a SSRS 2005 report i do not get a parameter pane.

Also, if i right click on a dimension & select "Add to filter" i get the following error message

"Object reference not set to an instance of an object"

It's not my installation of SQL 2005, as I can connect to an SSAS2005 cube and get the filter/parameter pane and do not get the error message. Parameters work fine.

Have i come across a known issue when reporting on SQL 2005 versus a SQL 2000 AS Cube... or can someone shed some light on the issue for me....

If someone can tell me how to display screenshots I can add them to a post Wink

Thanks

Will




Top

Re: No Parameter Pane when Reporting in 2005 against a 2000 AS Cube

Will Riley


Any ideas anybody






Top

Re: No Parameter Pane when Reporting in 2005 against a 2000 AS Cube

Will Riley

OK, I fixed it.

For those that are interested. SSRS 2005 will not support the parameter pane when connecting to an SSAS 2000 cube. The steps you need to follow are as follows:

1. Create a connection (data source) to your AS2000 cube using OLE DB connection (In my case I used version 8 - Provider=MSOLAP.2)

2. Add a new dataset - this will be the old text based MDX editor rather than the snazzy new SSRS 2005 AS GUI so you will need to put your MDX hat on Wink

3. Construct a base MDX query with the filter hard coded, e.g.

Code Block

SELECT NON EMPTY { [Measures].[Area Controlled] } ON COLUMNS,

NON EMPTY { [Developer].[Tracked].[Actively Tracked], [Developer].[Tracked].[Actively Tracked].CHILDREN } ON ROWS
FROM [QMSLand]

WHERE ( [Time].[Quarter].[Quarter].[Q1 2007])

4. Set up a second dataset to return the values for your parameter list e.g.

Code Block
WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{[Time].[Quarter].[Quarter].Members } ON ROWS
FROM
[QMSLand]

5. Create a Parameter in the norma way, specifying the values to be selected from the query in 4. Let's say we call it Quarter

6. Finally, edit your initial MDX query to include the new parameter as opposed to the hard-coded value e.g.

Code Block
="SELECT NON EMPTY { [Measures].[Area Controlled] } ON COLUMNS, NON EMPTY { [Developer].[Tracked].[Actively Tracked], [Developer].[Tracked].[Actively Tracked].CHILDREN} ON ROWS
FROM [QMSLand] WHERE ( [Time].[Quarter].[Quarter].[" + Parameters!Quarter.Value + "])"

Important things to note about step 6

a. The command must be enclosed in quotes i.e. ="....mdx here..."

b. Note also the way the parameter reference is wrapped in speech marks

c. Ensure you have your complete dataset before wrapping the command - you cannot add fields once the command is enclosed in parentheses

d. The command must be a SINGLE line (no carriage returns)...so I used notepad to ensure this

Painful and very lightly documented... but a solution none the less

Smile






Top