Kasper de Jonge PowerPivot Blog

My users want their report to default select the last year and month available in the cube, most of the times this wont be the current month as the dataware house is behind. In a previous blog post  I talked about creating date time sets like current year, month, this way we can select the last month and year. I want to use these sets as default value.

I found a litte trick to make selecting the default parameter a walk in the park

I created my report as i would usually and created 2 parameters year and month, thus automaticly creating 2 datasets with all the data from year and month attributes. 

Now here comes the tricky part, i copied the mdx created by visual studio and added a filter using the set i have in my cube and created a new dataset called CurrentMonth. The MDX ends with “ON ROWS FROM [CUBE]“  and you can change it to ”ON ROWS FROM ( SELECT ( [CurrentMonth] ) ON COLUMNS FROM [CUBE])” using the set which returns the current month. This new dataset returns the current month and the All dimension, i excluded the all dimension in a filter on the dataset.

Now you can use the new dataset to set the default value in the parameter by selecting “get values from a query” and selecting the new dataset and as value ParameterValue.

This will enable you to set the default value on default generated parameters.

When you want to want to govern the acces your users have to your data in the SSAS cube’s you have the ability to use Roles to set secutiry on all sorts of levels, from cube level to dimension data.

We want our users to get only acces to different parts of the data, like department managers to only data from sales in their department. When setting security on department levels Autoexists take care of the security on other dimensions and present the reports through reporting services to only show data with the department data selected in the query as well so Autoexists can do its task. This makes usermanagement of roles somewhat easier to maintain.  But users who have acces to the cube can also connect a cube through Excel and thus bypass the dimension Departments where we have put our secutiry, to counter this you can set secutiry on all dimensions you want the user to see or make sure the user can’t connect to the SSAS cube you don’ t want want to connect him to. My collegue Marc Valk  found out you can add a group policy that disables the “get external data”, ” from other sources” tab in excel:

http://www.marcvalk.net/2009/06/gpo-disable-office-ribbon-menu-items/ 

 This makes sure users cannot make a connection themselves but still can open existing connections from e.g. SharePoint.

I live in the Netherlands and our regional settings are different from the US so i want my cube Measures with 1000 sign in . and decimals with ,. To achieve this set the language property from the cube on Dutch and Formatstring from the measure on “#,#.00″  (dont pay attention to the , and . signs used here, he will parse them ok in the client).

Read more at:  http://msdn.microsoft.com/en-us/library/ms175613.aspx

Allan Folting, program manager who works on the Excel team has collected some. Questions customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

1. Filtering individual calculated members
2. Showing non-visual totals when filtering

He have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

1. Adding calculated members/measures
2. Adding named sets

Read the entire article with answers here:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

I have a cube to which report builders connect to make reports with report builder 2.0, since they don’t want to write MDX for complex functions and just use drag and drop to create the reports i decided to create named sets with some default time functions like YTD,  YTD previous year, year difference, current month, previous month and moth difference. This blog post tells the story of that achievement  .

To start with i have a pretty simple time dimension time with a simple hierarchy:

As first step i tried the most simple: current month. My initial thought was to use the LASTCHILD function with the following expression:
[Time].[Month].LASTCHILD
this looked ok, but on closer inspection it seems to return the last Month level, so when your in april (month 4) of 2009 but you have december (12) in your dimension as well it would return 12. After some googling i came up with a new idea, to create a new attribute called yearmonth (combine the 2 in the DSV) and use the LASTCHILD of that field, that worked but i didn’t feel good with me, I left it a while and continued with the rest and found the function ClosingPeriod some time later, this function returns the last member of a time level, perfect . I rewrote the function accordingly:
ClosingPeriod([Time].[Calendar].[Month])
Watch me use the complete hierarchy expression here, one thing that helped me alot understanding all these expression was the debug function of Mosha’s MDX Studio. 

Next up was previous month, the obvious function was to use ParallelPeriod, which eventually formed:
ParallelPeriod ([Time].[Calendar].[Month],1,ClosingPeriod([Time].[Calendar].[Month])).
Which worked great.

now to join these 2 (and use them on the x-axis on a report) i found the function Union:
Union ([Current Month] ,[Previous month])
I tried using crossjoin but the sets use the same dimension and thats not usable in crossjoin)

Now for the year level, Current year to date:
Ah this one is easy using the YTD function, first i used YTD([Time].[year].LASTCHILD) this returned the year in the set but i decided to use the ClosingPeriod again which returns the months of the current year in the set:
YTD(ClosingPeriod([Time].[Calendar].[Month]))

The last on my todo list is YTD from the previous year, this one took the longest to finish and let me to eventually find the ClosingPeriod function which made my life a lot easier

YTD(ParallelPeriod([Time].[Calendar].[Year]
, 1
, ClosingPeriod([Time].[Calendar].[Month]))
Get the current month and go back one year from that, determine the year to date to that month.

Join these tow named sets together to get a year difference, to do that i needed 2 identical sets with the same level, in my first YTD statement i used [Time].[year].LASTCHILD) this function uses the year level while the ytd from previous year uses the month level, these two aren’t joinable. This is the reason i used the ClosingPeriod on the YTD, it now returns a month and will lead the ytd function to return the months of the year.
So now i could use UNION:
Union ([Current year to date],[Previous year to date])

Eventually leading to the following script:

Chris web  just found  a very detailed white paper from Boyan Penev and Mark Dasco about setting up Kerberos for Microsoft BI which he and I thought was worth linking to for future reference:
http://bp-msbi.blogspot.com/2009/04/enhanced-security-and-integration-of.html

Theo Lachev  just keeps those great posts coming ! This post is about hacking the report parameters based on a Cube.

Read the post here: http://prologika.com/CS/blogs/blog/archive/2009/02/25/hacking-mdx-query-designer-parameters.aspx

SQLCat has created a new post about SQL Server 2008, it provides new functionality not found in previous versions and numerous performance and usability enhancements that are specifically designed for business intelligence (BI) solutions. These features are designed to improve performance, increase developer productivity, and enrich the end-user experience. Read the entire report at:
sqlcat.com