Questions about navigation for cick through report.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbuchanan - 07 Jul 2008 09:38 GMT

I have a complex stored procedure (sp) that prepares a hierarchical data for
use with a report. The sp returns a table.

I am using the click-through (or drill-through) model of report navigation
to traverse the hierarchy levels

Is there a way to use the same returned table from the sp (with out
regenerating it) and run separate filters against is for the same
ReportViewere and RDLC file and refresh it?

Any other suggestions for an approach?

Thanks,
Doug

Reply to this Message

Charles Wang [MSFT] - 07 Jul 2008 11:05 GMT

Hi Doug,
Thank you for your posting.

To let me better understand your meaning, could you please elaborate what
your requirements are and why you need to run separated filters againt the
same ReportViewer?

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
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.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

dbuchanan - 07 Jul 2008 13:58 GMT

Charles

Each report looks and a different level of the hierarchy. I can filter it by
a statement like "Where Lv = 1" or 2, or 3...

My other alternative is to create five sp's and run each at the opening of
the next report. Do I have to resort to that? the sp takes a while to run.

Thanks,
Doug

Reply to this Message

Charles Wang [MSFT] - 11 Jul 2008 09:44 GMT

Hi Doug,
If whole of your hierarchical data returned from your complex stored
procedure is not very large, I think that you need not to create 5 stored
procedures for this case. Instead, you can just create one stored procedure
that returns the full set of your hierarchical data. Then you can use
DataSet.Tables[0].Select method to filter the data and then merge the
result to a new dataset and then rebind it to your local report data
source.

Regarding DataTable.Select method, you can refer to:
DataTable.Select Method (String, String)
http://msdn.microsoft.com/en-us/library/way3dy9w.aspx

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

Bruce L-C [MVP] - 07 Jul 2008 14:40 GMT

If you are talking about a RDLC file that means you are using the
ReportViewer control in local mode. You have complete control over the data
being retrieved. You just need to have a variable somewhere set on whether
to use the existing dataset or generate a new one. In local mode you hand
the control the dataset (really the tableset) and the report. For drill
through you are writing code that responds to the event. In general I feel
that local mode is inferior to server mode. However, this is one scenario
that because you have total control, you are able to do exactly as you want
in this case (re-use the dataset). This would not be possible via the server
without lots of hoops (for instance, you could create a table that is based
on the user, the first report calls the stored procedure which fills in the
data along with the username, you can pass the username using the globals
variable UserID. Then the other reports query this table instead of calling
the sp. Again, using the UserID. Note that the UserID includes the domain
name).

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

>I have a complex stored procedure (sp) that prepares a hierarchical data
>for use with a report. The sp returns a table.
[quoted text clipped - 10 lines]
> Thanks,
> Doug

Reply to this Message

dbuchanan - 10 Jul 2008 08:00 GMT

Bruce,

What you say is quite interesting to me. Would you give me a little more
deail in some areas...

> If you are talking about a RDLC file that means you are using the
> ReportViewer control in local mode.

Yes

> You have complete control over the data being retrieved.

> You just need to have a variable somewhere set on whether to use the
> existing dataset or generate a new one.

In all cases for this report I want to use the same query.

> In local mode you hand the control the dataset (really the tableset) and
> the report.

Restate the above for me to clarify.

> For drill through you are writing code that responds to the event.

I see the 'back' and the 'drillthrough' events. How do I use these to add a
filter to the main query?

> In general I feel that local mode is inferior to server mode. However,
> this is one scenario that because you have total control, you are able to
> do exactly as you want in this case (re-use the dataset).

Could you explain how I would do this?

Do I fill a table adapter with the resuts of the stored procedure and then
query that dataset for each report?

If so, How do I filter the dataset and had the results to the report viewer?
Could you give me sampe (or pseudo) code for this?

> This would not be possible via the server without lots of hoops (for
> instance, you could create a table that is based on the user, the first
> report calls the stored procedure which fills in the data along with the
> username, you can pass the username using the globals variable UserID.

> Then the other reports query this table instead of calling the sp.

How do I do this.

More importantly: How do I supply the dataset to the ReportViewer.

Currently I am configuring a SqlDataSource with the results of a sp and then
wiring up the ReportViewer to use it. I can find no way to wire the Report
viewer to soe kind of coded dataset. Is it possible.

Please get me started on this. Thank you very much,

Doug

Reply to this Message