|
|
You're in the
sql server reporting services
group:Multi-value parameters
sql server reporting services:
some of those parameters, possible values include commas, as such: Value 1: a,b,c Value 2: 1,2,3 Reporting Services uses a comma as the delimiter, so the parameter list that gets passed into the stored procedure is "a,b,c,1,2,3". This obviously won't work. Here's my question: can I change the delimiter to something other than a comma? Failing that, is there a work-around for this problem? Thanks,
It is possible.
Let's say your multivalued parameter is called MultiValuedList. Define a second parameter and assign the following expression to it: =join(Parameters!MultiValuedList.Value,"~") By doing thar tilde will be your delimiter. Pass your second parameter to a stored procedure. Don't forget to check on prompt as Hidden for that second parameter. Yitzhak Khabinsky
You can use =Join(Parameters!Report_Parameter_0.Value,".") or any other thing
to join. Amarnath [quoted text, click to view] "Mark" wrote:
> I want to make certain parameters in my report multi-valued; however, for > some of those parameters, possible values include commas, as such: > > Value 1: a,b,c > Value 2: 1,2,3 > > Reporting Services uses a comma as the delimiter, so the parameter list that > gets passed into the stored procedure is "a,b,c,1,2,3". This obviously won't > work. > > Here's my question: can I change the delimiter to something other than a > comma? Failing that, is there a work-around for this problem? > > Thanks,
This works, except that I have 2+ cascading multi-value parameters, where the
selected items from one parameter are fed into the next parameter as input. Here is the problem that arises from that scenario: Suppose I have two multi-value report parameters, A and B, both of which are populated by stored procedures. The selected items from A act as the input for B. Heeding Yitzhak's advice, I add a parameter between A and B--let's call it X. X merely combines the selected parameters from A into a string (with a custom delimiter) so that they can be fed into B. Ideally, after the values for A are selected by the user, X should then be auto-populated, after which B should be populated by the database based on the input of X. Unfortunately, Reporting Services apparently tries to populate B before constructing X, causing an error. Hopefully this makes sense. Is there anybody who can help with this problem? [quoted text, click to view] "Yitzhak Khabinsky" wrote:
> It is possible. > Let's say your multivalued parameter is called MultiValuedList. > Define a second parameter and assign the following expression to it: > =join(Parameters!MultiValuedList.Value,"~") > By doing thar tilde will be your delimiter. > Pass your second parameter to a stored procedure. > > Don't forget to check on prompt as Hidden for that second parameter. > > Yitzhak Khabinsky >
Bruce,
I did have X between A and B, but I still got the error. (The error said B was missing the parameter X). I've double-checked spelling and I didn't see any typos. It might be that I'm misinterpreting what the problem is, but I haven't found any evidence toward that end myself. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Make sure that X shows up between A and B in the order of parameters. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Mark" <Mark@discussions.microsoft.com> wrote in message > news:7168A045-E625-47DB-9EE3-1897F9CA6975@microsoft.com... > > This works, except that I have 2+ cascading multi-value parameters, where > > the > > selected items from one parameter are fed into the next parameter as > > input. > > Here is the problem that arises from that scenario: > > > > Suppose I have two multi-value report parameters, A and B, both of which > > are > > populated by stored procedures. The selected items from A act as the input > > for B. Heeding Yitzhak's advice, I add a parameter between A and B--let's > > call it X. X merely combines the selected parameters from A into a string > > (with a custom delimiter) so that they can be fed into B. > > > > Ideally, after the values for A are selected by the user, X should then be > > auto-populated, after which B should be populated by the database based on > > the input of X. Unfortunately, Reporting Services apparently tries to > > populate B before constructing X, causing an error. > > > > Hopefully this makes sense. Is there anybody who can help with this > > problem? > > > > > > "Yitzhak Khabinsky" wrote: > > > >> It is possible. > >> Let's say your multivalued parameter is called MultiValuedList. > >> Define a second parameter and assign the following expression to it: > >> =join(Parameters!MultiValuedList.Value,"~") > >> By doing thar tilde will be your delimiter. > >> Pass your second parameter to a stored procedure. > >> > >> Don't forget to check on prompt as Hidden for that second parameter. > >> > >> Yitzhak Khabinsky > >> > >> > >
Make sure that X shows up between A and B in the order of parameters.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Mark" <Mark@discussions.microsoft.com> wrote in message news:7168A045-E625-47DB-9EE3-1897F9CA6975@microsoft.com... > This works, except that I have 2+ cascading multi-value parameters, where > the > selected items from one parameter are fed into the next parameter as > input. > Here is the problem that arises from that scenario: > > Suppose I have two multi-value report parameters, A and B, both of which > are > populated by stored procedures. The selected items from A act as the input > for B. Heeding Yitzhak's advice, I add a parameter between A and B--let's > call it X. X merely combines the selected parameters from A into a string > (with a custom delimiter) so that they can be fed into B. > > Ideally, after the values for A are selected by the user, X should then be > auto-populated, after which B should be populated by the database based on > the input of X. Unfortunately, Reporting Services apparently tries to > populate B before constructing X, causing an error. > > Hopefully this makes sense. Is there anybody who can help with this > problem? > > > "Yitzhak Khabinsky" wrote: > >> It is possible. >> Let's say your multivalued parameter is called MultiValuedList. >> Define a second parameter and assign the following expression to it: >> =join(Parameters!MultiValuedList.Value,"~") >> By doing thar tilde will be your delimiter. >> Pass your second parameter to a stored procedure. >> >> Don't forget to check on prompt as Hidden for that second parameter. >> >> Yitzhak Khabinsky >> >>
Different issue I believe. Parameters are case sensitive. Make sure you have
the parameter name correct. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Mark" <Mark@discussions.microsoft.com> wrote in message news:ED82A2A9-7952-4BBC-AC2E-C970A70A9597@microsoft.com... > Bruce, > > I did have X between A and B, but I still got the error. (The error said B > was missing the parameter X). I've double-checked spelling and I didn't > see > any typos. It might be that I'm misinterpreting what the problem is, but I > haven't found any evidence toward that end myself. > > > "Bruce L-C [MVP]" wrote: > >> Make sure that X shows up between A and B in the order of parameters. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message >> news:7168A045-E625-47DB-9EE3-1897F9CA6975@microsoft.com... >> > This works, except that I have 2+ cascading multi-value parameters, >> > where >> > the >> > selected items from one parameter are fed into the next parameter as >> > input. >> > Here is the problem that arises from that scenario: >> > >> > Suppose I have two multi-value report parameters, A and B, both of >> > which >> > are >> > populated by stored procedures. The selected items from A act as the >> > input >> > for B. Heeding Yitzhak's advice, I add a parameter between A and >> > B--let's >> > call it X. X merely combines the selected parameters from A into a >> > string >> > (with a custom delimiter) so that they can be fed into B. >> > >> > Ideally, after the values for A are selected by the user, X should then >> > be >> > auto-populated, after which B should be populated by the database based >> > on >> > the input of X. Unfortunately, Reporting Services apparently tries to >> > populate B before constructing X, causing an error. >> > >> > Hopefully this makes sense. Is there anybody who can help with this >> > problem? >> > >> > >> > "Yitzhak Khabinsky" wrote: >> > >> >> It is possible. >> >> Let's say your multivalued parameter is called MultiValuedList. >> >> Define a second parameter and assign the following expression to it: >> >> =join(Parameters!MultiValuedList.Value,"~") >> >> By doing thar tilde will be your delimiter. >> >> Pass your second parameter to a stored procedure. >> >> >> >> Don't forget to check on prompt as Hidden for that second parameter. >> >> >> >> Yitzhak Khabinsky >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |