Generate Columns in Matrix 1

[DebbieC]

I'm having a problem with using a matrix. It's hard to explain but basically I have two tables. I want the columns from one table to return rows in the matrix and the rows in another table to be the columns in the matrix. It works great when there is data for each one but if there isn't then the column is not created but I want it to generate the column with blank fields (basically NULL's). If I generate a row with NULL's it does it but I can't do this in Production. Is there a way to generate a column if there isn't a row in the table?

I hope this isn't too confusing.

 

[Catadmin]

I take it your two tables are two different data sets? If so, how do you have them defined in the Matrix?

Have you tried combining the tables in a stored procedure record set to see if it returns your NULL columns?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DebbieC]

No, they are in the same dataset using a Union All. I thought you couldn't use more than one dataset in the same matrix or table. Here is an example:

SELECT tDBHReports.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231, tDBHReports.QM232, tDBHReports.QM233, tDBHReports.QM234, tDBHReports.QM235, tDBHReports.QM251, tDBHReports.QM412, tDBHReports.QM413, tDBHReports.QM414,
tDBHReports.QM461
FROM tDBHReports
INNER JOIN tUnits ON tDBHReports.UnitNum = tUnits.UnitNum
WHERE (tDBHReports.RepPeriod = '10/2006') AND (tDBHReports.UnitNum = '36BG1')
Union all

SELECT tDBHReports.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231, tDBHReports.QM232, tDBHReports.QM233, tDBHReports.QM234, tDBHReports.QM235, tDBHReports.QM251, tDBHReports.QM412, tDBHReports.QM413, tDBHReports.QM414,
tDBHReports.QM461
FROM tDBHReports
INNER JOIN tUnits ON tDBHReports.UnitNum = tUnits.UnitNum
WHERE (tDBHReports.RepPeriod = '10/2006') AND (tDBHReports.UnitNum = '36CT1')
Union all

etc.....

This is just part of it.

It's the UnitNum's that I want to be the columns.

Do you mean trying it in SQL Query Analyzer? I did and it only returns those that have the records. It generates a record for each UnitNum that returns a survey and I need to generate a report that shows the results. I need blank columns for those that don't so that they know which ones haven't returned their surveys.

 

[Catadmin]

Okay, the reason it's not returning records with a NULL UnitNum is that you're doing INNER JOINS on that column. It will never return NULL as long as you're using an INNER JOIN.

Which table is it that will have the NULL UnitNum? That is the table you'll want on the left side of your join and then do a LEFT OUTER so it fetches all the records of that table and then only the matching records of the other table.

If both tables on either side of the join have possible NULLS for UnitNum, you need to find a different column to join on. NULL cannot be joined to NULL because it doesn't always mean the same thing.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DebbieC]

That sounds exactly what I need. I was trying to figure out a way to do that. I was thinking I had to do a function in the report that said For Each in the 1st table....If None some how still create a column. I don't know a lot about SQL so I didn't catch that. I even had one of our guru's here help me and he suggested the function too. I didn't want to have to do that.

Thank you so much. I will give that a try.

 

[DebbieC]

I changed it from:

FROM tDBHReports
INNER JOIN tUnits ON tDBHReports.UnitNum = tUnits.UnitNum

to:

FROM dbo.tUnits
LEFT OUTER JOIN dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum

Because it's the tUnits table that has the full list that I want included whether or not there is data in the tDBHReports table that corresponds to the UnitNum but it's still doing the same thing.

Did I do something wrong? It complicates things because I am using a Matrix so the columns are generated, I can't physically create the columns.

 

[Catadmin]

Sorry, I'm not sure I understood the last statement. Are you saying the Matrix is creating the columns or not creating the columns? Are you having the same exact problem as before, or does it seem different (even slightly)?

In QA or SSMS, does the code properly return tDBHReports' NULL columns in the SELECT list? Have you tested this with SELECTing both on tDBHReports and tUnits UnitNum column?





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DebbieC]

The Matrix is creating the columns for the records that exist in the second table but not for the ones that don't because there isn't a record so there aren't even any NULL's. If there were records with NULL's it would work.

As far as doing a SELECT on both tDBHReports and tUnits UnitNum column, I tried the following and it didn't work either:

SELECT tUnits.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231, tDBHReports.QM232, tDBHReports.QM233, tDBHReports.QM234, tDBHReports.QM235
FROM dbo.tUnits LEFT OUTER JOIN
dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum
WHERE (tDBHReports.RepPeriod = @Date) AND (tUnits.UnitNum = '36BG1')
Union all

