locked
ASP.NET, Report Viewer. How to set subreport filter expression or do i need to set filter expression? RRS feed

  • Question

  • User1501639495 posted

    Am using northwind sql server database to do a test. Using customer, order table to show master detail report. Customerid is the reference.  After Create a Parameter to run the subreport. Some how i cant able to refernce the master, detail. so it will show all the records in the detail section instead of showing relevant customer id details from the orders table. pls see the picture belowMainreport - > call the subreport parameter. but i miss with one more step to finish the report "Filter Expression", i guess. how to pass the parameter value subreport filter. pls help

     

     

     

     Customer.aspx

        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" 
                Font-Size="8pt" InteractiveDeviceInfos="(Collection)" 
                WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="820px">
                <LocalReport ReportPath="cust.rdlc">
                    <DataSources>
                        <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="CustomerDataSet" />
                        <rsweb:ReportDataSource DataSourceId="ObjectDataSource2" Name="ordersDataSet" />
                    </DataSources>
                </LocalReport>
            </rsweb:ReportViewer>
            <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" SelectMethod="GetData" 
                TypeName="OrdersDataSetTableAdapters.OrdersTableAdapter">
            </asp:ObjectDataSource>    
            <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" 
                TypeName="CustomerDataSetTableAdapters.CustomersTableAdapter">
            </asp:ObjectDataSource>
        </div>
        </form>

     

    customer.aspx.cs

        protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SetSubDataSource);
            this.ReportViewer1.LocalReport.Refresh();
        }
    
        public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {
            e.DataSources.Add(new ReportDataSource("ordersDataSet", "ObjectDataSource2"));
        }


    Almost 4 days gone.

     

     

    Wednesday, July 21, 2010 4:16 AM

Answers

  • User1471008070 posted

    Hi,

    You might need to add a filter to the subreport ,keep the SELECT  * FROM Orders query for the dataset rather than  "SELECT  * FROM Orders WHERE  Orders.CustomerID =@CustomerID" Steps for add a filter to the subreport:

    1. Select the report menu in the report designer interface, and then select report parameter item, in the popup window, add a new parameter named CustomerID, then click ok.
    2. Select the report menu in the report designer interface, and then select Data sources item,  in the popup window, click the property of the sub report datasource. Switch to the filter tab, use  “=Fields!CustomerID.Value” for the expression and “=Parameters!CustomerID.Value” for value, and the operator is “=”.

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 23, 2010 4:08 AM

All replies

  • User-624088818 posted

    1: In the Main report, Right click on CustomerId Textbox properties and select Actions

    2: Select Goto Report option button and select the sub report from dropdownlist

    3: Add parameters which should be same as Sub reports expecting

    4: E.g. CustomerId is parameter in sub report, then click on Add in the dropdown you can select parametername and value =Fields!CustomerId.Value

    5: Once you click on Customer Id, sub report will be generated with selected customer id


    Wednesday, July 21, 2010 5:23 AM
  • User1501639495 posted

     

    Tks for your quick help. still the same. tried to follow as what you suggest. it still return all the customer id for every master

    record. if anything wrong in the subreport paramter, guess it would not return any results for the subreport.

    i tried purposly rename the parameter name under subreport parameter property, if i rename the parameter name under

    subreport, it didnt works. still scratching my head...pls help

     

     

     

    Wednesday, July 21, 2010 5:52 AM
  • User-624088818 posted

    In sub report, query should be like select * from Orders where CustomerId=@CustomerID where @customerid is report parameter in the sub report, once you select the customer id in the main report that should be passed to sub report.

    Wednesday, July 21, 2010 6:13 AM
  • User1501639495 posted

    Let me pour my frus a bit here......Getting Scared, Worried and Confuse about all these express editions and new implementations. no guide at all. seems they develop product for their own. no words to say. am a php programer thought of use this asp.net for the reports. cystal report already lousy promting for parameter. thought of using this report RDLC, this one another sucks!!!....total product seems like try with error. Let me come back again and keep begging and support for this lousy...totally am feel like try to find a pin from a big bushhhhhhhhh...

    Good morning sir, your are kind enuf to give me a guidance instead of push me go and look here and there.

    1. Under OrdersDataSet.xsd, In the TableAdapter i have changed in to "SELECT  * FROM Orders WHERE  Orders.CustomerID =@CustomerID"

    Error Return: "Data retrieval failed for the subreport, 'SubReport', located at:"

    Fix Try: I changed the SQL like "SELECT  * FROM Orders WHERE  Orders.CustomerID ='FKLI'"

    Fix Try Answer: All the record in the main report can be seen and its details all showing bleong to customer id "FKLI"

    Conclusion: As what you say from the main report can not pass a value to the sub report parameter. I tried the mainreport.customer id textbox method too. even i change the parameter name. also tried to Report parameter properties -> get values from a query.

    All the above try afer change the subreport query to accomdate the parameter. nothing seems working....i will nicely crate a blog how to use this gr8 RDLC report if i successfully make this work.

    I hope every one using report in their buisness module, hop they need master detail summary report with searching parameter. why there is no help on this matter..i talk to myself...getting mad..

     

     

    Wednesday, July 21, 2010 11:01 PM
  • User1471008070 posted

    Hi,

    You might need to add a filter to the subreport ,keep the SELECT  * FROM Orders query for the dataset rather than  "SELECT  * FROM Orders WHERE  Orders.CustomerID =@CustomerID" Steps for add a filter to the subreport:

    1. Select the report menu in the report designer interface, and then select report parameter item, in the popup window, add a new parameter named CustomerID, then click ok.
    2. Select the report menu in the report designer interface, and then select Data sources item,  in the popup window, click the property of the sub report datasource. Switch to the filter tab, use  “=Fields!CustomerID.Value” for the expression and “=Parameters!CustomerID.Value” for value, and the operator is “=”.

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 23, 2010 4:08 AM
  • User-2037390479 posted

    Hi stevs,

     

    I figured this out if you're still needing assistance.

    In you master report you should have a textbox that contains your PrimaryKey or any textbox that you want to pass the values to a subreport.

    Just right-click on the textbox and scroll down to ACTION.  Fill that part out with the value you want to pass to the report.

    Make sure you have your event handler built on the VB page

    Drop your subreport onto the report and right click on it.  Pass the same parameters there.

    Now to to your subReport and ensure that you have the same parameters setup in both the development environment as well as the LIST or TABLE you are using for reporting.  That should be all you need.

    Thursday, March 17, 2011 5:17 PM