Crystal reports interview questions: We have compiled most frequently asked .NET Interview Questions which will help you with different expertise levels.
.NET Interview Questions on Reports
Note: We can not visualize a project with out reports and yes we can not visualize a .NET interview which does not have a reporting question. So here we have a complete Chapter which will cover two basic reporting tools one is crystal and the other is reporting services.
How do we access crystal reports in .NET?
Crystal reports come with the Visual Studio setup itself. Right-click the Solution Explorer -> Add New Item and you can see a Crystal Report template as shown in Figure 16.1. You can add a ‘.rpt’ file using this template.
What are the various components in crystal reports?
There are four major components in crystal reports Report designer, Reports engine, Report viewer, and object models.
Report designer gives a graphical interface to create and modify reports. To view the designer add a new crystal report file and double click on it you should see the report designer as shown in Figure 16.2.
The reports engine does the formatting and conversion part of crystal reports. It helps convert the contents of reports in Word, Excel, PDF (Portable Document Format), HTML (HyperText Markup Language), and other formats. Report viewers are controls that you can see on the Visual Studio toolbox; you can drag and drop those controls on an ASPX page or windows application to view reports made using crystal. Object models help us manage crystal reports objects during design time and runtime.
What basic steps are needed to display a simple report in crystal?
To understand this sample let display a simple report using crystal.
Step1: Create a Web application project.
Step2: Add New Item and select Crystal Report from the template. This adds a new rpt file in your Solution Explorer.
Step3: Double click on the rpt file click on Crystal Reports -> Field Explorer as shown in Figure 16.3. You
Step 4 -> Right click on ‘Database Fields’ on the Field Explorer -> then click on Database Expert -> Expand Create New Connection -> Expand OLE DB ADO (Object Linking and Embedding, Database, Active Data Objects) -> Select Microsoft OLE DB provider for SQL (Structured Query Language) Server (this depends on what kind of data you want to connect) -> Give the Server Credentials a Click Finish and Done.
Step5: Right-click on ‘Database Fields’ on the Field Explorer -> then click on Database Expert -> Expand the Server, database and select table which you want to add to the report. Figure 16.4 shows the right pane showing the table added using Database Expert.
Step 6 -> Expand Database fields -> select Table (in this case it is ‘FactCurrencyRate’ Table). Now you can drag and drop the fields on the report as shown in Figure 16.5.
Step 7 -> We now need to display the report on the ASPX page. For that, we need the ‘CrystalReportViewer’ control (See Figure 16.6). To expand the crystal reports section of the toolbar and drag the component on the ASPX page.
Step 8: Now we need to go to code behind and specify the report source. That’s it now compile and run the project you can see your report live in action (See Figure 16.7).
Can crystal reports be published as a Web service?
Right-click on the ‘rpt’ file and click ‘Publish as Web service’ as shown in Figure 16.8.
How do we invoke the crystal report Web service?
We can consume the Web service as a normal Web service in . NET. The easiest way is by using the ‘ReportViewerControl’ and specifying the ASMX URL (Uniform Resource Locator) in the report source property.
How do we add formulas using crystal reports?
To add any formula in the crystal report is a three-step procedure. Figure 16.9 shows the three steps in a pictorial format.
Step 1 -> Go to Field Explorer and right-click and click the ‘Formula Fields’. Click ‘New…” option.
Step 2 -> The ‘Formula Name’ dialog box appears. Give and name to the formula in the ‘Name’ textbox and click on ‘Use Editor’.
Step 3 -> You will be presented with Ul which has all the formulas and functions.
How do we pass parameters to crystal reports?
Sometimes we want to accept input parameters and the report works according to the parameter. To add an input parameter go to Field Explorer, go to Parameter Fields, right-click Create Parameter and you should be popped with a ‘Create Parameter Field’ dialog box as shown in Figure 16.9. Give a name to the parameter value, type and that’s it you are in action.
How do we export from crystal reports?
There is two way of using the Export Options: one is when we display a report using Crystal Report Viewer you can see an export icon as shown in Figure 16.11 below. You can select the options given in the ‘Format:’ drop-down list in which format you want to export.
The second option is through coding. Below is a sample code snippet that shows how we can export a report. Create an object of the crystal report and call the ‘ExportToDisk’ method specifying in which format you want to export.
Dim Report as New CrystalReportlReport. ExportToDisk (CrystalDecisions.Shared.ExportFormatType. WordForWindows, "c: \my.doc")
How do we print to the printer using crystal?
In print we have two ways by which we can print one is when you display the report using Crystal Report Viewer you have a Print option and the second is by code. Below is a sample code snippet that shows how we have created an object called a report from the rpt file, specified the printer name, paper size, and then called the ‘ PrintToPrinter’ method.
Report.PrintOptions.PrinterName = "MyPrinter" Report.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperA4 Report.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape Report.PrintToPrinter(1, True, 1, 3)
How do we generate cross-tab reports?
When we go for creating a new report you can see the cross-tab option in the ‘Choose, an Expert Section.
How can we do grouping in crystal?
For grouping in crystal, you need to use the Group Expert Wizard.
Can you explain three-pass reporting which crystal report uses?
Crystal report uses the three-pass method for parsing reports. Before we understand what does it means let’s define what is a pass? Pass is a process used by crystal reports to read and manipulate data as per the report format. Figure 16.13 shows how the parsing happens. Let’s understand the same step-by-step. In Pre-Pass1 crystal report engine evaluates constants like x = 1 and pie = 3.14 for a report. Pass 1 does two important things get data from the database and sort records with given conditions. Once this is done it’s saved in memory and given to pre-pass2 for further parsing and manipulation. Pre-pass2 is all about grouping and sorting the records according to conditions specified in the crystal report. Pass 2 formats a report, applies conditions,s and groups them. Pass 3 is the final tunnel it just counts the pages and generates reports.
Can you explain reporting services architecture?
Reporting services are mainly used to generate reports. Below are the main components of reporting services as shown in Figure 16.14. Reporting services have four major components Client, Web services, report server/manager, and reporting services database. Let’s understand all the major components and the sub-components of the same.
Client: These are the consumers of reporting services functionality. It can be the report manager or report server (we will discuss report server and manager in more detail further) or ASPX and Windows application.
Reporting Web service: Microsoft chose XML to expose the functionality of reporting services. So all the functionality is exposed through Web services. One of the most important gains for a Web service is that it can be platform-independent.
Report server/manager: Report server and manager form the core engine of reporting services. There are two important systems one is a ‘Report processor’ and the other is a ‘Scheduling and Delivery Processor’. Reporting processor is the main driver to deliver reports. They take request from the end-user and process the report and send it to the end client. Figure 16.15 shows how the flow moves. We have seven basic steps which can help us understand in more detail how the report processor works.
Step 1 -> Any client like ASPX or Windows application will request to the Web service for reports.
Step 2 and 3 -> Web service will forward the request to the report processor and get the report definition from the report server DB.
Step 4 -> Reporting services uses the security extensions to authenticate the end-user.
Step 5 -> Data processing extension calls queries the application database to get data. With data processing extension we can connect to standard data sources like SQL Server, ODBC (Open Database Connectivity), Oracle, etc. You can also extend the data processing extension to adapt it to some custom data processing extensions.
Step 6 -> Rendering extension then renders the report applies format and sends the same to the end client. Using rendering extension you can deliver reports in Excel, PDF, HTML, CSV (Comma Separated Values), and XML. You can also extend the rendering extension to deliver in custom formats.
The second system is ‘Scheduling and Delivery Processor. Delivery extensions make reports with the specified format and send the same to an output target like file, e-mail, FTP (File Transfer Protocol), etc. Scheduling and delivery processor does two important steps schedules reports and delivers them the same to an output like FTP,
Email etc. Schedule and delivery processor using the delivery extension to deliver reports to the defined output. So users can subscribe to the reports and depending on these subscriptions the delivery is done by schedule and delivery processor. There are two steps in this one is the subscription process and the other is the delivery process. Let’s first try to understand the subscription process. There are three steps basically as illustrated in Figure 16.16.
Step 1 -> First is the request for a subscription to the report.
Step 2 -> This subscription is then stored in the report server DB.
Step 3 -> A new SQL Server agent is created for this subscription. So we have completed the subscription process.
Now that the subscription is created let’s see how the delivery process works. Here are five basic steps in the delivery process as shown in Figure 16.17.
Step 1 -> When the event occurs SQL Server agent makes an entry in the report server DB.
Steps 2 and 3 -> RS windows service keeps polling SQL Server for any events. If an event has occurred it gets the event from the database and sends a message to the ‘Scheduling and Delivery Processor.
Step 4 -> The report is then processed by the report processor and sent to the delivery extension.
Step 5 -> who finally delivers it on an output which can be an e-mail address, folder, FTP, or any other kind of output.
Reporting Services Database: When you install reporting services you will see two databases ‘ReportServer’ and ‘ReportServerTempDB’ as shown in Figure 16.18. ‘ReportServer’ database contains report definitions, schedules, subscriptions, security details, and snapshots. ‘ReportServerTempDB’ store’s temporary information like session state about reports which is needed in-between HTTP requests and report cache information. Please note snapshots are stored in the report server and not in tempDB.
We have two IIS applications ‘Reports’ and ‘Reportserver’ what do they do?
When you install reporting services there are two virtual directories created as shown in Figure 16.19.
The ‘Reports’ virtual directory is like an admin. If you browse to HTTP: //your name/reports/home.aspx page you can view, edit properties, and display reports. We have Figure 16.20.
1 ->We can browse reports and see the output of reports.
2 -> We can upload an RDL (Report Definition Language) 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 assignments.
5 -> Using the 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 the execution, report execution logging, scheduling, manage jobs, and security settings.
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: //vourpcname/report server/ you can easily figure out what this virtual directory is all about.
Can you explain Report Definition Language (RDL) file in reporting services?
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. Figure 16.21 shows a simple snippet of an 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 folders ‘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, you should see an RDL file in your ‘Reports’ folder.
- Click on the View menu and select toolbox 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 to get saved in the 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.
- Once we have specified the ‘ TargetserverURL’ property we need to deploy the project. So right-click on the project and click ‘Deploy’.
- You can now view your report using HTTP: //localhost/Reports/Pages/Folder.aspx URL.
How can we consume reports in ASP.NET?
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,
This is the most basic way of accessing reports. We specify the URL as shown below which then displays the report on the browser. Figure 16.24 shows a basic format of how to display a report using the URL methodology.
1 -> This is the IIS (Internet Information Services) 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.
If we want to get the report in PDF format we need to use the ‘Format’ parameter as shown In Figure 16.25.
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 as shown in Figure
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 (Internet Protocol) 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 vourASP.NET project.
Step 2 -> We need to consume the Web service and use it. Figure 16.27 shows how the reporting service object is used.
1 -> You can see the Web service references to ‘ReportServicei005.asmx’.
2 -> We have named the Web service ‘Localhost’, so you can see we have imported the Web service.
3 -> We need to create an object of the ‘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.
The above example as shown in Figure 16.27 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 the ‘GetReportParameters’ method, if we want to display the report we use the ‘Render’ function. There are many other functions 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. Figure 16.28 shows how the ‘ReportViewer’ ^ Control looks like and how we can set the properties to view the report.
Can you explain the difference between private and shared data sources?
The private data source is used by a single report only while shared data sources are shared between reports. As a practice, it is good to have a shared data source so if you want to change anything you need to change only on one data source.
How do reports caching in reporting services work?
As said previously reporting services has two main databases ‘ReportServer’ and ‘ReportServerTempDB’. Figure 16.29 shows how caching works in reporting services. The client first sends a request to the reporting service for the report. Reporting processor gets data from the database and the report format from the ‘report server database. Both these elements are then used to create an ‘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 the report for every parameter combination. Cached versions expire after a particular schedule when reports are modified, deleted, or redeployed.
IN order to set the caching properties browse to a particular report using HTTP://localhost/reports/aclick on properties→ and go to the execution tab. Figure 16.30 ‘caching options’ shows different conditions on which you can define caching strategy.
What are the major differences between Crystal and SQL reporting services?
Ease of hosting reports: Using the URL technology in RS (Reporting Services) we can host reports more easily than in crystal report where we need to make a Ul for the same.
Supporting platforms: Crystal can run on Windows, IBM (International Business Machines), and sun while RS (Reporting Services) can run only on Windows environments.
Client tools: In reporting services we have Business Intelligence (Bl) and development studio while in crystal its Report designer.
Caching: This is achieved in crystal by using cache server while in reporting services it’s stored as snapshots in the Report server database.
Export formats: in crystal, we have HTML, PDF, Excel, XML, Word, PDF, RTF (Rich Text Format), CSV, text files while in SQL Server 2005 we have all the formats above it also gives the 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 (Active Data Objects), COM, Database excels Access, Exchange, NT, Xbase, JDBC (Java Database Connectivity), File system, and Paradox. RS supports only SQL Server, Oracle, ODBC (Open Database Connectivity), OLEDB and you can also extend additional data sources which do not exist in crystal reports.
Version issues: One of the main issues faced in the crystal is it has different versions which make 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.
Note: We have also shipped a PDF ‘SQLvsCrystaL WP.pdf’ which has been published by HTTP: // www.aiobaiknowiedae.com/. This PDF has very precise and detailed differences between crystal reports and SQL reporting services.