How to filter Report Sqldatasource in ASP.net Page load event.

[gc_0620]

Folks

Using RadControls for ASP.NET AJAX Q1 2013 SP2 with Reporting version = 7.1.13.705. In my report I have a SQLDatasource (i.e. SQLDatasource2).

I would like to Filter that Datasource in Page load event of ASP.net not inside Report NeedDataSource event.

This works in Report NeedDataSource event.

private void TestReport1_NeedDataSource(object sender, EventArgs e)
      {
          //Take the Telerik.Reporting.Processing.Report instance
          Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;

          string stsql = "SELECT M.ManagerID, C.FirstName + ' ' + C.LastName AS Name, E.Gender FROM  (SELECT DISTINCT ManagerID  FROM HumanResources.Employee) AS M INNER JOIN";
          stsql += " HumanResources.Employee AS E ON M.ManagerID = E.EmployeeID INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID WHERE (E.Gender = N'F') ORDER BY Name";
          this.sqlDataSource2.SelectCommand = stsql;


      }

But I would like to filter the Datasource in Page load event of asp.net. I came up with this..

Protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Report1 myReports = new Report1();

            Reports.TestReport1 myReport = new Reports.TestReport1();

            string stsql = "SELECT M.ManagerID, C.FirstName + ' ' + C.LastName AS Name, E.Gender FROM  (SELECT DISTINCT ManagerID  FROM HumanResources.Employee) AS M INNER JOIN";
            stsql += " HumanResources.Employee AS E ON M.ManagerID = E.EmployeeID INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID WHERE (E.Gender = N'F') ORDER BY Name";

// Need help here to filter SQLDatasource2..
        }

    }

Any help is appreciated. I use Asp.net Ajax a lot, trying to implement Telerik Reporting along with Webforms in a new project. Thanks

gc_0620

[gc_0620]

Any suggestions?
 

Basically I want to fill the Report data set  as well as a Report Parameter Datasource outside the report.
I am creating the data adapter, commands, connection, and data set, filling it from outside the report
and then passing the prepared data set/table to the report.

Here is a sample code I come up with:

 

class Class1
{
    Report report1;

    public Class1()
    {
        this.report1 = new Report1();
        this.report1.NeedDataSource += new EventHandler(this.report1_NeedDataSource);
    }

    ...

    void report1_NeedDataSource(object sender, System.EventArgs e)
    {
        string prm1 = "P1";
        string prm2 = "P2";

        report1.DataSource = this.CreateDataSet(prm1, prm2);

      // Here I would like to populate a Report Parameter Datasource (i.e. sqlDataSource2) from below code.

         string stsql = "SELECT M.ManagerID, C.FirstName + ' ' + C.LastName AS Name,  E.Gender
                        FROM  (SELECT DISTINCT ManagerID  FROM HumanResources.Employee) AS M INNER JOIN";
        stsql += " HumanResources.Employee AS E ON M.ManagerID = E.EmployeeID INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID ";
        stsql += " WHERE (E.Gender = N'F') ORDER BY Name";

//

    }

    DataSet CreateDataSet(string prm1, string prm2)
    {
        DataSet ds = new DataSet();
        DbDataAdapter adapter = null;
        ...
        adapter.Fill(ds);
        ..
        return ds;
    }
}

Thanks for any help

gc_0620

[gc_0620]

Just for clarification. The data source SQLDatatasource2 is inside Report, not in asp.net Web Page. Basically I want to update it's data source from asp.net Web Page.

[Peter]

Hello,

You can change the report definition datasource components as shown in the following example:

static Report GetReport()
{
    //Set the new datasouce to all data items
    SetDataSource(dataSource, report);

    return report;
}

static void SetDataSource(DataSource dataSource__1, ReportItemBase reportItemBase)
{

    if (reportItemBase.Items.Count < 1) {
        return;
    }

    if (reportItemBase is Report) {
        object report = (Report)reportItemBase;

        if (report.DataSource != null) {
            report.DataSource = dataSource__1;
        }
        foreach (var parameter in report.ReportParameters) {
            if (parameter.AvailableValues.DataSource != null) {
                parameter.AvailableValues.DataSource = dataSource__1;
            }
        }
    }

    foreach (var item in reportItemBase.Items) {
        //recursively set the datasource to the items from the Items collection
        SetDataSource(dataSource__1, item);

        //Covers Crosstab, Table and List data items
        if (item is Table) {
            object table = (Table)item;
            if (table.DataSource != null) {
                table.DataSource = DataSource;
                continue;
            }
        }
        if (item is Chart) {
            object chart = (Chart)item;
            if (chart.DataSource != null) {
                chart.DataSource = DataSource;
                continue;
            }
        }
        if (item is SubReport) {
            object subReport = (SubReport)item;
            if (subReport.DataSource != null) {
                subReport.DataSource = DataSource;
                continue;
            }
        }
    }
}

Regards,
Peter
Telerik