1

I would like to know how do I select a parameter based on the result set from a stored procedure.

My code looks something like this:

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
--I'll output the result set after applying all the logic like

SELECT UserID, UserName, DOJ
FROM CTE

Now, when the user selects the date on the report, I created another parameter and trying to retrieve records from the "Available Values" using 'Get values from a query'. I would like to show the list of UserIDs from the above query result set and allow the user SELECT based on that.

Parameters

Available Values for Parameters

Values from Query

Design View

5th image

UPDATE: I have created another stored procedure as there is no other alternative. Now my question is, how do I pass the second stored procedure value to the first one in WHERE clause?

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
--I'll output the result set after applying all the logic like

SELECT User_ID, UserName, DOJ
FROM CTE
WHERE User_ID = EXEC usp_Get_UserID

I am looking for something like this where the user can select the ID first and any date of his choice in the report.

Report Parameters

PS: I am not allowed to write in-line SQL and only SPs are allowed. I don't want to create two SPs for the same report.

Julaayi
  • 403
  • 2
  • 8
  • 23

2 Answers2

0

Use this stored procedure as the source of available values for the UserID parameter, and make sure that the UserID parameter comes AFTER the ReportDate parameter. Then the UserID available values will not be populated until after the user chooses ReportDate. Then this procedure will run using the user-selected ReportDate.

Also I think you need to remove the @User_ID parameter from your DataSet parameters (the last screenshot). Your stored procedure only has the one ReportRunDate parameter. The parameters in your SSRS dataset must match the parameters in the stored proc you are calling.

Regarding this error:

The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.

It sounds like your stored procedure code was changed after you last edited your SSRS report, and now it doesn't return the expected values. Use a profiler trace to see what command is sent from SSRS to your SQL Server, then execute that command in SSMS and look at the results. What are the column names being returned? Are they the same names in the Fields tab of your dataset? Are they the same names as your Value Field and Label Field in your report parameter?

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I tried this but it didn't work. The UserID dropdown list is showing some numbers that doesn't exist (say a -1 value or some 0s for example) in the actual result set from SP. Also, selecting the 'Available Values' option to retrieve the UserIDs is taking a lot of time to load into the drop-down list of SSRS. – Julaayi Jul 08 '16 at 19:40
  • OK, I've added screenshots to my OP. – Julaayi Jul 08 '16 at 20:11
  • I've added another screenshot at the end of the OP. Are you talking about that? – Julaayi Jul 08 '16 at 20:26
  • When I run the stored procedure in SSMS, I get the entire result set (what is needed). When I try to filter out that on the report based on ID, it doesn't work. When refreshed, I am getting the error _The expression used for the parameter'@User_ID' in the dataset '@User_ID' refers to a field. Fields cannot be used in query parameter expression_. – Julaayi Jul 08 '16 at 20:47
  • What is the expression you are using for the value of the @User_ID parameter? Your screenshot only shows `<>` – Tab Alleman Jul 11 '16 at 12:51
  • The value I'm having in the expression is **Fields!User_ID.Value** – Julaayi Jul 11 '16 at 15:05
  • I am confused now. Your first paragraph says to use `User_ID` parameter. And your second paragraph says to remove the `User_ID` parameter. If I remove the `User_ID` parameter, how do give an option to the customer to select a `User_ID` of his choice? – Julaayi Jul 11 '16 at 15:43
  • Ok, in the first paragraph, I am talking about the User_ID parameter of the report. In the second paragraph I am talking about the User_ID parameter of the dataset. This should be the dataset that is used to populate the User_ID parameter of your report. This dataset should not have a User_ID parameter in itself. – Tab Alleman Jul 11 '16 at 15:55
  • Well, that was what I tried in the very beginning. I am getting the error _The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error._ when doing so. – Julaayi Jul 11 '16 at 16:08
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/117008/discussion-between-julaayi-and-tab-alleman). – Julaayi Jul 11 '16 at 18:05
0

I've ended up using a stand alone SQL with advice from many experts to get the User_ID. I've added another parameter to the stored procedure and passing the second value to the parameter from the standalone SQL.

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL, @User_ID VARCHAR(10)) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
AND UserID = @User_ID
--I'll output the result set after applying all the logic like

SELECT UserID, UserName, DOJ
FROM CTE

And, my standalone SQL looks something like:

SELECT DISTINCT User_ID
FROM MyTable
ORDER BY User_ID
Julaayi
  • 403
  • 2
  • 8
  • 23