Drillthrough in Excel 2003

Hi,
I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
fine when I explore the cube with the cube browser in AS, but when I try to
invoke the drillthrough action in MS Excel 2003 I get an message the there
are no details to be displayed - it failes.
Does anyone know why? Maybe Excel doesn't support drillthroughs?
Thanks,
Marcin
Marcin Pksa wrote:
> Hi,
> I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
> fine when I explore the cube with the cube browser in AS, but when I try to
> invoke the drillthrough action in MS Excel 2003 I get an message the there
> are no details to be displayed - it failes.
> Does anyone know why? Maybe Excel doesn't support drillthroughs?
> Thanks,
> Marcin
>
Right, not supported out-of-the-box. But there's some sample VBA code
you can get from the microsoft web site somewhere that shows how to
implement drill through as a right-click context menu.

Drillthrough in Excel 2003

Hi,
I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
fine when I explore the cube with the cube browser in AS, but when I try to
invoke the drillthrough action in MS Excel 2003 I get an message the there
are no details to be displayed - it failes.
Does anyone know why? Maybe Excel doesn't support drillthroughs?
Thanks,
MarcinMarcin Pksa wrote:
> Hi,
> I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
> fine when I explore the cube with the cube browser in AS, but when I try t
o
> invoke the drillthrough action in MS Excel 2003 I get an message the there
> are no details to be displayed - it failes.
> Does anyone know why? Maybe Excel doesn't support drillthroughs?
> Thanks,
> Marcin
>
Right, not supported out-of-the-box. But there's some sample VBA code
you can get from the microsoft web site somewhere that shows how to
implement drill through as a right-click context menu.

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.

Drill-down is not working on browser : Please help

Hi,
Please help.. this is an annoying problem ......
I am running sample reports in the browser and found that none of the
reports drilldown
when I click on teh + sign.
In VS.NET environment the reports drilldown fine.
Is there a particular setting in the Internet Explorer options I have to set
?
I am using Evaluation edition with SP1.
Cheers
PawanDo you have an underscore in the server machine name?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
news:%23jkX%23ssZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Please help.. this is an annoying problem ......
> I am running sample reports in the browser and found that none of the
> reports drilldown
> when I click on teh + sign.
> In VS.NET environment the reports drilldown fine.
> Is there a particular setting in the Internet Explorer options I have to
> set
> ?
> I am using Evaluation edition with SP1.
> Cheers
> Pawan
>|||Yes !
Pawan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:eGmWsbtZEHA.1164@.TK2MSFTNGP12.phx.gbl...
> Do you have an underscore in the server machine name?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
> news:%23jkX%23ssZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Please help.. this is an annoying problem ......
> >
> > I am running sample reports in the browser and found that none of the
> > reports drilldown
> > when I click on teh + sign.
> > In VS.NET environment the reports drilldown fine.
> >
> > Is there a particular setting in the Internet Explorer options I have to
> > set
> > ?
> >
> > I am using Evaluation edition with SP1.
> >
> > Cheers
> > Pawan
> >
> >
>|||http://groups.google.com/groups?selm=%23HoAMdjBEHA.3284%40TK2MSFTNGP09.phx.gbl
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
news:uj5JMTyZEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes !
> Pawan
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:eGmWsbtZEHA.1164@.TK2MSFTNGP12.phx.gbl...
>> Do you have an underscore in the server machine name?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
>> news:%23jkX%23ssZEHA.1448@.TK2MSFTNGP12.phx.gbl...
>> > Hi,
>> >
>> > Please help.. this is an annoying problem ......
>> >
>> > I am running sample reports in the browser and found that none of the
>> > reports drilldown
>> > when I click on teh + sign.
>> > In VS.NET environment the reports drilldown fine.
>> >
>> > Is there a particular setting in the Internet Explorer options I have
>> > to
>> > set
>> > ?
>> >
>> > I am using Evaluation edition with SP1.
>> >
>> > Cheers
>> > Pawan
>> >
>> >
>>
>|||Hi Lev,
I changed my computer name to remove the underscore from name.
After that I used rsconfig.exe utility to change the connection settings of
report server.
Now, my reports show the images and drill down in the browser.
Many thanks for your help.
Pawan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:eJbVDc8ZEHA.3512@.TK2MSFTNGP12.phx.gbl...
>
http://groups.google.com/groups?selm=%23HoAMdjBEHA.3284%40TK2MSFTNGP09.phx.gbl
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
> news:uj5JMTyZEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes !
> >
> > Pawan
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:eGmWsbtZEHA.1164@.TK2MSFTNGP12.phx.gbl...
> >> Do you have an underscore in the server machine name?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Pawan" <pawan_kr_sharma@.hotmail.com> wrote in message
> >> news:%23jkX%23ssZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> >> > Hi,
> >> >
> >> > Please help.. this is an annoying problem ......
> >> >
> >> > I am running sample reports in the browser and found that none of the
> >> > reports drilldown
> >> > when I click on teh + sign.
> >> > In VS.NET environment the reports drilldown fine.
> >> >
> >> > Is there a particular setting in the Internet Explorer options I have
> >> > to
> >> > set
> >> > ?
> >> >
> >> > I am using Evaluation edition with SP1.
> >> >
> >> > Cheers
> >> > Pawan
> >> >
> >> >
> >>
> >>
> >
> >
>