Welcome to SQL Server Reporting Services (SSRS)
- Julie Latham
- Richard Goerwitz
- Russ Bauer
This document outlines the methods and processes used to get data and reports from via SQL Server Reporting Services, and, in addition, how to access some Excel templates we've published there.
What is SSRS?
At Carleton, we use SQL Server Reporting Services (SSRS) as our primary reporting tool for Colleague. When you hear the term 'SSRS', you can think of it as a system used to extract data. It's not just an isolated reporting tool, but also a space in which to collect your reports. When you go into SSRS, you'll see a collection of reports and spreadsheets that are pertinent to your position in the college.
ITS uses the SSRS tool 'Report Builder' to write reports that you can run. We design the reports to do a lot of the data massaging for you, so that you'll just need to enter the parameters and run the report. These reports can have all sorts of parameters (e.g. you can be prompted for a date) and the reports also have various output methods (printed to the screen, PDF, Excel, etc). All Report Builder reports run off of the Colleague ODS. The ODS is built every night from Colleague data - so when you run a report, please just know that it is pulling from yesterday's data in Colleague. We do this to simplify reporting and to make it faster. It is possible for you to gain access to Report Builder to write your own reports; ITS does not secure this tool from outside use. However, all reporting tools require some amount of training and ongoing use to ensure that they are effective. In most cases across the college, it makes sense for ITS to write the reports. For the exceptions, ITS provides some limited support for Report Builder.
If you prefer to work in Excel for certain projects, ITS also builds template spreadsheets for you in SSRS. Whenever you start a new spreadsheet report, you should pull up a template, make your modifications, and save it under a different name. We try to simplify the underlying Colleague data for you in the spreadsheets, while still leaving you lots of opportunity to extract a wide variety of data. We have links to documentation on each template, so please review that prior to creating a new spreadsheet.
Remote Work with Colleague Reporting (SSRS) |
|---|
Colleague Reporting can be accessed for remote work while maintaining the following:
SQL Server Reporting Services (SSRS) works well on:
Recommended References: |
How do security and accounts work?
Each folder, report, and template is assigned to a 'department' at the college. When a person is employed, they are assigned to one or more departments. This information flows through to our provisioning software. When you log in to your Windows machine (PC or laptop), your credentials are stored. Software like SSRS can read these credentials and can then associate you with your department and it's associated security. Thus when you pull up SSRS in Internet Explorer, you don't have to log in - it already knows who you are. And when employees come-and-go, they are automatically provisioned and de-provisioned into SSRS.
There may be exceptions to the rule, in cases where a person from outside of your department needs access to your reports. A call to the ITS Helpdesk can begin the process of provisioning this access.
How do I access SSRS?
To get into SSRS, pull up Internet Explorer (don't use Mozilla Firefox) and go to https://reports.ads.carleton.edu/reports. You'll see a screen that look something like this:
Within each office, we've identified specific subject areas to help organize the reporting and security. If we take a quick peek at the 'Accounts Payable' folder, we see this:
In here, you can see the two types of reports.
SSRS Report Builder: Example: 'Draft Inactive Vendor Report'
The quickest way to run an SSRS report is to double click on it. There are things that you can do with reports other than run them, however. The below image shows the various report options:
Function | Description |
|---|---|
Move | This option will come in handy if you are creating new folders and organizing your reports. If you'd like to move this report to a new folder, choose the 'Move' option and select the appropriate new folder. |
Delete | Most reports are written by ITS and should remain in your folders. If, for some reason, you really need to delete a Report Builder report, this is the proper way to do so. |
Subscribe | Subscriptions are requests to have reports sent to you automatically on a pre-arranged time schedule. For example, you may want a particular report emailed to you on the first of every month. While this option will let you set up your own subscriptions, we do not support this at Carleton because the subscription will keep your password and when you change your password in a few months, the subscription will no longer work. If you are interested in getting a subscription to a particular report, please place an ITS helpdesk call and a Colleague support person will assist you with your request. |
Create Linked Report | |
View Report History | |
Security | You are able to view the security settings of this report. |
Manage | |
Download | |
Edit in Report Builder | If you have been trained in Report Builder, this is how you modify reports. We do not secure Report Builder at Carleton, so anyone can try to write reports against the views that they have access to. However, it is highly encouraged that you gain knowledge in Report Builder prior to doing this. |
Run a Report Builder report
You can run Report Builder reports by double-clicking on them. They will open in an SSRS screen. If there are parameters in the report, you will see prompts at the top of the screen. If you look to the right-hand upper corner of the report, you will see a blue question mark. Click on this symbol to pull up the documentation for the report.
Excel Templates
In efforts to centralize distribution of Excel-based reports, we've set up folders in many of the departmental folders in SSRS to house them. Typically these Excel-based reports will be located in folders named "Templates" or the something similar.
These Excel-based spreadsheets all take data from the same back-end database as our SSRS reports. They display the data, though, as tables in Excel, rather than as a series of web pages, as with standard SSRS reports.
To use a template spreadsheet, simply click on it. This will download it to your computer. You can then load it into Excel (usually just a double click and an "Open").
But it's here where you need to be careful. Typically the template will come up in Excel with the data it held when the creator of the template last saved it. To fetch new data, you will have to click on the datasheet in Excel and select Data→Refresh All from the Excel ribbon. Note that this action will only succeed if you are on a Windows machine and either physically on the campus administrative network, or else connected to it via the VPN.
Keep in mind also that once you are in Excel, you are outside of the SSRS environment. All normal Excel functionality should be in place.
If you look in the right-hand upper corner of the spreadsheet, you will also typically see a blue question mark. Click on this symbol to pull up the documentation for the underlying view.
If this is a one-time usage spreadsheet, you can use it and delete it. If you plan to use it again or would like to share it with others, you should upload your final draft of the spreadsheet back up into the SSRS environment. To do this, simply go into the proper SSRS folder and choose the Upload option. If you don't have permission to upload files to SSRS, contact someone in your department who does, or arrange via the ITS Helpdesk to have someone on the Colleague Team to do it for you.
In general, SSRS is a secure place to store data and spreadsheets, and it is also backed up every night to a server. Please give your spreadsheet a name that will tell people about it's purpose, and also consider documenting right inside of your spreadsheet.