drill-through from matrix subtotals

I have a matrix report (Report1) with rowgroup and columngroup. Both
contain also totals (defined as group subtotals). The number of rows and
columns varies.
Area1 Area2 Totals
Product 1 10 12 22
Product 2 7 11 18
Totals 17 23 40
I also have another report where I can list this data more detailed
(Report2). Report2 uses Product and Area as parameters where possible
values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
Now I want to drill-through from Report1 into details in Report2 by
clicking the cell containing the number. I use 'Jump to report'-navigation
and fill the parameters from Report1 matrix dataset. This works fine as
long as I start drill-through from data cell ie. click numbers 10,12,7 or
11. The problem is that also the 'Totals' appear as links, but the
parameters are not filled in correctly.
So the question is how to define the parameters in 'Jump to report' so that
when clicking Product 1 totals (22) the parameters to Report2 are set to
Product 1 - All Areas.
-pasi
--
Message posted via http://www.sqlmonster.comPlease check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell and set
the drillthrough parameters accordingly. I.e. you would use an
IIF-expression to set the value of the drillthrough parameters correctly
based on the InScope return values. Note: a matrix cell is "in scope" of
column and row groupings, so you need at least two InScope function calls in
the case where you have 1 dynamic row and 1 dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pasi Norrbacka via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:dc114ab8cd48487a97ef6575f7bc7185@.SQLMonster.com...
>I have a matrix report (Report1) with rowgroup and columngroup. Both
> contain also totals (defined as group subtotals). The number of rows and
> columns varies.
> Area1 Area2 Totals
> Product 1 10 12 22
> Product 2 7 11 18
> Totals 17 23 40
> I also have another report where I can list this data more detailed
> (Report2). Report2 uses Product and Area as parameters where possible
> values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
> Now I want to drill-through from Report1 into details in Report2 by
> clicking the cell containing the number. I use 'Jump to report'-navigation
> and fill the parameters from Report1 matrix dataset. This works fine as
> long as I start drill-through from data cell ie. click numbers 10,12,7 or
> 11. The problem is that also the 'Totals' appear as links, but the
> parameters are not filled in correctly.
> So the question is how to define the parameters in 'Jump to report' so
> that
> when clicking Product 1 totals (22) the parameters to Report2 are set to
> Product 1 - All Areas.
> -pasi
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works.
--
Message posted via http://www.sqlmonster.com

Drillthrough from matrix subtotal value

I've got a matrix as shown below:
c1 c2 Total
r1 1 2 3
r2 4 5 9
Total 5 7 12
I've set the matrix up to drillthrough to a report when I click on the cell values. But I'm having a problem getting the right results when I click on the subtotal values. I'd like to be able to click on (for example) 9 and get a got to the report getting all detail for r2 regarless of the column group. Ditto for rows if I click on a column subtotal. And I'd like to get a complete detail report if I click on 12. However, I'm not sure how to set up the drillthrough on the cell to make this happen. And it doesn't appear to be possible to set up a different drillthrough on the subtotal row/column as you cannot access their properties explicitly.Take a look at the InScope function. It was designed with this scenario in
mind. There should be an example of exactly this usage in the
documentation.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
news:4D5302B1-6768-4E24-83BE-F16665CF7B2A@.microsoft.com...
> I've got a matrix as shown below:
> c1 c2 Total
> r1 1 2 3
> r2 4 5 9
> Total 5 7 12
> I've set the matrix up to drillthrough to a report when I click on the
cell values. But I'm having a problem getting the right results when I click
on the subtotal values. I'd like to be able to click on (for example) 9 and
get a got to the report getting all detail for r2 regarless of the column
group. Ditto for rows if I click on a column subtotal. And I'd like to get a
complete detail report if I click on 12. However, I'm not sure how to set up
the drillthrough on the cell to make this happen. And it doesn't appear to
be possible to set up a different drillthrough on the subtotal row/column as
you cannot access their properties explicitly.|||Thanks Chris. That worked great. I looked at InScope at first but I didn't understand its purpose correctly. Not it makes perfect sense.
"Chris Hays [MSFT]" wrote:
> Take a look at the InScope function. It was designed with this scenario in
> mind. There should be an example of exactly this usage in the
> documentation.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> news:4D5302B1-6768-4E24-83BE-F16665CF7B2A@.microsoft.com...
> > I've got a matrix as shown below:
> >
> > c1 c2 Total
> > r1 1 2 3
> > r2 4 5 9
> > Total 5 7 12
> >
> > I've set the matrix up to drillthrough to a report when I click on the
> cell values. But I'm having a problem getting the right results when I click
> on the subtotal values. I'd like to be able to click on (for example) 9 and
> get a got to the report getting all detail for r2 regarless of the column
> group. Ditto for rows if I click on a column subtotal. And I'd like to get a
> complete detail report if I click on 12. However, I'm not sure how to set up
> the drillthrough on the cell to make this happen. And it doesn't appear to
> be possible to set up a different drillthrough on the subtotal row/column as
> you cannot access their properties explicitly.
>
>|||Can u share the example. Please.
"virtualfergy" wrote:
> I've got a matrix as shown below:
> c1 c2 Total
> r1 1 2 3
> r2 4 5 9
> Total 5 7 12
> I've set the matrix up to drillthrough to a report when I click on the cell values. But I'm having a problem getting the right results when I click on the subtotal values. I'd like to be able to click on (for example) 9 and get a got to the report getting all detail for r2 regarless of the column group. Ditto for rows if I click on a column subtotal. And I'd like to get a complete detail report if I click on 12. However, I'm not sure how to set up the drillthrough on the cell to make this happen. And it doesn't appear to be possible to set up a different drillthrough on the subtotal row/column as you cannot access their properties explicitly.

