RS2005- Pie Chart Percentages and Filter

I have a pie chart with the following filter expression:
=Parameters!Year.Value
The point labels for the pie chart are determined from the following
expression:
=sum(Fields!Expr1.Value)/sum(Fields!Expr1.Value, "SalesDM")
My problem is that the above calulation for percentage is incorrect for the
filtered dataset; the percentage is of the whole dataset. How do I get the
percentage to honor the filter?Hi,
welcome to the MSDN newsgroup.
From your description, I understand you're using the SSRS 2005 to create
some reports, and when using the Pie chart together with some filters on
the data, you found the pie chart always display the value from unfiltered
data, correct?
Based on my local testing, this is likely a project or environment specific
issue. I've tested a Pie chart report with filters setting in both Chart
region and in Dataset and both of them work. Are you using the filters in
chart region or in dataset? Or have you tried other report or datasource to
see whether this is a datasource specific issue.
Please feel free to let me know if there's anything I missed.
Regards,
Steven Cheng
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.|||Thank you for responding, Steven.
My dataset, SalesDM, returns data for several categories over two years.
There are no filters on the dataset. The dataset is used for a table, and is
unfiltered- it returns results as expected. I then use the dataset for a pie
chart. I set up a chart filter for one year's worth of data from the
dataset.- it properly returns results. When I calculate the point labels for
the pie chart, I use the expression in my original post. That calculation
does not take into account the filter applied to the chart.
I'm guessing that I need to reformulate my expression, but I don't know what
the proper formulation should be.
"Steven Cheng[MSFT]" wrote:
> Hi,
> welcome to the MSDN newsgroup.
> From your description, I understand you're using the SSRS 2005 to create
> some reports, and when using the Pie chart together with some filters on
> the data, you found the pie chart always display the value from unfiltered
> data, correct?
> Based on my local testing, this is likely a project or environment specific
> issue. I've tested a Pie chart report with filters setting in both Chart
> region and in Dataset and both of them work. Are you using the filters in
> chart region or in dataset? Or have you tried other report or datasource to
> see whether this is a datasource specific issue.
> Please feel free to let me know if there's anything I missed.
>
> Regards,
> Steven Cheng
> 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.
>|||Thanks for your response.
Yes, I agree with you that the problem is specific to the Point Label's
data expression. And I think the problem is likely due to the denominator
in your expression.
the "sum(Fields!Expr1.Value)" will return the current item's sum value on
Expr1 field. However, as for the "sum(Fields!Expr1.Value, "SalesDM")", I'm
not sure what is "SalesDM" exactly, but I guess it is the Dataset's
name(which is bound to your chart), correct? If so, this will cause the
data be retrieved directly from DataSet which is not filtered by the filter
you defined in chart. If this is the case, you can consider specific the
Chart's name in the sum expression. e.g:
sum(Fields!Expr1.Value, "PieChart1")
this will make the data be retrieved from the chart's dataregion which is
filled by the chart's filters. Or alternatively, you can also define
another dataset which use the same filters like your chart control and make
the sum expression specified that dataset as the scope value.
Hope this helps,
Regards,
Steven Cheng
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.|||Thank you, Steven! Referencing the chart's dataregion is exactly the answer
I was looking for.
"Steven Cheng[MSFT]" wrote:
> Thanks for your response.
> Yes, I agree with you that the problem is specific to the Point Label's
> data expression. And I think the problem is likely due to the denominator
> in your expression.
> the "sum(Fields!Expr1.Value)" will return the current item's sum value on
> Expr1 field. However, as for the "sum(Fields!Expr1.Value, "SalesDM")", I'm
> not sure what is "SalesDM" exactly, but I guess it is the Dataset's
> name(which is bound to your chart), correct? If so, this will cause the
> data be retrieved directly from DataSet which is not filtered by the filter
> you defined in chart. If this is the case, you can consider specific the
> Chart's name in the sum expression. e.g:
> sum(Fields!Expr1.Value, "PieChart1")
> this will make the data be retrieved from the chart's dataregion which is
> filled by the chart's filters. Or alternatively, you can also define
> another dataset which use the same filters like your chart control and make
> the sum expression specified that dataset as the scope value.
> Hope this helps,
> Regards,
> Steven Cheng
> 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.
>|||You're welcome!
Also glad that we've got the problem resolved.
Have a good day!
Regards,
Steven Cheng
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.

RS/AS 2005 Reports on Cube datasource, drilldown not working

I have an AS 2005 cube created.
I am able to generate reports in RS 2005 by setting the report datasource to
point to my cube.
I have tried to setup a report to drillthrough to another report ( also
based on the same cube datasource ) via the TextBox Navigation/Jump To Report
setting.
I am able to select the report to which I need to jump. The parameters for
that report appear in the navigation setup dialog, and I am able to set the
parameter values e.g. =Fields!Product_Type.Value.
However, whenever I run the report ( in BIDS preview, via Report Manager, or
in a Report View web control ) and click the navigation text box, I always
get the message "The 'PRODUCTProductType' parameter is missing a value "
When trying to do this via a Report Viewer web control, I can look at the
parameters in the report being drilled into by examining the
DrillThroughEventArgs in my DrillThroughEventHandler of my Report Viewer web
control.
ServerReport serverReport = (ServerReport)e.Report;
ReportParameterInfoCollection parms = serverReport.GetParameters();
There are 3 parameters as expected, but for each of them, parms[i].state = "MissingValidValue"
I see that this exact same setup works when the datasource for the report is
relational, and I hope that I'm just missing something simple to make it work
when the data source is a cube.
Any help is greatly appreciated!If you are using the Analysis Services provider (the one that comes
with a UI for you to drag and drop), any query parameters are bound to
another dataset by default. The parameter value passed/received need
to be a member of the "available" list.
I got similar message ""The 'PRODUCTProductType' parameter is missing a
value " when the value is not found in the available list of values.
So, you may want to check this first?
Looking at the parameter name, its value should be of the format of
[PRODUCT].[Product Type].&[<a key>]. So, you need to also ensure the
value is passed in this format.|||One more thing:
RS has the behaviour of changing the query parameter to be
"multi-valued" enabled. So you need to make sure the parameters
defined at both main and sub reports are of the same type, either both
multi-valued or both single-valued.
Multi-valued requires a different format e.g.
=Fields!Product_Type.Value(0)|||Thanks for your help, I'm getting closer with this now.
For now, I've removed the product type parameter, because it's a calculated
field, adn I'm trying to use a parameter called FACDESC that is a straight
database column value.
When I literaly past in the value
[PRODUCT].[FACDESC].&[IMACS General Hospital]
the parameter is passed to the drill through report perfectly.
However, I'd like it to use the current value of the report fields in the
cell I'm drilling through. I've tried these:
[PRODUCT].[FACDESC].&[Fields!FACDESC.Value]
[PRODUCT].[FACDESC].&[Fields!FACDESC.Value(0)]
and it goes back to the missing parameter problem.
If I pass in as the drillthrough parameter
=[PRODUCT].[FACDESC].&[Fields!FACDESC.Value]
but then I get the build error:
[rsCompilerErrorInExpression] The Value expression for the textbox
â'DischargeQuarterâ' contains an error: [BC30451] Name 'PRODUCT' is not
declared.
"Rose" wrote:
> If you are using the Analysis Services provider (the one that comes
> with a UI for you to drag and drop), any query parameters are bound to
> another dataset by default. The parameter value passed/received need
> to be a member of the "available" list.
> I got similar message ""The 'PRODUCTProductType' parameter is missing a
> value " when the value is not found in the available list of values.
> So, you may want to check this first?
> Looking at the parameter name, its value should be of the format of
> [PRODUCT].[Product Type].&[<a key>]. So, you need to also ensure the
> value is passed in this format.
>|||Finally got this to work.
To set up a report in RS2005 that is based on an AS2005 Cube data source, so
that it will drill through to another report, also based on an AS2005 Cube
data source, passing parameter values to the subreport:
Pass the report parameters like this:
= "[PRODUCT].[FACDESC].&[" + Fields!FACDESC.Value + "]"
Thanks to Rose for pointing me in the right direction.
"Rose" wrote:
> If you are using the Analysis Services provider (the one that comes
> with a UI for you to drag and drop), any query parameters are bound to
> another dataset by default. The parameter value passed/received need
> to be a member of the "available" list.
> I got similar message ""The 'PRODUCTProductType' parameter is missing a
> value " when the value is not found in the available list of values.
> So, you may want to check this first?
> Looking at the parameter name, its value should be of the format of
> [PRODUCT].[Product Type].&[<a key>]. So, you need to also ensure the
> value is passed in this format.
>|||=Fields!Product_Type.UniqueName
should also work, exept when its part of a hierchy in the master-report and
not in the sub-report
"DTK" wrote:
> Finally got this to work.
> To set up a report in RS2005 that is based on an AS2005 Cube data source, so
> that it will drill through to another report, also based on an AS2005 Cube
> data source, passing parameter values to the subreport:
> Pass the report parameters like this:
> = "[PRODUCT].[FACDESC].&[" + Fields!FACDESC.Value + "]"
> Thanks to Rose for pointing me in the right direction.
>
> "Rose" wrote:
> > If you are using the Analysis Services provider (the one that comes
> > with a UI for you to drag and drop), any query parameters are bound to
> > another dataset by default. The parameter value passed/received need
> > to be a member of the "available" list.
> >
> > I got similar message ""The 'PRODUCTProductType' parameter is missing a
> > value " when the value is not found in the available list of values.
> > So, you may want to check this first?
> >
> > Looking at the parameter name, its value should be of the format of
> > [PRODUCT].[Product Type].&[<a key>]. So, you need to also ensure the
> > value is passed in this format.
> >
> >

RS Web Part Scrolling

I have installed 4 RS Viewer Web Parts on a share point page. When I
click on the + sign to expand the detail of a report the detail area
scrolls to the top of the sharepoint page. If all four web parts were
viewable and you were on the 2nd one down from the top, the first one
completely disappears. The only way to see it is to use the scroll bar
to move to the top of the page again. This behavior only happens with
RS Web Parts. This is very anoying as you must find your place on the
page everytime you click on a + or - sign. The page is always jumping
around.
Does anybody know how to stop this?Hi
It would be great if anyone can responde to this question since I'm having
the same problem. My end users are getting so anoyed that they have no
interest in using any drill-down reports. As soon as a report is anything
other than a very basic report with only one or two grouping levels,
drill-down functionality is useless.
This is very bad for those of us that recommended SQL RS to our
organizations as an enterprise reporting solution.
Can someone please help us with this?
"Jiles" wrote:
> I have installed 4 RS Viewer Web Parts on a share point page. When I
> click on the + sign to expand the detail of a report the detail area
> scrolls to the top of the sharepoint page. If all four web parts were
> viewable and you were on the 2nd one down from the top, the first one
> completely disappears. The only way to see it is to use the scroll bar
> to move to the top of the page again. This behavior only happens with
> RS Web Parts. This is very anoying as you must find your place on the
> page everytime you click on a + or - sign. The page is always jumping
> around.
> Does anybody know how to stop this?
>