SSRS – DESIGN LAYOUT FOR EXECUTIVE DASHBOARD

A year ago I had the opportunity to work on an executive dashboard re-design project. The current dashboard was developed few years back using SSRS 2005; it was quiet basic and only included small tables to show KPIs without any nice layout. The project was therefore to keep the existing KPIs but produce a “modern design” dashboard using SSRS 2012.

Before starting I’d like to mention that this article is not meant to be technical; it’s mostly my thoughts about report design after working on this project.

I must say that I was skeptical about being able to produce good looking dashboard with SSRS – I am talking here not only about showing a fancy gauge and a graph, but being able to see the whole report as a smooth and modern design layout. Ok we are developers, design is not our job but when working on a dashboard project, I think that is becoming part of the job.
I also often hear these days that SSRS is a dying tool as there is no much development made by Microsoft in the last year(s); the focus being mostly on Power BI. I agree with the fact that SSRS needs a bit of a refresh but having worked with it for a while I know that with a bit of work you can archive a lot.

So I started to look around SSRS blogs to see what actually people can archive visually with this technology. There are a lot of SSRS Blogs out there with good examples. Going through the reading I found a great article from Jason Thomas on how he managed to reproduce a Dundas dashboard. I must say that this article was a relief for me, seeing that you can reproduce all those Dundas chart convinced me that “modern” design dashboard with SSRS was feasible!
So I downloaded the .rdl file from his article, I realized that all the charts were somehow complex or needed lots of expressions to be managed properly, nevertheless it was still possible.

 

PROJECT
I first started to draft with my client how they wanted the dashboard to look like. This is a crucial step in order to get a good visualization of what is expected from the customers / users. Very often the customers don’t really know what they want until this step is done so it does help to get an overall picture for both the client and the developer.

