Prev: rsMissingElement
Next: IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002)
From: Sky on 30 Nov 2006 12:04
Hi,

I am trying to create a report using relational datasets rather than
warehoused data (which the Microsoft's book SQL Server 2005 Reporting
Services Step By Step uses mainly (rs2005sbsDW)) and I am coming across a few
niggles.

Currently we have an internal reporting system (written in old ASP!) that
uses procs and drilldowns to get master/detail data back from a relational
database e.g. clicking an id will go to another webpage passing in that id as
a parameter for a proc, and I am attempting to modernise this with Reporting
Services.

The problem is that I want to 'embed' the detail of a subreport into the
main report (using ToggleItem and Visible properties) but only query the
database and display the detail data (e.g. Product) when the user clicks on
the master row (e.g. Purchase Order) in the parent report, where at current,
Reporting Services loads all the data for the subreport (Product details) for
each master id i.e. NOT on demand, and then hides the data as requested
(using the ToggleItem and Hidden properties).

Can this be done? I have settled at mo for having the user redirected to the
Product Details report ('subreport') when they click on the master ID (the
purchase order id in the main summary report), this effectively mimics our
current reporting system.

All the examples in the book work because the data is denormalized in the
original select query, however, this is a serious performance hit for the
large data (not all my reports require this method) hence the need for
on-demand subreport loading.

I also tried to use multiple datasets but it seems again, all the detail
data needs to be loaded e.g. proc takes a null value but this is not an
option for performance reasons.

I know warehousing the data is the best way forwards, but right now, it's
not an option, and searching the web seems like Crystal Reports has such an
On-Demand subreport functionality.

If anyone can offer advice, I would be most grateful.

Kind Regards

S. Rumsey
 | 
Pages: 1
Prev: rsMissingElement
Next: IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002)