Posts Tagged ‘BI’

Add Microsoft SQL Server Reporting Services charting capability to Tableau

November 10, 2014 3 comments

Tableau is great for its interactivity in dashboard while Microsoft SQL Server Reporting Services (SSRS) is great for its pixel perfect reporting. When we add them together, they are truly awesome!

SSRS has a rich out of box charting library and a few 3rd party add-ons, e.g. Nevron. You can also use .NET drawing library to perform custom drawing in SSRS (Ref: In addition, SSRS is able to use Google Chart API to render chart, such as Venn diagram etc.

ssrs chart types


SSRS has powerful expression language support and tons of properties to precisely control the chart rendering and be able to export to various formats. TIFF, one of the output format, gives us the ability to prepare custom images, which can be used within Tableau as custom shapes. SSRS has another great feature, mighty “Data Driven Subscription” (be aware, SQL Server Enterprise Edition only!). Combining the image format export and data driven subscription, we can automate the Tableau custom shape image generation.

In next blog post, I’ll show you how to add gauge charts into Tableau.

Categories: BI, SSRS, Tableau Tags: , ,

News On SSAS Data Source Support In Power BI

November 7, 2014 Leave a comment

Chris Webb's BI Blog

Yesterday we heard (again) that SSAS will be supported as a data source for cloud-based reports in Power BI. Today, in a session, two new important details on this emerged:

  • It will work with both Tabular and Multidimensional
  • It will connect as the user running the query, so SSAS security (eg dimension security) will work just the same as it does on-premises. No special setup will be needed; there were no details apart from the fact it will work using the EffectiveUserName connection string property.

I’m sure a lot of people will be interested to hear this…

View original post

Floor Map Visualisation

November 6, 2014 3 comments

In a recent POC project, I have to produce the data visualization over custom images, namely, floor maps. I’ll briefly talk about what I did for Excel 2013 and Tableau 8.2.4
Excel 2013

Excel 2013 floor map visulisation

The only option is to use VBA Macro. I know, I know, VBA should be the past and we should avoid using it. However, with Excel 2013, it is the only option. Basically, I insert a image (floor map, PNG format preferred). “Format” ribbon –> “Color” –> “Set to Transparent Color”. Once you got the floor map displayed, “Insert” ribbon –> insert a shape to overlay the shape on top of the floor map. You can also set the shape to be transparent, as shown below. Of course, in the final solution, the transparent level should be controlled via the VBA code.

Excel Format Shape Pane

Excel 2013 allow you to assign Macro to all your shapes and custom images and text box. This is the entry point you can do some very powerful interactive features to your workbook.



Tableau 8.2

Tableau is no doubt the best commercial data visualization software and the overall dashboard design experience is awesome.

Tableau 8.2 floor map visulisation

Tableau 8.2 floor map visulisation

Categories: BI, Excel, Power BI, Tableau Tags: , ,

Filtering and Custom Maps arrive to Power Map with September update

September 10, 2014 Leave a comment
Categories: BI, Power BI Tags: ,

FWD: ERP industry moving from project to process, but what about reporting?

September 4, 2014 Leave a comment

Is this what it will take to step up the game and satisfy the ever growing needs of information hungry end users?

via ERP industry moving from project to process, but what about reporting?.

Categories: BI Tags:

How to Design SSRS Multi-Pie Chart

March 19, 2013 Leave a comment
Multi-Pie Chart in SSRS

Multi-Pie Chart in SSRS

The above is the output of my SSRS report. There is no built-in multi-pie chart in SSRS but it is very easy to design your own.

Multi-Pie Chart Design in SSRS

Multi-Pie Chart Design in SSRS

Basically, you place 4 table controls in the layout design and make sure all 4 are placed in a single rectangle control to make sure the vertical align is OK. All 4 table controls are sharing the same data set. In my case, it is “FY” which returns a single column table with values as 2001-2002, 2002-2003, 2003-2004 etc.And each table control contains a sub report which renders a pie chart. The trick is that I config the “Filters” in the table, shown as below:

SSRS_Multi-Pie Chart_Expression

As you can see, the modulo arithmetic operator enables splitting your data set into 4 sub sets. The 2nd table will have the filter with Value as 2 and 3rd table has value 3 and 4th table has value 4.

I did a quick and dirty way to quickly set it up. In reality, you probably want to check the first data member in your date set as they will impact the order and position of your pie chart. You could wrap the expression with addition IF / Case statement to check the first data member and decide the remainder accordingly. And if your data member does not have numeric values, you can always use row_number() over() to get an ordered numeric value.



Categories: BI, SSRS Tags: ,

FW: Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

March 15, 2013 1 comment
Categories: BI, SQL 2012, SQL Integration Services Tags: ,