Tips
Building a Dashboard in SQL Server Reporting Services
Your data warehouse is rock solid. You have multiple sets of fantastic reports. Departments utilize their own designated reporting areas, and you've increased productivity and value (ROI on the BI program) by focusing operations on critical areas by offering specialized filters and sorting as well as exception reports.
Great!
But top level executive management still doesn't have a clear picture of what direction the company is going in and can't make the strategic decisions needed to plan into the future. Your voluminous sets of reports look to them like the local telephone directory. They just don't have the time to wade through every report in all the (wonderful) departmental reporting solutions you have created.
Building Calculated Members for a SSRS Report using MDX
I have a requirement to develop a report that uses a SQL Server Analysis Services Cube as a data source. The report must allow the user to select the month from a dropdown list in a report parameter. Based on the selected month the report should show sales for the selected month, sales year to date for the selected month, last year's sales for the selected month, and last year's sales year to date for the selected month. In this tip we walk through how to do this step by step.
Centralize and Control Data Access in SSRS 2008 R2
Reporting is a common and frequent requirement of any business practice and often reports are developed by business users as well as the IT department of the organization. Data Source and Data Sets are two major entities used in any report development using SQL Server Reporting Services (SSRS) whether the report is developed using Business Intelligence Development Studio (BIDS) or report builder. To keep a track and control of the data access logic implemented on a report level, it becomes very necessary to centralize access to data from the report server for better maintenance, reducing repetitive efforts and efficient use of resources. In this tip we would take a look at how we can address this using SSRS 2008 R2 and BIDS.
Centralized vs Decentralized Datasources in Reporting Services
The Reporting Services datasource is a programming object that holds the configuration information on how to access data for reporting. This includes the server name, database name, the type of data source (i.e. SQL Server, Oracle, text file, etc.) and the credentials of the user accessing the data. Reporting Services offers many ways to use datasources. They can be localized (embedded) within a report or they can be "shared" (usable by more than one report). When shared, they can be decentralized within a single project (and report folder) or they can be utilized centrally amongst multiple projects and folders. What is the best method to use?
Conditional report rendering based on render formats for SSRS Reports
Reporting Services is a commonly used functions in an enterprise and Report Manager is one of the most convenient ways to make reports accessible to the users. When using Report Manager, users can export reports to different formats like PDF, XLS and others. When reports are exported to other formats they also include all of the content from the report, but rendered to the format of the report output. There maybe scenarios where you would not want a particular part or component of report to appear in these other report formats. A practical example of this could be that you want to display images when the report is displayed in HTML, but not when the report is exported to a PDF format. In this tip we take a look at a new feature in Reporti
Controlling dataset processing time in SQL Server Reporting Services reports
Long running queries in complex reports is one key performance optimization area. One way to keep your users from waiting for a long period of time to view a report, due to corrupt queries or poorly designed queries, is to configure your reports with an acceptable time limit to wait for the database engine to return data. In this tip, we will look at how to implement this for SSRS reports.
Create Centralized Report Headers Using Subreports in SSRS
In the SQL Server 2005 documentation, Microsoft suggests that [if] you have used subreports in the past to display separate groups of data, consider using data regions (tables, matrices, lists, or charts) instead. But does that mean if you are using SQL Server 2005, you should avoid using subreports altogether?
Do you find yourself copying and pasting code from report to report in order to standardize a look and feel? Do you wish there was a better way to centralize your reporting designs and re-use snippets of code in creative ways?
Creating a Reporting Service Report Template
You have several report developers working on a large scale SSRS 2008R2 project and they are generating report after report. You would like all the reports to have the same "look and feel", at least on the header and footer sections, and even include some base queries for items commonly included as parameters for each report. In this tip we create such a template and make it appear in the New Project List.
Creating a Reporting Services Histogram Chart for Statistical Distribution Analysis
Typically transactional data is quite detailed and analyzing an entire dataset on a graph is not feasible. Generally such data is analyzed using some form of aggregation or frequency distribution. One of the specialized charts generally used in Reporting Services for statistical distribution is Histogram Charts. In this tip we look at how Histogram Charts can be used for statistical distribution analysis and how to create and configure this type of chart in SSRS.
Creating a SSRS report using a mySQL data source
SQL Server Reporting Services is a great tool to create reports from a SQL Server database, but you can also use SSRS to create reports from a mySQL database. In this example I will show you how to create a report using SQL Server Reporting Services from a mySQL table.
Creating an SSRS Map report with data pinpoints
SQL Server Reporting Services has some cool features such as displaying maps and data points on the maps. In this tip I show how to take a list of addresses and display them as pinpoints on a map in an SSRS report.
Creating Dynamic Report Filters in SQL Server Reporting Services SSRS
I have a requirement where I need to provide two report parameters for a report. The first parameter will present a list of columns that the user can choose to filter the report. The second parameter will contain a list of available values based on what was chosen for the first parameter. As an example my first report parameter provides the options Gender and Marital Status. Based on the user selection I need to populate my second report parameter's available list with Male and Female or Married and Single. In this tip we walk through the steps on how to set this up.
Creating Trellis Charts to Make SSRS Reports More Readable
Factual data is generally analyzed from a lot of different dimensions. Because of this Grid based reporting is not an ideal reporting format to support analytical needs. Visual data representation like charts, graphs, and gauges are one of the best ways to report data that require comparison analysis. The challenge with this form of data representation is that every visualization has a modest limit to contain data points, after which the visual representation becomes uninterruptable to the human eye. Therefore the need is to have a scalable information visualization design that can report quantitative multi-dimensional data while still preserving its analytical value.
Cumulative Aggregates in SQL Server Reporting Services
Typically when working in Sales and Marketing Departments, every individual or group of individuals are given a Target for each year. All of these targets rolls up to the company's targets. Often companies will have one or more standard scorecards / dashboards to monitor the overall health of the organization based on a set of pre-defined KPIs /metrics. However, the individuals at the bottom or middle of the hierarchy need a way to review the detailed numbers for the current month, current quarter or current year to see how they are approaching the target. In this tip, we will see how to build a report which shows the cumulative sales amount for the current month, current quarter (QTD) and current year (YTD) in a single crosstab (tablix) re
Custom control and setup of SSRS report parameters from a web page
I have a ASP.NET web application that I would like to present SQL Server Reporting Services reports in. I understand that reports can be rendered using the ReportViewer control, but the interface for parameters is a little bland and doesn't match our site colors. Is there a way that report parameters can be passed to the Report Services instance without having to use the Reporting Services parameter prompts?
Customize Dropdown List in SQL Server Reporting Services Report using a SSAS Cube Data Source
I have a requirement to develop a report that uses a SQL Server Analysis Services Cube as a data source. The report must allow the user to select the desired month from a dropdown list. The dropdown list must include the last 24 months of available data with the default value being the current month. The administrator must have the ability to specify the current month and change it as necessary. I can create the report, but I am having some difficulty with implementing the report month parameter. In this tip we show you how this can be done.
Data Driven Colored Text for Reporting Services Reports
How can I color code certain entries in a SSRS report? In SSRS you can use data driven expressions to color code certain rows, so check out this tip to see how to accomplish this.
Dataset and tablix filtering in SQL Server Reporting Services
I'm creating a SQL Server Reporting Services (SSRS) report based on datasets that I have no control over and they have more data in the result set than what is required for the report. Is there a way that I can filter the datasets inside the SSRS report itself? Check out this tip to learn more.
Decouple context specific logic from SSRS reports using SQL Server facades
Reports designed using SQL Server Reporting Services, have parameters and report data as the two major entities which generally fetches data from database. This is generally facilitated by datasets that fetch data through the medium of a stored procedure. This makes the database logic arguably tightly coupled with the report design and it can be quite problematic when there is a change in the name or location of database objects connected to the report or when the same report needs to be used with context specific logic. In this tip, we look at a design that would help to decouple the regular implementation approach of a report design. Be advised that this article assumes that the reader has some basic working knowledge of SQL Server Repor
Developing SQL Server Reporting Services Reports in Teams
You know the old expression, "Be careful what you wish for, because it might come true!" That goes the minute your Reporting Services team grows from one person to any size larger. How do you manage the code? What if you are working on a multi-developer team project, all on the same reporting solution? If you are in a relatively small reporting team, perhaps it is not worth the overhead of a software-based version control system. So what do you do?
Display Graphics Dynamically in SQL Server Reporting Services
Sometimes having just numbers and tables on a report is not enough. It would be much easier to spot trouble issues on reports based on color coding or specific images instead of just the boring data. In this tip we will explore how to display specific graphics based on specific data content. Instead of just showing the data now you can have images highlight certain data elements too. Take a look at this tip to find out how.
Display Reporting Services drill through reports in new browser
When using the standard Reporting Services drill-through navigation the new report is rendered in the current browser window, therefore overlaying the original report. There is no built in way to indicate you want the drill-through report to open a new window. In a standard web browser you can use the (shift-click) functionality to open the link in a new window, but in Reporting Services this functionality is disabled. So how can you launch a new window when you open a drill-through report?
Displaying Multiple Series on an SSRS Chart using Chart Area
A report might contain multiple data series on a chart, which can have considerably varying scales but common category groups. In such cases where this a big difference in scales, the data series with the lower scale can become obscured. In this tip we will take a look at how to solve this problem using Chart Areas.
Distribution Analysis using a SQL Server Reporting Services Box Plot Chart
Distribution analysis is generally performed on quantitative data, by individually plotting data points on a graph. After plotting these points, visual clusters can be identified, which also helps in outliers analysis. This methodology is generally useful when data points are plotted based on just two parameters on a X and Y axis using a scatter plot graph as an example. To carry out distribution analysis on aggregated data or data points having multiple parameters, box plot charts can be used. In this tip, we will take a look at how to box plot charts in SQL Server Reporting Services.
Drill Down Techniques in SSRS with the Visibility Property
Reporting Services offers many standard solutions out-of-the-box. However, controlling how data is displayed can be frustrating without understanding some of the subtleties of the Reporting Services report designer. Users can get confused and turned off, if the presentation of data is less than perfect. You can create a fairly nice drilldown using the Report Wizard, but how about conditionally opening some groupings and not others? What about starting a drill down report with all the groups expanded? How do you change the behavior of the +/- signs (which is not always intuitive)?
Dynamically Control Data Filtering in SQL Server Reporting Services Reports
I have a number of reports that anyone in the organization can view. These reports are coming from a SQL Server 2005 database. However, there are limitations on the data that a person can access. A person may be able to see all data, data from just their office, or data from a specific list of offices. How can I go about implementing this security scheme in SQL Server Reporting Services (SSRS)?
Enable SSIS as data source type on SQL Server Reporting Services
SSIS packages can be used as a data-source for SSRS and this was well supported in SSRS 2005. In the SQL 2008 R2 release (Nov CTP), this data source extension is not enabled and supported. So when you create a data source in SSRS 2008 R2 (Nov CTP), you won't be able to get SSIS listed as a data source type. Therefore applications that are already using it as a data source or applications that require it as a data source get stuck. Let's learn how to enable and get SSIS listed back as a data source in SSRS 2008 R2.
Getting Started with Data Bars in SQL Server 2008 R2 Reporting Services
I'm looking at several new visualization features in SQL Server 2008 R2 Reporting Services and the data bar looks like something that I could really use. In this tip we cover how this can be used in your Reporting Services reports.
Getting started with report filters in SQL Server Reporting Services SSRS
I want to use user-definable parameters to provide filtering in my reports for SSRS, but I'm not sure how to proceed. I'm basically looking for the case where the user is able to set the value and then execute the report. For instance, I'm looking for the case where the user is able to set the start and end date of a period to report on. In this tip we walk through the steps to implement basic report filtering in SSRS.
Getting Started with Reporting Services
SQL Server has a lot of great features and taking the time to find out what these features are and secondly taking the time to understand these new features is often time consuming and usually only done if there is a real pressing problem. I still run into these challenges and it amazes me that there is always some way to solve the problem with SQL Server. I think one of the greatest new add-ons to SQL Server is Reporting Services, but it still seems like a lot of people have not yet adopted Reporting Services. Microsoft even included Reporting Services features with SQL Server 2005 Express, so this is definitely something you should take the time to investigate. The big question though is how to get started.
Getting Started with Sparklines in SQL Server 2008 R2 Reporting Services
I'm looking at several new visualization features in SQL Server 2008 R2 Reporting Services and the sparkline looks like something that I could really use. In this tip, we look at how to use the sparkline in Reporting Services reports.
Handling truncated leading spaces in Reporting Services
When attempting to display hierarchical data in Reporting Services by using additional spaces, the web browser truncates the additional leading spaces and all data becomes left justified with all of the indentation removed. This is because, like or not, the web browser's job is to compress multiple sequence space characters into one. This may be desired in normal HTML coding, but not so with reporting data. So how can you get Reporting Services to not remove the leading spaces, so you can display your hierarchal tree?
Hiding parameters and using default parameter values in SSRS reports
Many times in a parameterized report, we may come across situations where we need to execute a report using a fixed parameter value depending upon the users group. In such cases, displaying a list of parameters to the user would not make sense. Also a parameterized report can be used as a model to generate different flavors of the same report, varying on the parameter values. In such a case, hiding the parameter value would be the primary design requirement. In this tip we will take at look at how to implement this solution.
Highlighting repeating groups in SSRS 2008 R2 charts
In a typical bar chart, the height of the bars represents the value of the data along the X axis and the Y axis represents the category of the data point. All these values can be classified in logical groups depending upon the logic used for analysis. One general requirement that arises in the case of repeating groups, is to highlight these groups without representing it on the X or Y scale. In this tip we will look at how to address this requirement.
Highly Available, Scale-Out SQL Server 2008 R2 Reporting Services Farm
Delivering reports is becoming more critical due to the increasing demand for business intelligence solutions. And while there are a lot of guides that walk us through building a highly available database engine, you’ll rarely see one for SQL Server Reporting Services. How do I go about building a scale-out SQL Server 2008 R2 Reporting Services running on Windows Server 2008 R2? Check out this final tip in the series.
How to handle empty values in a line chart in SSRS
When continuous data is displayed on a chart, such as a line chart, data is displayed very smoothly. But when non-continuous data is displayed on such a chart, the chart behavior is different. The continuous data is displayed correctly, but the non-continuous data is ignored on the chart. In such cases, handling of empty points in the dataset is required to make the data continuous and displayed correctly on the chart. In this tip we will look at how to implement a solution for this problem.
How to use SSIS package as a data source for SQL Server Reporting Services 2008 R2
There are two constraints with using SQL Server Integration Services 2008 R2 (SSIS hereafter) packages as a data source with SQL Server Reporting Services 2008 R2 (SSRS 2008 R2 hereafter). Firstly, SSIS is not enabled as a data source type itself on any regular SSRS 2008 R2 installation and secondly specific configurations needs to be made so that SSRS reports can use data from an SSIS package as the data source which in turn would be executed by SSIS. In this tip we will discuss an example of how to deal with this topic. Please keep in view that this tip assumes some basic level of SSIS and SSRS working knowledge from the reader.
Maps in SQL Server 2008 R2 Reporting Services
I noticed a new feature in SQL Server 2008 R2 Reporting Services that allows you to render maps in your reports. Can you provide some details on this new feature and can I take advantage of it even though don't have any spatial columns in my data warehouse? In this tip we walk through the process step by step.
Optimize Report Parameter Dropdowns in SQL Server Reporting Services
We have many SQL Server Reporting Services (SSRS) 2005 reports that use our OLTP systems as data sources. These are legacy OLTP systems purchased from a variety of vendors. We do not have the luxury of reporting from a data warehouse. Reports typically have many parameters allowing the users to choose the values they want from dropdown lists. Many of the same parameters are used on every report. The users are complaining about the time it takes just to select the parameters and get a report running. Do you have any ideas on how we can get the parameter lists populated faster? In this tip I will cover a few techniques to handle this.
Pass parameters and options with a URL in SQL Reporting Services
Reporting Services has many great built in features, but accessing them externally can be tricky. Suppose you wanted to pass a variable directly within a URL? It would be great if you could provide a URL from a website or email that renders a report directly in Excel? Do you need to display a report (perhaps a chart) while hiding the reporting toolbar? This tip looks at these items as well as a few others and how you can use parameters in your URL to change the report behavior.
Report Models in Reporting Services 2005
Users have all but fallen in love with Reporting Services. First, for its presence anywhere an Internet browser can be opened and second for the opportunities to export data to numerous formats. But users also like their toys and want to manipulate the data many ways.
So does it really do any good to publish reports on the server when they're going to take it and "do their own thing" to it? The answer is it depends on who's accessing the reports. Having a predefined number of reports available can help you keep the hardware resources from being overtaxed, but there are individuals in the organization that need the ability to create additional reports. This ability is provided through Report Models.
Reporting Services condition based drill through reports
One great feature of Reporting Services is the ability to drill through to secondary reports. This is pretty straightforward to do, but the built-in option in Reporting Services is an all or nothing approach. Sometimes there is a need to drill through to secondary reports based on certain data conditions, but you do not want a link for every row only certain ones. How can you provide a link for some data records but not others?
Script to determine SQL Server Reporting Services parameters, path and default values
Our company has hundreds of reports with daily, weekly, monthly, quarterly, yearly and ad-hoc schedules. Is there a way to identify all the reports with their path, parameters and default values as well as if the parameters are hidden or visible when executing the reports? How do I check this information programmatically without manually reviewing each report? Is this information stored in system tables or DMV's that I can query?
Show and Hide Reporting Objects in SQL Server Reporting Services
Using the Reporting Services report designer, there are several situations when it would advantageous to be able to hide reports from the user, while still allowing access to them. Several situations come to mind. One is where you want to provide a drillthrough report. Another is when you use a sub-report. You will also want to hide objects like datasources and supporting graphics in the project. This tip will show you how to hide objects that you do not want users to see, but that your reports still depend on.
SQL Server 2008 R2 Reporting Services ATOM Data Feeds
I noticed a new feature in SQL Server 2008 R2 Reporting Services called ATOM data feeds. This sounds very interesting. Can you provide an example of how I can use this? Check out this tip to find out.
SQL Server 2008 R2 Reporting Services Lookup Functions
I noticed a new Lookup function in SQL Server 2008 R2 Reporting Services. This sounds like something that I've needed for quite some time. In this tip we look at all three of the lookup functions.
SQL Server Reporting Services Conditional Formatting
I have a report that lists all databases for which a full database backup has not been applied in the previous 24 hours from the time the report is run. When I originally created the report I had a manageable number of databases to support and I was the sole DBA. The environment has grown significantly since then. Furthermore, I now have a junior Database Administrator that will eventually be responsible for making sure backups are completing as required. When I was just in the DBA role I knew which databases were important and needed to have backup failures addressed immediately and which databases were test or training versions, or were of lesser importance. In short, I need to upgrade my report to provide a triage mechanism.
SQL Server Reporting Services Development Best Practices
Using the Business Intelligence Development Studio, you can bang out some fairly nice, basic reports with the Report Wizard without much effort. But making your reports sizzle with all the bells and whistles of a professionally created report, requires some customization. There are so many options in all the object property windows, it is difficult for beginning users to know what they all do. How does one know where to start and which options to learn first?
SQL Server Reporting Services Image Source Report Options
Companies often need to generate reports and forms from scanned images and various government supplied formats. Proper knowledge of how to incorporate images in a report is a must. I am new to SQL Server 2008 Reporting Services (SSRS) and Business Intelligence Development Studio and have been assigned to use these tools. I have used Crystal Reports as my reporting tool from my previous jobs, so I had some background in reporting, but needed to learn these new tools and features available in SSRS 2008. In preparation for this task, I tried to create a report where I need to display an images. When I saw the options for selecting the image source, a lot of questions came to my mind. When should I embed an image? Why should I use an external
SQL Server Reporting Services Linked Reports Example
Reports outputs that differ on parameter values are a very common report design scenario. A common business scenario is where each report output needs a different caching / security / storage configuration. In the absence of any mechanism to deal with this requirement, a workaround would be to create multiple reports and configure them individually. This would lead to duplication of the same report in multiple places and maintainability would become a big challenge. In SSRS, there is a smart way to deal with this issue and in this tip we will show you how.
SQL Server Reporting Services Scatter Chart for Data Correlation
Correlation analysis is a method of data analysis where the relation between two measures are determined in addition to identifying outliers in the data set. For this type of analysis, a scatter chart is very helpful. In this tip we would look at how to create a scatter chart based on a X and Y coordinate system in SQL Server Reporting Services.
SQL Server Reporting Services Tutorial
In this tutorial, Ray Barley will step through the process of successfully building a SSRS report. These items include Reporting Services components, installing Reporting Services, Business Intelligence Development Studio (BIDS) overview, installing a sample database, creating a simple SSRS Report and more.
SSRS 2008 R2 KPIs with bullet graphs
Key Performance Indicators are typically displayed in a scorecard with stop light indicators, which are in the either red, amber or green light icons. The limitation for these kind of indicators is that you can see the actual and target values in two different fields as well as see the status of the KPI in red, amber or green color. If the user wants to figure out the thresholds associated with the KPI, these values are generally not visible. Further, representing the threshold values in the scorecard itself defeats the purpose of the scorecard. The scorecard should display the KPI's status in the most summarized form and use a minimal amount of space on the dashboard. In this tip we would look at how to address this issue.
SSRS Report Parts Versus Sub Reports FAQ
I'm trying to decide on a development strategy to satisfy the reporting needs in my organization. I would like to increase our efficiency in responding to report requests, while minimizing our maintenance burden. Two topics that I would like to dig in to are Report Parts and Subreports. In this tip, we look at some considerations for using one versus the other.
Working with a Parent Child Hierarchy in SQL Server Reporting Services SSRS
I'm trying to create a report that uses a parent-child hierarchy in a dimension of a SQL Server Analysis Services cube. I've done this before where I had a parent-child hierarchy in a SQL Server relational database, but the cube has me completely baffled. Can you provide an example of how to do this?
Working with Report Snapshots in SQL Server Reporting Services SSRS
We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?
