This is a migrated thread and some comments may be shown as answers.

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

3 Answers 71 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
gc_0620
Top achievements
Rank 1
gc_0620 asked on 17 Jul 2013, 08:38 PM
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


3 Answers, 1 is accepted

Sort by
0
gc_0620
Top achievements
Rank 1
answered on 19 Jul 2013, 09:07 PM

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

0
gc_0620
Top achievements
Rank 1
answered on 19 Jul 2013, 11:39 PM
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.
0
Peter
Telerik team
answered on 22 Jul 2013, 03:33 PM
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

Have you tried the new visualization options in Telerik Reporting Q2 2013? You can get them from your account.

Tags
General Discussions
Asked by
gc_0620
Top achievements
Rank 1
Answers by
gc_0620
Top achievements
Rank 1
Peter
Telerik team
Share this question
or