C1Reports offers various features to it's users which make it easier for them to create and customize their data as per their requirements. One of them is to 'Add Parameters for Adding/Limiting Data'. This feature can be used to create reports with parameterized queries, allowing users to customize the report by adding/limiting the data that should be included in the report before it is rendered. One can specify a value for a report field, filter data, control sorting and grouping, and more allowing the user to display only the necessary data in the report. This blog deals with the various scenarios of Parameters Handling in C1Report, namely :
*** Disable the parameters dialog and to avoid user entry
- Passing Parameters from Main-Report to Sub-Report
- Passing Parameters to Stored Procedure in C1Reports**
Create Parameterized Report
Before dealing with the above-mentioned scenarios, we should know as how to create a parameterized report. In order to create a Parameterized Report, one need to write a 'Parameter Query' and assign it to the 'Record Source' property of C1Reports and add a PARAMETER clause to it. For instance,
"PARAMETERS [Beginning Date] DateTime 1/1/1994,[Ending Date] DateTime 1/1/1995,[Sales Goal] Int 15000; SELECT DISTINCTROW Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal AS SaleAmount FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID WHERE (((Orders.ShippedDate) Between [Beginning Date] And [Ending Date]));"
Here is what the dialog box looks like for the 'Parameter Query' statement listed above. For more information on this, refer to the C1Reports Documentation.
1. Disable the parameters dialog and to avoid user entry:
There might be certain situations when we want to display the data in report on the basis of the parameter passed but we do not want the user to enter the same, every time the report is to be displayed. In order to disable the parameters dialog and to avoid user entry, add the following line of code under the 'InitializeParametersDialog' event of the C1Report :
e.ShowDialog = False
This will not show the parameters dialog at the time of rendering of the report. Attached is the sample for your reference. Download Sample
2. Passing Parameters from Main-Report to Sub-Report:
When there are more than one report in the report definition file, that is the main report and the subreport, and we want to get the filtered data i.e. retrieve the data of the subreport on the basis of the parameter passed to the mainreport. To achieve the same, we just need to transfer the parameters to SubReport in OnFormat event of MasterReport's detail section. Attached is the report xml implementing the same. Download Sample
3. Passing Parameters to Stored Procedure in C1Reports:
Many a times StoredProcedures are specified as the DataSource of the reports. When we want to create a parameterized reports for such type of report, then we need to pass parameters to stored procedure while loading/previewing the report. Following are the steps to do the same :
- Set the 'ConnectionString' property
- And assign the name of the stored procedure to the 'RecordSource' property of the report. And pass the value of the parameter in the same.
'replace with your connection string C1Report1.DataSource.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=IN-DBTest" C1Report1.DataSource.RecordSource = "CustOrdersDetail(10256)"
Attached is the sample for your reference. Download Sample