Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSRS 2008 - show all columns in matrix? Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 7:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Hey everyone, I'm building a matrix report and I'm having an issue with all the columns not showing. Only columns with available values (where they intersect with the row attribute) display. I would like my report to display all columns whether or not they have a corresponding value with the row attribute. I tried setting the "FixedData" property to True, but this didn't seem to fix the issue. I have a hunch that the fix is pretty simple. Any advice would be much appreciated.
Post #1340595
Posted Monday, August 6, 2012 8:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:48 AM
Points: 2,057, Visits: 3,578
You'll need to do it in SQL unfortunately, returning NULL or 0 or 'None', whatever's appropriate.
Fixed data property is for keeping the data onscreen while scrolling.
Post #1340631
Posted Monday, August 6, 2012 8:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Gazareth (8/6/2012)
You'll need to do it in SQL unfortunately, returning NULL or 0 or 'None', whatever's appropriate.
Fixed data property is for keeping the data onscreen while scrolling.


Gazareth, thanks for the info. Are you referring to an IF ELSE statement? Currently, my data set is as follows:

SELECT * FROM SSRS_SumTotals_View
WHERE Month(per_start) = @Month
AND YEAR(per_start) = @Year
AND Account BETWEEN 10200 AND 10299

I believe this is limiting my location groups, but I'm not sure how to proceed. Sorry for the novice code question.
Post #1340663
Posted Monday, August 6, 2012 1:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
After further review, I believe I understand your recommendation now. Are you suggesting that I input NULL or 0 values in the table, for all the location groups that currently do not have a value?
Post #1340843
Posted Monday, August 6, 2012 1:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:58 AM
Points: 2,706, Visits: 3,426
dj1202 (8/6/2012)
After further review, I believe I understand your recommendation now. Are you suggesting that I input NULL or 0 values in the table, for all the location groups that currently do not have a value?
That or create a table that houses all available location groups, then do a left join instead of an inner join. I used to do this by creating a temp table or using a cte to gather all distinct values if it is dynamic (getting all products from a product table that are still active products), or just build a table if it is static (months in a year).


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1340845
Posted Monday, August 6, 2012 1:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:08 PM
Points: 990, Visits: 2,224
dj1202 (8/6/2012)
After further review, I believe I understand your recommendation now. Are you suggesting that I input NULL or 0 values in the table, for all the location groups that currently do not have a value?


Yes, you need to artificially add rows with missing location groups to your dataset and set all other columns in those rows to NULL, blank, 0, or whatever default value you see fit.

Left Join that Jared suggested is probably the best way to do this.

Post #1340870
Posted Monday, August 6, 2012 2:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Thank you guys. It's looking like I may have to go the insert route. I tried the LEFT JOIN and for some reason, the output did not produce records with other null values. I figured I would try a CROSS JOIN, outlined in this recommendation:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/71474038-ca6b-443e-823b-3aeb5a63cbda/

Unfortunately, I need to perform aggregate functions on the entries in my report. For example: SUM(Amount). I am not sure if that, by doing a cross join and producing duplicate amount records, it may interfere with the calculations. I will have to try it out and see. If this does not work, I have no choice than to add the artificial records. Either way it goes, it's going to be a long night! Thanks again
Post #1340902
Posted Monday, August 6, 2012 3:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:08 PM
Points: 990, Visits: 2,224
Not sure why your LEFT JOIN didn't work but just to illustrate the idea:

--This is a list of All location groups
CREATE TABLE #LocationGroups(
LocationGroup VARCHAR(20) PRIMARY KEY
);
INSERT INTO #LocationGroups(LocationGroup)
SELECT 'AAA' UNION ALL
SELECT 'BBB' UNION ALL
SELECT 'CCC' UNION ALL
SELECT 'DDD' UNION ALL
SELECT 'EEE'


--This is your current data with some location groups missing
CREATE TABLE #ReportData(
LocationGroup VARCHAR(20)
,SomeOtherData VARCHAR(100)
);
INSERT INTO #ReportData(LocationGroup, SomeOtherData)
SELECT 'AAA', 'DataA1' UNION ALL
SELECT 'AAA', 'DataA3' UNION ALL
SELECT 'CCC', 'DataC1' UNION ALL
SELECT 'EEE', 'DataE1' UNION ALL
SELECT 'EEE', 'DataE2' UNION ALL
SELECT 'EEE', 'DataE3'


--This is Left join that will produce dataset with all location groups.
SELECT
LG.LocationGroup
,RD.SomeOtherData
FROM #LocationGroups AS LG
LEFT OUTER JOIN #ReportData AS RD
ON LG.LocationGroup = RD.LocationGroup

--Cleanup
DROP TABLE #LocationGroups;
DROP TABLE #ReportData;

Post #1340926
Posted Thursday, August 16, 2012 2:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Thank you all for the responses. Unfortunately, I am still having this problem, even after implementing a join query that produces NULL values. The matrix table simply does not show columns where there is no "amount" value (the columns are "locations" and the rows are "accounts"). I tried inserting an ISNULL in my WHERE clause which produced the rows/records with the null amounts.

(ISNULL(dbo.table.date, '2012-01-01 00:00:00.000') = '2012-01-01 00:00:00.000')

All the records come up in Management Studio. When I run my report, the matrix does not show the location columns where there is a NULL amount value. Do the amount values need to be '0' for them to show? Unfortunately, I don't rights to modify the NULLS, so inputting 0's where amt IS NULL is not an option. I also tried putting a boolean in the report cell to display values (=IIF(IsNothing(Fields!amt.Value),FormatCurrency("0.00"),FormatCurrency(Sum(Fields!amt.Value)))) but this does not get the extra columns to show, only displays 0.00 for NULLS for existing columns. Anyone have any ideas? This is driving me beyond nuts...
Post #1346244
Posted Thursday, August 16, 2012 2:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Actually, I believe I found the issue. The NULL containing records are also leaving the date column as NULL, and the report is using a year(datecolumn) and month(datecolumn) parameters to pull the data, so it's not returning those records. Guess I have to find a way to join each record with all the needed dates.
Post #1346250
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse