Wednesday, April 3, 2013

SSRS - How to Change Chart Type Dynamically

This article shows you how to change an SSRS chart type dynamically. In order to change the SSRS chart type dynamically there are a few steps. SSRS does not support this natively, but there are a few things you can do to dynamically change a chart type.

The first step to dynamically change your SSRS chart type is to add a parameter called "chartType" and give it a default value of "Bar".

Next we add a bar chart and style it the way we want:

Next we add a table that will help us to dynamically hide our charts. Once you have added your table, delete all of the columns except for the first one, and delete the bottom row so all you have left is just one row / column table. After you do this, resize the table box so it is the desired chart size.

The next step in creating a dynamically changing chart type is to cut and paste our chart inside of the table

The next step is to add rows to our table (we will have six, so add five rows to the table)

Now copy and paste your chart into the rows below and then resize each so they are the exact same size. How you can do this is by clicking on your chart and opening properties (F4) and going down to the Position area and copy the size of your chart and pasting that value in all of your rows.

Here is what your report will look like:

The next step is to change the chart type of each one (You can also have different charts in each table row if you would like)

So now that we have this table with several different chart types, we are going to do some conditional hiding on our rows. Right click on the first row and click Row Visibility.... Click Show or hide based on an expression. Open the function (fx button) and insert this expression:
=iif(Parameters!chartType.Value = "Bar", false, true). 
Click OK and OK to apply the visibility expression. This is a critical step in changing a chart type dynamically.

Repeat this step for every chart, changing the chartType.Value="Bar" to each chart type value (Line, Stacked Column, Stacked Bar, etc). What this will do is whenever we change our parameter to a different value, it will hide every other row and only show the row we want making it look like it is a dynamically changing chart type!

Steps to dynamically change an SSRS chart type (Condensed version):
1. Add a parameter called chartType and give it a default value of "Bar"
2. Add a parameter called DrillAction and give it a default value of "swap"
3. Add a bar chart with the data that you would like displayed and style it how you would like
4. Add a table
5. Delete all the columns except for the first
6. Add six rows to the table
7. Cut and paste the Bar chart into the first row
8. Paste the Bar chart into the second, third, fourth, fifth, and sixth row
9. Change the size of each row to be: 5.5in, 3.74261in
10. Right click on the second chart and click Change Chart type and choose another chart type (Line, scatter, etc)
11. Repeat for each chart so each chart is a different Chart type
12. Right click on the first row and click Row Visibility and click Show or Hide based on an expression and enter the expression:
=iif(Parameters!chartType.Value = "Bar", false, true)
13. Repeat for each row, changing the value to the chart type ("Line", "Scatter", etc)

Tuesday, March 5, 2013

SSRS Source Control

When developing SQL Server Reporting Services reports, one method is to use SQL Server Data Tools 2012 (SSDT).  A huge benefit of using SQL Server Data Tools is that Data Tools is a derivative of Visual Studio, and therefore supports source control for version management of reports, datasets and data sources.

SQL Server Data Tools will support using TFS, SVN or any other source control system which has a provider built for Visual Studio.  In a large Microsoft shop, TFS may be a logical choice, however, in my experience, it has proven to be buggy and somewhat unreliable.  We saw issues in our environment with checkout, checkin and conflict merging.  

In light of the issues we experienced, we decided to go with Subversion (SVN).  If you don't have SVN in your environment, but would like to experiment with it, a low cost way to do this is with CollabNet SVN Edge.  SVN Edge is relatively easy to setup and can be run on Windows, Linux or Solaris.  I have personally run it on Windows and Ubuntu 12.04 LTS.  Both were easy to setup and configure by following CollabNet's instructions.  Technically, they don't support Ubuntu, but I had no issues with it.

If you decide to use SVN, you will need to download AnkhSvn, which is a free Source Control provider for Visual Studio provided by CollabNet.  Exit all versions of Visual Studio prior to installing AnkhSvn.  Once the installation is done, you will be able to interact with your Subversion repository directly from within the IDE.

In addition to using AnkhSvn, I like to have TortoiseSvn installed in tandem.  TortoiseSvn allows you to interact with your SVN repository from Windows Explorer.  TortoiseSvn comes in both x86 and x64, so when downloading make sure you know which architecture of windows you are running.

This article assumes that you already have an SVN repository created, with the appropriate trunk, branches and tags directories included in the repository.

For this demonstration, you will be using the project we created in our article on creating a new SSRS project.  To add our project to our existing repository do the following:

  • Open Data Tools
  • Open your SSRS project

  • In the File Menu under Subversion, select Add Solution to Subversion

  • Enter the information for your SVN repository
  • As per SVN best practice, you will want to make sure that your Repository has a trunk, branches and tags directory.  In later posts, we'll talk about why, but for now, please make sure that you follow the SVN best practice.

  • When you click "OK", you will be prompted to enter a comment and check your project into SVN.  This is because the "Commit files in the initial commit" checkbox is selected.  If you uncheck that option, you would have to manually checking the project to achieve your initial checkin.

On of the best ways to recover reports, datasets and data sources for SSRS is to ensure that the projects from which the reports, datasets and datasources are deployed  are checked into source control.  The alternative is to have regular backups of the SSRS Database which is of course a best practice, but if a report developer is having issues with his/her reports on an enterprise SSRS environment, it just isn't practical to restore the database.  However, if the developer is following source control best practices with branching and tagging in svn, or branching and labeling in TFS, recovery of reports, datasets and data sources will go much smoother.

Check out our next article on setting up a Shared Data Source

SSRS Deployment

How to Deploy in SSRS
  1. If you have followed the steps in Configure Deployment, then this will be one of the easiest things you will do. And you will love it.
    1. Click the Configuration Manager drop down in your toolbar and select the desired environment
      1. NOTE: This is critical. Make sure you select the correct environment. You should have already have configured this before this step. If you don't think you have, please go configure it.
    2. There are three options for deployment:
      1. Deploy full project
        1. In your Solution Explorer, right-click on your project and click Deploy
      2. Deploy one project item
        1. In your Solution Explorer, right-click on a specific project item and click Deploy
          1. i.e. DatasetName.rsd, or ReportName.rdl, or DataSourceName.rds
      3. Deploy multiple project items
        1. In your Solution Explorer, use Ctrl and select multiple project items and click Deploy
    3. Your Output will show your Build and Deployment.
    4. Here is a successful output:
------ Deploy started: Project: ProjectName, Configuration: Stage ------
Deploying to ReportServerUrl
Deploying report '/ProjectName/ReportName'.
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

You have now deployed! You can verify it by clicking on the ReportServerUrl in the output

SSRS Configure Deployment

Configure your deployment

Build configurations allow you to specify which environment to which you want to deploy reports.  For example, if you have a Development, Functional Test, Stage and Production environment, you will want to create a build configuration each said environment.  I would name them Dev, Test, Stage, Prod.

  1. Build your project
    1. Right-click on your project and click Build
    2. Make sure it succeeds and doesn't throw any errors or warnings
      1. If it does try to resolve errors / warnings and build again.
  2. Set up your environment(s)
  3. Set up the configuration for your environment(s)
    1. Click on the drop down Configuration Manager window in the toolbar
    2. Click Configuration Manager
    3. Under the Active solution configuration: drop down select <New…>
    4. Name your environment 
      1. i.e. Test, Dev, Stage, Prod
      2. NOTE:  Name your environments so that you will know exactly which environment it is going to.  The use of Test, Dev, Stage, and Release are highly recommended
    5. Select the drop down under Copy settings from: to copy settings of an existing configuration and then tweak.
      1. I highly recommend you do this
    6. Make sure the checkmark is marked on Create new project configurations
    7. Click OK
  4. In the Solution Explorer navigation pane, right-click on your project and select Properties.
  5. The Project Property Pages window will open and will show Configuration options.
  6. On Configuration:  Ensure that the dropdown is on the desired configuration

    1. i.e. Test, Dev, Stage, Release, etc.
  7. There are three options under the General tab: Build, Debug, and Deployment
    1. On Build change the OutputPath to bin\EnvironmentName
    2. i.e. bin\Test
      1. If it is already that way, don't worry about it, otherwise this will cause you grief if you have two configurations pointing to the same output folder
    3. Under Deployment you will see the following properties:
      1. OverwriteDatasets: false
      2. OverwriteDataSources: false
        1. These two are defaulted to false because it is safer. Whenever you deploy your project for the first time these will be deployed. HOWEVER every time after your first deployment, these will not be updated. This is to ensure that your connections do not get overriden.
        2. If you change these properties to true then they will override each time you deploy them.
        3. I would keep these properties set to false, especially your DataSources. If you find yourself updating either your Datasets or your DataSources then you can change this property.
    4. Under Deployment change the following items:
      1. Target Dataset Folder: Your Dataset Location
        1. i.e: /ProjectName/DataSets
      2. Target DataSourceFolder: Your Data Source Location
        1. i.e.: /ProjectName/Data Sources
      3. Target Report Folder: Your Report Location
        1. i.e.: /ProjectName/Reports
      4. Target Report Part Folder: Your Report Part Location
        1. i.e.: /ProjectName/Report Parts
      5. Target Server URL: Your Server URL Location
        1. i.e.: http://ServerName.domainname/SSRSReportServer
      6. Target Server Version: Your Server version (drop down menu)
        1. i.e.: SQL Server 2008 R2 or later
    5. Change Configuration: drop down to other environments
      1. i.e. Test, Dev, Stage, Release, etc.
    6. Click OK
See our next Tutorial: SSRS Deployment

SSRS Setup Report

Setup an SSRS Report

  1. Right-click on the Reports Folder 
  2. Add, New Item…
    1. This ends up being easier than clicking Add New Report
    2. An Add New Item window will open and select Report as the template.  Insert a new Name.
      1. Try as much as possible to use a single naming convention to make your life and others' easier.
    3. Press Add
  3. This will automatically open the Report
    1. If it doesn't, double click on the report 
      1. i.e. ReportName.rdl
  4. Open the Report Data View
    1. Add Report Items to Report:
    2. Connect report item to a Dataset
      1. In the Report Data view, right-click on Datasets, Add Dataset…
      2. A Dataset Properties window will open similar to the Shared Dataset window. You can either utilize an existing shared dataset, or you can create your own query here. 
        1. NOTE: I suggest creating a shared dataset and then selecting it from a list here.
      3. Make sure to name your dataset.
        1. If you are using a shared dataset I HIGHLY recommend you name it exactly like the shared dataset to eliminate confusion.
      4. If done correctly Fields should auto-populate
      5. Set any Filters as required or desired
      6. Set any Parameters as required or desired
Add an Existing Report
  1. Right-click on Report Folder
    1. Add
      1. Existing Item
  2. Navigate to your existing project workspace
  3. Double click on your project work folder
  4. Sort by type make it easier to find
  5. Select 
    1. ReportName.rdl
    2. You can select multiple by holding shift or cntrl
  6. Add
  7. Double click on ReportName.rdl in Solution Explorer to open report
  8. Click the Preview and ensure that no errors occur
  9. If no errors occur your Report is functioning properly
See our next tutorial: SSRS Configure Deployment

SSRS Dataset

Setup a Shared Dataset

  1. Right-click on Shared Datasets folder
  2. Add New Dataset
    1. A Shared Dataset Properties window will open with five tabs: Query, Fields, Options, Filters, and Parameters
  3. On the Query tab:
    1. Name your Dataset
      1. NOTE: It is critical that you think about what you are naming your Dataset because it can be difficult to rename.
    2. Choose the Data Source (Dropdown) or you can click the New… button and you can create a Data Source
    3. Enter your Query
      1. i.e. SELECT * FROM DUAL…
      2. Click Refresh Fields button
      3. Enter Time out (in seconds) 
  4. On the Fields tab:
    1. If the query was correct and the Data Source was setup correctly, then there should be two columns: Field Name, and Field Source
      1. NOTE: You have the ability to edit these fields, but I suggest that you edit your query so these fields auto-populate and no errors occur.
  5. On the Options tab:
    1. You can leave everything for Default, or change as necessary / desired
  6. On the Filters tab:
    1. You can Add or Delete a filter here
      1. NOTE: This may not be the place where you will want to add a filter. You can also add a filter inside of a report. Since this is a Shared dataset, you may not want a filter unless you want the filter on all reports sharing this dataset.
  7. On the Parameters tab:
    1. If you have formatted your parameters in your query correctly they will show up here.
    2. You can set a Default Value here or at the report level
