0

After trying a lot on Google, I ran out of luck and here I am seeking your help. I am trying to create a dataset in SSRS using a Stored Procedure and I am having hard time. Could someone please shed me light of what I am missing?

Here is the how my Stored Procedure looks like:

    CREATE PROCEDURE [dbo].[usp_GetUserData](@ReportRunDate DATE        = NULL,
                                         @ReportName    VARCHAR(50) = NULL)
AS
     BEGIN
         WITH CTE
              AS (SELECT [USER_ID], [Name], [Address], [DOB], [Status], [UserType], [DateAdded]
                  FROM [dbo].[User_Information]
               WHERE [DateAdded] = @ReportRunDate)
              SELECT *
              INTO #UserReport
              FROM CTE;
         IF(@ReportName = 'Internal')
             BEGIN
                 SELECT [USER_ID], [Name], [Address], [DOB]
                 FROM #InternalReport
                 WHERE [UserType] = 'Internal';
                 SELECT *
                 FROM #InternalReport;
             END;
         IF(@ReportName = 'External')
             BEGIN
                 SELECT [Name],[Status]
                 FROM #ExternalReport
                 WHERE [UserType] = 'External';
                 SELECT *
                 FROM #ExternalReport;
             END;
     END;

I've changed the order of parameters as mentioned by many people in SO but that didn't help either as well.

Julaayi
  • 403
  • 2
  • 8
  • 23
  • Where did you change the order of the parameters? The stored procedure, or in the report's RDL? The RDL is where the order really matters when you are trying to use cascading parameters. A parameter that depends on a value from another parameter must be listed after the parameter it depends on. – R. Richards May 20 '16 at 21:04
  • Thanks @R.Richards. I did change at the RDL level itself. – Julaayi May 20 '16 at 21:33
  • Is it possible to add a screen capture of the Parameters that you have in your report? And the SQL that each parameter uses for its source data, if applicable. I can reproduce this error, but only when the parameters are not in the right order. – R. Richards May 20 '16 at 22:02
  • Unfortunately, I cannot add the screen capture due to some restrictions from my work machine. What I did currently is create two datasets with each parameter being passed to the Stored procedure. That way I have two result sets and I can pass only the date so that I get all the reports for that day. I am not sure if that is a valid way to do but I really appreciate if you could let me know doing it in a better way. – Julaayi May 23 '16 at 15:12
  • I don't see any issue in the Stored Procedure, are there any other datasets used in the report for populating parameters which has default value or are filtered based on other parameter selection? – Jatin Patel Jun 20 '16 at 10:09
  • 1
    Thanks @JatinPatel. I've deleted the report and recreated it and added default values to the ReportName parameter and it worked. – Julaayi Jun 22 '16 at 00:59

0 Answers0