1. How do u implement Cascading parameter?
Function StyleElement (ByVal Element As String) As String
Report can be optimized in terms of Grouping, Filters.
1. The total time to generate a report (RDL) can be divided into 3 elements:
Time to retrieve the data (TimeDataRetrieval).
Time to process the report (TimeProcessing)
Time to render the report (TimeRendering)
Total time = (TimeDataRetrieval) + (TimeProcessing) + (TimeRendering)
These 3 performance components are logged every time for which a deployed report is executed. This information can be found in the table ExecutionLogStorage in the ReportServer database.
SELECT TOP 10 Itempath, parameters,
TimeDataRetrieval + TimeProcessing + TimeRendering as [total time],
TimeDataRetrieval, TimeProcessing, TimeRendering,
ByteCount, [RowCount],Source, AdditionalInfo
ORDER BY Timestart DESC
2. Use the SQL Profiler to see which queries are executed when the report is generated. Sometimes you will see more queries being executed than you expected. Every dataset in the report will be executed. A lot of times new datasets are added during building of reports. Check if all datasets are still being used. For instance, datasets for available parameter values. Remove all datasets which are not used anymore.
3. Sometimes a dataset contains more columns than used in the Tablix\list. Use only required columns in the Dataset.
4. ORDER BY in the dataset differs from the ORDER BY in the Tablix\list. You need to decide where the data will be sorted. It can be done within SQL Server with an ORDER BY clause or in by the Reporting server engine. It is not useful to do it in both. If an index is available use the ORDER BY in your dataset.
5. Use the SQL Profiler to measure the performance of all datasets (Reads, CPU and Duration). Use the SQL Server Management Studio (SSMS) to analyze the execution plan of every dataset.
6. Avoid dataset with result sets with a lot of records like more than 1000 records. A lot of times data is GROUPED in the report without an Drill down option. In that scenario do the group by already in your dataset. This will save a lot of data transfer to the SQL Server and it will save the reporting server engine to group the result set.
7. Rendering of the report can take a while if the result set is very big. Look very critical if such a big result set is necessary. If details are used in only 5 % of the situations, create another report to display the details. This will avoid the retrieval of all details in 95 % of the situations.
Image Source : Embedded
Local report images are embedded in the report and then referenced. When you embed an image, Report Designer MIME-encodes the image and stores it as text in the report definition.
When to Use:
When image is embedded locally within the report.
When you are required to store all images within the report definition.
Image Source : External
When you use an external image in a report, the image source is set to External and the value for the image is the URL to the image.
When to Use:
When images are stored in a File System, External File Share or Web Site.
Image Source : Database
If we add images that are stored in a database to report then such image is known as a data-bound image. Data-bound images can also be displayed from binary data (BLOB) stored in a database.
When to use:
When image is stored in a Database.
When you specify a dataset field that is bound to a database field that contains an image.
1. Right-click the row, column, or corner handle of a tablix data region, and then click Tablix Properties.
2. On the General tab, under Row Headers or Column Headers, select Header should remain visible while scrolling.
3. Click OK.
To keep a static tablix member (row or column) visible while scrolling
1. On the design surface, click the row or column handle of the tablix data region to select it. The Grouping pane displays the row and column groups.
2. On the right side of the Grouping pane, click the down arrow, and then clickAdvanced Mode. The Row Groups pane displays the hierarchical static and dynamic members for the row groups hierarchy and the Column groups pane shows a similar display for the column groups hierarchy.
3. Click the static member (row or column) that you want to remain visible while scrolling. The Properties pane displays the Tablix Member properties.
4. In the Properties pane, set FixedData to True.
20. How to add Page Break
1. On the design surface, right-click the corner handle of the data region and then click Tablix Properties.
21. A main report contain subreport also. Can we export both main report and subreport to Excel?
Step 2: Need to implement TRY CATCH blocks for all the Stored procedures used in the SSRS reports through datasets. The CATCH section of every procedure should have the provision to save the error details into DB table, if any error occurred while execution of that procedure.
Step 3: Add one more additional dataset with the name "ErrorInfo" which should call the store procedure (USP_ERROR_INFO). This procedure should be accepting a unique value. This unique value should be passed to all the data sets for every click of 'View Report' button made by the user. This dataset will return the error information available in the data base table by verifying records with the unique id which has passes as input parameter.
Step 4:Enable the “Use Single Transaction When Processing Queries” option in data source properties, which makes all the query executions through a single transaction.
Step 5: After successful completion of all the above mentioned steps, insert new table on SSRS report with custom error information which will be shown to the report user if the user gets any error during execution of the report.
1. We can deploy the reports using rs.exe tool
2. In the Solution Explorer,
2.1.Right-click the report project, and then click Properties.
2.2.In the Property Pages dialog box for the project, select a configuration to edit from the Configuration list. Common configurations are DebugLocal, Debug, and Release.
2.3.In StartItem, select a report to display in the preview window or in a browser window when the report project is run.
2.4.In the OverwriteDataSources list, select True to overwrite the shared data source on the server each time shared data sources are published, or select False to keep the data source on the server.
2.5.In the TargetDataSourceFolder text box, type the folder on the report server in which to place the published shared data sources. The default value for TargetDataSourceFolder is Data Sources. If you leave this value blank, the data sources will be published to the location specified in TargetReportFolder.
2.6. In the TargetReportFolder text box, type the folder on the report server in which to place the published reports. The default value for TargetReportFolder is the name of the report project.
2.7. In the TargetServerURL text box, type the URL of the target report server. Before you publish a report, you must set this property to a valid report server URL.
3. There are 2 options for deploying the reports that you create with Report Builder 3.0:
1. Report Manager
2. SharePoint document library
The first time a user clicks the link for a report configured to cache, the report execution process is similar to the on-demand process. The intermediate format is cached and stored in ReportServerTempDB Database until the cache expiry time.
3 components: Report Builder, Report Model and Model Designer
Use 'Model Designer' tool to design 'Report Models' and then use 'Report Model' tool to generate reports.
- Windows Winform application for End users to build ad-hoc reports with the help of Report models.
32. Explain the Report Model Steps.
select "Report Model Project" in the Templates list
A report model project contains the definition of the data source (.ds file), the definition of a data source view (.dsv file), and the report model (.smdl file).
2. Define a data source for the report model
3. Define a data source view for the report model
A data source view is a logical data model based on one or more data sources.
SQL Reporting Services generates the report model from the data source view.
4. Define a report model
5. Publish a report model to report server.
RDL files are created for Sql Server Reporting Services and .RDLC files are for Visual Studio Report Viewer Component.
To control the Sort order of data in report, you must set the sort expression on the data region or group. The does not have control over sorting.
You can provide control to the user by adding Interactive Sort buttons to toggle between ascending and descending order for rows in a table or for rows and columns in a matrix. The most common use of interactive sort is to add a sort button to every column header. The user can then choose which column to sort by.
Windows Winform application for End users to build ad-hoc reports with the help of Report models.
A Table Report can have fixed number of columns and dynamic rows.
A Matrix Report has dynamic rows and dynamic columns.
38. When to use Table, Matrix and List
1. Use a Table to display detail data, organize the data in row groups, or both.
2. Use a matrix to display aggregated data summaries, grouped in rows and columns, similar to a PivotTable or crosstab. The number of rows and columns for groups is determined by the number of unique values for each row and column groups.
3. Use a list to create a free-form layout. You are not limited to a grid layout, but can place fields freely inside the list. You can use a list to design a form for displaying many dataset fields or as a container to display multiple data regions side by side for grouped data. For example, you can define a group for a list; add a table, chart, and image; and display values in table and graphic form for each group value
Stores configuration settings for feature areas of the Report Server service: Report Manager, the Report Server Web service, and background processing.
Stores the code access security policies for the server extensions.
Stores the code access security policies for Report Manager.
4. Web.config for the Report Server Web service
Includes only those settings that are required for ASP.NET.
6. Registry settings
7. Web.config for Report Manager
Includes only those settings that are required for ASP.NET
Ad Hoc reporting allows the end users to design and create reports on their own provided the data models.
Adhoc Report is created from existing report model using Report Builder.
1. Authorization is provided through a role-based security model that is specific to Reporting Services.
Different Types of Roles provided by SSRS :
- Content Manager
- My Reports
- Report Builder
2. IIS security controls access to the report server virtual directory and Report Manager.
In the following example, assume that a table is bound to a dataset that includes a field for the product identifier ProductID. A separate dataset called "Product" contains the corresponding product identifier ID and the product name Name.
=Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")
In the above expression, Lookup compares the value of ProductID to ID in each row of the dataset called "Product" and, when a match is found, returns the value of the Name field for that row.
43. Difference between Report Server and Report Manager
Report Server handle authentication, data processing, rendering and delivery operations.
The configuration settings of Report Manager and the Report Server Web service are stored in a single configuration file (rsreportserver.config).
Report Manager is the web-based application included with Reporting Services that handles all aspects of managing reports (deploying datasources and reports, caching a report, subscriptions, snapshot).
44. Steps to repeat Table Headers in SSRS 2008?
1. Select the table
2. At the bottom of the screen, select a dropdown arrow beside column groups. Enable "Advanced Mode" by clicking on it.
3. under Row Groups,select the static row and choose properties / press F4.
4. Set the following attributes for the static row or header row.
Set RepeatOnNewPage= True for repeating headers
Set KeepWithGroup= After
Set FixedData=True for keeping the headers visible.
45. How to add assemblies in SSRS
45. Report Extensions?
46. parent grouping, child grouping in SSRS
47. How to show "No Data Found" Message to end user?
Add a Text box with expression =IIF(Count(
and set the visibility of this Text box as =IIF(Count(
Open the data source dialog in report designer, and select the "Use Single Transaction when processing the queries' check box. Once selected, datasets that use the same data source are no longer executed in parallel. They are also executed as a transaction, i.e. if any of the queries fails to execute, the entire transaction is rolled back.
The order of the dataset execution sequence is determined by the top-down order of the dataset appearance in the RDL file, which also corresponds to the order shown in report designer.
49. ReportServer and ReportServerTempDB Databases
ReportServer: hosts the report catalog and metadata.
For eg: keeps the catalog items in the Catalog table, the data source information in the Data-Source table of ReportServer Database.
ReportServerTempDB: used by RS for caching purposes.
For eg: once the report is executed, the Report Server saves a copy of the report in the ReportServerTempDB database.