matrix column grouping

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name	Sales -			Costs +	Revenue -
Client A +	Europe +	Middle East +	Asia Pacific +	$12,000,000	Products +	Investments +
	$12,000,000	$8,000,000	$4,000,000		$10,000,000	$22,000,000
Client B +	$77,000,000	$16,000,000	$9,000,000	$22,000,000	$8,000,000	$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

...Hmm, I'll assume it's not possible to do this at the moment. Looks like I'll be doing my reports in asp.net for now.|||

There's no built in way of doing this. There are always workarounds but hese tend to be messy.

It will be fairly straight forward if using SQL less straight forward with MDX.

Basically need to consider the data in terms which portion of the report it is to appear on rather than what the actual entity is. Let me clarify. For your example instead thinking of Europe, Middle East and Asia Pacific as regions think of them column grouping level 1. Similarly the products fall into this same category. Hence you can generalise your SQL query for the report to have this 1 column instead of having region and product e.g.

SELECT client
, measure = 'Sales'
, column_level_1 = region
, amount = SUM(sales)
FROM some_table
GROUP BY client
, region

UNION ALL

SELECT client
, measure = 'Revenue'
, column_level_1 = product
, amount = SUM(revenue)
FROM some_table
GROUP BY client
, product

The result is a dataset that looks like:

client measure column_level_1 amount
=============================================
Client A Sales Europe 12000000
Client A Sales Middle East 8000000
Client A Sales Asia Pacific 4000000
Client B Sales Europe 77000000
Client B Sales Middle East 16000000
Client B Sales Asia Pacific 9000000
Client A Revenue Products 10000000
Client A Revenue Investments 22000000
Client B Revenue Products 8000000
Client B Revenue Investments 32000000

|||Thanks Adam. I'm using MDX, I'll give this some thought.|||

hi, can anyone help on this? i am new to MDX and also encounter this problem.
Thanks a lot in advance.

Best regards,

Tommy