Calculating a dataset depending on a parameter in a report

Dear all
I have a SSRS report getting data from a cube. It has 3 tables and 3
different datasets linked to these tables. One table shows all members of one
dimension. Therefore this dataset requires most of the processing time.
I want to give the user a possibility inside this report to choose if he
wants to see all the members or not. Well one easy way would be to make 2
Reports. Another way more elegant way would be to define a report parameter
which will hold a part of a mdx querry that will be used in this dataset:
- [Suppliers].[Key].[All]
- [Suppliers].[Key].members
Inside the dataset I use the STRTOSET function with this parameter.
My problem is now that the first mdx querry let the column Key disappear
inside the dataset and therefore I get a error message while displaying the
report one table is referring an inexistent field.
Do you have an idea to solve this problem or do you see another approach?
Regards,
MarcHello Marc,
My understanding is that your customer wants to have an option to show the
Key field in the Report or not. And you wants to use a parameter in the
dataset MDX query to control the dataset. If I misunderstood your concern,
please feel free to let me know.
Well, since your dataset definition have been modified by the parameter,
you will lost the Key field in the dataset. That of course will cause an
error.
One workaround is using the subreport in the report and include 2 subreport
and then use the parameter to control which one to show up.
Also, I think the drilldown feature is another workaround for your
situation.
Hiding Report Items
http://msdn2.microsoft.com/en-us/library/ms157454.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. Can you explain me your workarounds more in detail?
My problem with one dataset showing all members is that it takes too long to
execute. So I have to give the user control if he wants either more
information but wait or less information and have quick report execution.
Your first workaround using a subreport sounds fine. But I don't know how I
can give a parameter value to the subreport (no function button there).
Hiding report items will not help because the dataset will be filled even
though the report item is hidden.
How does the drilldown feature work?
Thanks for your help,
Regards,
Marc|||Hello Marc,
For the Subreport, you could use the parameter to control its visability
property.
The drilldown feature works like a toggle item.
You could accomplish this by the article I provided in the previous post.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei
Thanks for your answer. Just hiding a report items does not solve my problem
because a filling of a dataset causes a long execution time. This happens
when a report item is visible or not.
To solve this issue I think I need a way to control which mdx is used to
fill a dataset or which dataset gets executed, both depending on a parameter.
Is there a way to achieve this?
Regards, Marc|||Hello Marc,
Unfortunatly, you could not use the parameter to control which dataset the
report item use.
This is because the layout will refer the fields in the dataset. If the
fields are not match, the layout could not render correctly.
My suggestion is to use the cache to have a better performance of the
report.
Report Caching in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms155927.aspx
Also, if you are developing a ASP.NET web application, you could use the
Report Viewer Control to refer different report in your Web Page.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. I solved this problem like you said with a
parameter. This parameter determines if a report item is visible or not.
In the dataset bound to this report item I defined members which get
calculated when the table is shown or just show 0 when the report item is
hidden (case â?¦ when â?¦ thenâ?¦ end).
But I still think this is complicated to achieve and it would be nice to
have a feature that does determine if a dataset gets calculated or not.
And it would be also nice if reporting services would show the all member as
a separate column - right now it gets hidden.
Kind regards,
Marc|||Hello Marc,
Thank you for your reply.
You could send your feedback directly to the product team via the following
web site:
http://connect.microsoft.com/SQLServer
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support