We got a request from our customer wanting to use a CSV file as Data Source in Telerik Report Designer to generate the report. They were unable to parse DateTime column to DateTime data type in Telerik Report Designer because the CSV returns the column with double quotes for example (“4/14/2021 12:42:25PM”).
Values can be easily cast to DateTime in Telerik Reporting during the datasource definition. The issue here was the quotes that came in the datasource.
In this blog post, we will explore how we can work with this scenario using Expressions provided by Telerik Report Designer..
First, we need to Add Data Source so click on Data in Menu then select the CSV Data Source and select and then click on Next button as below :
check the checkbox if CSV file has headers then click on NEXT button.
Now we the screen appear for Map columns to type and you can see the StartTime column below with double quotes:
Now we try to change the type of StartTime column string to DateTime. Now need to provide Date format in our case like yyyy-mm-dd hh:ss but in our case it will not work and the column will show blank.
Cause of blank again we need the change DateTime to String and Click on NEXT Button and Finish.
Now to the Data Source is connected with the report. the customer want the report with the parameter of data. So we need to convert StartTime(string) to StartTime(DateTime) using expression.
First we need to remove double quotes from StartTime using Replace function.
//Syntax of Replace function
=Replace(text, old substirng, new substring)
In a below code we take a text from Fields.StartTime and provide old substring that to be remove is double quotes in single quotes. and new substring is blank.
=Replace(Fields.StartTime,'"',"")
We got a StartTime in string without double quotes. so now we need to convert String to DateTime using CDate(value)Funciton. In a CDate function we provide the above Replace function that returning the StartTime without double qoutes.
= CDate(Replace(Fields.StartTime,'"',""))
Now we got the StartTime with the type of DateTime. and the above Expression we can use any where we want like filter the data and making parameter range with StartTime.
One of our customers required their Telerik Reporting application to connect with their Postgres SQL (for effect, no not Microsoft SQL Server but the open source Postgres SQL).
While at the outset it may appear to be almost impossible but this task is very easy to implement with Telerik Reporting.
We accomplish this with using ADO.NET driver for Postgres SQL. The same approach can be used for other databases like MySQL.
In this post, we will discuss how to get Telerik Reporting working on .NET Core 3.1.
The first thing is to understand that Telerik Reporting is a framework that has three distinct and independent pieces:
Telerik Report Definition
Telerik Reporting Host Application
Telerik Report Viewer
First you would want to create the Telerik Report Definition (a trdp file). To create this, we use the Telerik Reporting Designer. By default, there is nothing that supports Postgres SQL in there. So, here is the first step – Download the NgpSql driver (the MSI installer).
Once done, this will add a new datasource to the SQL Data Source of the designer:
Click on the SQL Data Source, and add a new data connection. In the dropdown, please select the new available provider “Ngpsql Data Provider”:
Following this you will need to provide the connection string. The Postgres connection string is of the following format:
Provide the relevant SQL statement and complete creating the data connection. The rest of the remaining steps are the same as creating a regular Telerik Report definition.
Make sure that the data is as preview in the report.
This will make the host application provide the Telerik Reporting Service. You can check if this the reporting has been correctly setup by browsing to the URL: http://localhost/api/reports/formats
The extra step in the host application is to include an additional nuget package in the host application: Ngpsql
The next change in the host application is changing the connection string and its provider. Specify your connection string in the appSettings.json as follows:
Pay special attention to the providerName above.
Congratulations you are done!!
The third and final piece, the Telerik Report Viewer doesn’t require any changes.
Your host application (in my case a simple HTML 5 application) can now simply render the report from the host application.
On Jun 13th we conducted a webinar titled “Reporting Solution for ASP.NET Applications using Telerik Reporting”. This is part of an ongoing webinar series we here at Telerik India are doing targeting APAC region. We call this the Telerik .NET Ninja Developer Webinar Series. You can take a look at the Jun-Jul schedule here: https://telerikhelper.net/2013/05/24/telerik-webcasts-june-july/
Telerik Reporting is a lightweight reporting solution for all .NET cloud, web, and desktop platforms (Azure, Silverlight, WPF, ASP.NET and Windows Forms) which targets developers and end-users alike. Rich interactive and reusable reports can be created by developers in Visual Studio, and by end users in the desktop-based Report Designer. With the help of Telerik Reporting users can examine and probe data, export reports to Microsoft Office Word and Excel for further analysis, and even present them as PowerPoint documents.
Slide Deck:
Here is the slide deck which I used for the webinar:
Webinar Video:
As usual we have this webinar recorded. So if you missed attending the webinar live, you still have a chance to catch up and that too at your own time. Here is the video recording:
Question & Answers:
During the webinar may questions were asked by the audience. We tried our best to answer as many as we can. We will try to address each and every questions from the webinar in this section.
Q: How to save report data in cache?
A: Data is provided to a Telerik Report using the DataSource property. So you can actually make use of the ASP.NET Caching mechanisms and store your report data. Then at runtime read the data from cache and provide it to the report data source.
Q: Does report have any source code?
A: Yes. The Report is a POCO class inhering from Telerik.Reporting base class. So although you design the report using the designer, it is basically a C# class file under the hood.
Q: Is it building the app while we see the preview?
A: When you preview, yes the class library project in which the report is hosted is built.
Q: Difference between Preview and Html Preview?
A: Preview uses Windows Host and it is as if you are hosting the report in a Windows Application. Html Preview uses IE as the host and it is as if you are hosting the report in a Web Application.
Q: How to create report from WCF service?
A: You can always feed the report with a data coming from WCF Service. Telerik Reporting support Business Objects as a valid data source.
Q: Can we export the report using report viewer?
A: Yes, absolutely. Export to PDF, Excel and other formats.
Q: Can we style and individual cells? and copy the style from one to other?
A: Yes each individual cells can be styled. You can copy one style to another in the designer.
Q: Can’t we add this thumbnail Image in during report creation through wizard ?
A: Yes you can. The demo was to showcase our Data Explorer window feature which makes it easy for your to drag and drop data fields on to the designer.
Q: will the Telerik menu come if we install Telerik Reporting in our machine
A: Yes. when you install Telerik Reporting you will see the Menu Item inside your VS.
Q: Can we give parameter at run time?
A: You normally define the report parameters at design time itself and use the report parameters in your queries to filter. You can always set value of a parameter at run time.
Q: Can we create a drop down list instead of textbox to pass a parameter?
A: Yes you can. In fact you do not have to do anything except to say that the report parameter supports MultiValues. You do this by setting the MultiValue=true in the report parameter properties.
Q: If any changes is done to Report Library as in this case today, will it get auto refresh?
A: You will need to redeploy the report library DLL to you Web Application Bin folder and you will have the refreshed report.
Q: Can we schedule the report subscriptions like SSRS?
A: Nope. Telerik Reporting is not a side reporting solution. So out of the box we do not have this feature. But having said that – its easy to build it. The reports are nothing but C# classes under the hood so you can build a scheduling agent which programmatically creates the report, export it to a format of your choice and send it through email.
Q: Can we call this report from c# code without report viewer and get result in the PDF file?
A: Yes you can. The report is basically a C# class file. You can instantiate this at runtime and export to any format of your choice.
Q: Can we edit any grid row/column as in other grid?
A: No. This is reporting so no editing feature.
Q: Can we use any of web control as input parameter for report viewer?
A: You do not need to use any web control. Report Viewer itself is a control as part of our RadControls for ASP.NET AJAX suite.
Q: Do you have loading window when you click on Print Preview?
A: Yes we have. When you click on Print Preview, we will show a “Generating Report” loading animation.
Q: Can we expand/collapse a particular group as a bunch of values?
A: Yes you can. The Expand/Collapse feature is supported.
Q: How can we display the Telerik reports using viewer in Winforms?
A: WinForms suite also contains Report Viewer control. So the same report can be displayed on WinForms too.
Q: Can we create charts – like pie, bar charts? A; Yes you can. Check out our demos to take a look at the charting capabilities.
Q: Can we have dependency of parameters, ex: first parameter is Countries, when country is selected, States should be displayed in second parameter
A: Yes you can. The cascading of parameters is possible.
Q: Can we directly print reports without print preview?
A: Yes. The report viewer has a print report button which will invoke the OS print dialog.
Q: Export functionality is inbuilt or we need to do some coding for it?
A: Export to different formats is in built in Report Viewer control. You don’t have to do anything extra to use the feature.
Q: Can I email the report directly without showing the report to user?
A: You will need to use Reporting API to achieve this functionality.
Q: Will it also be compatible with the recent version s of any report viewer like with excel 2010 or later versions?…I am asking in terms of generating output format
A: Yes the generated reports are compatible.
Q: Can we have docs/examples to implement Telerik reporting – sample projects?
A: Sample projects are installed once you install Telerik Reporting on your machine. More demos can be found at: http://demos.telerik.com/reporting/home.aspx
Q: Can we put a calendar in filters ?
A: When you create a parameter of type Date/Time, the UI for that parameter is automatically rendered as a calendar.
.NET Ninja T-Shirt Giveaway:
we select 2 person from the webinar attendees and give away our .NET Ninja T-Shirt in each of our webinars. we have picked up 2 lucky persons in this webinar too. They are:
Manoj Singh
Sulabh Sarna
Congratulations to the winners. We will be contacting you shortly and we will ship your t-shirts. Rest of the folks don’t worry – we still have loads of webinars lined up for this year. So do attend our future webinars without fail and try your luck to be the .NET Ninja.
Helping Ninja Technologists 