Hidden Subreport Executing Queries

I'm currently developing a report in which the customer has requested a
drill-down type of interface that starts at a regional level to a
district level to a territory level and finally to the individual
customers within the territory. The drill-down interface was easy to
create by using Groups in conjunction with the ToggleItem and
Visibility.Hidden properties at the different Grouping levels.
The problem I have run into is that there are around 1 Million
customers across the country and the report was attempting to retrieve
all of the records at all levels at once. This turns out to be
unworkable from a performance standpoint.
To try to work around this I proposed using drill-through at the
territory level to open a new report with that territories customer
list. The customer was unwilling to accept that approach since they
wanted to keep the flexibility of navigating through the tree without
the disruption of opening a separate report.
Next I tried to work around the problem by implementing a subreport at
the customer level thinking that the subreport would only execute once
the user drilled down into the territory level. Following is a general
overview of this solution:
+ North East
- South East
- Georgia
- Atlanta
- subreport displays here with territory customer list
+ Savannah
+ Athens
+ Florida
+ Kentucky
- Midwest
+ Kansas
+ Iowa
+ Minnesota
+ West
By using SQL Profiler I found that the report was actually executing
the query for each individual territory's corresponding subreport
even though those levels were hidden. This was essentially the same
scenario that I had with my initial attempt.
To try to work around this I created a parameter within the subreport
called "show_subreport" and if that parameter is set to 1 the dataset
will return the customer list and if it is set to 0 the dataset will
not return any rows. By manually setting the parameter to 0 through
the parameter mapping in the parent report this worked well. However,
I have not been able to figure out how to dynamically change that
parameter by using an expression. I have been trying to set the
parameter expression to something like:
= IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
However, I have not been able to get to that property or any other
property which would be relevant to the "show_subreport" parameter.
This seems like it would be a very common type of report request so I
assume there is a way to do this. Any help would be appreciated.I've got the same problem.
--
/Mattias
"fred_mumble@.yahoo.com" wrote:
> I'm currently developing a report in which the customer has requested a
> drill-down type of interface that starts at a regional level to a
> district level to a territory level and finally to the individual
> customers within the territory. The drill-down interface was easy to
> create by using Groups in conjunction with the ToggleItem and
> Visibility.Hidden properties at the different Grouping levels.
> The problem I have run into is that there are around 1 Million
> customers across the country and the report was attempting to retrieve
> all of the records at all levels at once. This turns out to be
> unworkable from a performance standpoint.
> To try to work around this I proposed using drill-through at the
> territory level to open a new report with that territories customer
> list. The customer was unwilling to accept that approach since they
> wanted to keep the flexibility of navigating through the tree without
> the disruption of opening a separate report.
> Next I tried to work around the problem by implementing a subreport at
> the customer level thinking that the subreport would only execute once
> the user drilled down into the territory level. Following is a general
> overview of this solution:
> + North East
> - South East
> - Georgia
> - Atlanta
> - subreport displays here with territory customer list
> + Savannah
> + Athens
> + Florida
> + Kentucky
> - Midwest
> + Kansas
> + Iowa
> + Minnesota
> + West
> By using SQL Profiler I found that the report was actually executing
> the query for each individual territory's corresponding subreport
> even though those levels were hidden. This was essentially the same
> scenario that I had with my initial attempt.
> To try to work around this I created a parameter within the subreport
> called "show_subreport" and if that parameter is set to 1 the dataset
> will return the customer list and if it is set to 0 the dataset will
> not return any rows. By manually setting the parameter to 0 through
> the parameter mapping in the parent report this worked well. However,
> I have not been able to figure out how to dynamically change that
> parameter by using an expression. I have been trying to set the
> parameter expression to something like:
> = IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
> However, I have not been able to get to that property or any other
> property which would be relevant to the "show_subreport" parameter.
> This seems like it would be a very common type of report request so I
> assume there is a way to do this. Any help would be appreciated.
>sql