Drillthrough question - how to return to same report page where drillthrough occurred?

Hello,

I have a master report that uses the "Jump to report" property to drill through to a detail report when the user clicks a report column value. This all works great and the users love it. However, when I click the brower's "Back" button to return to the master report, the master report is rerun and I am returned to page 1. If I drilled through on page 25 of the master report then I have lost my original position and I must navigate back to page 25. The users don't love this "feature"!

Is there a way I can return from the drill through detail report to the master report without using the browser's back button and losing my original position in the master report?

Thanks,

BCB

I forgot to mention that I am using Report Manager and not the .NET report viewer control. Also, I don't see any navigational control on the drillthrough report that allows me to "back up" to the master - that is why I have used the browser's "Back" button.

Thanks,

BCB

Drillthrough question - how to return to same page?

Hello,

I have a master report that uses the "Jump to report" property to drill through to a detail report when the user clicks a report column value. This all works great and the users love it. However, when I click the brower's "Back" button to return to the master report, the master report is rerun and I am returned to page 1. If I drilled through on page 25 of the master report then I have lost my original position and I must navigate back to page 25. The users sure don't love this "feature"!

I am using Report Manager and not the .NET report viewer control. Also, I don't see any navigational control on the drillthrough report that allows me to "back up" to the master - that is why I have used the browser's "Back" button.

Is there a way I can return from the drill through detail report to the master report without using the browser's back button and losing my original position in the master report?

Thanks,

BCB

Hi BCB,
sorry I haven't the answer, but can you just explain me how you do to drill through to a detail report with the option "jump to report"... I have a post here : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1455055&SiteID=1

Thank you to take few minutes...
please
|||

Hi Alineo,

You open the properties window for the report field that you want to turn into a drillthrough link. On the "Navigation" tab you select the drillthrough report. Finally, you click the "Parameters" button to add all parameters that the drillthrough report requires. That's all it takes.

BCB

|||

I've had the opportunity to investigate this a little more and I believe it has something to do with report parameters that do not have default values. I'm convinced that a report with no parameters will return to the same page when you click the brower's Back button. In other words, the report won't be run again before you see it.

Reports that have parameters without defaults always seem to be rerun when the user clicks the Back button even though the previously entered values are used when the report is generated.

My results with reports that have parameters with default values have varied. Some reports seem to return to the last-viewed page when the Back button is clicked and other reports are rerun.

I'm still looking for an answer to this so if anyone has a theory I'd appreciate hearing it.

Thanks.

BCB

|||

After spending several hours decomposing a report that suffers from this problem, it appears that a report with a parameter of multi-value type will be always run when the user returns to it by using the browser's Back button. This causes the original report to display page #1 again. Not very nice if you happened to viewing page #39 when you clicked the drillthrough link! I can't be sure there is no other way to cause this problem but the multi-value property is guaranteed to do the trick. I'm assuming this is a bug.

We are running SP2 on our servers.

If anyone has experience working around this problem I'd appreciate hearing your solution. Multi-value parms are a requirement for us.

Thanks,

BCB

Drillthrough Query

I have the following Drillthroug query. i require it to return only those documents that fall within a given date range for a particular customer and worktype.

But all the records for a customer and worktype are returned irrespective of the date

DRILLTHROUGH maxrows 10000

SELECT [Measures].[TAT] ON 0 ,

([Hierarchy].[CustomerName].[ABC], [Hierarchy].[Work Type Name].[EEG] ) on 1

FROM ( SELECT ([Date].[Short Date].[2004-03-15] : [Date].[Short Date].[2006-04-10]) ON COLUMNS FROM [DW]) Return[$Customer].[CustomerName] As [CustomerName],[$Measures].[TAT] AS [TAT],[$Document].[Document ID] AS [Document ID]

Hello Reena,

First of all bad news: AS 2005 doesn't allow DrillThrough to be performed on multiple cells, so unfortuantly you'd need to generate multiple drillthrough statements to retrive details for all those dates.

Secondary, why this query doesn't return an error, but doesn't take subselect into consideration. The issue is that drillthrough is performed on the current coordinate, but subselect doesn't affect the current coordinate and the current coordinate for the time dimension in this case is a default member of time - ALL. So, drillthrough is performed on the cell corresponding to the ALL and not to the range.

Hope this helps,

Irina

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen