Report Builder problem with no All level on Scenario?

At first glance, this appears to be a bug in Reporting Services...
I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
set IsAggregatable to False (since it doesn't make sense to add Actuals and
Budgets). This effectively removes the All level, as I understand it. I've
set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
datasource in SSRS and generate a model off it. When I build a Report Builder
report off the model, I have a problem. If the report does not specify
scenario as a row, column, or filter, then the MDX that gets generated
(captured this via Profiler) starts...
WITH
MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
And since [Agg_0] is used in the body of the query inside an Exists()
function, it results in the query returning no cells (so the report says that
no data was returned). This may be a problem with the engine that translates
a model query into MDX. For some reason it's not paying attention to the
DefaultMember. (That's why I posted to this newsgroup, not the SSAS
newsgroup.)
When I flip IsAggregatable back to True such that there's an All level,
everything works fine. It gives me the correct numbers. But I don't want to
show an All level for the Scenario dimension.
Please let me know if you have any suggestions or if you can confirm this is
a bug. And please let me know if the SSRS guys feel this is an SSAS issue
that should be posted in that newsgroup.
(As a side note, though I get the right numbers by turning IsAggregatable to
True, I think the MDX is still not quite right because it references
[Scenario].[Scenario].[All] in the Exists function when it really should be
referencing [Scenario].CurrentMember. But that's another topic and not nearly
as crucial. The worst that could happen there is that you show some unneeded
rows. And I may be wrong on whether it should use CurrentMember.)"FurmanGG" wrote:
> At first glance, this appears to be a bug in Reporting Services...
> I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> set IsAggregatable to False (since it doesn't make sense to add Actuals and
> Budgets). This effectively removes the All level, as I understand it. I've
> set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> datasource in SSRS and generate a model off it. When I build a Report Builder
> report off the model, I have a problem. If the report does not specify
> scenario as a row, column, or filter, then the MDX that gets generated
> (captured this via Profiler) starts...
> WITH
> MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> And since [Agg_0] is used in the body of the query inside an Exists()
> function, it results in the query returning no cells (so the report says that
> no data was returned). This may be a problem with the engine that translates
> a model query into MDX. For some reason it's not paying attention to the
> DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> newsgroup.)
> When I flip IsAggregatable back to True such that there's an All level,
> everything works fine. It gives me the correct numbers. But I don't want to
> show an All level for the Scenario dimension.
> Please let me know if you have any suggestions or if you can confirm this is
> a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> that should be posted in that newsgroup.
> (As a side note, though I get the right numbers by turning IsAggregatable to
> True, I think the MDX is still not quite right because it references
> [Scenario].[Scenario].[All] in the Exists function when it really should be
> referencing [Scenario].CurrentMember. But that's another topic and not nearly
> as crucial. The worst that could happen there is that you show some unneeded
> rows. And I may be wrong on whether it should use CurrentMember.)|||Hello,
Did you get any response to this problem or find any solution? I have about
the same problem; I am building an OLAP cube with a time dimension and stock
number. Those stock figures cannot be aggregated over time, so I set the
IsAggregatable option on the time dimension to False. When I do that, Report
Builder does not work as I would expect...
Thanks, Hans.
"FurmanGG" wrote:
> At first glance, this appears to be a bug in Reporting Services...
> I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> set IsAggregatable to False (since it doesn't make sense to add Actuals and
> Budgets). This effectively removes the All level, as I understand it. I've
> set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> datasource in SSRS and generate a model off it. When I build a Report Builder
> report off the model, I have a problem. If the report does not specify
> scenario as a row, column, or filter, then the MDX that gets generated
> (captured this via Profiler) starts...
> WITH
> MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> And since [Agg_0] is used in the body of the query inside an Exists()
> function, it results in the query returning no cells (so the report says that
> no data was returned). This may be a problem with the engine that translates
> a model query into MDX. For some reason it's not paying attention to the
> DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> newsgroup.)
> When I flip IsAggregatable back to True such that there's an All level,
> everything works fine. It gives me the correct numbers. But I don't want to
> show an All level for the Scenario dimension.
> Please let me know if you have any suggestions or if you can confirm this is
> a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> that should be posted in that newsgroup.
> (As a side note, though I get the right numbers by turning IsAggregatable to
> True, I think the MDX is still not quite right because it references
> [Scenario].[Scenario].[All] in the Exists function when it really should be
> referencing [Scenario].CurrentMember. But that's another topic and not nearly
> as crucial. The worst that could happen there is that you show some unneeded
> rows. And I may be wrong on whether it should use CurrentMember.)|||Should be fixed in SP1...
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=77da7b11-f20a-41ee-a18a-a74fd5ac4650
"Hans Geurtsen" wrote:
> Hello,
> Did you get any response to this problem or find any solution? I have about
> the same problem; I am building an OLAP cube with a time dimension and stock
> number. Those stock figures cannot be aggregated over time, so I set the
> IsAggregatable option on the time dimension to False. When I do that, Report
> Builder does not work as I would expect...
> Thanks, Hans.
> "FurmanGG" wrote:
> > At first glance, this appears to be a bug in Reporting Services...
> >
> > I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> > set IsAggregatable to False (since it doesn't make sense to add Actuals and
> > Budgets). This effectively removes the All level, as I understand it. I've
> > set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> > datasource in SSRS and generate a model off it. When I build a Report Builder
> > report off the model, I have a problem. If the report does not specify
> > scenario as a row, column, or filter, then the MDX that gets generated
> > (captured this via Profiler) starts...
> >
> > WITH
> > MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> > SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> >
> > And since [Agg_0] is used in the body of the query inside an Exists()
> > function, it results in the query returning no cells (so the report says that
> > no data was returned). This may be a problem with the engine that translates
> > a model query into MDX. For some reason it's not paying attention to the
> > DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> > newsgroup.)
> >
> > When I flip IsAggregatable back to True such that there's an All level,
> > everything works fine. It gives me the correct numbers. But I don't want to
> > show an All level for the Scenario dimension.
> >
> > Please let me know if you have any suggestions or if you can confirm this is
> > a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> > that should be posted in that newsgroup.
> >
> > (As a side note, though I get the right numbers by turning IsAggregatable to
> > True, I think the MDX is still not quite right because it references
> > [Scenario].[Scenario].[All] in the Exists function when it really should be
> > referencing [Scenario].CurrentMember. But that's another topic and not nearly
> > as crucial. The worst that could happen there is that you show some unneeded
> > rows. And I may be wrong on whether it should use CurrentMember.)