SQL Server - how to add a input parameter in reporting service sql server 2008

Asked By aman on 10-Jan-12 02:46 AM
hello,

i have created a report services using sql server 2008, but i have to add a input parameter for searching the content inside the report and display the report according to it  but finding it difficult.....

so please help me with some good example.....

thanks,
regards,
Jitendra Faye replied to aman on 10-Jan-12 03:07 AM

　

In windows application

1- define ParameterDiscreteValue object

2- set the value for that object

3- add this object to CurrentValues of the CrystalReport ParameterFields

here is an example

DetailedCustomerOrderReport aReport = new DetailedCustomerOrderReport(); // your crystalReport

ParameterDiscreteValue paramDV_UserId = new ParameterDiscreteValue(); // Step 1

paramDV_UserId.Value = userid; // step 2:userid is the value for the parameters

aReport.ParameterFields["@UserId"].CurrentValues.Add(paramDV_UserId); // step 3: @UserId is the parameter you add at the design of crystal report

--------------------------------------------


In web application

ReportDocument report = new ReportDocument();

report.Load(Server.MapPath(@"~/reports/rptCustomerRecords.rpt"));

report.FileName = Server.MapPath(@"~/reports/rptCustomerRecords.rpt");

report.SetParameterValue("@Userid", userid);
 
// @UserId is the parameter you added at the design of the crystalReport
and userid is the value for the parameters

CrystalReportViewer1.ReportSource = report;

I hope it help you

Riley K replied to aman on 10-Jan-12 03:13 AM


If you wan to add the parameter at database level you will need to add in SQL Query

SELECT [FiscalYear]
   , [ProductCategoryName]
   , [ProductSubcategory]
   , [Region]
   , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] IN (@pRegion)

You need to tell the query where the @pRegion parameter comes from.

Go to the Parameters area of the Dataset Properties dialog. If Visual Studio / BIDS has not already done so, click add to add the @pRegion.

Then in the drop down pick the @Report parameter object. Click OK to save the changes

Regards.