Matrix Subtotal Action Hyperlink

I've created a matrix report that uses the "Jump to URL" option of the action property to allow my report to drill down to a website using query strings.

For example, the action property looks something like this:
="www.mywebsite.com/page.htm?company=" + Fields!Company.Value + "&status=" + Fields!Status.Value

This works nicely for all the cells in the matrix, but it doesn't work for the subtotals. For example if I click on the subtotal for company "ABC" for all statuses, I would like my query string to link as follows:
="www.mywebsite.com/page.htm?company=" + Fields!Company.Value + "&status="
where no status is specified.

Any ideas or suggestions?
ThanksI did a little more searching and found my question has already been answered here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=24371&SiteID=1

Matrix Reprt Drill Through

Hi:
I wonder is there anyway I can show all items in a grouped level in a
cross-tab report with one click of a button (or an action)? The report is
query based.
For example, I have 10 stores and I want the user to be able to click a
botton and see all the customers in the 10 stores instead of clicking one by
one.
--
AraleHelo Arale,
What is a botton? Is that Chinese for button? Plewse spell better.
"Arale" wrote:
> Hi:
> I wonder is there anyway I can show all items in a grouped level in a
> cross-tab report with one click of a button (or an action)? The report is
> query based.
> For example, I have 10 stores and I want the user to be able to click a
> botton and see all the customers in the 10 stores instead of clicking one by
> one.
> --
> Arale

Matrix drill through problem

So here goes the explnation. The problem is specifying an all parameter in a drill through on the value cell of a matrix for the columns in the matrix that are not expanded.

Here goes the example

Matrix for sales.

Region | Shop | Number of sales. And then financial period would be at the top. Everything is collapsed on load so it looks something like this.

Region1 | | 50

Region2 | | 75

Then you expand Region.

Region1 | Shop1 | 30

Region1 | Shop2 | 20

Region2| | 75

At this point if you click on the 30 for shop 1 is drills through into another report passing parameter Region1, shop1 and financial period. This is perfect. The problem comes in that if you haven't expanded region1 and you click on the 50 it drills into the other report but passes through Shop1 as the shop parameter. How can I get this to pass though an all option for the unexpanded columns in the matrix. This is ofcourse just an example and my matrix has quite a few more column and stuff.

Please guys, any help would be appreciated.

This is Reporting services 2005 linking to SSAS2005 cube.

Thanks in advance

Hello,

Try this: http://msdn2.microsoft.com/en-us/library/ms156490.aspx

|||

Yeah.

iif(InScope("MatrixColumnGroup"), ParamaterIfTrue, ParameterIfFalse)

On interesting thing about this. Is that in BIDS the drillthrough doesn't work on the totals but it works on the report viewer control. I think this is a bug. If you drill through on a total it will take the first row grouping as the parameter but if you drill through from a front end using the report viewer control it works perfectly.

Good luck, thanks for the help.

Matrix Drill down Value not Correct

I am making a report that has column groups Day of Week and then "late".
(early late ontime) Row groups consist of region,district,store,dept. The
value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
numbers throughout the drilldown. I am having problems with the Ratio value
calculated as format(sum( Fields!Measures_Rec_Count.Value
,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
"matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
level but remains the same throughout the drilldown...I have tried using the
InScope() function but not getting any positive results....
Any Ideas?
Sunday Monday
...etc
Early ontime late Early ontime
late
region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
district 1 3(83%) 2(17%)
district 2 4(83%) (17%)
district 3 3(83%) (17%)still could not get dynamicly grouped columns and rows to drill down with the
correct numbers....altered my MDX to create a matrix with one dynamic column
and 3 static columns and the drill down works now.....If anyone knows of a
way to have multiple dynamic columns where calculated cells drill down with
correct values please let me know.
Thanks.
"Raz" wrote:
> I am making a report that has column groups Day of Week and then "late".
> (early late ontime) Row groups consist of region,district,store,dept. The
> value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
> numbers throughout the drilldown. I am having problems with the Ratio value
> calculated as format(sum( Fields!Measures_Rec_Count.Value
> ,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
> "matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
> level but remains the same throughout the drilldown...I have tried using the
> InScope() function but not getting any positive results....
> Any Ideas?
> Sunday Monday
> ...etc
> Early ontime late Early ontime
> late
> region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
> district 1 3(83%) 2(17%)
> district 2 4(83%) (17%)
> district 3 3(83%) (17%)
>

Matrix Drill Down Issue

I have a matrix with dynamic columns and rows. If I apply any filters to the
row groups then the higher levels only reflect the first "store" result set
in the higher levels instead of the aggregate total. If I remove the filters
on each group then the values at each level are correct but my first
[member_caption] at each level is null.
Expression: =Fields!location_Division.Value="" operator: = value: =false
Thanks in advance for your help.
col1 col2 col3 col4 col5 col6
division 5 5 4 4 5 4
region 5 5 4 4 5 4
district 1 5 5 4 4 5 4
store 10 5 5 4 4 5 4
store 12 3 1 2 3 2 3Found a way around this problem....have to select the leaves in your MDX
and sum the column value......
"Raz" wrote:
> I have a matrix with dynamic columns and rows. If I apply any filters to the
> row groups then the higher levels only reflect the first "store" result set
> in the higher levels instead of the aggregate total. If I remove the filters
> on each group then the values at each level are correct but my first
> [member_caption] at each level is null.
> Expression: =Fields!location_Division.Value="" operator: = value: =false
> Thanks in advance for your help.
> col1 col2 col3 col4 col5 col6
> division 5 5 4 4 5 4
> region 5 5 4 4 5 4
> district 1 5 5 4 4 5 4
> store 10 5 5 4 4 5 4
> store 12 3 1 2 3 2 3

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

Matrix column drill down

I have a matrix with a row grouping of project and a column grouping of
project manager. I am summing the gross margin, labor cost, and gross
profit. Is there a way to only display the gross profit initially, but
supply a drill down on that field to display the sum of the other two fields
when the user clicks on the gross profit?This is what I have:
PM 1
PM2
Margin Labor Profit
Margin Labor Profit
+ Project 1
100 75 25
- Project 2 Foreman1 50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
This is what I would like:
PM 1
PM2
Margin Labor Profit
Profit
+ Project 1
+ 25
- Project 2 Foreman1
50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
"MikeS" wrote:
> I have a matrix with a row grouping of project and a column grouping of
> project manager. I am summing the gross margin, labor cost, and gross
> profit. Is there a way to only display the gross profit initially, but
> supply a drill down on that field to display the sum of the other two fields
> when the user clicks on the gross profit?|||The requirement is to see as much as possible on one page.
This is what I have:
PM 1
PM2 ...
Margin Labor Profit
Margin Labor Profit ...
+ Project 1
100 75 25 ...
- Project 2 Foreman1 50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
This is what I would like:
PM 1
PM2 ...
Margin Labor Profit
Profit ...
+ Project 1
+ 25
- Project 2 Foreman1 - 25
50 25
Foreman2 - 50
75 25
+ Project 3 -5
Is it possible to do this? If so can someone please point me in the right
direction?
Thanks
"MikeS" wrote:
> I have a matrix with a row grouping of project and a column grouping of
> project manager. I am summing the gross margin, labor cost, and gross
> profit. Is there a way to only display the gross profit initially, but
> supply a drill down on that field to display the sum of the other two fields
> when the user clicks on the gross profit?