Grouping with Crosstab

[BNA]

I've got a report which consists of a crosstab used to present a weekly calendar of events.
The first issue I ran into was that with the crosstab in the Details section of the report, the entire thing repeated for each record in the data set.  (If I had 7 items in the data set, I'd get the full 40-item crosstab repeated 40 times.)

I solved that by moving the crosstab into a group header, figuring I'd add a label to say which group it was part of.  Unfortunately, now I'm getting the entire thing repeated once for each group, so now my 40-item crosstab is repeated 4 times.  That's 'better', but it's still not what I want.

I can deal with not having group headers, since the normal use-case for this particular report is to view it one group at a time, but it does need to be an option to see the data across all groups.  How can I either filter the results shown so that you only see one group's data at a time, or simply stop it from repeating for each group?

[Peter]

Hi Theo,

Generally the data items (subreport, table, list or crosstab) are a separate data region and does not make use of the report's data source. They have their own DataSource property which you have set in order to populate the item with data. However if you have in addition set the Report.DataSource property. The report engine will create a detail section with the data item for every row of your datasource and the appropriate group sections for each group instance. To avoid this behavior our suggestion is to set the Report.DataSource property to none or move the table item to another non repeating section such as report header or unbound group header (group without grouping). 

Greetings,
Peter
the Telerik team

[BNA]

So each grouping around a sub-report, table, list or crosstab is going to result in a database call to fetch the sub-set of data for that particular sub-group?

I've got a report coming up where I need two tables (side-by-side) to display related, but uncorrelated data sets for potentially upwards of 200 groups.  I can fetch all of the data quickly in a single stored procedure call, but the database call overhead is going to swamp the actual processing time if I have to make 200+ database calls to fill the report.  In that particular case I do need the grouping, so putting it in an 'unbound group' isn't going to solve the problem.

There has to be a better way to use these types of objects without exponentially increasing the number of database calls being made.

For example, today's version of this new report would result in 281 resource sub-groups just for one site/area grouping.  A query returning everything I need as a single data set runs in 1-2 seconds.  Running a reduced query to get the groupings, and then running the needed sub-queries for just *one* site/area/resource combination takes 6 seconds.  If I have to run those two sub-queries 281 times each, this report is going to take forever to run.

[Peter]

Hello Theo,

The data will be retrieved for every data item instance that utilizes a declarative data source component. In other words if you set the Report.DataSource to declarative data source component the query will be executed only once. However if you have a Table item located in a repeating section such as detail section and it's DataSource property is set to a declarative data source component the data will be retrieved for each table instance.

However if the report already have all of the required data you can set the Table.DataSource property with binding as shown in the following example:

Property	Expression
DataSource	=ReportItem.DataObject

ReportItem is the current item(report, table, textbox, panel and etc.) and in case of data bindable items such as table, the DataObject is the parent item's data. Thus in your case =ReportItem.DataObject expression returns the reports grouped data. Check out the Product Catalog demo that has similar setup.

Give it a try and if you still experience difficulties we will appreciate if you elaborate further on the report layout you try to achieve and data.

Greetings,
Peter
the Telerik team

[BNA]

Ok, I think I understand in theory, but I'm having trouble putting it into practice.

Right now, I've been programatically assigning a result set to the DataSource property of subreports, crosstabs, tables, etc.  This was being done to simplify handing off second or third data tables to these subreport types.

I'm trying to change it so that if the name of the control doesn't match our Name_# scheme, it will be assigned the ReportItem.DataObject, but ReportItem isn't actually a property I can find, and neither is DataObject.

Here's the code I'm using currently to do the automatic assignments.  Maybe I'm making things too difficult, and you can provide a better way to do this, or you can show me what I'm missing.

public void InitializeDataSource()
{
    System.Data.SqlClient.SqlCommand cmd = this.BuildSqlCommand();
    this.GetSqlCommandParameters(ref cmd);
    this.SetSqlCommandParameters(ref cmd);

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
    System.Data.DataSet ds = new System.Data.DataSet();
    adapter.Fill(ds);

    // map datasource contents to sub reports and tables
    foreach (Telerik.Reporting.Crosstab item in Wornick.ReportLib.ReportBase.GetDescendantsByType(this.Report, typeof(Telerik.Reporting.Crosstab)))
    {
        if (null == item.DataSource)
        {
            System.Int32 index = Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name);
            if (-1 != index)
            {
                item.DataSource = ds.Tables[Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name)];
            }
    }
    foreach (Telerik.Reporting.Table item in Wornick.ReportLib.ReportBase.GetDescendantsByType(this.Report, typeof(Telerik.Reporting.Table)))
    {
        if (null == item.DataSource)
        {
            System.Int32 index = Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name);
            if (-1 != index)
            {
                item.DataSource = ds.Tables[Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name)];
            }
        }
    }
    foreach (Telerik.Reporting.SubReport item in Wornick.ReportLib.ReportBase.GetDescendantsByType(this.Report, typeof(Telerik.Reporting.SubReport)))
    {
        if (null == item.Report.DataSource)
        {
            System.Int32 index = Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name);
            if (-1 != index)
            {
                item.Report.DataSource = ds.Tables[Wornick.ReportLib.ReportBase.GetDataSetIndex(item.Name)];
            }
        }
    }
    this.DataSource = ds.Tables[0];
}

I'd want to be adding an else to each of the if statements, so that if the name doesn't specify to use a specific data table, it would just hand off the current group.

[Peter]

Hello,

Generally our suggestion is to data bind your data items in declarative manner. This is why we have suggested the Binding to =ReportItem.DataObject that is an expression.

Still up to the shared code we are unable to provide you with any suggestions based only on partial code snippet. We will appreciate if you send us the whole report definition with any dependent code to review on our end. Once we review it we will be able to advise you accordingly.

Regards,
Peter
the Telerik team

[Irmgard]

hi Peter,

I have a similar problem using Telerik Reporting Q1 2013 in VS 2012.
In a report I have defined  2 groups "land" (multiselect parameter) and "year" (from-to parameter). Now I want to add a crosstable with some details of week, product and quantity with the special values of the groups. Unfortunately I cannot define a filter in the crosstab with the values of the groups nor can I define binding property with the values of the groups.
how can I solve this problem?

The report I tried looks like this. See attached file.

Thanks for help,
Irmgard

[Peter]

Hi Irmgard,

How do you set the crosstab datasource property. Generally you don't need to set up crosstab filter. Instead just databind the crosstab to the current group instance data with Binding to =ReportItem.DataObject that is an expression.

For a similar example check out the ProductCatalog demo that came with your Telerik Reporting installation. 

Regards,
Peter
Telerik

[Irmgard]

Hi Peter,

now I've got it. It works! After looking at the example and further more the webinar "Crosstab".

Thanks a lot
Irmgard