drilldown group expanded by default

Hello!!

I have a matrix in a report with groups and subgroups like this :

*group1

*subgroup1.1

item1

item2

etc.

I would like to show the group expanded by default so when I view the report I see:

*group1

subgroup1.1

*group2

subgroup 2.1

Right now I've played with InitialToggleState and visibility->Hidden properties but I don′t know how to do it....help me please

Thanks

You need to set the visibility of the inner group to true. Right click and edit the group in the matrix.|||

I published an article showing the steps for this (at least an example similar enough to be useful, hopefully):

http://www.databasejournal.com/features/mssql/article.php/3527321

This was for MSRS2000, but the logic should be good in SSRS2005.

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

Drilldown for Parent Attribute

Does anyone know how to create a drilldown matrix or table using a parent attribute in Analysis Services?

Currently, I have a dimension with this parent attribute which contains up to 10 levels. Then, if you click "Browse" in the Analysis Services toolbar it will let you expand each node until you reach the end of the branch. This allows a variable length tree. However, the problem is that when I go to add this attribute to my query in reporting services it only adds the first level. In other words, when you expand the attribute in the query designer, you can see all ten levels exist but only the first level is added to the report. I've also tried to manually add each level into the query, but this also fails. It ignores whatever level I add and just keeps the attribute's first level.

I know this is kind of a confusing problem, but I'd really appreciate any advice.

Thanks!!
Joel

Does anyone have any ideas or need more clarification?

Thanks

|||

Still stuck... Just wanted to get a few more looks at this problem.

Thanks

Drill Through to report from Matrix Subtotal

Hi,
I have the following result from a matrix:
| Week 1 | Week 2 | Week 3 |
DSM 1 | Salesrep 1| Sales | $100 | $200 | $300 |
|New cust | 3 | 2 |
5 |
Salesrep 2| Sales | $200 | $300 | $100 |
|New cust | 2 | 1 |
3 |
---
DSM 1 Totals | $300 | $500 | $400 |
|New cust | 5 | 3 |
8 |
What I'm trying to accomplish is to have the user click on the field "New
Cust" and open a report that shows the new customers (detail) for the entire
row. The marix report has to and from date parameters that I am passing to
the second report, but the second report also has the DSM and Salesrep
parameters (each set up as a multi parameter). I want the user to be able to
click on the indevidual Salesrep line, the DSM subtotal Line, and the Grand
total line. I know this requires the InScope function, but I can't seem to
get the statment right.
Any help is greatly appreciated.
Thanks!
--
JohnWell either this was too hard or it is not possible. Either way, can someone
give me some insight?
--
John
"John" wrote:
> Hi,
> I have the following result from a matrix:
> | Week 1 | Week 2 | Week 3 |
> DSM 1 | Salesrep 1| Sales | $100 | $200 | $300 |
> |New cust | 3 | 2 |
> 5 |
> Salesrep 2| Sales | $200 | $300 | $100 |
> |New cust | 2 | 1 |
> 3 |
> ---
> DSM 1 Totals | $300 | $500 | $400 |
> |New cust | 5 | 3 |
> 8 |
> What I'm trying to accomplish is to have the user click on the field "New
> Cust" and open a report that shows the new customers (detail) for the entire
> row. The marix report has to and from date parameters that I am passing to
> the second report, but the second report also has the DSM and Salesrep
> parameters (each set up as a multi parameter). I want the user to be able to
> click on the indevidual Salesrep line, the DSM subtotal Line, and the Grand
> total line. I know this requires the InScope function, but I can't seem to
> get the statment right.
> Any help is greatly appreciated.
> Thanks!
> --
> John

Drill Through bringing back too many results

I have created a Matrix Report through the Report Builder and specified a filter on a Date field so it only shows the current months work. This works for the initial Totals within the Matrix but when you click on the Totals to show the Drill Down it will show all the results regardless of the Parameter.

How do I make the results in the Drill Through look at the Parameter I am passing to the Report?

Many Thanks

I'm not sure I understand your scenario, and I can't repro the behavior you describe using my best guess. Can you clarify:

1. What fields are in your report?

2. Did you actually create a report parameter by setting Prompt on the filter condition, or did you just create a normal report filter?

Any other info you can provide to further clarify your scenario would be helpful. Thanks!

Drill down matrix report slow performance

I have created OLAP cubes and we are creating reports on top of them. The reports take incredibly long to render (40 seconds to 5 minutes). The MDX for the reports runs in less than 15 seconds when executed as a query in Mgt. Studio. Browsing is also fast. The reports are drill down matrix-type reports and they do eventually display properly. Not that this should help with the mechanics of the issue but the report data source query will show how we drill across dimensions:

<code>

SELECT NON EMPTY { [Measures].[Number of Queries Run] } ON COLUMNS, NON EMPTY { ([ClientProjectDim].[Project ID].[Project ID].ALLMEMBERS * [UserDim].[UserName].[UserName].ALLMEMBERS * [QueryDim].[Status].[Status].ALLMEMBERS * [DateDim].[Date Hierarchy].[ClusteredHourOfDay].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Ad Hoc] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

</code>

The RS and security logs don't have anything meaningful. As the MDX works well when executed as a query but not in the reports I can't tell if it's a rendering issue somewhere. Has anyone experienced this issue and is there a resolution aside from restructuring the report?

I am experiencing similar issues but no answers, did you ever resolve youra?|||We did not resolve this, but rather restructured our reports. You might consider this. You can set up your reports with params for drill-through.|||Thanks, however I did parametize this report. I will try to restructure and do this long and laborious way. I noticed in the AS browser and MDX query designer they are using nice drill through tree for the parameters, but no drill through in the rendered form. Is that due to a property setting I am missing.