SELECT tUnits.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231, tDBHReports.QM232, tDBHReports.QM233, tDBHReports.QM234, tDBHReports.QM235
FROM dbo.tUnits LEFT OUTER JOIN
dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum
WHERE (tDBHReports.RepPeriod = @Date) AND (tUnits.UnitNum = '36CT1')
Union all
etc....

I don't know what else to try but it sounds like we are on the right track.

 

[Catadmin]

If there isn't a record at all, I'm not sure what else you can do. Unless, in the SELECT statement, you try using the ISNULL() function around your tDBHReports columns.

Like:

SELECT tUnits.UnitNum, tUnits.UnitNameAbbrv, ISNULL(tDBHReports.QM231,'NADA'), ISNULL(tDBHReports.QM232,'NADA)......
FROM dbo.tUnits LEFT OUTER JOIN
dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum
WHERE     (tDBHReports.RepPeriod = @Date) AND (tUnits.UnitNum = '36BG1')
Union all

Again, I'm not sure that will work or not. But in SSMS or QA, if you're running the code, then for every record in tUnits that is not in tDBHReports, then all the tDBHReports columns should return 'NADA' while the tUnits columns return proper values.

If there is a record in tDBHReports that isn't in tUnits, then this won't help you. If there aren't any records in either, then you won't get anything at all because you can't generate a record set on something that doesn't exist.

Anyway, you should be first testing your code in QA or SSMS before going back to the Matrix. Once you get your code there to display the records you want and display them correctly, then you can go back to SRS and attempt the Matrix again. But if your code isn't pulling what you want in QA/SSMS, then don't bother fiddling with Reporting Services because you're just going to get frustrated.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DebbieC]

Unfortunately it's not working because there isn't a NULL in the database. There isn't one at all. However I would think that with the LEFT OUTER JOIN it would at least return a record if there is one in tUnits. Is the following correct to get each one in tUnits?

FROM dbo.tUnits
LEFT OUTER JOIN dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum

Should I use dbo.tDBHReports.UnitNum in the SELECT statement also or do I just need dbo.tUnits.UnitNum?

I might have to do a FUNCTION in Reporting Services that says if a record is not returned then put in a blank record anyways but I don't know if you can use a FUNCTION to return records for a matrix.

 

[Catadmin]

Wait a minute. SO you're saying that when you have a record in tUnits that does not correspond to a UnitNum in tDBHReports, the columns for tDBHReports do not show up at all in your record set in QA?

They should at least be showing up as NULL unless there's something in your WHERE clause which is looking for specific values in tDBHReports. In which case, you need to enclose all the specific values into parens together and then add the "OR tDBHReports.UnitNum IS NULL" to the end of the WHERE clause.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DebbieC]

Yes, that is correct. It would only return the rows where there was a corresponding UnitNum in the second table. I took your advice and did the following:

SELECT tUnits.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231
FROM dbo.tUnits LEFT OUTER JOIN
dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum
WHERE (tDBHReports.RepPeriod = '11/2006' AND tUnits.UnitNum = '36BG1') OR tDBHReports.UnitNum IS NULL

That didn't return anything (because 36BG1 is one of them that doesn't exist in the second table). I had a select statement for each of the UnitNum's with a union so I decided to do one select statement without specifying any specific UnitNum's and it still only returned those that also appeared in the second table.

 

[DebbieC]

I researched more on LEFT OUTER JOIN's and one of the websites showed the syntax a little bit different. Instead of doing the WHERE statement it was done in the LEFT OUTER JOIN part.

This is what I did and it worked:

SELECT tUnits.UnitNum, tUnits.UnitNameAbbrv, tDBHReports.QM231
FROM dbo.tUnits LEFT OUTER JOIN
dbo.tDBHReports ON dbo.tUnits.UnitNum = dbo.tDBHReports.UnitNum AND tDBHReports.RepPeriod = '11/2006'

Not only that but I didn't have to put AND tUnits.UnitNum = '36BG1' for 14 different UnitNum's and Select statements. It just takes one select statement and it works.

Thank you so much for all of your effort to help me resolve this problem. You helped me see that it was in the JOIN statement which helped to resolve the issue.

 

[Catadmin]

I'm glad you were able to figure out the problem. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"