Parameter problem with OLAP and spaces

Hello all,

I have an issue with parameters. Let me explain.....

I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value.

On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried.

When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If convert spaces to underscores I get a little further, but sites that have numbers, for example 15_OxfordStreet still do not work. The error message I get is..

"Parser: The syntax for '_Oxford' is incorrect.

Can anyone suggest anything to help?

Many thanks

Matt

You may need to escape the member names. To avoid similar issues, the SSAS team introduced undocumented function UrlEscapeFragment. If you open the sample AW cube, flip to the Actions tab in the Cube Designer, select the Sales Reason Comparision action, and expand the Parameters section, you will see how this function is used to escape the member name.|||

Teo,

Thanks for your reply. I'm still learning SQL 2005 and although I tried your suggestion I could not get it to work. However I did fix the problem. The issue was that I was passing through the "friendly name" of the value, instead of [data1].[field name].&[0] etc. as the jump to parameter!

Arrrrgggg...so simple when you think about it!

M

parameter boxes are empty

Hi,

I am have a reporting server which connects to a analysis server which connects to a sql server and a db2 server. I am using windows integrated security all the time except for the DB2 connection. That is a fixed username/password.

Reporting server (RS), analysis server (AS) and sql server are all on the same machine.

I created a local group and added the correct users to it.
I gave that group browse rights on the reports so that works correctly.

I created a role in AS and added that group to that role. Gave all read rights to that role.

I created a login for that group on the SQL server and gave that group db_datareader role.

When I connect as an administrator every reports runs fine.

When the test user connects they can browse the reports but the drop down boxes for the parameters are empty.

What goes wrong ?

With regards,

Constantijn Enders

Hi Constantjin

Im having the same problem...

I'm guessing when you say: <<Give all read rights to that role>>
you mean that you are setting the read rights in the cell data tab
when editing your role's security....

If I am correct, try the following:

Uncheck the Combo boxes on the cell data tab
(The one for 'Enable Read Permissions' and 'Enable Read Contingent Permissions')
and see the effect.
User will now be able to see everything.

It seemes that any attempt to set permissions on the cell data tab causes this
problem.

G

|||

Hi,

Unchecking did solve the problem.

I also removed them from SQL server and it works.

So basically what I had to do to give report users rights was
- create a role in AS
- put users/groups in that role
- give that role read rights on the cubes

and that's it.

Thanks for your quick help

Constantijn Enders

Parameter All Level

Hello,

I'm using SQL Server Reporting Services with an Analysis Services Cube. My current report has a query based parameter. The query returns country codes and the ALL-Level of my country dimension.

Is there a way to remove the ALL-Level of the result set of the query? How can I realize this?

Thanks in advance.

Christian

Hi,

I suggest to change the query. I think, your generated query looks like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Country].[Country].ALLMEMBERS ON ROWS FROM [MyCube]

Change it to:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([Country].[Country], [Country].[yourLevel1name]) ON ROWS FROM [MyCube]

For further help, look at the Descendants Function in MDX.

Hans