SSRS DataSet not Asking for Parameter Values

Question

Today, While working with SSRS, i got an amazing Problem there. I'm still not being able to findout what the problem is all about. Well, I've an Stored Procedure :

ALTER PROCEDURE [dbo].[S_MEMBER_DROP_OUT_REPORT](@DTNAME VARCHAR(50),
                                                 @BRCODE VARCHAR(3),
                                                 @F_DATE VARCHAR(10),
                                                 @T_DATE VARCHAR(10),
                                                 @CLOSECODE VARCHAR(50),
                                                 @CENTRALIZED VARCHAR(3))
WITH RECOMPILE
AS BEGIN

SET NOCOUNT ON;
DECLARE @DTBASE VARCHAR(50)
DECLARE @CLOSEOPT VARCHAR(50)
SET @DTBASE=''

IF @CENTRALIZED='YES'
    SET @DTBASE = @DTNAME
ELSE
    SET @DTBASE = Left(@DTNAME, 13) + @BRCODE

IF @CLOSECODE ='0'
    BEGIN
        Exec (' 
                SELECT T1.CENTER_CODE + '' - '' + CTR.CENTER_NAME AS CENTER,COUNT(DISTINCT T1.CUSTOMER_CODE) AS CNT 
                FROM 
                    (SELECT * FROM CENTER W WHERE BR_CODE='''+@BRCODE+''') AS CTR,
                    (SELECT A1.CENTER_CODE,A1.CUSTOMER_CODE,A2.CLOSE_CODE,A2.CLOSE_DESC_CODE,MAX(A2.CLOSE_DATE) AS REDG_DATE,MAX(A2.TRAN_ID) AS TRAN_ID FROM '+@DTBASE+'.dbo.CENTER_GROUP_MEMBER AS A1, '+@DTBASE+'.dbo.CUST_REDG_HIST A2,'+@DTBASE+'.dbo.CUST_STATUS_HIST A3
                        WHERE A2.CUST_STATUS_CODE=''04'' AND A2.CLOSE_CODE<>''03'' 
                        AND A1.BR_CODE='''+@BRCODE+''' AND A2.BR_CODE='''+@BRCODE+'''
                        AND (A2.CLOSE_DATE BETWEEN '''+@F_DATE+''' AND '''+@T_DATE+''')
                        AND A1.CUSTOMER_CODE=A2.CUSTOMER_CODE 
                        AND A1.CUSTOMER_CODE=A3.CUSTOMER_CODE AND A1.CENTER_CODE=A3.CENTER_CODE AND A1.GROUP_CODE=A3.GROUP_CODE
                        AND A3.TRAN_ID=(SELECT TOP 1 TRAN_ID FROM '+@DTBASE+'.dbo.CUST_STATUS_HIST CSH WHERE A3.CUSTOMER_CODE=CSH.CUSTOMER_CODE ORDER BY TRAN_ID DESC)
                    GROUP BY A1.CENTER_CODE,A1.CUSTOMER_CODE,A2.CLOSE_CODE,A2.CLOSE_DESC_CODE
                    ) AS T1 
                WHERE T1.CENTER_CODE=CTR.CENTER_CODE
                GROUP BY T1.CENTER_CODE,CTR.CENTER_NAME
                ORDER BY T1.CENTER_CODE
             ') 
    END
ELSE
    BEGIN       
        IF LEN(@CLOSECODE) > 0 SET @CLOSEOPT=' AND A2.CLOSE_CODE IN ('+@CLOSECODE+')' ELSE SET @CLOSEOPT=''
        Exec (' 
                SELECT T1.CENTER_CODE + '' - '' + CTR.CENTER_NAME AS CENTER,CCC.CLOSE_NAME,CCD.CLOSE_DESC_NAME AS CLOSE_DESC,COUNT(DISTINCT T1.CUSTOMER_CODE) AS CNT FROM 
                    (SELECT * FROM '+@DTBASE+'.dbo.CUST_CLOSE_CAUSE) AS CCC,
                    (SELECT * FROM '+@DTBASE+'.dbo.CUST_CLOSE_DESC) AS CCD,
                    (SELECT * FROM CENTER WHERE BR_CODE='''+@BRCODE+''') AS CTR,
                    (SELECT A1.CENTER_CODE,A1.CUSTOMER_CODE,A2.CLOSE_CODE,A2.CLOSE_DESC_CODE,MAX(A2.CLOSE_DATE) AS REDG_DATE,MAX(A2.TRAN_ID) AS TRAN_ID FROM '+@DTBASE+'.dbo.CENTER_GROUP_MEMBER AS A1,'+@DTBASE+'.dbo.CUST_REDG_HIST A2,'+@DTBASE+'.dbo.CUST_STATUS_HIST A3
                        WHERE A2.CUST_STATUS_CODE=''04'' '+@CLOSEOPT+'
                        AND A1.BR_CODE='''+@BRCODE+''' AND A2.BR_CODE='''+@BRCODE+'''
                        AND (A2.CLOSE_DATE BETWEEN '''+@F_DATE+''' AND '''+@T_DATE+''')
                        AND A1.CUSTOMER_CODE=A2.CUSTOMER_CODE 
                        AND A1.CUSTOMER_CODE=A3.CUSTOMER_CODE AND A1.CENTER_CODE=A3.CENTER_CODE AND A1.GROUP_CODE=A3.GROUP_CODE
                        AND A3.TRAN_ID=(SELECT TOP 1 TRAN_ID FROM '+@DTBASE+'.dbo.CUST_STATUS_HIST CSH WHERE A3.CUSTOMER_CODE=CSH.CUSTOMER_CODE ORDER BY TRAN_ID DESC)
                    GROUP BY A1.CENTER_CODE,A1.CUSTOMER_CODE,A2.CLOSE_CODE,A2.CLOSE_DESC_CODE
                    ) AS T1 
                WHERE T1.CLOSE_DESC_CODE =CCD.CLOSE_DESC_CODE 
                AND T1.CLOSE_CODE =CCC.CLOSE_CODE 
                AND T1.CENTER_CODE=CTR.CENTER_CODE
                GROUP BY T1.CENTER_CODE,CCD.CLOSE_DESC_NAME,CCC.CLOSE_NAME,CTR.CENTER_NAME 
            ')  
    END
END

GO

The above Stored Procedure asks for a Parameter Named CLOSECODE, and based on the value provided, e.g. '0' or '01' or '02' it returns the desired fields from the appropriate table. Returned fields vary on that parameter.

When I define a dataSet in SSRS like the following:

It is not asking for any parameter values and it is producing the following fields by default:

I tried all the things that comes into my mind that what the problem is about. But still could not Solve this. And, actually, Im wondered, why the SSRS is Behaving like that Today. Im working With it Since 2-3 Months and I've so far designed lot of Reports like that, which contain the Dynamic Query, Fields from the Stored Procedure but there was no any problem, but this one.

Answer 1

Short Answer: put first result set into a #TempTable in your proc, and it then starts prompting for the Parameters in SSRS again!

Long Answer: I have written HUNDREDS of Proc's for SSRS reports where we use a Parameter to control what dataset is being output to the report, so we can have multiple data sets in the report, as well as have the parameters and data pull from the same proc. So the Proc and report are looping back an forth to provide the data. In that case, when you hit REFRESH Fields, it should ask you for the parameter of control (ours is called @DatasetFlag) So in that case, I put "Dataset1" in @DatasetFlag, and in the proc, we have IF @DatasetFlag = "Dataset1" Begin xxxxx End ELSE If @DatasetFlag = "Dataset2" Begin END ESLE and so on... Here is where SSRS if funky. SOMETIMES it asks for the parameters, sometimes it does not, in that case (not asking) it will just grab the first result set and use those fields! or better yet just stay empty!

I did find a hint at a possible solution to FORCE it to ask, and it does work!

You have to Select your data into a TEMP Table (ie #temptable1) and then it will start asking again! So at least the FIRST result set has to use a TEMP table, and then at that point, it does pull the proper fields list from the @DatasetFlag variable.

My new routine is to copy this to the start of my proc...

If (@Datasetflag = "Dummy") Begin Select getdate() as DummyDate into #TempDummy

Select DummyDate from #TempDummy

end ELSE.... The real if's then start here...

This forces the REFRESH FIELDS to always ASK for a Parameter, which then pulls the fields in dynamically and lets me create reports much faster... no hand typing 50+ field entries!

Answer 2

When SSRS determines fields for SP Datasets (at least up to 2008R2), it will call the underlying SP with SET FMTONLY ON. This only returns the resultset metadata, not all the data.

It seems like SSRS determines whether asking for parameters is required - it seems not in your case. In my experience I only get prompted when temp tables are involved in the SP - I have been unable to find any explanation here.

The problem with your Dataset is that there are two possible sets of columns returned from the SP, and it's only getting the first one, which only returns two columns - this is what you're seeing above. SSRS just hits the first return point, which has two columns, and ignores any other possible select statements.

Ignoring why you're not being asked to enter parameters (again, this is not unusual for me), I can think of a couple of options:

1: Adjust your SP to make sure whatever statement is run, the same columns are always returned.

So, in you first possible statment, I would change

IF @CLOSECODE ='0'
    BEGIN
        Exec (' 
                SELECT T1.CENTER_CODE + '' - '' + CTR.CENTER_NAME AS CENTER
                  ,COUNT(DISTINCT T1.CUSTOMER_CODE) AS CNT 
                FROM

To include all the required columns - that way SSRS will always get the correct metadata. So something like:

IF @CLOSECODE ='0'
    BEGIN
        Exec (' 
                SELECT T1.CENTER_CODE + '' - '' + CTR.CENTER_NAME AS CENTER
                  ,COUNT(DISTINCT T1.CUSTOMER_CODE) AS CNT
                  ,CAST(NULL AS VARCHAR(100)) AS CENTER
                  ,CAST(NULL AS VARCHAR(100)) AS CLOSE_NAME
                  ,CAST(NULL AS VARCHAR(100)) AS CLOSE_DESC
                  ,... -- Extra columns as required
                FROM

2: Add in missing columns in manually.

You can just add more columns into the Dataset manually in the Designer: