Matrix export to Excel format difficulty

I made a matrix report with drilldown.

My users like to export it to excel and work with it there.

The + signs don't lie up correctly when it is exported- you have to click the '+' one row up from what you really want to expand it.

Any tips on getting the right data lined up with the right '+' on export? Thanks

How are you exporting it to excel? When I use the standard export method in Report Server, it works fine.|||The user chooses 'excel' format and then clicks export.|||

Try changing your subtotal rows Position property to "Before" instead of "After". You can get to this property by clicking on the green triangle in the corner of the subtotal row, it's in the Layout category.

|||Make sure the toogle item property is set properly.

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

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

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

matching users profiles using sql

Hi folks

I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile.

I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease.

My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet.

If anyone could give me some advice on where to start i would be very grateful

I am develping in vb, with an sql server 2005 back-end

Hello my friend,

You are right about the SQL LIKE clause not being so useful when dealing with large amounts of text. I have worked on something similar before. The best thing to do is to have as many closed questions as possible regarding the profiles. And when they want to be more specific, add sub categories, and sub sub categories and so on. Then the SQL is easy. Most of the big guys do this. If you ever try and create a new hotmail account for example, they throw loads of checkboxes at you and throw the appropriate adverts at you every so often.

Kind regards

Scotty

|||

Hi Scotty

Thanks for the advice. I'm going to begin the development of this feature very soon so i will document what i have come up with here.