SSRS Report Manager: Linked report behaving strangely but SQL queries work fine

Question

I have created a report (call it Primary) and a drillthrough (call it Secondary) in Report Builder. Each of these has a SQL statement.

When executed in SQL Server Management Studio, SQL statements work as expected.

However, when the Primary.rdl and Secondary.rdl are uploaded to Report Manager (the web interface in Internet Explorer), they do not generate the correct data when run.

Because of this, I think the problem is not the SQL statements. I think it's something to do with the Report Manager.

Primary SQL statement:

This statement grabs a bunch of user data from multiple tables and checks if their passwords are acceptable. It populates a list of users whose passwords failed the check.

This is pseudocode so pardon inconsistencies in var names

with details as (
select u.userid
     , u.password
     , u.firstname
     , u.lastname
     , u.userdescription
     , u.status
     , u.lastlog
     , dbo.IsPassswordAcceptable(u.userid, u.password) as passStatus
  from masterListOfUsers as u
)
select d.*, p.datavalue
  from details as d
 left join passwordDetailList as p
    on p.keyvalue = d.passStatus
   and p.datatype = 'ERRORMESSAGE'
 where d.passStatus <> 1 
   and d.passStatus <> -5 
   and d.status = (@USERSTATUS) -- only user ids in use
     ;

Secondary SQL statement:

This statement is a drillthrough. The person running the report can click on a userID in the above list. A drillthrough is performed where the contact information for that userID is populated.

This is pseudocode so pardon inconsistencies in var names

   SELECT 
      m.userid 
    , c.address
    , c.city 
    , c.state 
    , c.zip 
    , c.cphone 
 FROM userMasterList AS m
left join userDetailList AS d 
   ON d.userid = m.userid 
left join anotherList as e on d.fullkey = e.fullkey 
left join yetAnotherList AS c 
WHERE m.userid = @USERID;

Expected result:

When the user runs the Primary, a list of users with bad passwords is populated. Each user's userID can be clicked on, which triggers the Secondary to populate the location/contact info associated with that userID.

Actual result:

On userID click, the Secondary fails to populate any location/contact info associated with the userID. This occurs only sometimes. Other times, it works fine.

I made a list of these "empty" userIDs and ran the Secondary's SQL statement in Management Studio, and it populates all the expected location/contact info.

Solutions I've tried:

I'm absolutely stumped. I've triple-checked the SQL statements and tested them in Management Studio. I've re-uploaded both .rdl files to Report Manager. I've reassigned the Secondary to the Primary via the "Create Linked Report" option in Report Manager AND ALSO in Report Builder's Action > Go To Report option.

What else can I do?

Answer 1

This is not really an answer as such, but a list of things I would work thru in the same situation.

1. Run SQL Profiler to trace your report session and make sure the query being executed is what you expect. Depending on how parameters are passed to the SQL statements, SSRS will not always do things quite the way you expected.

2. Check if you can repeat the issue by just running the drill thru report on it's own (not via the primary report)

3. Determine if the issue is consistent with specific userids? i.e. does user A always fail and User B always work? If the issue is consistent, the issue is most likely to be data related. Check for special characters in the fields that appear to be blank such as chr(13)/chr(10), they may just be forcing the 'real' content onto a new line inside the textbox.

4. Add some debug info to your report to help identify the issue such as:

   a. Edit the dataset query to add some more info from dataset itself SELECT .... , c.addrees, len(c.address) as AddresLen from .... You can add this to a copy of your report

   b. Add another textbox that does the same thing but directly in SSRS (e.g. expression would be something like =LEN(Fields!address.Value)). You then have two numbers to compare against what you can see. If the LEN textbox says 20 but the address field appears blank, then special characters could be the issue.

Answer 2

After hours of tinkering, the problem ended up being that the userID was being trimmed of all leading and trailing whitespace by some of the query tools but not by the SQL statements themselves. So when the final report is run in Report Manager, the data is queried with superfluous whitespace, resulting in no data being found.

This issue is resolved when the data points are trimmed.

The fixed Secondary SQL statement:

This is pseudocode so pardon inconsistencies in var names

SELECT 
      rtrim(m.userid) as userid 
    , rtrim(c.address) as address
    , rtrim(c.city) as city 
    , rtrim(c.state) as state
    , rtrim(c.zip) as zip 
    , rtrim(c.phone) as phone 
FROM userMasterList AS m
left join userDetailList AS d 
  ON d.userid = m.userid 
left join anotherList as e on d.fullkey = e.fullkey 
left join yetAnotherList AS c 
WHERE ltrim(rtrim(m.userid)) = ltrim(rtrim(@USERID));