Multiple references to same data set

[GER]

I have a rather complex report that presents data results from a SQL Server stored procedure in a table then displays charts of the data for each category.  The chart is the same data set as the table but with a filter.  The table and each chart reference the same data source which in turn references the stored procedure.  I discovered that this causes the same rather complex stored procedure to be executed not only for the table but again separately for each chart.

My first thought was to execute this complex stored procedure only on the table while stashing the results into a SQL Server table then have the charts reference only the resultant SQL Server table.  However, a temporary table generated within the stored procedure vaporizes at the completion of the procedure.  I cannot use a static table as it would cause conflicts between multiple executions of the report from different users.  What solutions exist for this situation?

[GER]

OK, I did some more digging and found several references to the same issue.  Now that I know that my situation is not unique, I'm still having trouble.  Most references tell me to set binding for my chart to =ReportItem.DataObject, however this is insufficient.  In my report I have two tables (TableA and TableB) and I have multiple charts based upon those two tables.  ChartA1 needs the same data that's in TableA with an applied filter.  ChartB1 likewise needs to reference TableB's data with a filter.  What do I need to set at design time to make this happen without customized C# coding?

[Todor]

Hello,

We provided the answers of your questions and the possible workarounds in the support ticket you opened for the same issue (ID #1154187).

Regards,
Todor
Progress Telerik

[GER]

Is there a reason why you don't want to post the solution here too? It might be nice in case other users are having the same issue.

[Nasko]

Hello GER,

There is no sensitive information in your support thread, so it is not an issue to share the original answer here as well:

"Currently, each data item DataSource is treated on its own, i.e. although the Table and the Graphs use the same data source, for each of them the data will be fetched independently.
We are considering optimization of this behavior.

The workaround is to use an ObjectDataSource, which data member method fetches the data from the database on the initial call and caches it to be reused upon subsequent requests.
This approach involves writing custom code.

ReportItem.DataObject could be used to assign the data source of a parent item to its child data item, i.e. it is applicable for nested data items.
You could check how to use it properly in this help article.

I will suggest an optimization for Design time using the ReportItem.DataObject.

I noticed that one of the data sources is used 8 times, so I suggest you to set it as data source of the Report and follow the approach below.
In the case of a single DataSource multiple calls to the database could be avoided by :

- assigning the DataSource to the Report
- placing the data items (Table and Graphs) in Report Header or Footer (see below)
- assigning data items' DataSources through bindings to the ReportItem.DataObject (this would be the DataSource of the Report).

With the above scenario the data items should be placed in the Report Header/Footer to avoid multiplication when placed in the detail section (it is rendered for each data record), and since data items cannot be used inside Page Header/Footer.

If this approach is applicable, the data items using the second data source should also be placed inside Report Header/Footer, for reasons already explained.
This way ProcB would be executed only once. ProcA would still be executed twice."

Regards,
Nasko
Progress Telerik