SSRS Overview BeginnerIntermediate John Manguno Outline What will

SSRS - Overview (Beginner/Intermediate) John Manguno

Outline What will we be discussing today? SSRS Feature Overview SSRS Installation Requirements SSRS Permissions Example Paginated Report. 3

Features SSRS stands for SQL Server Reporting Services and it is a server based report generation software that has been developed by Microsoft. SSRS provides a unified, server based, extensible, and scalable platform through which all the business reporting needs can be satisfied. This reporting content can further be shared to various users through emails, file shares etc. for maximum reach. SSRS has the capability to generate reports in various file formats such as HTML (Hypertext Markup Language), Microsoft Excel or CSV (Comma Separated Values) format etc. . Editor’s Note: Don’t worry, unlike G. O. T SSRS gets better over time and won’t leave you feeling enraged. TL; DR: SSRS is a part of the overall Microsoft Business Intelligence (BI) suite of tools and is a phenomenal (dare I say, indispensable) tool for getting at your CAMS data in ways that simply cannot be replicated from the available CAMS stock reports. 4

Features You may have a few questions: Is this dashboards? Am I analytics now? Where all the new students I was promised!!! 5

Features Reporting: The process of organizing data into informational summaries in order to monitor how different areas of a business are performing. Analysis: The process of exploring data and reports in order to extract meaningful insights, which can be used to better understand improve business performance. “Reporting translates raw data into information. Analysis transforms data and information into insights. ” https: //theblog. adobe. com/reporting-vs-analysis-whats-the-difference/ SSRS is the first step to analysis which can be supplemented further by tools like SSAS, Power BI, Machine Learning, etc…. 6

Features Report server items include paginated and mobile reports, KPIs, shared data sources, shared datasets, and other items that you can publish, upload, or save to a report server. Folders On a native report server, folders provide the hierarchical navigation structure and path of all addressable items stored in a report server. You use the folder hierarchy and site and folder permissions to help control access to report server items, known as item-level security. Schedules On a native report server, you can schedule paginated reports, shared datasets, and subscriptions to retrieve data and deliver reports and dataset queries at specific times or during off-peak hours. Subscriptions and delivery A subscription is a standing request to deliver a report at a specific time or in response to an event, and in an application file format that you specify in the subscription. https: //docs. microsoft. com/en-us/sql/reporting-services-concepts-ssrs? view=sqlserver-2017 7

Features Table. A table is a data region that presents data row by row. Table columns are static: you determine the number of columns when you design your report. Table rows are dynamic: they expand downwards to accommodate the data. You can add groups to tables, which organize data by selected fields or expressions. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). Matrix. A matrix is also known as a crosstab. A matrix data region contains both dynamic columns and rows: they expand to accommodate the data. A matrix can have dynamic columns and rows and static columns and rows. Columns or rows can contain other columns or rows, and can be used to group data. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). List. A list is a data region that presents data arranged in a freeform fashion. You can arrange report items to create a form with text boxes, images, and other data regions placed anywhere within the list. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). Chart. A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more styles are supported. For more information, see Charts (Report Builder and SSRS). Gauge. A gauge presents data as a range with an indicator pointing to a specific value within the range. Gauges are used to display key performance indicators (KPIs) and other metrics. Examples of gauges include linear and circular. For more information, see Gauges (Report Builder and SSRS). Map. A map enables you to present data against a geographical background. Map data can be spatial data from a SQL Server query, an ESRI shapefile, or Microsoft Bing map tiles. Spatial data consists of sets of coordinates that define polygons that represent shapes or areas, lines that represent routes or paths, and points represented by markers. You can associate aggregate data with map elements to automatically vary their color and size. For example, you can vary the marker type for a store based on sales amount or the color for a road based on speed limit. For more information, see Maps (Report Builder and SSRS). https: //docs. microsoft. com/en-us/sql/reporting-services-concepts-ssrs? view=sql-server-2017 8

Features Table. A table is a data region that presents data row by row. Table columns are static: you determine the number of columns when you design your report. Table rows are dynamic: they expand downwards to accommodate the data. You can add groups to tables, which organize data by selected fields or expressions. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). Matrix. A matrix is also known as a crosstab. A matrix data region contains both dynamic columns and rows: they expand to accommodate the data. A matrix can have dynamic columns and rows and static columns and rows. Columns or rows can contain other columns or rows, and can be used to group data. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). List. A list is a data region that presents data arranged in a freeform fashion. You can arrange report items to create a form with text boxes, images, and other data regions placed anywhere within the list. For more information, see Tables, Matrices, and Lists (Report Builder and SSRS). Chart. A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more styles are supported. For more information, see Charts (Report Builder and SSRS). Gauge. A gauge presents data as a range with an indicator pointing to a specific value within the range. Gauges are used to display key performance indicators (KPIs) and other metrics. Examples of gauges include linear and circular. For more information, see Gauges (Report Builder and SSRS). Map. A map enables you to present data against a geographical background. Map data can be spatial data from a SQL Server query, an ESRI shapefile, or Microsoft Bing map tiles. Spatial data consists of sets of coordinates that define polygons that represent shapes or areas, lines that represent routes or paths, and points represented by markers. You can associate aggregate data with map elements to automatically vary their color and size. For example, you can vary the marker type for a store based on sales amount or the color for a road based on speed limit. For more information, see Maps (Report Builder and SSRS). https: //docs. microsoft. com/en-us/sql/reporting-services-concepts-ssrs? view=sql-server-2017 9

Features Our current report Count at SAGU is around to 1500 individual reports/subreports 10

SSRS Installation The best thing about SSRS is that you already own it! It is included with SQL and you already have a SQL license with your CAMS installation. 11

SSRS Installation The big question you need to determine for your institution is if it is best to have a dedicated Report Server or to install reporting services on the existing SQL Instance. If possible I recommend a separate SQL Instance for Reporting services so that it can be updated independently of CAMS. It also prevents a competition for resources between reporting and CAMS. 12

SSRS Permissions SSRS Reporting Permission is controlled by Active Directory and is granular down to the individual item level. There are many ways this can be configured including configuring separate data sources for especially sensitive data. The default way that 3 D normally configures SSRS is to have a single SQL account with DB read access that is used for all reports, and then permissions to the individual reports are controlled via folder and report level access, but there have been cases where additional security is required, and the options for creating a secure environment are endless. 13

SSRS Permissions SSRS Reporting Permission is controlled by Active Directory and is granular down to the individual item level. There are many ways this can be configured including configuring separate data sources for especially sensitive data. The default way that 3 D normally configures SSRS is to have a single SQL account with DB read access that is used for all reports, and then permissions to the individual reports are controlled via folder and report level access, but there have been cases where additional security is required, and the options for creating a secure environment are endless. 14