This is awesome!
Is this what it will take to step up the game and satisfy the ever growing needs of information hungry end users?
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.
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:
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.
Excellent slide by Matt Masson. http://www.mattmasson.com/2013/03/slides-from-ssis-performance-design-patterns-techdays-hong-kong-2013/
In a conventional T-SQL query, pagination can be applied to retrieve a subset of the total result-set. In the web development, we can use this technique to achieve lazy loading. This post will discuss how to do pagination in MDX to achieve lazy-loading
The key is to use subset function in MDX. MSDN has documented the function here.
But before you use the subset function, you have to know the total number of the result set first to calculate the total page number. The below template illustrate how you get the row count:
you can simply wrap the above MDX into a OpenQuery and assign to a variable.
Once you have the row count then you can decide how you control the pagination
In a web page, the initial load is not so fast considering the row count is extra task. But once you go to page 2,3, etc, you’ll feel the performance is really fast.