Paramters with Multiple Values

I know I must be missing something obvious but I cannot find it in the
documentation. How do I allow the user to input multiple values for a report
parameter. For example, the main query has this for test criteria:
where CustID in(100, 200, 300)
so I changed to:
IN(@.CustId) expecting to input 100,200,300
but instead I get an error "Application uses a value of the wrong type for
the current operation"
? ThanksTo do that you have to write dynamic sql and your parameter has to be a
varchar... the reason you are getting an error is you are comparing an
INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
"100,200,300" to integers.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> I know I must be missing something obvious but I cannot find it in the
> documentation. How do I allow the user to input multiple values for a
report
> parameter. For example, the main query has this for test criteria:
> where CustID in(100, 200, 300)
> so I changed to:
> IN(@.CustId) expecting to input 100,200,300
> but instead I get an error "Application uses a value of the wrong type for
> the current operation"
> ? Thanks
>|||So does that mean if it were a varchar column it would accept the commas and
treat them as delimiters between separate values ie 100 OR 200 OR 300?
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> To do that you have to write dynamic sql and your parameter has to be a
> varchar... the reason you are getting an error is you are comparing an
> INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> "100,200,300" to integers.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
>> I know I must be missing something obvious but I cannot find it in the
>> documentation. How do I allow the user to input multiple values for a
> report
>> parameter. For example, the main query has this for test criteria:
>> where CustID in(100, 200, 300)
>> so I changed to:
>> IN(@.CustId) expecting to input 100,200,300
>> but instead I get an error "Application uses a value of the wrong type
>> for
>> the current operation"
>> ? Thanks
>>
>|||No, it would treat it as one string... the easiest way to do a query with a
parameter that contains multiple integer values is dynamic sql like...
DECLARE @.CustID VARCHAR(100)
DECLARE @.sql VARCHAR(1000)
SET @.CustID = '100,200,300'
SELECT @.sql = 'SELECT * FROM Customer WHERE CustID IN (' + @.CustID + ')'
EXEC(@.SQL)
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O1XluJDyEHA.2212@.TK2MSFTNGP15.phx.gbl...
> So does that mean if it were a varchar column it would accept the commas
and
> treat them as delimiters between separate values ie 100 OR 200 OR 300?
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> > To do that you have to write dynamic sql and your parameter has to be a
> > varchar... the reason you are getting an error is you are comparing an
> > INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> > "100,200,300" to integers.
> >
> >
> > "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> > news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> >> I know I must be missing something obvious but I cannot find it in the
> >> documentation. How do I allow the user to input multiple values for a
> > report
> >> parameter. For example, the main query has this for test criteria:
> >> where CustID in(100, 200, 300)
> >> so I changed to:
> >> IN(@.CustId) expecting to input 100,200,300
> >> but instead I get an error "Application uses a value of the wrong type
> >> for
> >> the current operation"
> >>
> >> ? Thanks
> >>
> >>
> >
> >
>

Paramters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

|||I've seen a lot of these posts where using a parameter in the WITH section throws an exception. The only solution I've found so far is to revert to connecting via the OLEDB provider and using an expression based MDX query. See this thread for full details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726232&SiteID=1|||

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?