Monday, January 26, 2015

Installing Master Data Services Add-in for MS-Excel version 2013

On a PC with an Office 2013 installation, the SQL Server 2012 MDS Add-in for MS-Excel will not install, the installer displays an error message that says Excel 2007 or 2010 is needed. The solution is simple, just download the SP1 version of the Add-in, you can find it here Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Master Data Services Add-in For Microsoft® Excel®

The installation file to download for SP1 has exactly the same name as the version for Excel 2007/2010.

image

After download, run the MSI installation and when the installer has finished, you can see the new ‘Master Data’ item in the Excel menu

image

Hope this helps.

Wednesday, January 14, 2015

Sort date names order in a matrix report based on SSAS Data Source

 

I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.

The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …

image

Problem

The Report designer sorts the columns alphabetically by the name of the month.

Solution

The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.

The solution is quite easy: remove the sort order from the Month Column Group.

image

in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:

image

So that no sorting is set:

image

The report shows the months in chronological order:

image