CODEWITHSUNDEEP
sql-servert-sqlreporting-services
Serdia
Serdia

Reputation: 4418

How to create SSRS report using procedure that returns data based on parameter

I have a stored procedure that returns number of columns based on parameter @SearchType. So first it checks what is the parameter value, then SELECT statement returns data.

My problem is how can I use this procedure in SSRS? It doesn't bring me any columns so I can add it to a table or matrix.

In SSMS the procedure works fine. There gotta be a work around for that.

Thanks

enter image description here

ALTER PROCEDURE dbo.RPT_spLoadUserOpenTasks
      (
        @UserGUID varchar(8000) = NULL,
        @NoteStoreType INT = NULL,
        @DateFrom datetime = NULL,
        @DateTo datetime =NULL,
        @LineGUID varchar(8000) = NULL
      ) 

AS
 IF @SearchType = 0
 BEGIN 
       SELECT DISTINCT 
            TOP 100 PERCENT 
                Column1,
                Column2,
                Column3
                --other columns
         FROM 
                  dbo.tblNoteEntities  (nolock) 
           --some other JOINs here 
         WHERE tq.LineGuid IN (SELECT * FROM @LineTable)
                AND               
                  (@UserGUID IS NULL OR tblNoteRecipients.UserGUID = @UserGUID) 
             ORDER BY  Column1
      RETURN
END
--------------------------------------------------------------------------------
if @SearchType = 1  --eq and eq access 
BEGIN 
SELECT DISTINCT 
            TOP 100 PERCENT 
                Column4,
                Column5,
                Column6
         FROM 
                  dbo.tblNoteEntities  (nolock) 
           --some other JOINs here 
         WHERE tq.LineGuid IN (SELECT * FROM @LineTable)
                AND               
                  (@UserGUID IS NULL OR tblNoteRecipients.UserGUID = @UserGUID) 
             ORDER BY  Column1
      RETURN
END
--------------------------------------------------------------------------------
if @SearchType = 3 --And so on

Upvotes: 0

Views: 72

Answers (1)

niktrs
niktrs

Reputation: 10066

You have to define the columns of your query outputs manually in the dataset.

Since column names and number is not static try to do so by having each query to return the same number of columns having empty data for columns not required.

For each value of your parameter, create the table containing the required columns and set table visibility depending the selected value of the parameter (for example if parameter has 3 values you have to create 3 tables but only one will be visible each time).

Upvotes: 1

Related Questions