Filtering results in Report.rdlc
Question
Answers
-
User-1828494216 posted
Hi,
step 1: In Visual Studio open Server Explorer or in SQL Server Management Studio, navigate to your DataBase and create a stored procedure.
CREATE PROCEDURE dbo.getReportData ( @Status bit, @Year int ) AS SELECT MemberTable.ID, MemberTable.FirstName, MemberTable.LastName, MemberTable.Address, MemberTable.POBox, MemberTable.City, MemberTable.State, Payment_Table.AmountPaid FROM MemberTable INNER JOIN Payment_Table ON MemberTable.ID = Payment_Table.ID INNER JOIN YearTable ON Payment_Table.YearID = YearTable.YearID WHERE (Payment_Table.Status = @Status) AND (YearTable.YearID = @Year)
Execute it and make sure that it returns data. I created the tables based on the information you provided in your previous post and they should match. BUT please do check if they really do!
step 2: Create a new EMPTY Web Application and add a new Form. Add the code below to your new form and make sure that it's matching! Then open your Web.Config file in SolutionExplorer and add a connection string.
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <div> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Year_Of_Payment" DataValueField="YearID"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>" SelectCommand="SELECT * FROM [YearTable]"></asp:SqlDataSource> <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2" DataTextField="Status" DataValueField="Status"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>" SelectCommand="SELECT DISTINCT [Status] FROM [Payment_Table]"> </asp:SqlDataSource> </div> </form> </body> </html><?xml version="1.0"?> <!-- For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="testConnectionString" connectionString="Data Source=./SQLEXPRESS;Initial Catalog=YOURDBNAME;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web>now run the application and make sure that your DropDownLists hold the data from your DataBase.
step 3: Add ReportViewer control to your form and from Solution Explorer add new report.
step 4: Drag Table from Toolbox into the report and a wizard will open.
Step 5: Click New to create Data source, use the connection from your WebConfig and select ONLY STORED PROCEDURE you created in first step. Click OK until you see your report.
Step 5: You should be now back on your report. In table click into the Data (not Header) row and a small table icon will apear in upper right corner (see 2nd image). Click on the image and add ur fields. When u're done, dont forget to save!
Step 6: Go back to the the Form (Design view) and click on the arrow in upper right corner of reportViewer and select the report you just created.
Step 7: Look below the ReportViewer, there should be a box named ObjectDataSource, click on it, again use the arrow, click configure datasource, click next (If you'll see some error about not able to find datatable, just reBuild your project and get back to this step), Choose Method GET DATA blah blah blah... (that's the name of the stored procedure you created in first step). Click Next. Now we need to set parameters. Select Contol and ID DropDownList2, repeat the same for the year but choose DropDownList1! Finish.
Step 7: Now go in the code behind (press F7 in you report) of your form and add this line of code in Page Load event:
protected void Page_Load(object sender, EventArgs e) { if (IsPostBack) ReportViewer1.LocalReport.Refresh(); }now everything should work!
Hope this helps you.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
All replies
-
User-851246726 posted
I designed a demo to show what I am looking for (what filters):
I actually want 2 kinds of filters to work with the report,
1) Select Year
2) Status (Paid / Un-paid / All)
Here's the screenshot:
-
User-1828494216 posted
Hi,
step 1: In Visual Studio open Server Explorer or in SQL Server Management Studio, navigate to your DataBase and create a stored procedure.
CREATE PROCEDURE dbo.getReportData ( @Status bit, @Year int ) AS SELECT MemberTable.ID, MemberTable.FirstName, MemberTable.LastName, MemberTable.Address, MemberTable.POBox, MemberTable.City, MemberTable.State, Payment_Table.AmountPaid FROM MemberTable INNER JOIN Payment_Table ON MemberTable.ID = Payment_Table.ID INNER JOIN YearTable ON Payment_Table.YearID = YearTable.YearID WHERE (Payment_Table.Status = @Status) AND (YearTable.YearID = @Year)
Execute it and make sure that it returns data. I created the tables based on the information you provided in your previous post and they should match. BUT please do check if they really do!
step 2: Create a new EMPTY Web Application and add a new Form. Add the code below to your new form and make sure that it's matching! Then open your Web.Config file in SolutionExplorer and add a connection string.
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <div> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Year_Of_Payment" DataValueField="YearID"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>" SelectCommand="SELECT * FROM [YearTable]"></asp:SqlDataSource> <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2" DataTextField="Status" DataValueField="Status"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>" SelectCommand="SELECT DISTINCT [Status] FROM [Payment_Table]"> </asp:SqlDataSource> </div> </form> </body> </html><?xml version="1.0"?> <!-- For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="testConnectionString" connectionString="Data Source=./SQLEXPRESS;Initial Catalog=YOURDBNAME;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web>now run the application and make sure that your DropDownLists hold the data from your DataBase.
step 3: Add ReportViewer control to your form and from Solution Explorer add new report.
step 4: Drag Table from Toolbox into the report and a wizard will open.
Step 5: Click New to create Data source, use the connection from your WebConfig and select ONLY STORED PROCEDURE you created in first step. Click OK until you see your report.
Step 5: You should be now back on your report. In table click into the Data (not Header) row and a small table icon will apear in upper right corner (see 2nd image). Click on the image and add ur fields. When u're done, dont forget to save!
Step 6: Go back to the the Form (Design view) and click on the arrow in upper right corner of reportViewer and select the report you just created.
Step 7: Look below the ReportViewer, there should be a box named ObjectDataSource, click on it, again use the arrow, click configure datasource, click next (If you'll see some error about not able to find datatable, just reBuild your project and get back to this step), Choose Method GET DATA blah blah blah... (that's the name of the stored procedure you created in first step). Click Next. Now we need to set parameters. Select Contol and ID DropDownList2, repeat the same for the year but choose DropDownList1! Finish.
Step 7: Now go in the code behind (press F7 in you report) of your form and add this line of code in Page Load event:
protected void Page_Load(object sender, EventArgs e) { if (IsPostBack) ReportViewer1.LocalReport.Refresh(); }now everything should work!
Hope this helps you.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
-
User-851246726 posted
Hi,
Thank you for the nice tutorial. I followed all the steps but when I try to run the page I get this error:
.............................
- A data source instance has not been supplied for the data source 'DataSet1'.
.............................
I also rebuilt my project still getting the same error, what could be the problem ?
I also noticed on thing, when I try to "Execute" the stored procedure, I get no result of any kind of values, this is what I get in output window:
Running [dbo].[getReportData] ( @Status = True, @Year = 2006 ).
ID FirstName LastName Address POBox City State AmountPaid ----------------
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[getReportData].As far as I can see the in query builder the table mapping is correct.
For I don't know what reason I see duplicate controls here: (I only used your code which means one dropdown menu for year and one for status)
-
User-851246726 posted
I rebuilt my project once again but now I am not getting dataset in the drop down. Instead of that I am getting ajaxtoolkit values
Here's my screenshots:
MyDataset:
My Report Viewer:
When I try to configure ObjectDataSource, I get this:
-
User-1828494216 posted
When I try to configure ObjectDataSource, I get this:
click show only data components...
-
User-851246726 posted
If I mark the checkbox, I don't get anything:
-
User-1828494216 posted
In adition to my code, you can capture the id of the control that cauzed postBack and compare it to the ID's of your DropDowns. If they match refresh the ReportViewer.
If that is not done, obviously the ReportViewer will refresh itselfe on everyPostback and when you click refresh guess what happenes... postback :) If you don't know how to do it, i sugest you search this forum or open up a new threat.
EDIT:
The best solution is to create a SelectedIndexChanged Event and bind it do both of the DropDowns and in that event refresh ReportViewer.
-
User-851246726 posted
ok I did everything from starting to show all the steps I followed:
Step 1:
Created a stored procedure:
CREATE PROCEDURE dbo.getStateReport
(
@State nvarchar
)
AS
SELECT *
FROM MemberTable
WHERE (State =@State)
Step2:
Added new "DataSet" in "App_Code" named as "dropfilterState.XSD"
Then I added "TableAdapter" in which I added the storedprocedure like this:
Step 3:
Now I added a "Report" like this which I named as "dropfilterstate.RDLC"
Step4:
In my report I added "Data Set" like this:
Step 5:
I added columns in my report like this:
Step 6:
Now I added a webpage named as "dropfilterbystate.ASPX" to display the report
Step 7:
Now I added the report viewer in my page and the drop down menu.
In my report viewer I selected the report that I created above "dropfilterstate.RDLC"
Step 8:
Now after selecting report, I choose "Data Source" > NEW DATA SOURCE like this:
Here is my output in which when I select State from dropdown menu, my report doesn't show the member in that state:
This is the code of my "dropfilterbystate.aspx" page
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<h2> Generate Report - Please make your selections</h2><br />
<div>
Select State:
<asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True"
DataSourceID="SqlDataSource1" DataTextField="State" DataValueField="State" >
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT DISTINCT [State] FROM [MemberTable]"></asp:SqlDataSource>
</div><br />
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="11in"
Width="8.5in">
<LocalReport ReportPath="Account\dropfilterstate.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="state" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
TypeName="dropfilterbystateTableAdapters.getStateReportTableAdapter">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="State"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
</asp:Content>NEED HELP
-
User-851246726 posted
Ok I re-built my project this time and corrected the path of rdlc report.
When I run the project, view the webpage in browser> as soon as I select any state in dropdown menu,
I get this error:
When I press F11, then I get this error:
An attempt was made to set a report parameter 'State' that is not defined in this report.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownReportParameterException: An attempt was made to set a report parameter 'State' that is not defined in this report.
Source Error:
Line 18: ReportViewer1.Visible = true;
Line 19: ReportViewer1.LocalReport.ReportPath = "Account/dropfilterstate.rdlc";
Line 20: ReportViewer1.LocalReport.SetParameters(new ReportParameter("State", DropDownList2.SelectedValue.ToString()));
Line 21:
Line 22: ReportViewer1.LocalReport.Refresh(); -
User-851246726 posted
hi Ashutosh
so I did what you mentioned above, but still I don't see anything in thr report on selection of any state from the dropdown menu.
It's just that I am not getting any error message.
This is what I did: PLEASE CORRECT ME IF I AM WRONG.
