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?