|
|
You're in the
sql server reporting services
group:Please Help with Internal Parameter Problem
sql server reporting services:
I am using SQL Server 2005. My report has four parameters; three prompt for
values and one is internal. The internal one is the source of the problem, since when I remove it the report works. I am using this internal parameter to display database information in the page header, following Brian Larson's instructions in his book, "SQL Server 2005 Reporting Services", pages 291-299. I add the parameter manually, give it a name, check the "Internal" checkbox, set "Default values" to "From query", select the Dataset, and select the Value field. In preview mode, or if I run the report from Visual Studio, it works as it should. I am prompted for the first three parameters but not the internal one, and the data displays properly, including in the page header. When I deploy and then view the report I get the error, "Parameter validation failed. It is not possible to provide valid values for all parameters. ---> Parameter validation failed. It is not possible to provide valid values for all parameters. ---> Parameter validation failed. It is not possible to provide valid values for all parameters." Yes, it repeats itself twice. This happens whether I browse to the report and pass parameters on the querystring, or open it in Report Manager, or call it from the web service passing a parameter array. From this information, can someone see what I'm missing? Your help is appreciated.
Thanks, Bruce. It already is last.
[quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Try making the internal parameter last in the list of parameters.
Okay. The datasource uses sql server authentication with a username and
password that are valid for the referenced database. There is only one dataset for this report, and when I take out the internal parameter, the report accesses and displays data from it with no problem. Other thoughts? [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Check the credentials for the datasource used by the dataset that uses the > hidden parameter.
Try making the internal parameter last in the list of parameters.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message news:64F494BA-59F4-45E4-974D-15C3F5AD93CE@microsoft.com... >I am using SQL Server 2005. My report has four parameters; three prompt >for > values and one is internal. The internal one is the source of the > problem, > since when I remove it the report works. > > I am using this internal parameter to display database information in the > page header, following Brian Larson's instructions in his book, "SQL > Server > 2005 Reporting Services", pages 291-299. I add the parameter manually, > give > it a name, check the "Internal" checkbox, set "Default values" to "From > query", select the Dataset, and select the Value field. In preview mode, > or > if I run the report from Visual Studio, it works as it should. I am > prompted > for the first three parameters but not the internal one, and the data > displays properly, including in the page header. > > When I deploy and then view the report I get the error, "Parameter > validation failed. It is not possible to provide valid values for all > parameters. ---> Parameter validation failed. It is not possible to > provide > valid values for all parameters. ---> Parameter validation failed. It is > not > possible to provide valid values for all parameters." Yes, it repeats > itself > twice. > > This happens whether I browse to the report and pass parameters on the > querystring, or open it in Report Manager, or call it from the web service > passing a parameter array. > > From this information, can someone see what I'm missing? Your help is > appreciated. > > Fred
This report displays prescription information for a hospital patient. In a
web page, the doctor chooses the patient from a search, then indicates whether he wants inpatient data, outpatient data, or both. Finally, he indicates whether he wants active prescriptions, expired ones, or both. These are the three parameters which are sent to the report: PatientID, Active, and FacilityID (which just means whether the patient is in the hospital, out of it, or both kinds of records should be returned). There is only one patient per report, but several prescriptions. One of the requirements for this report was that the patient name should be displayed in bold at the top of each page. Quoting from Brian Larson's book here: "You cannot place information from a dataset in the page header...directly! You can, however, put the value of a parameter in the page header. You can also use a dataset to specify the default value of a parameter. Therefore, using this two-step approach, we can put [a value from the dataset] into the page header." This is what I have done with the patient name. Given a PatientID, I query for patient data joined to prescription data; one of the returned fields is patient name. I create a PatientName parameter, set its default value to "From query", and from the report's only dataset make the parameter's value field "PatientName". Then I put a textbox on the page header and set its value to, ="Patient: " & Parameters!PatientName.Value. This displays the name of the patient at the top of each page. It works properly in the IDE. Only when I deploy and then reference the report by browser do I get the error. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> If I understand this is what you are trying to do. > > One dataset is in the report that is based on three visible parameters. > > You are putting a value(s) from the dataset into an invisible parameter > because you want the value(s) to be in the header. Is this one or is it > multiple values? > > I am not familiar with this technique. You can't just use the First > aggregate in the header? (Which only works if you have a single record OR > the same value in each record). > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message > news:45CE869C-BBC2-4C33-B0E5-3DD71991E228@microsoft.com... > > Okay. The datasource uses sql server authentication with a username and > > password that are valid for the referenced database. There is only one > > dataset for this report, and when I take out the internal parameter, the > > report accesses and displays data from it with no problem. Other > > thoughts? > > > > "Bruce L-C [MVP]" wrote: > > > >> Check the credentials for the datasource used by the dataset that uses > >> the > >> hidden parameter. > > > >
Check the credentials for the datasource used by the dataset that uses the
hidden parameter. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message news:CA7A9DBC-8E1F-47C9-B894-A4AC176D0DD7@microsoft.com... > Thanks, Bruce. It already is last. > > > "Bruce L-C [MVP]" wrote: > >> Try making the internal parameter last in the list of parameters. >
If I understand this is what you are trying to do.
One dataset is in the report that is based on three visible parameters. You are putting a value(s) from the dataset into an invisible parameter because you want the value(s) to be in the header. Is this one or is it multiple values? I am not familiar with this technique. You can't just use the First aggregate in the header? (Which only works if you have a single record OR the same value in each record). -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message news:45CE869C-BBC2-4C33-B0E5-3DD71991E228@microsoft.com... > Okay. The datasource uses sql server authentication with a username and > password that are valid for the referenced database. There is only one > dataset for this report, and when I take out the internal parameter, the > report accesses and displays data from it with no problem. Other > thoughts? > > "Bruce L-C [MVP]" wrote: > >> Check the credentials for the datasource used by the dataset that uses >> the >> hidden parameter. >
OK, so you have multiple records but each record of the dataset has the
patient name. This seems overly tricky. You can put aggregates in the header and one of the available aggregates is the First aggregate. The expression builder will give you the option for this. You do not even need the parameter. The other option is to create a second dataset that all it does is return the patients name and use that for the source of your parameter. But, if you do that, again, you do not need the parameter. You just use the First aggregate. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message news:CBE71BBC-D686-4A09-82C6-4053B0EED97C@microsoft.com... > This report displays prescription information for a hospital patient. In > a > web page, the doctor chooses the patient from a search, then indicates > whether he wants inpatient data, outpatient data, or both. Finally, he > indicates whether he wants active prescriptions, expired ones, or both. > These are the three parameters which are sent to the report: PatientID, > Active, and FacilityID (which just means whether the patient is in the > hospital, out of it, or both kinds of records should be returned). > > There is only one patient per report, but several prescriptions. One of > the > requirements for this report was that the patient name should be displayed > in > bold at the top of each page. Quoting from Brian Larson's book here: > > "You cannot place information from a dataset in the page > header...directly! > You can, however, put the value of a parameter in the page header. You > can > also use a dataset to specify the default value of a parameter. > Therefore, > using this two-step approach, we can put [a value from the dataset] into > the > page header." > > This is what I have done with the patient name. Given a PatientID, I > query > for patient data joined to prescription data; one of the returned fields > is > patient name. I create a PatientName parameter, set its default value to > "From query", and from the report's only dataset make the parameter's > value > field "PatientName". Then I put a textbox on the page header and set its > value to, ="Patient: " & Parameters!PatientName.Value. This displays the > name of the patient at the top of each page. It works properly in the > IDE. > Only when I deploy and then reference the report by browser do I get the > error. > > > "Bruce L-C [MVP]" wrote: > >> If I understand this is what you are trying to do. >> >> One dataset is in the report that is based on three visible parameters. >> >> You are putting a value(s) from the dataset into an invisible parameter >> because you want the value(s) to be in the header. Is this one or is it >> multiple values? >> >> I am not familiar with this technique. You can't just use the First >> aggregate in the header? (Which only works if you have a single record OR >> the same value in each record). >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Fred Sawtelle" <FredSawtelle@discussions.microsoft.com> wrote in message >> news:45CE869C-BBC2-4C33-B0E5-3DD71991E228@microsoft.com... >> > Okay. The datasource uses sql server authentication with a username >> > and >> > password that are valid for the referenced database. There is only one >> > dataset for this report, and when I take out the internal parameter, >> > the >> > report accesses and displays data from it with no problem. Other >> > thoughts? >> > >> > "Bruce L-C [MVP]" wrote: >> > >> >> Check the credentials for the datasource used by the dataset that uses >> >> the >> >> hidden parameter. >> > >> >> >>
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 |