Change RecordSource of Wijmo ReportViewer

Wijmo ReportViewer is used to display reports. Many of our customers have asked us how to change the RecordSource of the reports. In such a case, we need to modify the SQL query of the report and then, display the report. This blog explains the approach to set the RecordSource of the report loaded in Wijmo ReportViewer and then, modify it according to the end-user selection. Here are the details:

Create and Register Dynamic reports

To display any report in Wijmo ReportViewer, we need to register it first. You may create Dynamic report at run time and then, register dynamic report document from code-behind. This registration is done using the static C1ReportViewer.RegisterDocument method.

The C1ReportViewer.RegisterDocument method accepts two parameters:

1. the first parameter is the name of the dynamic report document.
2. the second parameter is a delegate, which will be called in order to generate report document.

The FileName property should be set to the name of the dynamic report that is used as first parameter for the C1ReportViewer.RegisterDocument method.

The following is a sample code to display the initial report. It also shows how we can register and display the dynamic report document:

string dbpath = HttpContext.Current.Server.MapPath("~/App_Data/TwoTables.mdb");  
string rptpath, query, rptname;  

protected void Page_Load(object sender, EventArgs e)  
{  
    query = "SELECT DISTINCT [Ten Most Expensive Products].TenMostExpensiveProducts, [Ten Most Expensive Products].UnitPrice   FROM  [Ten Most Expensive Products];";  
    rptpath = HttpContext.Current.Server.MapPath("~/ReportLayout/MostExpensive.xml");  
    rptname = "MostExpensive";C1ReportViewer.RegisterDocument(rptname, MakeDoc);  

    C1ReportViewer1.FileName = rptname;  
    C1ReportViewer1.ReportName = rptname;  
}  

protected  C1PrintDocument MakeDoc()  
{  
    C1Report report = C1ReportViewer.CreateC1Report();  
    report.Load(rptpath, rptname);  
    report.DataSource.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath + ";Persist Security Info=False";  
    report.DataSource.RecordSource = query;  
    report.Render();  
    return report.C1Document;  
}

Un-Register the previous document and change the RecordSource

To modify the query and display the new report, you need to un-register the previously registered document. This is because the document is automatically cached by the Wijmo ReportViewer.

After doing so, we need to re-create the document using the modified sql query (i.e. based on end user selection) and then, register it again. To re-register, you need to use RegisterDocument method again :

C1ReportViewer.RegisterDocument(rptname, MakeDoc);

The complete code looks as follows :

protected void Button1_Click(object sender, EventArgs e)  
{  
    // previous document is un-registered  
    if (C1ReportViewer.HasCachedDocument(C1ReportViewer1.FileName, C1ReportViewer1.ReportName))  
        C1ReportViewer.UnRegisterDocument(rptname);  
    // query is modified based on the country selected by the end user  
    query = "SELECT DISTINCT [Quarterly Orders].CustomerID, [Quarterly  Orders].CompanyName,[Quarterly Orders].City, [Quarterly Orders].Country  FROM  [Quarterly Orders] WHERE [Quarterly Orders].Country = \\"" +      ddlCountry.SelectedItem.Text + "\\";";  
    rptpath = HttpContext.Current.Server.MapPath("~/ReportLayout/QuarterlyOrdersReport.xml");  
    rptname = "Quarterly Orders Report";  

    // registering the new report  
    C1ReportViewer.RegisterDocument(rptname, MakeDoc);  
    C1ReportViewer1.FileName = rptname;  
    C1ReportViewer1.ReportName = rptname + "_" + ddlCountry.SelectedItem.Text;  
}

Please refer to the attached sample for complete implementation. In the sample, you can select the Country from the dropdown list and corresponding report will be displayed in the Reportviewer. Download Sample - C# Download Sample - VB