none
How to pass parameters to subreport

    Question

  • Hi Gurus,

    I want to pass 0 value to all subreport parameters in the beginning when main report is run.  This is because i dont want subreport query to run.

     

    when user clicks on '+' in main report, i want to pass the parameter values so that the subreport will run with correct parameters for that particular main report row.

     

    My purpose of doing this is to achieve speed with subreport

     

    Thanks a lot in advance.

     

    Respectfuly,

    Korr

    Wednesday, December 26, 2007 10:10 PM

Answers

  • >> the subreport should be a drillthrough

     

    Look: I have already suggested a drillthrough, which the person asking the question seems not to want for some reason. 

     

    You can't equate subreport and drillthrough.  They work differently.

     

    You don't *need* custom code to make this efficient with a drillthrough. The drillthrough is going to run without impacting the parent, as a separate report, just naturally. (You also don't need it with the jump to URL variant of a drillthrough that I described earlier in the thread.)  But custom code will not help you with a subreport from an efficiency point of view, as far as I can tell.

     

    >L<

    Saturday, December 29, 2007 7:59 AM

All replies

  • This is pretty much how subreports work, if you associate their parameters with data elements in the main report <s>.

     

    http://msdn2.microsoft.com/en-us/library/aa179168(SQL.80).aspx

     

    ... I think it will do what you want. IOW, I don't think all the subreports (one per data row are actually run until the user requests them.  I know that the help topic that I just pointed to say "When you run a report that contains a subreport, the report server has to process each report. If the report contains data regions instead, the report server processes only one report." but I think the person who wrote this might have been slightly confused by the information s/he got from the developers.  WHile it's true that multiple reports are being processed, that doesn't mean they are all being processed at the same time (when the report first runs).

     

    I can't test this right now, but I think you could easily do so.

     

    >L<

     

    PS: I have now tested (as I said, it was easy to do) and the multiple sub reports *are* being processed up front, even if they are initially marked to be hidden until you click to expand them.  Sigh.

    Thursday, December 27, 2007 2:47 AM
  •  

    Lisa,

    Thanks a lot for the reply.  I agree.  This is how Subreports work.

     

    But we cannot live with problem.  I need to provide solution. 

     

    Since it is the problem known and many of us faced this situation, Any round about work?

    or some hints ....

     

    Korr
    Thursday, December 27, 2007 6:03 PM
  • Would a "drill through" (jump to report) work for you? It would resolve the issue that you are discussing...

     

    >L<

     

    Friday, December 28, 2007 12:05 AM
  • No Lisa.

     

    Korr

    Friday, December 28, 2007 12:24 AM
  • Well that was succinct <g>.  (Is it because it doesn't appear in the same display? I seem to remember that there was a target ability but now I can't find it, thinking maybe you could put it in a frame -- IAC this is the obvious way to get reports not to be run until you actually want them to run...)

     

    I will try to think of a way to get the report parameters to do what you want, then.  No promises, and can't do it immediately.

     

    >L<

    Friday, December 28, 2007 2:04 AM
  • Korr, I'm sorry to tell you that the only way I can give you to not run a report until you're ready to run the report is with a drillthrough...

     

    "one report" (including subs or nested data regions) is going to go after the data once.  It doesn't know about your interactive clicks in a way that allows it to re-run and re-fresh the data for a single subreport.

     

    I think there might be a way to capture the mouse/expand/visibility change but the problem is that you would essentially be running the *entire* parent report at that moment and the subreports (with the single/appropriate param value) all at that moment as well.  So it doesn't make sense.

     

    However, if this helps, you can run the drillthrough to a separate, definable window to  have something like the presentation you are looking for.  You can probably run it to a targetted frame as well with slightly more javascript than I show here. 

     

     In this example, and in similar approaches, we're using jump to URL rather than to report.  Notice that I am defining the window as part of this, as well as passing the param at that moment, as part of the REST-ful URL.   You could drive servername using report global values, of course, just as we are driving the parameter value dynamically in this navigate url expression:

     

    Code Block

    ="javascript:void(window.open('http://servername/Reportserver?
    /Test/Products&Prov=Window&rs:Command=Render&Category="
     
             & Fields!CategoryName.Value.ToString()
             &
    "','subreport','resizable=1,height=200,width=200'))"

     

     

    I hope this helps, I really can't think of another way to avoid running a report before you want the report. 

     

    >L<

     

    Friday, December 28, 2007 4:38 AM
  • using custom code with simple logic may help this. Lets create a custom code which accepts the cell value clicked by the user and saves it in a global variable.Let's pass the the value retrun by this code as parameter value to the sub report. First time since the user wouldnt have clicked the cell the code will return the default value(0 if retrun type is integer) which will be passed to the subreport. If user clicks particluar cell then in the navigation to report paramter value, call the same custom code and pass the cell value.

    In the subreport query   Drillthorugh to the subreport query string pane write an expression if paramter = 0 then load the data set with 0 else run the actual query.

    Friday, December 28, 2007 10:18 AM
  • >> let's create a custom code which accepts the cell value clicked and saves it

     

    I think you are kind of missing the point of what I wrote last time, so I'll repeat it:

     

    I think there might be a way to capture the mouse/expand/visibility change but the problem is that you would essentially be running the *entire* parent report at that moment and the subreports (with the single/appropriate param value) all at that moment as well.  So it doesn't make sense.

     

    IOW you might possibly be able to prevent all the subqueries from running at the same time with data at the beginning of the report.  When you changed the value, you might possibly *also* prevent all the subqueries from collecting *different* data (they would all be given the *same* parameter), they'd all be run with the param value appropriate to just one of them, but they would all still run.

     

    Let's assume you could surmount the last issue, with an IIF() on the parameter assignment or something.  Here's what you didn't fix:

     

    You need to refresh the report to get that subreport to run, some way.  And even if you succeeded in getting all the subreports except for the "right" one from running with a bunch of data... you would have rerun the outer/parent report in the process.  So have you really gained anything, efficiency wise, if you have to rerun the whole outer report every time a user clicks to expand any one of the subreports??

     

    >L<

     

    Friday, December 28, 2007 11:41 PM
  • yes, the subreport should be a drillthorugh report from the main report's matrix/table cell click, ie, navigating to the same report on click of any cell. As we can capture the cell value and store it in a variable using custom code we can restrict the parentreport's query to not run whenever user clicks the cell in the parent report using iff expression, only the subreport will be run as explained above in my previous thread.

    Saturday, December 29, 2007 7:15 AM
  • >> the subreport should be a drillthrough

     

    Look: I have already suggested a drillthrough, which the person asking the question seems not to want for some reason. 

     

    You can't equate subreport and drillthrough.  They work differently.

     

    You don't *need* custom code to make this efficient with a drillthrough. The drillthrough is going to run without impacting the parent, as a separate report, just naturally. (You also don't need it with the jump to URL variant of a drillthrough that I described earlier in the thread.)  But custom code will not help you with a subreport from an efficiency point of view, as far as I can tell.

     

    >L<

    Saturday, December 29, 2007 7:59 AM
  • Hi Lisa,

              Sorry to intrude in between as it is not related to the above conversation.I have a different issue with Reports.

     

    In my application I am exporting the 2 different tables in the report to the 2 Excel Sheets by giving page break at the first table,The first sheet is getting exported fine. The problem is ,the 2nd Sheet got exported with the report from the cell 'A2' only. (ie) The first row is blank,The export starts from the 2nd row only. The row starting with 'A1' is hided, remaining blank.

     

        What has to be done to get the excel exported in the first row itself without missing the same. Please help me with this issue.Hope you will provide solution for my problem.

     

    With Thanks

    M.Mahendra

    Thursday, January 10, 2008 6:10 AM
  • Hi there,

     

    I have not seen this happen -- is it easy to reproduce in another report?  I could give it a try...

     

    Nope, I can't reproduce it in a simple report.

     

    What can you tell me about your tables?

     

    For example:

     

    * -- I assume that you used "page break after" on the first one, right?

     

    * -- are there groups?

     

    * -- are there visibility settings used on any elements?

     

    * -- is there a page header in this report?

     

    * -- does the table have a header row?

     

    * -- are headers for the table set to "repeat on page"?

     

    *-- table header set "remain visible when scrolling"?'

     

    ... anything else?

     

    >L<

     

    Thursday, January 10, 2008 6:41 AM
  • Hi Lisa,

    Thanks for your quick reply

     

    I assume that you used "page break after" on the first one, right?

    Yes

    are there groups?

    No

     are there visibility settings used on any elements?

    No

    is there a page header in this report?

    No

    does the table have a header row?

    Yes

    are headers for the table set to "repeat on page"?

    No

    table header set "remain visible when scrolling"?'

    No

     

    I am having 2 tables one below the other. Both the tables are just for displaying the data. Just i want to get them exported in the excel for further processing .I have removed the table footer also. I have checked with providing no gap in between the 2 tables. Nothing more than this.

     

    With Thanks

    M.Mahendra

     

    Thursday, January 10, 2008 7:29 AM
  • I can definitely repro this, on all pages after page 1 <sigh>.

     

    And I can't fix it.  I tried everything, every combination...

     

    All I can say is: if you don't like the Excel that RS produces, you can definitely fix it.  Just not by having *them* do the Excel. <g>

     

    See http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

     

    That's really all I can do to help this kind of thing... the Excel produced by default in RS is just not that good... but we can do better.

     

    And maybe Katmai does better too <s>.  We can hope <g>.

     

    >L<

     

    Thursday, January 10, 2008 8:04 AM
  • Hi Lisa,

              Thanks for your Explanation.

    -Mahendra

     

    Thursday, January 10, 2008 11:06 AM
  • Thanks a lot for all the help.

    My client requirement is drilldown as the report was already prepared by some other developer.

    I am hired to make it fast without changing the look and feel and adding additional functionality like another embeded drilldown.


    After lot of deliberations, I created one SP to give all the data that comes for main report and embedded drilldown sub-report.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Added 2 detail rows with different grouping with drilldown portion visible only upon clicking ‘+’

    This worked excellently.


    Tx for all the help
    Friday, January 11, 2008 5:09 AM
  • Thursday, February 14, 2008 7:17 PM