August 6, 2012 at 7:28 am
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.
August 6, 2012 at 8:00 am
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.
August 6, 2012 at 8:34 am
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.
August 6, 2012 at 1:00 pm
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?
August 6, 2012 at 1:04 pm
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).
Jared
CE - Microsoft
August 6, 2012 at 1:37 pm
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.
--Vadim R.
August 6, 2012 at 2:52 pm
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:
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
August 6, 2012 at 3:53 pm
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;
--Vadim R.
August 16, 2012 at 2:38 pm
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...
August 16, 2012 at 2:48 pm
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.
August 16, 2012 at 3:38 pm
Alright all, after three panicky posts in the span of an hour, the issue has been resolved. Moved to the dataset query in SSRS and added a 'OR Year(datecolumn) IS NULL' and it returns the rows. Thanks again for the feedback.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply