Parameter Prompt List & Filter selection

I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper

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