3
votes

Is there a way to create a table/matrix of data whereby the row & column groups are driven from a separate query/dataset than that of the main report data?

Take, for example, the construct of "Person" with columns: - name - gender - marital_status

I want the columns of the matrix to always contain all "genders", and the rows to always contain all "marital statuses" regardless of the criteria of the query, and the intersection of rows/columns to be the aggregate count of records.

for example, the query might be select * from person where name = 'aaron'

All records returned are "male", but I still want to include the column for "female" (where all the counts in the "female" column would be 0)

I would expect output to look like:

Marital Status:  ~ Male ~ Female
Single ~ 5 ~ 0
Married ~ 8 ~ 0
Defacto ~ 2 ~ 0
...

I do not want to have to do a dummy query, for example:

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 fine 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 person where [...criteria]

Is there any way to get a Matrix control to use separate queries for Row/Column groups?

1
Is it pertinent to have 3 datasets i.e. your data exists over 3 separate servers or databases, or can you combine all into one neat SQL? - glh
Looks like, with a bit of creativity in the expression, I can roll it up into 1 query that has a cross join against all possible values - AndrewP

1 Answers

4
votes

Why so difficult? Think easier... Create query in SQL as 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

Which produce 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 columns and rows: enter image description here

The expression here is =IIF(Sum(Fields!PersonID.Value) IS Nothing, 0, Sum(Fields!PersonID.Value)) for printing 0 if we don't have persons. And you get result which you want:

enter image description here

It's will easier and better for performance.