Reporting Services (SSRS)

How to create and configure SQL Server mobile reports

September 2, 2016 by Rajendra Gupta

Microsoft has recently launched SQL Server 2016 with lots of new features like row level security, always encrypted, stretch database, PolyBase, R Services, hybrid cloud capabilities, Dynamic Data Masking, query store, etc. There is a rich set of business intelligence, security features, and high availability solutions.

How to use SQL Server Reporting Services (SSRS) to execute SQL Agent Jobs

August 22, 2016 by Sifiso Ndlovu

Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilized to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organization that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.

Reporting in SQL Server – Combine three reports into one using SQL Server Data Tools

August 4, 2016 by Steve Simon

Introduction

In our last “fireside chat” we discussed a few of the challenges that the HR Manager of a major hardware chain was experiencing. Mary Smith, the HR manager has since approached us to modify her existing reports to function more efficiently and effectively by utilizing her existing data, yet reduce the total number of reports.

Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports

July 27, 2016 by Steve Simon

Introduction

With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.

In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.

Reporting in SQL Server – How to use pivot tables and date calculations to obtain valuable reports

July 26, 2016 by Steve Simon

Introduction

A few months back I had been working on an interesting proof of concept for a human resources client (Mary Smith) who is the HR manager in a major hardware chain. The firm has a “ServiceNow” installation, with tables that stores employee ”hours worked”.

SQL Server and BI – How to document your Tabular model with Reporting Services 2016

July 20, 2016 by Steve Simon

Introduction

A few weeks back I had been working on an interesting proof of concept for a client within the food/grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. In our previous “get-together” we discussed how to create a tabular model project and how to create efficient and effective reports utilizing Excel.

Report filtering: Excel slicer vs SQL Server Reporting Services (SSRS) parameters

May 4, 2016 by Sifiso Ndlovu

This article explores the report filtering using Excel Slicer and SQL Server Reporting Services.

SQL Server pivoting on non-numeric data types

February 19, 2016 by Sifiso Ndlovu

Introduction

In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.

Which Reporting Services dataset fields are being utilized by the reports?

March 18, 2015 by Steve Simon

Introduction

Have you ever felt like pulling your hair out, trying to ascertain exactly which fields in your existing Reporting Services datasets are being utilized by your reports. This happened to me recently during a corporate conversion and cleanup exercise for a database migration to the cloud.

The “aha moment” came after having presented a paper at the PASS SQL Server Nordic Rally (March 2015), when one attendee came up to me and asked if I knew of a method to do this. As they say ‘necessity is the mother of invention’ and spiking my interest, I played around until I came up with the solution that we are going to chat about today. The end solution may be seen below

How to convert data format into a valuable dataset using SQL Server Reporting Services

March 16, 2015 by Steve Simon

Introduction

Oft times we are forced into situations where we must clearly think outside of the box. In today’s “get together”, we are going to discuss a challenge that I encountered during the last week of February of this year. The client had been charting weekly business calls placed by his sales reps. Our client had been tracking these results within an Excel spreadsheet (see the screen dump below) and he would be using this spreadsheet to report the sales reps progress going forward. My task was to source this data for the corporate reports in Reporting Services, from this spreadsheet and do so on a weekly basis. The client, being resistant to change, was not willing to change the format of the spreadsheet to something more conducive to be utilized by the chart that he wished to produce (see immediately below).

Reporting in SQL Server – create a matrix based sub-report called by the previously created main report

March 4, 2015 by Steve Simon

Introduction

As you will remember from our last “get together” we created an application that permitted us to report upon financial data based upon an unorthodox financial year. In fact, our fiscal year started in July and ended in June. We created a chart to display the data.

In today’s “get together” we are going to push our application a bit further and build in a subreport which will bring up the underlying data when the end user clicks upon the chart for any particular month. Thus should the user click on February 2015, then all of February’s data (for the selected funds) is shown in a matrix. If the user chooses March, then March’s data is shown.

Reporting in SQL Server – create a chart based on the data extracted for a given date range

February 27, 2015 by Steve Simon

Introduction

I recently heard from a lady from overseas who wanted to find a quick and dirty mechanism of extracting data for a given date range (based upon a fiscal year that started July 1^st and ended June 30^th). The idea interested me and as always, I had to try it out.

In today’s “get together”, we are going to have a look at how this may be achieved.

Using a cursor to correctly extract SQL Server data and place it in a Reporting Services matrix

February 23, 2015 by Steve Simon

Introduction

In our last two chats, we discussed enterprises that have had financial years that began in July and ended at the end of June. One of our clients works with this fiscal calendar and their financial folks are Excel “Fundi’s” (Fundisa is a Nguni word for “expert”). Many of their reports contain the current month’s sales, in addition, carrying running totals from the beginning of the fiscal year to date. Read more »

SQL Server Reporting Services Best Practices

February 11, 2015 by Steve Simon

Introduction

In past chats, we have had a look at a myriad of different business intelligence techniques that one can utilize to turn data into information. In today’s “get together” we are going to try to pull all these techniques together, rationalize our development plans, and moreover, look at some good habits to adopt or for the want of better words utilize SQL Server Reporting Services Best Practices.

Create reports like a champion! Tips and tricks with Reporting Services

December 24, 2014 by Steve Simon

Introduction

Over the past few months we have covered a lot of ground in our little “get togethers”. We have seen a few of my favorite tips and tricks. In today’s discussion we are going to have a look at a few gotcha’s upon which I have banged my head many times.

How to reduce the report complexity using the “Visibility” options in SQL Server Data Tools

December 11, 2014 by Steve Simon

Introduction

Far too often we encounter clients that are really too keen to establish all inclusive reports for decision making purposes. While this is super (in principle) oft times these folks will inform you that all the data that is within the tables should be present within the report, and this is not always feasible nor practical.

After much thought I came up with an alternative to permit these folks to have their ‘cake and eat it’ and yet not render a cluttered report.

How to enhance your reports with SQL Server Reporting Services (SSRS)

November 18, 2014 by Steve Simon

Introduction

A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.

Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.

The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!