Hello Experts,

I am using 3 Datasets for my report, which contains 3 tablix.  Dataset1 linked to Tablix1, Dataset2 linked to Tablix2 and Dataset3 to Tablix3 respectively. Also I have 4 parameters @StartDate, @EndDate, @ISTop10 and @Is_Top10.  I have set this @ISTop10 (Prompted as :  Top 10 Practices ) parameter as Boolean, Available values 'None' and Default values 'Specify values' (false). Similarly @Is_Top10 parameter set as Boolean, Available values 'None' and Default Values'specify Values' (false)

All the three separate query (Dataset 1, 2, 3) are running successfully  in SSMS. Even there is no error while running this report. However, this report shows perfect data for Tablix1 only while for Tablix2 and Tablix3 pulling NO data only the Header and Column Name . 

FYI - During Preview, I am selecting StartDate, EndDate, Top 10 Practice : True  and Top 10 Providers : True then hitting View Report.  I am using SSRS 2008 r2.

Please help ASAP. Thank you  :)

-- Dataset2 for Top_10_Practices --

DECLARE @Date1 DATETIME ,
    @Date2 DATETIME ,
    @ISTop10 INT


SELECT        TOP 10 d .practice_id, d .mapped_to_practice, LTRIM(b.User_Desc) AS Worklist, a.Sent_To, a.Username AS Sent_From, 
                          COUNT(DISTINCT c1.proccode) AS Proc_Count, row_number() OVER (ORDER BY COUNT(DISTINCT proccode) DESC, Practice_ID) 
AS Rank_Prac
FROM            HTPN_Medic.dbo.HTPN_OI_Worklist AS a INNER JOIN
                         HTPN_Medic.dbo.HTPN_OI_Users AS b ON a.Sent_To = b.Username INNER JOIN
                         htpn_vision.dbo.ifx1_fin_header AS c ON LEFT(Item_ID, CHARINDEX('|', Item_ID) - 1) = RTRIM(c.patkey) AND RIGHT(Item_ID, LEN(Item_ID) - CHARINDEX('|', Item_ID)) 
                         = RTRIM(c.invno) INNER JOIN
                         htpn_vision.dbo.ifx1_fin_detail AS c1 ON c.patkey = c1.patkey AND c.invno = c1.invno AND c1.rec_type = '1' LEFT OUTER JOIN
                         htpn_vision.dbo.ifx1_fin_auxdr AS c2 ON c.patkey = c2.patkey AND c.invno = c2.invno AND c2.orderno = 1 LEFT OUTER JOIN
                         HTPN_Warehouse.dbo.r_Provider_Xref_Master AS d ON d .Source_ID = 27 AND c.drno = Dr_ID AND c.groupno = d .GroupNo AND c1.faccode = d .faccode AND 
                         c.deptno = d .deptno AND ISNULL(c2.auxdr, '0') = d .Extender_ID
WHERE        Sent_To IN ('BHNIADJ_V', 'BHNIADJ_M') AND (a.Item_Type = 3) AND Date_Stamp BETWEEN @Date1 AND @Date2 AND @ISTop10 = 1
GROUP BY LTRIM(b.User_Desc), a.Sent_To, a.Username, d .practice_id, d .mapped_to_practice



-- Dataset3 for Top 10 Providers --

DECLARE @Date1 DATETIME, @Date2 DATETIME, @Is_Top10 INT 

SELECT        TOP 10  LTRIM(b.User_Desc) AS Worklist, a.Sent_To, 
                                                                                                                                                                        a.Username AS Sent_From, d .dr_id, d .mapped_to_physician, COUNT(DISTINCT c1.proccode) 
                                                                                                                                                                        AS Proc_Count, row_number() OVER (ORDER BY COUNT(DISTINCT proccode) DESC, dr_ID) 
AS Rank_Prov
FROM            HTPN_Medic.dbo.HTPN_OI_Worklist AS a INNER JOIN
                         HTPN_Medic.dbo.HTPN_OI_Users AS b ON a.Sent_To = b.Username INNER JOIN
                         htpn_vision.dbo.ifx1_fin_header AS c ON LEFT(Item_ID, CHARINDEX('|', Item_ID) - 1) = RTRIM(c.patkey) AND RIGHT(Item_ID, LEN(Item_ID) - CHARINDEX('|', Item_ID)) 
                         = RTRIM(c.invno) INNER JOIN
                         htpn_vision.dbo.ifx1_fin_detail AS c1 ON c.patkey = c1.patkey AND c.invno = c1.invno AND c1.rec_type = '1' LEFT OUTER JOIN
                         htpn_vision.dbo.ifx1_fin_auxdr AS c2 ON c.patkey = c2.patkey AND c.invno = c2.invno AND c2.orderno = 1 LEFT OUTER JOIN
                         HTPN_Warehouse.dbo.r_Provider_Xref_Master AS d ON d .Source_ID = 27 AND c.drno = Dr_ID AND c.groupno = d .GroupNo AND c1.faccode = d .faccode AND 
                         c.deptno = d .deptno AND ISNULL(c2.auxdr, '0') = d .Extender_ID
WHERE        Sent_To IN ('BHNIADJ_V', 'BHNIADJ_M') AND (a.Item_Type = 3) AND Date_Stamp BETWEEN @Date1 AND @Date2 AND @Is_Top10 = 1
GROUP BY LTRIM(b.User_Desc), a.Sent_To, a.Username, d .dr_id, d .mapped_to_physician

Above codes are running successfully in SSMS and pulling correct data but when I am using this same code for SSRS 2008 r2 reports then are neither pulling any data nor showing any error. I think I am missing something. Please help me.

Hello Experts,

Below codes are running successfully in SSMS and pulling correct data but when I am using this same code for SSRS 2008 r2 reports then are neither pulling any data nor showing any error.  The parameters @StartDate, @EndDate, @ISTop10 and @Is_Top10.  I have set @StartDate, @EndDate as Date/Time and this @ISTop10 (Prompted as :  Top 10 Practices ) parameter as Boolean, Available values 'None' and Default values 'Specify values' (false). Similarly @Is_Top10 parameter set as Boolean, Available values 'None' and Default Values'specify Values' (false).  During Preview I am selecting StartDate, EndDate, Top 10 Practice : True  and Top 10 Providers : True then hitting View Report. Tablix1 and 2  are pulling NO data, only the Header and Column Name for both of them.

Please help ASAP. I believe I am missing something. I am desperately looking for some tips on this issue. 

-- Dataset1 for Top_10_Practices --

DECLARE @Date1 DATETIME ,
    @Date2 DATETIME ,
    @ISTop10 INT


SELECT        TOP 10 d .practice_id, d .mapped_to_practice, LTRIM(b.User_Desc) AS Worklist, a.Sent_To, a.Username AS Sent_From, 
                          COUNT(DISTINCT c1.proccode) AS Proc_Count, row_number() OVER (ORDER BY COUNT(DISTINCT proccode) DESC, Practice_ID) 
AS Rank_Prac
FROM            HTPN_Medic.dbo.HTPN_OI_Worklist AS a INNER JOIN
                         HTPN_Medic.dbo.HTPN_OI_Users AS b ON a.Sent_To = b.Username INNER JOIN
                         htpn_vision.dbo.ifx1_fin_header AS c ON LEFT(Item_ID, CHARINDEX('|', Item_ID) - 1) = RTRIM(c.patkey) AND RIGHT(Item_ID, LEN(Item_ID) - CHARINDEX('|', Item_ID)) 
                         = RTRIM(c.invno) INNER JOIN
                         htpn_vision.dbo.ifx1_fin_detail AS c1 ON c.patkey = c1.patkey AND c.invno = c1.invno AND c1.rec_type = '1' LEFT OUTER JOIN
                         htpn_vision.dbo.ifx1_fin_auxdr AS c2 ON c.patkey = c2.patkey AND c.invno = c2.invno AND c2.orderno = 1 LEFT OUTER JOIN
                         HTPN_Warehouse.dbo.r_Provider_Xref_Master AS d ON d .Source_ID = 27 AND c.drno = Dr_ID AND c.groupno = d .GroupNo AND c1.faccode = d .faccode AND 
                         c.deptno = d .deptno AND ISNULL(c2.auxdr, '0') = d .Extender_ID
WHERE        Sent_To IN ('BHNIADJ_V', 'BHNIADJ_M') AND (a.Item_Type = 3) AND Date_Stamp BETWEEN @Date1 AND @Date2 AND @ISTop10 = 1
GROUP BY LTRIM(b.User_Desc), a.Sent_To, a.Username, d .practice_id, d .mapped_to_practice



-- Dataset2 for Top 10 Providers --

DECLARE @Date1 DATETIME, @Date2 DATETIME, @Is_Top10 INT 

SELECT        TOP 10  LTRIM(b.User_Desc) AS Worklist, a.Sent_To, 
                                                                                                                                                                        a.Username AS Sent_From, d .dr_id, d .mapped_to_physician, COUNT(DISTINCT c1.proccode) 
                                                                                                                                                                        AS Proc_Count, row_number() OVER (ORDER BY COUNT(DISTINCT proccode) DESC, dr_ID) 
AS Rank_Prov
FROM            HTPN_Medic.dbo.HTPN_OI_Worklist AS a INNER JOIN
                         HTPN_Medic.dbo.HTPN_OI_Users AS b ON a.Sent_To = b.Username INNER JOIN
                         htpn_vision.dbo.ifx1_fin_header AS c ON LEFT(Item_ID, CHARINDEX('|', Item_ID) - 1) = RTRIM(c.patkey) AND RIGHT(Item_ID, LEN(Item_ID) - CHARINDEX('|', Item_ID)) 
                         = RTRIM(c.invno) INNER JOIN
                         htpn_vision.dbo.ifx1_fin_detail AS c1 ON c.patkey = c1.patkey AND c.invno = c1.invno AND c1.rec_type = '1' LEFT OUTER JOIN
                         htpn_vision.dbo.ifx1_fin_auxdr AS c2 ON c.patkey = c2.patkey AND c.invno = c2.invno AND c2.orderno = 1 LEFT OUTER JOIN
                         HTPN_Warehouse.dbo.r_Provider_Xref_Master AS d ON d .Source_ID = 27 AND c.drno = Dr_ID AND c.groupno = d .GroupNo AND c1.faccode = d .faccode AND 
                         c.deptno = d .deptno AND ISNULL(c2.auxdr, '0') = d .Extender_ID
WHERE        Sent_To IN ('BHNIADJ_V', 'BHNIADJ_M') AND (a.Item_Type = 3) AND Date_Stamp BETWEEN @Date1 AND @Date2 AND @Is_Top10 = 1
GROUP BY LTRIM(b.User_Desc), a.Sent_To, a.Username, d .dr_id, d .mapped_to_physician

• Merged by Katherine XiongMicrosoft contingent staff, Moderator 2 hours 4 minutes ago duplicate

Hi jmtnan,

Based on my test, I find this issue is caused by declare statement. When we declare some variables, all variables are initialized as NULL, unless a value is provided as part of the declaration. This scenario is just like yours. It means that @Date1= NULL, @Date2=NULL and @ISTop10=NULL, so under the where conditions, the query should be return no data.

According to your requirement, we can use the following two methods to fix this issue:
Method 1:

1. Delete the declare statement in the Dataset2 and Dataset3.
   
2. Modify the @Date1 to @StartDate, @Date2 to @EndDate in the Dataset2 and Dataset3.

Method 2:

1. Use the query as below to create a stored procedure in the SSMS.
   create procedure Top_10_Practices (@StartDate DATETIME , @EndDate DATETIME ,@ISTop10 INT)
   as
   begin
   SELECT   TOP 10 d .practice_id, d .mapped_to_practice
   where  Sent_To IN ('BHNIADJ_V', 'BHNIADJ_M') AND (a.Item_Type = 3) AND Date_Stamp BETWEEN @StartDate AND @EndDate AND @ISTop10 = 1
   GROUP BY LTRIM(b.User_Desc), a.Sent_To, a.Username, d .practice_id, d .mapped_to_practice
   END
   
2. Use the Top_10_Practices stored procedure as the Dataset2 query.
   
3. Use the same method to modify the Dataset3 query.

If there are any other questions, please feel free to let me know.

Thanks,
Katherine Xiong