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.
OpenQuery has query string length limit to varchar(8000) or nvarchar(4000)
To overcome this limit you can use the below query:
DECLARE @mdx AS VARCHAR(MAX) SET @mdx=' <Your MDX longer than varchar(8000)> ' SELECT len(@mdx) AS LENGTH_OF_MDX Exec (@mdx) At <Your Linked Server>;
to use dynamic SQL, you can use the below tempalte:
BEGIN TRY DECLARE @SQL nvarchar(max) SET @SQL = 'Exec (''' + @MDX + ''') At ' + @CubeLocation EXEC sp_executesql @SQL END TRY BEGIN CATCH SET @ErrorMessage = 'Query execution failed!' RAISERROR(@ErrorMessage , 16, 1) WITH NOWAIT END CATCH
However, although the above can bypass the varchar(8000) limit, it brings another side issue. If you MDX returns a empty set, the SQL Server connection will be dropped. And even worse, this error cannot be captured by Try Catch block. Hence, if you need to implement this logic in your SQL stored procedure, you need to implemented MDX Count function to avoid returning no result set. For instance, count the result-set first and if count = 0, there will be no need to go ahead with the query. Here is the code sample for a function to return the row count of your MDX result set:
</pre> WITH SET myset AS <Your SET Definition> Member [Measures].[cnt] AS myset.count SELECT [Measures].[cnt] ON columns FROM <Your Cube> WHERE ( <Your Slicers> ) <pre>
You can then wrap the above query in dynamic SQL which uses OpenQuery and linked server.
OpenQuery + Linked Server is commonly used if you want to querying against SSAS cube through T-SQL. However, you cannot use “top N” to specify how many records you want to retrieve. try SELECT top 1 1 AS id, * FROM OPENQUERY(<Linked Server>,<Your MDX Query>) yourself and you’ll find all records are returned, which means the top 1 is ignored.
However, if you try the following:
Select * from ( Select top 1000 ROW_NUMBER() over(order by id) as RowNum, t.* from ( SELECT top 1 1 AS id, * FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t ) as ds
You’ll get top 1000 rows if the whole result set contains more.
Now to achieve fake (what I mean by fake is that it is not true pagination at SSAS side) pagination, you do sth like:
DECLARE @start AS INT ,@end AS INT set @start = 1 set @end = 20 Select * from ( Select top 1000 ROW_NUMBER() over(order by id) as RowNum, t.* from ( SELECT top 1 1 AS id, * FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t ) as ds where ds.RowNum between @start and @end Go