We came up with this result:

  • 6 different parts, each of them including a main KPI (with an arrow for value trend comparing to last year value
  • Couple of graph to show the value trend for the last 4 years, all charts must use the same Vertical axis range, not starting from 0.
  • Maximum of 4 seconds for rendering
  • Good looking report and easily readable
  • Readable on Ipad

1stDraftOfTheDashBoard
1st draft of the Dashboard

 

GENERAL DESIGN
What I mostly realized when working on the design of this report is that you should change most of the default SSRS layout setting. And first of all, do not use black color, use a nice gray. I know that this seems like a detail but black color is aggressive for the eye and you will not have a smooth report with black in it (unless it’s your main color like using a black background). So go to properties and change all the text color, font color, line color, border color, etc… to Dim Gray + Bold instead of Black. This is also/mostly valuable for chart: change the label, data label, Axis line, background line, Tick Marks color, etc… to Dim Gray or even Silver color. You can also use report properties custom code in order to write a function to assign a default color.
For colors, use light colors and colors that match, I know that seems logical but how many reports I have seen that looks sooo terrible only because of wrong colors chosen. You can go on internet and check for colors on website you like and you can get the color code using some of the nice and free tools on the internet like instant Eye dropper or ColorZilla. This will save you a bit of time to find the right colors. I’m sure you have already some websites in mind!

InstantEyeDropper

Instant Eye dropper selecting a green color on a webpage

Then in order to make the report easily readable, I started removing all the visual pollutions => exit the huge logo, decorative lines or background color “for decoration”. The actual trend for website design is “flat design” so we should also use this technique, and it makes a dashboard much more readable! Take a minute and read the Top 10 web design trends 2014, especially the number 1.
Therefore exit also the 3D chart that looks like 1995… It is better to use a nice flat 2D chart. No, SSRS cannot render properly 3D …
I found a lot of nice dashboards on the internet very complicated or with black background, I definitely think that when working daily with numbers you need an as clean as possible visualization. Demos are one thing, daily work is another… (The wow effect does not work every day…

 

KPI BOX
I do not like the default KPI box in SSRS so I used Jason’s article way to build my own box. It does take a bit of time but it looks better at the end. Simply draw something first and then reproduce it inside a table by hiding the unnecessary lines and borders. Hide as much lines as possible, keep only the necessary.

Here is an example of KPI boxes created from a table:

Design:
Design View
Rendering :
RenderingView
For the trend arrow I use my own image and added a custom code property to display the correct image depending on the percent increased vs. last year (Percent already calculated in the query).

CustomCodeWindow

PropertiesChart

 

You can also implement the logic directly as an expression for the Image value, however for good practice and future references it is better to add all code in one place so the custom code window is a good candidate.

 

CHARTS
One of the requirements was to use the same vertical axis range for all charts in order to compare them easily. The best way I found was to calculate the Min, Max and intervals in advance in the T-SQL query using this logic:
Take the Max value +20% and round it up: 3012621 + 20%= 3615146, rounded up= 4000000
Take the Min value -20% and round it down: 1087495 – 20%= 906246, rounded down= 900000
Take the difference divided by 4 (for 4 intervals) then round it up: 4000000 – 900000 =3100000 / 4 = 775000, rounded up = 800000.
So the interval will have a value of 800000.

SELECT
*
,ROUND( (MaxTotalModifiedRound – MinTotalModifiedRound) /4 , – (LEN((MaxTotalModifiedRound – MinTotalModifiedRound) /4)-1)) as interval — Dif Min and Max value then divided by 4 then rounded up

FROM (
SELECT
id, YearNum, Total

,CAST( Min(Total) OVER() as int) as MinTotal
,CAST( MAX(Total) OVER() as int) as MaxTotal

,CAST( Min(Total) OVER() / 1.2 as int) as MinTotalModified
,CAST( MAX(Total) OVER() * 1.2 as int) as MaxTotalModified

,ROUND( CAST( Min(Total) OVER() / 1.2 as int) , -(len( CAST((Min(Total) OVER())/1.2 as int))-1) ) as MinTotalModifiedRound — Min Total rounded : Total – 20% then rounded down
,ROUND( CAST( MAX(Total) OVER() * 1.2 as int) , -(len( CAST((MAX(Total) OVER())*1.2 as int))-1) ) as MaxTotalModifiedRound — Max Total rounded : Total + 20% then rounded up

FROM …

DataChartAxis

You can then add these values into the vertical axis properties of the charts:

ChartAxisProperties

It will then display like this:

ChartView

 

PERFORMANCE
The goal was a maximum of 4 seconds for rendering. Having lots of dataset and going back until 4 years of data, I decided to create Indexed views that will include only the grouping level necessary for the report.
There are 2 parameters in the report to choose the team and the date of analyze, I have therefore grouped my data this way in the indexed view. You can get a very high increase of performance with indexed views (x10+) ; however there is a lot of drawback with these particular views. See recommendation here.

 

IPAD
One of the goals of the dashboard was to be able to visualize it from an Ipad. With SQL Server 2012 SP1, you can now view SSRS reports on the Ipad and Windows surface device, however the report will not necessary be resized correctly. One of the options is to use a command in the URL which will resize the report: &rc:Zoom=Whole%20Page. Using this command the report will render in full page weather you use your Ipad vertically or horizontally. « Whole Page » will attempt to zoom the report to such a level that the entire page is displayed at once in your report viewing frame – whether than means scaling it up or down.
Being able to visualize report from an Ipad is a great improvement, however it is still not easy to navigate or change parameters in small devises. I did a bit or research on internet and found that there are a lot of app. or tools to help visualized SSRS reports on mobile devises.

Here are my best 3:

  • Forerunners, they basically redeveloped the Report Manager in HTML5 so you can visualize SSRS reports on all devises. It is also much easier to use the parameters on small screen. It has been created by Jason Carlson, previous SSRS architect in MS (read interview here) and the new V3 seems promising:
    – Allowing designers to add client side JavaScript on text boxes or images
    – Define fixed size tables and matrixes
    – Scroll within a fixed region instead of expanding the page size
    – Enable editing of data with updatable controls
    – Easily post data to another application using HTML forms in your reports
    – Include information from other applications in your report via an IFrame
  • MobiWave, This App display SSRS report and also SharePoint documents
  • SSRS Report Viewer, This App display SSRS report and also SharePoint documents

My best bet is Forerunners so far as the license cost is low comparing to other products, and implementation very simple – no need to install any app. on each devises. There is also lots of other good tools, maybe better, but usually much more expensive. My goal here was only to display existing reports on mobile devises without any extra development.
Please let me know if you tried other / better tools!

 

FINAL REPORT
Finally here is a screenshot of the dashboard:

DashBoard

 

Overall it is not a complicated dashboard to produce, it only takes some time to build the table/boxes for your KPIs (and make them rendering correctly and in-line once deployed!). I also add to work with the requirement of the customer which does not seem logical to me sometimes… like using histograms instead of line chart for sales trend…

What I mostly wanted to show here is that you can show data that does not look like simple tables with only few tricks in SSRS.

You will find the .rdl file here for VS 2010, I have modified and hardcoded all values so simply point the DataSource to the master db and you should be able to execute the report.

Let me know what you think and if you have other ideas please share with me!

Les Composed Looks dans SharePoint 2013

Introduction

Beaucoup de choses ont changé depuis l’arrivée de la nouvelle version de SharePoint. Vous rappelez-vous des thèmes dans SharePoint 2010 ? Et bien dites maintenant bonjour aux …  Composed Looks !

Change the look

Galerie des Composed Looks

Les Composed Looks, c’est quoi ?

Pour en donner une définition simple, les Composed Looks sont un assemblage de composants graphiques qui vont vous permettre en un minimum d’effort  de créer des thèmes.

Bien sûre, ces derniers ne seront pas pertinents pour tous vos projets mais ils ne sont pas à négliger car cette nouvelle fonctionnalité va vous permettre de composer une charte graphique de manière très simple en utilisant :

  • une Master Page
  • une palette de couleurs
  • une palette de polices
  • une image de fond

Lire la suite

Indexation DataWarehouse

L’indexation d’un entrepôt de données est délicate :

  • si vous avez trop peu d’indexes les temps de chargement de votre entrepôt seront optimum mais les temps de requêtage seront déplorables
  • au contraire, si vous avez trop d’indexes, les temps de chargement vont exploser mais les performances de requêtage seront excellentes.

Afin de prévoir un plan d’indexation optimum vous devez prendre en compte certains critères :

  • le type d’entrepôt (archive vs temps (quasi) réel)
  • la taille des dimensions et des tables de faits
  • le nombre d’utilisateurs ayant accès à l’entrepôt (le nombre d’accès concurrents max à gérer)
  • le type d’accès aux données (ad-hoc ou via des interfaces d’applications structurées)
  • le mode d’alimentation (upsert, annule/remplace …)
  • etc …

Voici néanmoins quelques règles basiques à respecter lors du design de votre entrepôt.

Lire la suite

Designing great reading experience

Session consacrée aux best practice en termes de design pour les applications orientées sur la lecture pour mettre en avant ses contenus.

La session démarre avec un tour des applications du store dans ce domaine, à commencer par Bing actu et voyage, ou Wired.

Là encore, le speaker insiste sur le fait de soigner sa page de Hub, et sur la hiérarchisation de l’appli. Le speaker met également en garde les layouts qui sortent du c-shape, car ils ont tendances a être plus difficiles à adapter aux différentes résolutions.

Le speaker nous montre le hub de l’appli du Monde, du new zealand herald, en nous montrant comment il se comporte à différentes résolutions (par exemple en augmentant le nombre d’items affiches, ou en affichant plus d’items en grand format).

On voit ensuite le zoom sémantique qui est aussi une part importante du hub, et la personnalisation des sections affichées (dans une action en appbar).

On voit ensuite le design des blocs de lecture. Là encore, il faut penser à l’adaptation aux différentes résolutions. Le speaker recommande aussi de ne pas utiliser Segoe pour les grandes portions de texte (préférer Seraph, Georgia, Calibri, …), et de pouvoir personnaliser la taille de police. Ce point est illustre dans l’appli NBC News. Le speaker explique qu’il vaut mieux mettre les videos en fullscreen dans un écran a part, pour des raisons ergonomiques, et car cela permet d’économisera la batterie.

Le speaker recommande aussi de mettre les articles dans une flipview, pour naviguer entre articles plutôt que de forcer l’utilisateur à faire un back. Il faut dans ce cas penser a implémenter une nav au clavier, et éventuellement un backup dans la appbar.

On enchaine sur comment gérer beaucoup d’articles, en catégorisant intelligemment, et en proposant des filtres. On voit l’application icookbook en exemple.

On parle ensuite des tuiles. On voit que l’objectif des tuiles animées est aussi d’essayer de faire revenir l’utilisateur dans l’application, et de mettre en avant sa marque.

On poursuit sur les arguments habituels sur le search et le share.

Guillaume