| Table of Contents |
|---|
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.
...
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
Excel template: Example: 'Template - Check spreadsheet.xlsx'
We've taken all of our SQL views and created templates for them in SSRS. Whenever you need to create a new spreadsheet, please start with one of the Templates. There's a fair amount of pre-processing done in the views and the templates to try to ensure that you get the appropriate data. Double click on the spreadsheet name and choose 'Open' to pull up the spreadsheet. You can now consider yourself to be outside of the SSRS environment, and all 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 go to the Data tab and click on Refresh, Excel will go and get updated data for you from the Colleague ODS. 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.