| Author |
Topic |
|
Sojarat
Starting Member
11 Posts |
 Posted - 2007-03-19 : 15:56:39
|
| I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects. However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ? I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.Any help and/or suggestions are appreciated !!!Thanks. |
|
|
jhermiz
3564 Posts |
Posted - 2007-03-20 : 09:42:04
|
quote:
Originally posted by Sojarat
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects. However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ? I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.Any help and/or suggestions are appreciated !!!Thanks.
First things first, does the sproc return the results in Query Analyzer? Or if you're using 2005 in SQL Server Management studio?Execute that same sproc with the same parameter outside of RS and let me know if it returns results. If it does return the results that means your report is not passing the parameter. This is a simple fix as it is a bug in RS. If you ever add parameters later in time they seem to go into the report, but if you check the actual code view (The XML of the report) then you will notice that RS forgot to add the parameters in the code view. What this amounts to is basically a report which you think has a parameter and actually shows one and displays one in the report designer, but the actual XML code missing the parameter.Simply right click your report and do a view code. Go to the parameters section and ensure that the parameter exists.Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
 |
|
|
Sojarat
Starting Member
11 Posts |
Posted - 2007-03-20 : 10:45:33
|
| Hi, thanks for the response.Yes, if I execute the sproc in SQL Studio it does return a result set to me sucessfully. I did look at the code for the RDL file and saw there was no section for the Report Parameters.I added code for the parameters which I included below, and I can see them when I go to the Report Parameters dialog from the Report menu, but that still doesn't seem to do it. Is there another step I need to do to link them together somehow ?<ReportParameters> <ReportParameter Name="cJobTaskSAK"> <DataType>String</DataType> <DefaultValue> <Values> <Value>449490</Value> </Values> </DefaultValue> <Prompt>@JobTaskSak</Prompt> </ReportParameter> <ReportParameter Name="cListID"> <DataType>String</DataType> <Nullable>true</Nullable> <Prompt>@ListID</Prompt> <ValidValues> <ParameterValues> <ParameterValue /> </ParameterValues> </ValidValues> </ReportParameter> </ReportParameters> Thanks again |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-03-20 : 11:08:08
|
| I hope you added the code correctly, is the name of the report parameter in the report dialog the same as the name in the rdl code ?Do this add another parameter via the dialog box, call it whatever you want, and make it a string type with a default value of YES.Now go to the rdl and do a search for YES, check that the parameter was placed in the RDL correctly, then look for all your other parameters. If you see the parameters there then it is set correctly and you can delete the newly created parameter. Otherwise if you do NOT see the other parameters in this section, manually add them and delete the newly created parameter.The last thing I dcan think of is run profiler and filter out any call to that stored procedure to check if the parameter is being sent.Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
|
|
|
Sojarat
Starting Member
11 Posts |
Posted - 2007-03-20 : 11:23:28
|
| One thing I forgot to mention. My DataSource is an ODBC System DSN. I have found that when I tried using a SQL Server based datasource which points to my database, I can get the sproc to execute just fine. However, I don't think that will be useful for me as these will be executed across multiple servers and likely need to have the DataSource be based on the system DSN.Thanks. |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-03-20 : 13:58:15
|
quote:
Originally posted by Sojarat
One thing I forgot to mention. My DataSource is an ODBC System DSN. I have found that when I tried using a SQL Server based datasource which points to my database, I can get the sproc to execute just fine. However, I don't think that will be useful for me as these will be executed across multiple servers and likely need to have the DataSource be based on the system DSN.Thanks.
Ugh bad designs...One thing I cannot stand is when a programmer / analyst tries to get a certain system to work with ALL systems. It is not as easy as you think, in fact you have to end up using a whole slew of different tiers to accomplish this. If your report works fine with an SQL Data Source, then it is not a report issue, nor is it an SQL issue.The issue is ODBC...which I thought died years ago ? :)...Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
|
|
|
MikeDevenney
Starting Member
20 Posts |
Posted - 2008-03-10 : 12:20:08
|
| My useless rambling and dribble has been removed. |
|
|
|
MikeDevenney
Starting Member
20 Posts |
Posted - 2008-03-10 : 12:23:14
|
| Added my Report parameters section for reference: <ReportParameters> <ReportParameter Name="DateStart"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>1/10/2008</Value> </Values> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt>Start Date?</Prompt> </ReportParameter> <ReportParameter Name="DateEnd"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>2/10/2008</Value> </Values> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt>End Date?</Prompt> </ReportParameter> <ReportParameter Name="Type"> <DataType>String</DataType> <DefaultValue> <Values> <Value>DATES</Value> </Values> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt>Type?</Prompt> <ValidValues> <ParameterValues> <ParameterValue> <Value>DATES</Value> </ParameterValue> <ParameterValue> <Value>MONTH</Value> </ParameterValue> </ParameterValues> </ValidValues> </ReportParameter> </ReportParameters>Mike Devenney |
|
|
|
MikeDevenney
Starting Member
20 Posts |
Posted - 2008-03-10 : 12:43:41
|
Got my answer to this in another thread... Now I have a new problem. Ahhh, there's nothing like starting with a new technology to really knock you down a step on the ladder of ego.  Link to new thread for anyone interested in helping the NOOB  --> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98725Mike Devenney |
|
|
|
|
Parameter value not being passed for my SProc