Add Existing Dataset to your project
  1. Right-click on Shared Data sets Folder
    1. Add
      1. Existing Item
  2. Navigate to your existing project workspace
  3. Double click on your project work folder
  4. Sort by type make it easier to find
  5. Select 
    1. DatasetName.rsd
    2. You can select multiple by holding shift or cntrl
  6. Add
  7. Double click on DatasetName.rsd in Solution Explorer
  8. Click the Refresh Fields button and ensure that no errors occur
  9. If no errors occur your Dataset is functioning properly
See our next tutorial: Setup an SSRS Report

SSRS DataSource

Setup Shared Data Source(s)

  1. Right-click on Shared DataSources folder
    1. This is on the Solution Explorer navigation window
    2. If this window is closed, open by clicking View, Solution Explorer. Or Ctrl + Alt + L
  2. A Shared Data Source Properties window will open with two tabs: General and Credentials
  3. On the General tab:
    1. Name your Data Source
      1. NOTE: It is critical that you think about what you are naming your Data Source because it is incredibly difficult to rename your Data Source. (Especially after you are using it)
    2. Choose the Type
      1. i.e. Oracle, Microsoft SQL Server, etc
    3. Enter a Connection String
      1. i.e. Data Source= "(local)"; initial catalog=AdventureWorks
    4. On the Credentials tab:
      1. Choose one of four options for credentials:
        1. Windows Authentication
        2. Use this user name and password
        3. Prompt for credentials
        4. Do not use credentials
    5. Press OK
Add an Existing Data Source to Project
  1. Right-click on Shared Data Sources Folder
    1. Add
    2. Existing Item
  2. Navigate to your existing project workspace
  3. Open your project folder
  4. Sort the files by type to make it easier to find
  5. Click on your data source you want (DataSourceName.rds)
  6. Click on Credentials
    1. Add Credentials as needed
  7. Click OK
Check out another tutorial: SSRS Dataset

Create a New SSRS Project

How to create a new project:

In order to create a SQL Server Reporting Services Project, SQL Server Data Toos 2012 (SSDT) will need to be installed.  Data Tools can be installed from the SQL Server installation media.  When doing a SQL Server feature install, Data Tools will be on of the options.  For instructions installing Data Tools, please see the following article.

To Create a new project:
  1. Open SQL Server Data Tools from the Window Start Menu.
  2. Click New Project...

  1. Click Report Server Project as the template

Give a name to your project, and specify where on the file system the project will reside.  By default it new projects will be create under C:\Users\[current user]\Documents\Visual Studio 2010\Projects\[Your project name].

Click OK after you have named your project and specified the location.

When creating your new SQL Server Reporting Services project, it is highly recommend that your project be placed under source control.  Data Tools (SSDT) will work with any source control provider that integrates with Visual Studio, this includes Team Foundation Server (TFS) and Subversion (SVN).  We prefer SVN as we had issues with TFS 2010.  For more information on integrating your new SQL Server Reporting Services project with source control, please read see our article.

To build your new SQL Server Reporting Services project, go to the Build Menu and select Build [Your Project Name].  Alternately, you can use the hot key combination ctrl+shift+b.  This will build the project.

Please be sure to check out our articles on the following topics:

SSRS Introduction

What is SSRS?

Microsoft SSRS or SQL Server Reporting Services is Microsoft's business reporting answer. It provides a platform in which you can deliver information in an intelligent, present, and meaningful way. This platform will allow you to generate reports that your end-users will love.

SSRS is only one component of the Microsoft Business Intelligence platform. There are also more tools like SQL Server, SQL Server Analysis Services (SSAS), and SQL Server Integration Services (SSIS). However in this tutorial we will be focusing on the SSRS side of things.

So, with that in mind, let's get started.