Microsoft SQL Server Reporting Services (SSRS)
Microsoft SSRS is part of the suite when you purchase Microsoft SQL Server. It was released in 2004 as an add-on to SQL Server 2008 and has been supported by Microsoft for 14 years. SSRS may not be the easier solution to use, but it is a great tool when used in the right environment. SSRS is included in the SQL Server Integration Services (SSIS) functionality, so if you have SQL Server you also have access to SSIS and SSRS.
With SSRS you can build your own customised SQL reports that access your live data from your corporate systems, so every time you run the report you can be confident that the data the report is consuming is current and up-to-date. SSRS is a web-based reporting tool with enhanced performance and scalability. By default, reports in SSRS are displayed using the HTML 5 rendering engine. Being a web-based tool means that reports are available to any number of users from their own computers via the web browser. Many of my clients access customised SQL reports via their department’s internal intranet page, SharePoint Service, or directly from their bespoke Asset Management system, and even sometimes from their GIS Map Viewer. Despite the rendering being web-based, you can export reports to several different file formats, including PDF, CSV, Word, and Excel. Once the customised reports have been built in SSRS, the only thing the report consumer needs to do is click on the link and run the report – very easy for the end user. These files can also be scheduled to go out via an email or saved to a file share on a regular basis based on a trigger that has been set up in the SQL database.
SSRS is undoubtedly a powerful tool that can query data from different databases and present the data in a variety of different formats. Some use-case examples include pulling property data from the Property & Ratings system and linking it with spatial data held within the SQL Spatial Database. If there is a unique ID to link the data within the tables, then we can pull the data together into one report and present the data in a variety of ways. We could visualise and map Customer Request data for, let’s say, Barking Dogs, Mosquito Sightings, Blocked SW Drains, Fallen Trees, etc to help determine areas of interest. Or, from the Asset Management System, a customised SQL Report that provided work details and site maps to Contractors engaged to do the work. SSRS is an ideal fit for any operational reporting, anything that needs printing on a regular basis such as Work Orders, Invoices, Purchase Orders, Valuation and Depreciation Reports, Mail merges, etc. SSRS is very well suited to have fine-grained control over your reporting documents. Once the Customised Report has been developed, the end user only needs to run the report.
SQL Report Examples below:
This example has a parameter built in to run the valuation report from the date entered by the user. In this case, 30/06/2018 has been entered. Grouping has also been applied so the report will be grouped on Asset Types.
Customised Reports in SQL Server Visual Studio
I am not going to go into great detail about how install and configure any of the software I have used in this blog, but if you are interested and would like further information then please contact me here jill.singleton@iamdata.solutions
Building Customised SQL Server Reports
So, what do we need to do to build some customised SQL Reports that utilise the data we have in our SQL Server databases?
Firstly, you will need to create a data source and a dataset in SQL Reporting Services for the report to use.
Report parameters to filter the data.
In this example, the data will be filtered on a start date and an end date.
And export the Report out into any format
If your data has a spatial component, you can including a map in your Report
And write various SQL queries that you can use in the report parameters to enable you to pull the required data into the report.
Power BI
Power BI is a suite of business analytics tools that allows you to connect to many different data sources. The basic version is free to download to your desktop and has lots of functionality. I created this interactive visualisation very quickly using the free download version. The report provides in-depth information that is presented in an easy to interpret and understand format in a matter of minutes. In the past, a report like this using Excel would normally have taken me hours to create.
View the data in real-time
When Power BI is connected to live data, via SQL Server for example, consumers of the reports will have access to live dashboards with real time updates. As data gets processed via the many different corporate databases, e.g. Finance, AMS, CRM etc, and Power BI is connected to that data source, the reports and dashboards reflect those updates and changes. This is a great time saver and the consumer of the data can be confident that the data is current and up to date. Power BI works perfectly with static data sources such as Excel, but we must remember that any dashboards or reports created from a static data source will just be a snapshot in time, not a living document.
Power BI dashboards are dynamic, if I click on an element in one visual, for example ‘Concrete’ , then the other graphs also focus on the Concrete element, providing me with information for that element.
Interactive dashboard
In this example I have built a filter on Suburb to provide the ability to query out the relevant information and location map.
So, in Fullarton, the data shows 7 Aged Facilities and I can see them located on the map.
Sharing the dashboards with others in your organisation is easy, the data is always up to date because it is coming from the data source, and it is also mobile. I can view my Power BI dashboards via my mobile phone when I’m away from the office.
Excel
Excel is still widely used in all organisations, but sometimes Excel has been misused and wrongly considered as a database - Excel is definitely not a database - for so many reasons that I won't go into here.
However, you can connect to the data that is stored in your database and view it in Excel.
I do like this feature available in Excel. You can do some spatial analysis and visualise your data spatially in Excel using 3D Map as long as you keep the datasets small, and you usually have to perform some sort of data cleanse to prepare the data.
You can read more about 3D Map in Excel in my blog here: 3DMap
Conclusion
SQL Server Reporting Services - SSRS is excellent for displaying lots of textual and numerical data that the organisation runs on a regular basis – such as work orders, valuation / depreciation reports, mail-merges, any report where you want to keep the format of the report constant and where only the underlying data changes.
Power BI - Power BI is excellent for displaying charts and for interactive reporting. Power BI is my personal favourite. Power BI makes it easy to tap into your organisation's data and easily converts your raw data into Business Intelligence. Sharing the dashboards with others in your organisation is easy, the data is always up to date because it is coming from the data source, and it is also mobile. I can view my Power BI dashboards via my mobile phone when I’m away from the office.
Excel - Excel is good for displaying numbers and performing mathematical equations, but the formatting and layout can get messy very quickly. In my experience, most organisations have large amounts of data that they need to interrogate, and Excel is not the best tool for this type of analysis. As soon as the data is pulled into the Excel Spreadsheet it becomes separated from the main data source. This means that the data in the spreadsheet is already out of date. It is also easy to inadvertently delete data from a cell or corrupt the data, and Excel tends to crash out when performing any type of data analysis on large datasets. There are some occasions where Excel may be the best tool, but for most of the regular operations undertaken by any Council or Organisation, I would not recommend Excel for the job.
I hope you have enjoyed this blog. I would love to hear about your experiences with SSRS, Power BI, Excel, or any another reporting / visualisation tool you particularly love (or hate). Please share your thoughts!
The datasets used for this article have been obtained through creative commons licensing
Other datasets such as the defect and hazard tables, some attribute information, etc have been created solely for the purpose of demonstration for this article.
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining the GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and like to hear about it too! Please email Jill at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comentários