|
|
You're in the
sql server reporting services
group:True suppression of subreports
sql server reporting services:
I have a specialized report that produces detailed schedules (simple table)
for manufacturing systems based on criteria parameters. There are numerous systems being reported on, but schedules for all must be consolidated into a single report output. The catch is that each system's schedule is in a slightly different layout, and this is driven by a "system type" categorization. For example... systems named "1607", "1608" are of type "PRESS", and "2102", "2103" are of type "PBOM". The report needs to appear like this: [---- System: 1607 (PRESS) ----] ........... < Layout A > .......... <-page break-> [---- System: 1608 (PRESS) ----] ........... < Layout A > .......... <-page break-> [---- System: 2102 (PBOM) ----] ........... < Layout B > .......... <-page break-> [---- System: 2103 (PBOM) ----] ........... < Layout B > .......... <-page break-> The way I have this working now is that I drive a master report which is essentially a list of the Type/Systems that pass the criteria filters. The list is set to pagebreak on every iteration. In the list itself, I have a subreport for each system type, with Hidden being set if the current record is not of the appropriate type for the subreport. It is arranged somewhat like this: [-Body Begin [---DataList (Systems) [-----Subreport (Hidden if Type != PRESS, has Layout A) [-----Subreport (Hidden if Type != PBOM, has Layout B) [---DataList End [-Body End Technically, this *functions* correctly as far as output is concerned. However, the performance is horribly slow. Running SQL Profiler reveals that the subreport queries are being executed once for every subreport type per row in the master report (in practice, there are actually 8 different layout types, not just the 2 in my example). In other words, for every iteration of my DataList in the master report, all but one of the subreports is hidden, yet the queries for *all* are being executed. The result is that the final report takes 8 times longer than necessary to fetch data. I also suspect the rendering is being affected negatively too, as that is much slower than expected. It seems as if the subreports are also rendered, even though they are hidden (and never made visible). There must be a less brutal way of doing this... I feel like I am cramming a square peg in a round hole here. Any suggestions? -- Keith Rome
We are seeing exactly the same thing (via SQL Profiler). It kind of
seems...dare I say...stupid...that all queries for all sub-reports are executed at the beginning when the report is first opened regardless if the report is hidden or not. Someone please say there is a simple thing we are missing.
That is exactly what happens. In my case, I worked around the problem...
I moved the contents of each subreport into the body of the main report. The outermost container within each subreport was a List, so now I have eight Lists within my main report, each with a different layout of controls to handle the data associated with each. I also copied all the Datasets into the main report, and tied the Lists to the corresponding Datasets. Moving the criteria checking to filters instead of parameters to the database server allwed me to enable caching/snapshots on the report. This has made a tremendous difference in performance. Performance is now very acceptable. The only drawback is that I repopulate the snapshot every 10 minutes, which means report data could be up to 10 minutes stale. Keith [quoted text, click to view] "osbornchris@gmail.com" wrote:
> We are seeing exactly the same thing (via SQL Profiler). It kind of > seems...dare I say...stupid...that all queries for all sub-reports are > executed at the beginning when the report is first opened regardless if > the report is hidden or not. > Someone please say there is a simple thing we are missing. >
Our problem is that our data isn't very wide, but it is pretty deep.
So it would be very nice to be able to click through to the data and hopefully just query for that data at the time of the click. Meaning one monolithic report isn't the ideal situation (unless the queries execute only when the section is show). That said we too, have found another solution. In the Navigation tab in the advanced section of the edit dialog, you can turn the textbox into a hyperlink which is linked to another report using the "Jump to report" option. It allows you to specify the jump to report and even pass parameters to that report. I think we will take this approach unless a better alternative comes up. Chris
That sounds like a reasonable solution for your situation. I would have
chosen a similar tactic as well (it is called "drill-through") except that in my case, the printing of the composite report is higher priority than drill-down or drill-through. I am glad that we have both found workable solutions to our problems. Keith [quoted text, click to view] "osbornchris@gmail.com" wrote:
> Our problem is that our data isn't very wide, but it is pretty deep. > So it would be very nice to be able to click through to the data and > hopefully just query for that data at the time of the click. Meaning > one monolithic report isn't the ideal situation (unless the queries > execute only when the section is show). > > That said we too, have found another solution. In the Navigation tab > in the advanced section of the edit dialog, you can turn the textbox > into a hyperlink which is linked to another report using the "Jump to > report" option. It allows you to specify the jump to report and even > pass parameters to that report. I think we will take this approach > unless a better alternative comes up. > > Chris >
You have both found 'work-arounds', but we still do not have a solution. Is
there a way to specify that a subreport, if hidden, will not be rendered until it is made visible? I too have the same issue... Thanks, Mike [quoted text, click to view] "Keith Rome" wrote:
> I have a specialized report that produces detailed schedules (simple table) > for manufacturing systems based on criteria parameters. There are numerous > systems being reported on, but schedules for all must be consolidated into a > single report output. The catch is that each system's schedule is in a > slightly different layout, and this is driven by a "system type" > categorization. > > For example... systems named "1607", "1608" are of type "PRESS", and "2102", > "2103" are of type "PBOM". The report needs to appear like this: > > [---- System: 1607 (PRESS) ----] > .......... < Layout A > .......... > <-page break-> > [---- System: 1608 (PRESS) ----] > .......... < Layout A > .......... > <-page break-> > [---- System: 2102 (PBOM) ----] > .......... < Layout B > .......... > <-page break-> > [---- System: 2103 (PBOM) ----] > .......... < Layout B > .......... > <-page break-> > > The way I have this working now is that I drive a master report which is > essentially a list of the Type/Systems that pass the criteria filters. The > list is set to pagebreak on every iteration. In the list itself, I have a > subreport for each system type, with Hidden being set if the current record > is not of the appropriate type for the subreport. It is arranged somewhat > like this: > > [-Body Begin > [---DataList (Systems) > [-----Subreport (Hidden if Type != PRESS, has Layout A) > [-----Subreport (Hidden if Type != PBOM, has Layout B) > [---DataList End > [-Body End > > Technically, this *functions* correctly as far as output is concerned. > However, the performance is horribly slow. Running SQL Profiler reveals that > the subreport queries are being executed once for every subreport type per > row in the master report (in practice, there are actually 8 different layout > types, not just the 2 in my example). In other words, for every iteration of > my DataList in the master report, all but one of the subreports is hidden, > yet the queries for *all* are being executed. The result is that the final > report takes 8 times longer than necessary to fetch data. I also suspect the > rendering is being affected negatively too, as that is much slower than > expected. It seems as if the subreports are also rendered, even though they > are hidden (and never made visible). > > There must be a less brutal way of doing this... I feel like I am cramming a > square peg in a round hole here. > > Any suggestions? > > > -- > Keith Rome
My workaround is still functional. Since then I have avoided report designs
that rely on repeating subreports. Keith Rome MCSD, MCAD, MCDBA [quoted text, click to view] "Michael R" wrote:
> You have both found 'work-arounds', but we still do not have a solution. Is > there a way to specify that a subreport, if hidden, will not be rendered > until it is made visible? I too have the same issue... > > Thanks, > Mike > > "Keith Rome" wrote: > > > I have a specialized report that produces detailed schedules (simple table) > > for manufacturing systems based on criteria parameters. There are numerous > > systems being reported on, but schedules for all must be consolidated into a > > single report output. The catch is that each system's schedule is in a > > slightly different layout, and this is driven by a "system type" > > categorization. > > > > For example... systems named "1607", "1608" are of type "PRESS", and "2102", > > "2103" are of type "PBOM". The report needs to appear like this: > > > > [---- System: 1607 (PRESS) ----] > > .......... < Layout A > .......... > > <-page break-> > > [---- System: 1608 (PRESS) ----] > > .......... < Layout A > .......... > > <-page break-> > > [---- System: 2102 (PBOM) ----] > > .......... < Layout B > .......... > > <-page break-> > > [---- System: 2103 (PBOM) ----] > > .......... < Layout B > .......... > > <-page break-> > > > > The way I have this working now is that I drive a master report which is > > essentially a list of the Type/Systems that pass the criteria filters. The > > list is set to pagebreak on every iteration. In the list itself, I have a > > subreport for each system type, with Hidden being set if the current record > > is not of the appropriate type for the subreport. It is arranged somewhat > > like this: > > > > [-Body Begin > > [---DataList (Systems) > > [-----Subreport (Hidden if Type != PRESS, has Layout A) > > [-----Subreport (Hidden if Type != PBOM, has Layout B) > > [---DataList End > > [-Body End > > > > Technically, this *functions* correctly as far as output is concerned. > > However, the performance is horribly slow. Running SQL Profiler reveals that > > the subreport queries are being executed once for every subreport type per > > row in the master report (in practice, there are actually 8 different layout > > types, not just the 2 in my example). In other words, for every iteration of > > my DataList in the master report, all but one of the subreports is hidden, > > yet the queries for *all* are being executed. The result is that the final > > report takes 8 times longer than necessary to fetch data. I also suspect the > > rendering is being affected negatively too, as that is much slower than > > expected. It seems as if the subreports are also rendered, even though they > > are hidden (and never made visible). > > > > There must be a less brutal way of doing this... I feel like I am cramming a > > square peg in a round hole here. > > > > Any suggestions? > > > > > > -- > > Keith Rome
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |