Query Driven SSRS Matrix Rows and Columns
Is there a way to create a table / data matrix where groups of rows and columns are cast from a separate query / dataset than the main report data?
Take, for example, a Face construct with columns: - name - Gender - marital_status
I want the columns of the matrix to always contain all "gender groups", and the rows to always contain all "family statuses" regardless of the query criteria, and the row / column intersection to be the cumulative record count.
for example the request could be select * from person where name = 'aaron'
All records returned are "men", but I still want to include a column for "woman" (where all counts in the "female" column will be 0)
I expect the output to look like this:
Marital Status: ~ Male ~ Female
Single ~ 5 ~ 0
Married ~ 8 ~ 0
Defacto ~ 2 ~ 0
...
I don't want to make a dummy request like:
select 'male' as gender, null as name, null as marital_status
union all
select 'female' as gender, null as name, null as marital_status
union all
select * from person where [ ... criteria]
It would be nice to have 3 datasets driving the matrix if possible ...
- "RowData" containing
select distinct marital_status from person
- "ColumnData" containing
select distinct gender from person
and - "MainData" containing `select * from a person, where [... criteria]
Is there a way to get the Matrix control to use separate queries for Row / Column groups?
source to share
Why is it so difficult? Think simpler ... Create a query in SQL like this:
WITH
Gender AS
(
SELECT 1 AS GenderID, 'Male' AS GenderName
UNION ALL
SELECT 2 AS GenderID, 'Female' AS GenderName
),
MartialStatus AS
(
SELECT 1 AS MartialStatusID, 'Single' AS MStatus
UNION
SELECT 2 AS MartialStatusID, 'Married' AS MStatus
),
Persons AS
(
SELECT 1 AS PersonID, 'John' AS Name, 1 AS GenderID, 2 AS MartialStatusID
UNION ALL
SELECT 2 AS PersonID, 'Linda' AS Name, 2 AS GenderID, 1 AS MartialStatusID
UNION ALL
SELECT 3 AS PersonID, 'Mike' AS Name, 1 AS GenderID, 1 AS MartialStatusID
UNION ALL
SELECT 4 AS PersonID, 'Jenna' AS Name, 2 AS GenderID, 1 AS MartialStatusID
)
SELECT Gender.GenderName, MartialStatus.MStatus, Persons.PersonID, Persons.Name
FROM Gender
CROSS JOIN MartialStatus
LEFT JOIN Persons ON Persons.GenderID = Gender.GenderID AND
Persons.MartialStatusID = MartialStatus.MartialStatusID
Result:
Male Single 3 Mike
Female Single 2 Linda
Female Single 4 Jenna
Male Married 1 John
Female Married NULL NULL
Then in BIDS in your matrix map the columns and rows:
The expression here =IIF(Sum(Fields!PersonID.Value) IS Nothing, 0, Sum(Fields!PersonID.Value))
to print is 0 if we don't have people. And you will get the result you want:
It will be easier and better for performance.
source to share