Passing a parameter to subreport SQLSelectCommand

6 posts, 0 answers
  1. Sid
    Sid avatar
    52 posts
    Member since:
    Sep 2008

    Posted 29 Jul 2009 Link to this post

    I've been trying to design a report with a subreport.  I'm stuck on trying to get the subreport to update with a sql select using a parameter to the sqlselectcommand which calls a stored procedure.  I've looked at the invoice demo and have tried to understand the process.  I'm just not sure how the Report Parameters and the SqlSelectCommand parameters are able to communicate.  If I try and code the parameter initialization in the constructor of the subreport the ReportParameter doesn't exist yet at this point and it errors out.

    The main report works fine with the above parameter manipulation as shown below:
            public RenewalList(int KeyPK)  
            {  
                /// <summary>  
                /// Required for telerik Reporting designer support  
                /// </summary>  
                InitializeComponent();  
                this.sqlDataAdapter1.SelectCommand.Parameters["@KeyPK"].Value = KeyPK;  
                this.DataSource = this.sqlDataAdapter1;  
     

    But inside the subreport you don't have the ability to hardcode the parameter so I'm not sure how to get the value to an identical type SelectCommand in the subreport.  I've set a ReportParameter in the subreport called KeyPK, the main report then sets the subreport with KeyPK = =Parameters.[@KeyPK], I've put a break in the subreport after the InitializeComponent and the parameter is not set to the new value. 

    Hope I've explainded this sufficiently.

    Thanks,
    -Sid.
  2. Sid
    Sid avatar
    52 posts
    Member since:
    Sep 2008

    Posted 29 Jul 2009 Link to this post

    OK.  I've switched things around a little and I've got it working but I hope there's a better solution.

    I took out the call to the stored procedure and instead followed the example and created the data adapter by using a query to fill the adapter and then the filter property to gather the correct detail records.

    I would hope I should be able to utilized the stored procedure in the subreport.  Hopefully, I'm just missing something here.

    If not, does the subreport query the records first and then apply the filter?  This would seem to be a very inefficient solution.  I'm only looking for a handful of records not my entire detail table of 50K records.

    Thanks,
    -Sid.
  3. DevCraft banner
  4. AkAlan
    AkAlan avatar
    121 posts
    Member since:
    Jun 2009

    Posted 04 Nov 2009 Link to this post

    Have you found a better solution for using stored procedures with parameters in a sub report. I have tried hard to find documentation on how to pass a parameter to a subreport with no luck. Thanks.
  5. Sid
    Sid avatar
    52 posts
    Member since:
    Sep 2008

    Posted 04 Nov 2009 Link to this post

    Crystal Reports!

            new private void Window_Loaded(object sender, RoutedEventArgs e) 
            { 
                WindowsFormsHost host = new WindowsFormsHost(); 
                crReport = new crRenewalList(); 
                crViewer = new CrystalReportViewer(); 
     
                crReport.SetParameterValue("@KeyPK"this.tKeypk); 
                crViewer.ReportSource = crReport; 
     
                host.Child = crViewer; 
                grdViewer.Children.Add(host);  
     
            } 

    Then inside the main report just create a subreport with the "Select - Record" using the parameter you're trying to pass

    I've attached a small capture of what this report looks like, the Crystal Report design layout, and the subreport link dialog box.

    HTH,
    -Sid.
  6. Guy Meredith
    Guy Meredith avatar
    4 posts
    Member since:
    Oct 2009

    Posted 12 Dec 2009 Link to this post

    Has anyone ever found a solution to this requirement, other than through Crysal Reports? I too have a query in my subreport (which shows the invoice lines for an invoice on the main report), to which I wish to pass a parameter (being the InvoiceID). But I wish to pass the parameter directly to the query before it fires, and not to the report as a filter, as this will be far more efficient than filtering thousands of invoice line records to get the one or two records that correspond to the invoice being shown on the main report.
  7. Chris Gillies
    Chris Gillies avatar
    384 posts
    Member since:
    May 2006

    Posted 14 Dec 2009 Link to this post

    As pointed in previous thread of Guy's, this KB would get you in the right direction: http://www.telerik.com/support/kb/reporting/designing-reports/using-data-source-with-parameters-in-telerik-reporting.aspx. The approach is exactly the same with subreports, the only thing different is that you're going to use the NeedDataSource event either of the subreport item itself or of the report used as subreport, depending on your exact needs.

    Cheers.
Back to Top
DevCraft banner