In my last post, i took a look at how to use SQLite database in Telerik Reporting Standalone Report Designer. In this post, i have recorded a short 10 minute video which provides you step by step instruction on how to use SQLite database in Telerik Reporting Standalone Report Designer. Continue reading
In this post we will take a look at how a report designed in Standalone End User Report Designer, can be rendered from your applications – be it WPF, WinForms, SilverLight or ASP.NET. One of the customer I was working with had this requirement of end users to design the report and that report should be rendered within the application. The end users who design the report will have the flexibility to upload the designed report to the application. The application had to render the uploaded report in a reporting module. So we will see how to achieve this. If this requirement has excited you, then read through rest of this post.
In order to design reports using the standalone end user report designer, you will need to first download the Telerik Reporting solution. This is available as a free 30 day trial download. Here is the download link:
in order to mimic the scenario of End User connecting to a staging DB and designing the report on their desktop – I will be making use of 2 Databases. So you will need a RDBMS product of your choice. In my example I will be using SQL Server 2012.
Creating Staging and Production DB:
For the sake of the demo, I will create 2 database and name them as StagingDB and ProductionDB respectively. The database will have only one table named Contacts. It will have 2 columns an Id and Name. Here is the code snippet for the table:
|CREATE TABLE [dbo].[contacts]|
|[Id] [int] IDENTITY(1,1) NOT NULL,|
|[Name] [varchar](50) NOT NULL,|
|CONSTRAINT [PK_contacts] PRIMARY KEY CLUSTERED|
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]|
|) ON [PRIMARY]|
So at his time, if you are following along, you should have created 2 databases – StagingDB and ProductionDB and in both the DB you should have 1 table named Contacts. In staging DB contacts table I will fill some dummy data and it looks like below:
Similarly in the Contacts table of production DB, I have inserted the following data:
We will mimic a scenario where in the end users will connect to the StagingDB->Contacts table and design the report using the Standalone Report Designer. After designing it they will upload to the application. And application will connect to production DB and render the report.
Designing the report:
when you install the Telerik Reporting suite, we will also install the Standalone report designer for end users. This is an executable which can be run on any system. It can be in a folder named Report Designer at the root of Telerik Reporting installation folder. When you launch the ReportDesigner,exe, you will see the following interface:
Click on New Report, give a name for the report and select a path to save the report.
Next, we will be presented with a Report Wizard. In the “Choose Data Source” dialog, create a new data source and connect to the Staging DB we have created earlier. In order to connect to our SQL Server database, select SQL Data Source in the data source selection dialog and give it a name.
In the data connection selection page, configure the connection settings to your Staging DB.
Next we configure the command i.e. the select query to fetch the report data. For this demo it’s a simple statement – “Select * from Contacts”. Next you can preview the results of the query
Click on finish to finish the connection settings configuration.
Now you will see that we have set up a data source for the report and following dialog shows the details:
Next we will configure the data layout of the report, In this example, we have only two columns in the table and I will show both the column in the body of the report.
Next select the report layout i.e. is the report a stepped report or outline or left aligned. I have selected Stepped report i.e. items are arranges in columns and they don’t overlap vertically.
Next you can select a report style and finish the wizard.
At this point we have successfully designed a report using the report designer. In order to preview the report, click the Preview button on the Home tab. The report will be executed and shown as below:
As you can see we have connected to our staging db and pulling data from the contacts table.I have named the report as DemoReport and is saved as DemoReport.trdx. The TRDX is a custom file format that Telerik Reporting uses. It stands for Telerik Reporting Definition XML. Next we will see how we can upload this file to a application and render the report inside the application.
Building a WPF client:
In order to mimic a production application, I have built the following WPF app.
Although I have selected WPF app for this demo, it can be anything in real world – ASP.NET Web App, WinForms or SL. Here is the work flow of this app:
An end user uploads the .TRDX report file he designed using the Standalone Report Designer, in the app.
The app saves the file in the application folder.
App will list the uploaded report file in a list box.
When the report is selected in the list box, the report will be rendered in the report viewer.
For the sake of the time, I will not be describing the portion of the app which allows us to browse and select a .TRDX file, Saves the uploaded .TRDX file in one of the application folder and listing of the uploaded report in list box. Those are pretty straight forward code that you will be familiar with. We will pay our attention to how do we render the .TRDX file inside the Report Viewer in the coming sections.
TRDX Report Rendering using URI:
The report viewer has the capability to just take a URI of a .TRDX file and render it. The ReportViewer exposes a property called ReportSource. One of the supported report source is UriReportSource. As the name goes, we are supposed to create an instance of UriReportSource and set the URI to the .TRDX file. Be it a WPF app as the one we are dealing with now or a web scenario – you just need to get the complete URI of the .TRDX file. Here is the code snippet to achieve this:
|var uriReportSource = new UriReportSource();|
|uriReportSource.Uri = "<URI of .TRDX file>";|
|reportViewer.ReportSource = uriReportSource;|
TRDX Report Rendering Using XML Source:
In previous section we saw UriReportSource as the report source for the report viewer. The report viewer also supports XML as a report source. We can pretty much give a XML literal string which confronts to Teelrik Reporting Definition XML schema and the report viewer will be able to parse and render the same. The .TRDX report file is nothing but a XML in itself. Here is the content of the DemoReport we designed in its XML form:
|<?xml version="1.0" encoding="utf-8"?>|
|<Report DataSourceName="ReportDataSource" Width="6.45833333333333in" Name="DemoReport" xmlns="http://schemas.telerik.com/reporting/2012/2">|
|<SqlDataSource ConnectionString="stagingdb1" SelectCommand="select * from contacts" Name="ReportDataSource" />|
|<PageHeaderSection Height="0.28125in" Name="pageHeader">|
|<TextBox Value="DemoReport" Size="6.41666666666667in, 0.200000027815501in" Location="0.0208333333333333in, 0.0208333333333333in" Name="reportNameTextBox" StyleName="PageInfo" />|
|<PageFooterSection Height="0.28125in" Name="pageFooter">|
|<TextBox Value="=NOW()" Size="3.19791666666667in, 0.200000027815501in" Location="0.0208333333333333in, 0.0208333333333333in" Name="currentTimeTextBox" StyleName="PageInfo" />|
|<TextBox Value="=PageNumber" Size="3.19791666666667in, 0.200000027815501in" Location="3.23958333333333in, 0.0208333333333333in" Name="pageInfoTextBox" StyleName="PageInfo">|
|<Style TextAlign="Right" />|
|<ReportHeaderSection Height="0.808234930038452in" Name="reportHeader">|
|<TextBox Value="DemoReport" Size="6.45833333333333in, 0.787401596705119in" Location="0in, 0in" Name="titleTextBox" StyleName="Title" />|
|<ReportFooterSection Height="0.28125in" Name="reportFooter" />|
|<DetailSection Height="0.28125in" Name="detail">|
|<TextBox Value="=Fields.Id" CanGrow="True" Size="3.19791666666667in, 0.200000027815501in" Location="0.0208333333333333in, 0.0208333333333333in" Name="idDataTextBox" StyleName="Data" />|
|<TextBox Value="=Fields.Name" CanGrow="True" Size="3.19791666666667in, 0.200000027815501in" Location="3.23958333333333in, 0.0208333333333333in" Name="nameDataTextBox" StyleName="Data" />|
|<Font Name="Tahoma" Size="18pt" Bold="True" Italic="False" Underline="False" Strikeout="False" />|
|<StyleSelector Type="ReportItemBase" StyleName="Title" />|
|<Style Color="Black" VerticalAlign="Middle">|
|<Font Name="Tahoma" Size="10pt" />|
|<StyleSelector Type="ReportItemBase" StyleName="Caption" />|
|<Font Name="Tahoma" Size="9pt" />|
|<StyleSelector Type="ReportItemBase" StyleName="Data" />|
|<Font Name="Tahoma" Size="8pt" />|
|<StyleSelector Type="ReportItemBase" StyleName="PageInfo" />|
|<MarginsU Left="1in" Right="1in" Top="1in" Bottom="1in" />|
|<GroupHeaderSection PrintOnEveryPage="True" Height="0.28125in" Name="labelsGroupHeader">|
|<TextBox Value="Id" CanGrow="True" Size="3.19791666666667in, 0.200000027815501in" Location="0.0208333333333333in, 0.0208333333333333in" Name="idCaptionTextBox" StyleName="Caption" />|
|<TextBox Value="Name" CanGrow="True" Size="3.19791666666667in, 0.200000027815501in" Location="3.23958333333333in, 0.0208333333333333in" Name="nameCaptionTextBox" StyleName="Caption" />|
|<GroupFooterSection Height="0.28125in" Name="labelsGroupFooter">|
|<Style Visible="False" />|
Here is the code snippet to prepare the XmlReportSource and bind that to ReportViewer ReportSource.
|XmlReportSource xmlReportSource = new XmlReportSource();|
|xmlReportSource.Xml = "<Contents of .TRDX file>";|
|reportViewer.ReportSource = xmlReportSource;|
Wait, What about the data source?
Well, if you followed the post so far, you will probably be getting this question for sure. Well remember that when we designed the report using Standalone Report Designer we did set up a data source and a connection string. But that points to our staging database. Meaning the schema of the staging db and the production db are the same but they will be two different environments when it comes to reality. You will never allow somebody to work with a production environment for design. Instead you will allow people to play around in the staging or local setup and finally deploy the code to production. So when we rendered the report using the URI Report Source or XML Report Source – the report does contain the data source object embed within the report and will use that when executing in the production. Remember that the connection string set up during design points to our staging or local setup. So when the report is rendered in the production, report viewer will search for the connection string name given during the design. It may be there or may not be there – whatever it is, this is not ideal scenario to support. So how can we overcome this. In the next section, we will see what is the solution for this.
Report Xml Serializer:
when we looked at XmlReportSource, we understood the .TRDX file is nothing but a XML content. It is nothing but the report definition. The Telerik Reporting solution comes with a ready made XmlSerializer which can read this XML definition and gives us an instance of Telerik.Reporting.Report – an object representation of the Report we designed. The Report class exposes the Data Source we set up and the Connection String on that data source. So the solution to render a .TRDX file in production environment will be as follows:
Read the content of the .TRDX file as XML string
Use ReportXmlSerializer to serialize the XML to Report object
On the report object, get the SqlDataSource and change the connection string of the data source to match your production. You can either provide the complete connection string itself or the name of the connection string element from your configuration file.
Here is the code snippet on how to use the Report Xml Serializer:
|XmlReaderSettings settings = new XmlReaderSettings();|
|settings.IgnoreWhitespace = true;|
|using (XmlReader xmlReader = XmlReader.Create(<.TRDX URI or .TRDX XML Content>, settings))|
|Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =|
|Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);|
|(report.DataSource as SqlDataSource).ConnectionString = "<Connection String OR Connection String Name>";|
|reportViewer.Report = report;|
Standalone Report Designer is a great tool for end users who just create reports and are not technical people. Telerik Reporting Solution has provisions to use the designed report i.e. .TRDX file and render that inside the report viewer. Since it supports both URI Report Source and XML Report Source, you can decide the way you want to store the report i.e. either store it as a physical file in your app in a separate folder or store the content of the .TRDX file in a database column. In both the cases you will be able to use the Report XML Serializer to serialize the XML as a Report instance, update the connection string to match your production and set it to the report viewer.
With Telerik Reporting Solution, you now have the power in your apps to let the end user design adhoc reports, upload it in your app and render the same in a report viewer. Hope this post gave you some insights into Telerik Reporting capabilities.
Till next time – Happy Coding!