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.)

Report Builder Designer problem

Hello,
When I create a new matrix report in the Report Builder, then the height of
the title cell of the dimension on the left of the matrix, is half height.
If you want to see, I have a pdf to illustrate it.
Is this a bug in the designer? Or can I solve this?
Regards,
JaapHello Jaap,
I would like to get the screenshot to understand this issue better.
And I also would like to know whether the title cell in the Preview is
still half height?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jaap,
I did not reproduce this issue. When I add a column into the Report
Builder, it appeared correct.
Did you applied the latested services pack for reporting services?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Report Builder and confirmed dimension

Hi Experts,

I have build a cube and placed report builder on it. I have a Dimensional model with two fact table. When i try to create a report it allows me to select measures from only one fact. It doesnt allow me to create report from two different fact and two different dimensions.

Please help

Regards

Neeraj

Hi, you can't never do that in report builder, because [Report Builder] model does not work with more them one fact table.|||

I believe Loobo is correct in stating that Report Builder doesn't accept more than one measure group/fact table. Report Builder seems to be focused on more of a relational database model which does not always work well with Analysis Services.

Thanks,
Bryan Smith

|||Thank You Guys for that Insight.

Report Builder and confirmed dimension

Hi Experts,

I have build a cube and placed report builder on it. I have a Dimensional model with two fact table. When i try to create a report it allows me to select measures from only one fact. It doesnt allow me to create report from two different fact and two different dimensions.

Please help

Regards

Neeraj

Hi, you can't never do that in report builder, because [Report Builder] model does not work with more them one fact table.|||

I believe Loobo is correct in stating that Report Builder doesn't accept more than one measure group/fact table. Report Builder seems to be focused on more of a relational database model which does not always work well with Analysis Services.

Thanks,
Bryan Smith

|||Thank You Guys for that Insight.