Title: Crystal and Reporting Services FAQ Part 2
Author: Shivprasad Koirala
Email: shiv_koirala@yahoo.com
Language: ASP.NET
Level: Beginner
Description: .Net Interview Questions 4th Edition
Crystal and Reporting Services FAQ Part 2
Introduction
We have two IIS application ‘Reports’ and ‘Reportserver’ what do they do ?
Can you explain Report definition language (RDL) file in reporting services?
How can we consume reports in ASP.NET?
Can you explain the difference between private and shared data sources?
How does reports caching in reporting services work ?
How does reports caching in reporting services work ?
What are the major differences between Crystal and SQL reporting services?
This FAQ will give you a quick start for two report giant’s crystal and reporting services.
To view Crystal and Reporting Services FAQ Part 1 Click the below link
CrystalReporting.aspx
When you install reporting services there are two virtual directories created as shown in the figure below.
Figure 19 : - IIS Applications for reports
The ‘Reports’ virtual directory is like an admin. If you browse to http://yourpcname/reports/home.aspx page you can view, edit properties and display reports. We have numbered the below figure. 1 We can browse reports and see the output of reports. 2 We can upload a RDL file directly in the report’s database. 3 Using the report builder link we build new reports. 4 Using the properties we can do role assignment. 5 Using subscription link we can add new outputs (FTP, Folder etc) to subscribe to the reports. 6 Site settings help us to decide how many numbers of snapshots we want in the history, report time out execution, report execution logging, scheduling, manage jobs and security settings.
Figure 20 :- Reports Admin
The second virtual directory is the ‘ReportServer’ this helps us to use browse reports and use the same in ASP.NET code using URL methodology , try browsing to http://yourpcname/reportserver/ you can easily figure out what this virtual directory is all about.
RDL is an XML description of a report in reporting services. So basically the RDL file has the XML grammar which describes a reporting services report. Below figure ‘Snip of RDL file’ shows a simple snippet of a RDL file.
Figure 21 : - Snip of RDL file
What is the basic process of making a report in reporting services?
Here are the basic steps which will help you in creating reports in reporting services:-
• Open Visual studio 2005 File New project Select ‘Business Intelligence Reports’ Select Report Server project.
• When the project is created in the solution explorer you will see two folder ‘Shared data resources’ ( we will come to data sources in the coming questions) and ‘Reports’. Right click the ‘Reports’ folder and click add new reports.
• You need to give credentials in the wizard. Once done it pops up a query builder. Specify the report query in the query builder and go next.
• Wizard then helps you to select the report type and some other features. Next , next and you should see a RDL file in your ‘Reports’ folder.
• Click on the view menu and select tool box and you should be able to design the report.
• Till now we are only in the design mode of report. As said previously all reports meta-data are stored in SQL Server database ‘ReportServer’. In order that the report’s meta-data gets saved in database we need to publish the report.
• To publish the report we need to specify the IIS report path. So right click on the report project, select properties and specify the ‘TargetServerURL’ property.
Figure 22:- TargetServerURL property
• Once we have specified the ‘TargetServerURL’ property we need to deploy the project. So right click on project and click ‘Deploy’
Figure 23: - Deploying reports
• You can now view your report using http://localhost/Reports/Pages/Folder.aspx URL.
There are three famous ways of calling reporting services report in ASP.NET:-
• Using URL way to access reports.
• Using reporting web service to programmatically access reports.
• Using report viewer control.
URL way
This is the most basic way of accessing reports. We specify the URL as shown below which then displays the report on the browser. Below is a basic format of how to display a report using the URL methodology. 1 This is the IIS report application. 2 This is the project name or the project folder in which the report resides. 3 This is the report name. 4 This is the command to the reporting service of what to do. In this case we are saying render the report.
Figure 24: - Basic URL
If we want to get the report in PDF format we need to use the ‘Format’ parameter.
Figure 25: - Generate the report in PDF format
If we want to pass parameters to the report we can do something as shown in the figure ‘Pass parameters to the report’. In this scenario we have passed the ‘Category’ parameter.
Figure 26: - Pass parameters to the report
Consuming reporting services web service
If you look at the architecture of reporting service all reports are exposed through XML i.e. Web services.You can see the web service using this URL http://localhost/ReportServer/ReportService2005.asmx. In localhost you need to put the server name or IP address. So below are the steps to display reports using web service.
Step 1 First add the web service using http://localhost/ReportServer/ReportService2005.asmx in your ASP.NET project.
Step 2 We need to consume the web service and use it. Below is the code snippet which shows how the reporting service object is used. 1 You can see the web service references to ‘reportservice2005.asmx’. 2 We have the named the web service as ‘Localhost’, so you can see we have imported the web service. 3 We need to create object of ‘ReportingService2005’ class. 4 We need to set the credentials of the reporting object. 5 In this we will display which reports are present in the reporting server database. So we use the ‘ListChildren’ method to get the reports from the web service and then loop to display the same. 6 This sample code should display the reports present in the reporting service.
Figure 27: - Display reports in reporting server
The above example is a sample of how to use the reporting web service. If you want to get how many input parameters the report has we need to use ‘GetReportParameters’ method, if we want to display the report we use the ‘Render’ function. There are many other function and methods which we have not discussed to keep the chapter simple and to the point. Please experiment around the reporting web service object for new methods and functions.
Reportviewer control
ASP.NET 2.0 comes with crystal report viewer control. You can drag and drop the control on the ASPX page and set the properties. Below figure shows how the ‘ReportviewerControl’ looks like and how we can set the properties to view the report.
Figure 28:- ReportViewerControl in action
Private data source is used by single report only while shared data sources are shared between reports. As a practice it is good to have shared data source so if you want to change anything you need to change only on one data source.
As said previously reporting services has two main databases ‘ReportServer’ and ‘ReportServerTempDB’. Below figure ‘Reporting Services Caching’ shows how caching works in reporting services. Client first sends a request to the reporting service for report. Reporting processor gets data from the database and the report format from the ‘reportserver’ database. Both these elements are then used to create ‘Intermediate report format’. This intermediate format is saved in ‘ReportServerTempDB’. This intermediate format is for a particular user and for a session. So if the user generates the same report he will get a cached version. If the report is having parameters then there are different cached versions of report for every parameter combination. Cached versions expire after particular schedule, when reports are modified, deleted or redeployed.
Figure 29: - Reporting services Caching
In order to set the caching properties browse to a particular report using http://localhost/reports/ click on properties and go to execution tab. Below figure ‘Caching options’ shows different conditions on which you can define caching strategy.
Figure 30:- Caching strategy
Ease of hosting reports: - Using the URL technology in RS we can host reports more easily than in crystal report where we need to make a UI for the same.
Supporting platforms: - Crystal can run on windows, IBM and sun while RS (reporting services) can run only on windows environment.
Client tools: - In reporting services we have Business intelligence ( BI ) and development studio while in crystal its Report designer.
Caching: - This achieved in crystal by using cache server while in reporting services it’s stored as snapshots in Reportserver database.
Export formats:- In crystal we have HTML,PDF,Excel,XML,Word , PDF , RTF , CSV, text files while in SQL Server 2005 we have all the formats above it also gives capability to extend custom reporting formats.
Data sources:- Crystal support more data sources while RS only supports Microsoft and oracle data sources. Crystal supports ADO, COM, Database excel Access, Exchange, NT, Xbase, JDBC, File system and Paradox. RS supports only SQL Server, Oracle, ODBC, OLEDB and you can also extend additional data sources which does not exists in crystal reports.
Version issues: - One of the main issues faced in crystal is it have different versions which makes it difficult to use, while RS comes with SQL Server minimizing the version issue.
Web server support: - Crystal can run on IIS 5/6, Apache, lotus etc while RS works only with IIS 5.0 and above.
For Further reading do watch the below interview preparation videos and step by step video series.
