I have a filtered table (filtered by t1 and t3 values) with 6 columns (it has event number and t2 - t6 visible) in my SSRS report. I am using report builder. I am having a problem because the t6 (Reason_Note) variable doesn't always have a value. And I don't mean it has a blank value. It appears as if that test does NOT EXIST.
For example: Over a timeframe, There are 5 rows of data that should appear in report. But for 2 of the 5, t6 has NO data (test doesn't exist), so the report only shows 3 rows. But I still want it to show all 5 rows it should, just leaving t6 blank. And I still want the t6 value to correspond to the correct rows (lined up with other columns). Is it possible to force t6 to be blank if the test doesn't exist?
I have tried IsNothing tests, but it doesn't work. It doesn't even recognize the test, so it seems like it doesn't even test for something/nothing.
Any help would be appreciated. Here is my current query:
select e.event_num, event_status, e.event_ID, t1.result as Quality_issue, t2.result as
Reason, t3.result as Storage, t4.result as Grade, t5.result as Roll_Bulk, t6.result as
Reason_Note, prodstatus_desc_local P from events e
left outer join tests t1 on (e.timestamp = t1.result_on)
left outer join tests t2 on (e.timestamp = t2.result_on)
left outer join tests t3 on (e.timestamp = t3.result_on)
left outer join tests t4 on (e.timestamp = t4.result_on)
left outer join tests t5 on (e.timestamp = t5.result_on)
left outer join tests t6 on (e.timestamp = t6.result_on)
left outer join Production_status P on (e.Event_Status = P.ProdStatus_Id)
Where (e.pu_id =2 and t1.var_id = 24777 and t2.var_id = 16 and t3.var_id = 312 and t4.var_id
= 1249 and t5.var_id = 1130 and t6.var_id = 3657 )
and t1.result_on >=@StartTime
and t1.result_on <@EndTime
That is the data I get currently (Not sure if that picture worked). I would expect to have three more rows of data, but there is no value for t6 in those 3 rows, so it's not pulled in. The only parameters are start time and end time.
