DataSource is refreshed for every chart in trdp file

[Mosart]

Hello,

I'm evaluating Telerik Reporting for an asp.net solution. I'm using the standalone Report Designer to test edit trdp files, and the html5 report viewer wrapper to display in the asp.net application (using IdentifierType="UriReportSource" and Identifier = "Dashboard.trdp").

Questions:

1. The sample trdp file I'm testing on has 9 charts/tables and they all specify the same DataSource object (a MySQL connection string is set and tested with a query). The query issued to the DB server takes around 5 seconds to return, and, given I have 9 charts linked to the DataSource, the report takes nearly 50 seconds to render. Is there a way to limit the DataSource refresh to only once and have the charts pickup from the DS?

2. In the same subject, I've tried to set a DataSet to be the datasource (ObjectDataSource) for the report, but I stumble upon the same issue in different snippets from your forums: The ReportSource data type is not DirectCast compatible with the InstanceReportSource type used by the html5 wrapper. It's been a mission to figure this out, and I wonder if I should just give up on the html5 version of the viewer and roll back to the previous version (apparently all snippet I've found appear to have no issues converting these data types).

Dim instanceReportSource As Telerik.Reporting.InstanceReportSource = DirectCast(reportViewer1.ReportSource, Telerik.Reporting.InstanceReportSource)

(and vice versa).

3. Given the data types incompatibility, is there a way for my code-behind to access the items in the report? The examples in article "Accessing Report Items Programmatically" are of no help, as apparently it uses the older version of the viewer (not html5).

Looking forward to hear from someone more experienced.
Thank you!

 

[Stef]

Hello Mosart,

In regards to your questions:

1. Each data item has a DataSource property. Each DataSource property executes the data-retrieval method of the data source component on its own.
2. To reduce the used data source components and the round-trips for data, you can use the data-binding approaches from Use DataObject as a datasource for nested data items (Table, List, Crosstab, Graph)Steps and How to Databind to Collection Properties.
3. To use a data model in the Standalone Report Designer and applications, please check Extending Report Designer and Previewing a report definition that uses an external assembly.
4. If you need to modify the report at run-time, you will need to iterate each data item to access its DataSource. Note that the report is wrapped in a ReportSource object depending on the report format. Please check the following examples illustrating updates on DataSource properties at run-time: Changing the connection string dynamically according to runtime data and Changing a parameter's datasource connection string at runtime.
   If you are using already instantiated data objects, there is no need to wrap them in a data source component. In general, the purpose of the ObjectDataSource component is to provide data to the report in a declarative manner. The ObjectDataSource.DataSource property should be the assembly qualified name or Type of the data access layer (class), where the reporting engine will use System.Reflection to create the instance of the class (by using the type's default constructor) and to execute its method specified by the ObjectDataSource.DataMember property.
   
   

I hope this helps.

Regards,
Stef
Telerik by Progress

[Mosart]

Stef,

Thank you for your reply.

I'm trying to follow your suggestion in order to 'replace' the datasource of chart elements with data bindings. (use dataobject as a datasource). I need additional help.

The sample report Dashboard.trdp has several charts, and as soon as I replace the chart's DataSource with Binding (Datasource =ReportItem.DataObject), I lose the preview (which I think is expected), but also, when previewing the report, I get over 900 pages... The chart series has filters, picking only top 5 salespersons, for instance, but seams the binding source is not the same as DataSource object.

Can you guide me to accomplish the DataSource replacement? I'm using Telerik's Dashboard sample to evaluate this scenario. The objective is to avoid data round-trips in a scenario where several charts need data (like Dashboard.trdp).

Thank you.

[Stef]

Hello Mosart,

When you use an expression to provide data, the in-time preview is lost as expressions are evaluated on processing the report. The data supplied through the binding must have the fields used by the Graph item, where the binding expression looks like =ReportItem.DataObject. This expression will take the data associated with the container of the current item - data scope.
The Dashboard demo report is not suitable as the nested data items use mainDataSource and the container data item (the report) does not have any DataSource set.


About the newly generated pages, please check the report's PageSettings properties and the available space in the defined physical page. Arrange items in such manner that only horizontal paging will occur - Understanding Pagination.

Check also the report structure in the Report Explorer and which data items will visualize data. for example, if data will be visualized by a Table item, you need to set the Table.DataSource property but leave the report's DataSource property to avoid repeating the Table item.


Regards,
Stef
Telerik by Progress

[Mosart]

Hello Stef,

 

Thank you again for your help.

I'd like to insist on the Dashboard sample, as it has elements and design similar to the reports we aim to have. So here is how I changed it, to comply with your suggestions:
- Changed the container data item (the report) DataSource to mainDataSource.
- Changed the chart elements datasource to "no datasource", and added one binding Property Path = DataSource, with expression =ReportItem.DataObject.
- Chart series still have the same field information (which worked well when DataSource was in use). Not sure if the chart series should be modified now we are using binding concept.

Do you think it's feasible to use this sample with databinding? Again, all I'm trying to do is to avoid 9 queries (round trips) to generate this report.
Last but not least, would it make sense to push a datatable (or dataset) to the report's datasource from code-behind? This way, 9 round-trips would not be a problem as it's all done locally, not a sql server query.

Thank you.

[Stef]

Hello Mosart,

1. Please check the attached modified Dashboard report:
   1. All data items are moved in the ReportHeaderSection (can be any other static section that will be rendered only once even if the report's DataSource is set, see  Report Structure and Grouping Data);
   2. The report's DataSource is set to mainDataSource;
   3. Nested data items' DataSource properties are set via binding to =ReportItem.DataObject. In this case this will be the report's data object.
   4. The Detail section's Visible property is set to false.
2. You can use the data items' NeedDataSource events to get data from an already instantiated data object, instead of using a data source component to create the data instance - Using Report Events.

Regards,
Stef
Telerik by Progress

[Mosart]

That worked perfectly. thank you! Much faster without repeated queries.

I have another question, and please excuse me if I'm off topic here:

I'm using MySQL, and I need to send the SelectCommand of my datasource with some database user variables. MySQL declares user variables with prefix @, and this is interpreted as a parameter by the report designer (sends NULL instead of the @variable)

How can I send an @variable to MySQL in the SelectCommand property?

Thank you!

[Stef]

Hi Mosart,

On configuring the SqlDataSource component you can map SQL parameters to report parameters - step 4. The next step is to provide exact values to be used for getting the data schema for the Report Designer.
For more details check Using Parameters with the SqlDataSource component.

Note that the syntax of the SQL query must be considered with the specification of the selected .NET data provider. It is the provider that interprets the SQL query and providers information what ADO.NET classes to be used for retrieving data.

Regards,
Stef
Telerik by Progress

[Mosart]

Stef,

Thank you for your reply, but it does not cover my question.

I am comfortable with the datasource parameters and how to push them to MySQL, however, the @ prefix in this case is not a parameter, but a local variable. I give you an example, to make my case clear.

When you need a row number in your query, MySQL does not have a rownumber() function (like SQL Server). It requires you to set a variable to 0, and add 1 to the variable on every row returned. It's done like this:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
    FROM orders
    GROUP BY itemID
    ORDER BY ordercount DESC;

This creates a column 'rank' with a serial number (1, 2, 3...)

Notice how @rank is the user_variable, and it's not a parameter at all. I need to pass that @rank exactly as it is, however, the datasource object in the Report Designer automatically creates a parameter with null value (even if I delete it).
What I need is to have the query to be sent intact. I've tried to send @@rank, and other combinations, but no luck.

Thank you!

[Stef]

Hello Mosart,

thank you for the additional details.

If the data provider recognizes the SQL variable as a parameter, it will be listed as SqlDataSource.Parameters entry. To avoid that, you can create a stored procedure.

Regards,
Stef
Telerik by Progress

[Mosart]

Hello Stef,

In this case, I'm pretty sure it's not the data provider recognizing the SQL variable as a parameter. Instead, it's the Report Designer (or the processor behind it) intercepting the @rank and sending it as a parameter to the provider.

I assume this due to the chart's error shown (@rank is replaced by NULL), and also because the report's datasource automatically creates a parameter.
So maybe, this is a good chance to suggest a new feature, where a special character could be used in the query, to avoid interpreting it as a parameter (like @@rank@@, or #rank)

Anyhow, I will try the stored procedure approach.

Thank you again.

[Mosart]

Hello Stef,

Stored Procedure did the trick (no variables with @ prefix are passed, just the sp name).

I have another question about conditional formatting, which we intend to use. Should I ask here "off topic" or initiate another thread? I haven't seen my question answered anywhere, appears to be a bug.

Thanks!

[Stef]

Hi Mosart,

Thank you for this update.
In general, we recommend separating unrelated questions (per feature) in different threads. This allows us to keep a better track on the exchanged information and the progress of each issue.

Note that we try to monitor forums, but we do not guarantee a response by Telerik representative in a timely manner here. If it is an emergency, please submit a support ticket. The support communication can be tracked after logging into your Telerik account.

Regards,
Stef
Telerik by Progress