SSRS Reports not Rendering in Chrome, Safari and Firefox?

By default SSRS Reports don't render in Chrome, Firefox and Safari.  However, there is a hack that can be implemented to get this working.

To fix the problem, we need to create a custom stylesheet for SSRS.  The new style sheet will use media queries to target the non-IE browsers.  So, In theory, IE will not be impacted using this solution.  This by no means a perfect solution, and I have not had time to test it in every possible scenario.  However, it has worked for me when I know a user needs to view a report in a non-IE browser.  It works best if you are hyper linking to a report from another location.

For SQL Server 2012,  you will find the SSRS css files located in C:\Program Files\Microsoft SQL Server\MSRS11.[Instance Name]\Reporting Services\ReportServer\Styles.

Create a new css file.  In the file paste the following styles:

@media screen and (-webkit-min-device-pixel-ratio:0) {    div[style] {        overflow:visible !important;    }}@-moz-document url-prefix() {    div[style] {         overflow:visible !important;    }}

Then you will want to take the contents of the HtmlViewer.css file and paste it before the styles above.  From what I can tell, this will tell SSRS to override the entire default css file with the new css file.

In order to call the report with the new css file you will need to use the following url format:

http://[your server dns name]/[Your SSRS Instance Webservice Path]?[Report Path]&rc.StyleSheet=[the new css file name]

Example

http://www.somedomain.com/ReportServer?/MyReports/Reports/TestReport&rc:Stylesheet=NonIEStylesheet

where NonIEStylesheet is the name of the css file we placed into C:\Program Files\Microsoft SQL Server\MSRS11.[Instance Name]\Reporting Services\ReportServer\Styles.  Also notice we didn't use the /Reports/ endpoint for SSRS.  We have to use the /ReportServer/ endpoint.

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)

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