How to connect Report in ReportViewer to a datasource?

Hi:

I am new to ReportViewer. I have an .aspx page that takes data that makes a request for a Permit. When the User clicks submit, I add the record to the database then get the unique identity of that record and display it in a popup (as a confirmation number). I would then like a report to show the data the User just submitted.

I have added an .aspx page called RequestReport.aspx and put a ReportViewer on it. I added a report called PermitRequest.rdlc with a textbox that acts as a Title. I linked RequestReport to pull PermitRequest.rdlc and it successfully displays the report. My question is that I don't know how to add a datasource to the report to show the new record. I did save the unique record id to a Session variable called "RequestID".

Please help with directions.

Thanks in advance!

a few questions:

1) Are you using textbox to display the report or some other contrl (like a table)

2) Do you want an SQL type solution that you can handle programatically or a solution where the report is tied to a fixed dataset.

3) What IDE are you using (Visual Web Express or Studio)

bullpit

|||

Hi:

(1) I am using a textbox to display the report name and a table to display the data.

(2) Either one is OK. I just want to be able to pass the newly given unique id to the report and then have the report display only the record data for that id. Each record will change with each user and each new entry.

(3) Visual Studio 2005 SP1 and framework 2.0 talking to a SQL Server 2000 database.

I now have the report displaying all records, but I only want to display the last saved record. Do I need to set a parameter to pass? If so, I see how to pass it programmatically with VB code behind. I don't see how I tell the report to accept a parameter as part of the SQL Statement.

|||

The way I do, I handle everything from codebehind...that gives me more control...that way i can send an SQL query to fetch whatever i want...in your case, you have the id...build a select query string in codebehind to select only the records with that id...then bind to the report...

these two funtions will help you get started if you want to go that way:

public void FindAll()
{
// Set the processing mode for the ReportViewer to Local
ReportViewer1.ProcessingMode = ProcessingMode.Local;
LocalReport rep = ReportViewer1.LocalReport;
rep.ReportPath = "Report.rdlc";
DataSet ds = GetSalesDataFull();
// Create a report data source for the sales order data
ReportDataSource dsMaintenanceDS = new ReportDataSource();
dsMaintenanceDS.Name = "DataSet1_Main";
dsMaintenanceDS.Value = ds.Tables["Main"];
rep.DataSources.Clear();

//example to set report parameter values from codebehind
ReportParameter param = new ReportParameter("nRows", dDownListNRows.SelectedValue);
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { param });

rep.DataSources.Add(dsMaintenanceDS);
rep.Refresh();
}

private DataSet GetSalesDataFull()
{
DataSet ds = new DataSet();
string str = "";

str = @." WHERE ""ID""='" + <YOUR ID> ')";
string sqlSalesData = @."SELECT * FROM <TABLE NAME> " + str;

OdbcConnection connection = new OdbcConnection(<CONNECTION STRING>);
OdbcCommand command = new OdbcCommand(sqlSalesData, connection);
OdbcDataAdapter salesOrderAdapter = new OdbcDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}

let me know if you have more questions.

good luck...bullpit

|||

The way I do, I handle everything from codebehind...that gives me more control...that way i can send an SQL query to fetch whatever i want...in your case, you have the id...build a select query string in codebehind to select only the records with that id...then bind to the report...

these two funtions will help you get started if you want to go that way:

public void FindAll()
{
// Set the processing mode for the ReportViewer to Local
ReportViewer1.ProcessingMode = ProcessingMode.Local;
LocalReport rep = ReportViewer1.LocalReport;
rep.ReportPath = "Report.rdlc";
DataSet ds = GetSalesDataFull();
// Create a report data source for the sales order data
ReportDataSource dsMaintenanceDS = new ReportDataSource();
dsMaintenanceDS.Name = "DataSet1_Main";
dsMaintenanceDS.Value = ds.Tables["Main"];
rep.DataSources.Clear();

//example to set report parameter values from codebehind
ReportParameter param = new ReportParameter("nRows", dDownListNRows.SelectedValue);
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { param });

rep.DataSources.Add(dsMaintenanceDS);
rep.Refresh();
}

private DataSet GetSalesDataFull()
{
DataSet ds = new DataSet();
string str = "";

str = @." WHERE ""ID""='" + <YOUR ID> ')";
string sqlSalesData = @."SELECT * FROM <TABLE NAME> " + str;

OdbcConnection connection = new OdbcConnection(<CONNECTION STRING>);
OdbcCommand command = new OdbcCommand(sqlSalesData, connection);
OdbcDataAdapter salesOrderAdapter = new OdbcDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}

let me know if you have more questions.

good luck...bullpit

|||

Hi Bullpit:

Thank you for your response. Do you have this in Visual Basic code behind? Also, if i do this all in the code behind...will I still have the fields in the dataset on the report to drag and drop to the table on the report? Will I still be able to layout the design of the report?

|||

Sorry, I do not have this code in VB. And yes, you will be able to design the table the way you want. I wanted my report to be dynamic, so I gave the user the choice to choose the fields she/he wants on the report..so whatever field name user chooses, corresponding values are bound to that column in details section...but if you don't want that, you can have the column names as static (by drag and drop)...i believe it should work...also, there are several tools online to convert c# to vb...you can use them...

P.S. If you choose to go codebehind, then remember to add the name of the datasource (in our case "DataSet1_Main") in the Report Data Source option in IDE.

good luck