SSAS Reporting Action with Date Parameters

Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or month level, you will need to get the start date and end date dynamically. Let’s see how to do that.

Here you can see three reporting actions in a cube in SSAS. Each one handles a different date level. The user will see only one action when clicking in excel.

clip_image001

To ensure the user only sees the proper action at the proper date level you need a conditional statement in the optional condition box. So for the month level the code is:

[Due Date].[Date].CurrentMember.Level is [Due Date].[Date].[Month Name]

clip_image003

The server name is the name of the server where reporting services is running. The report path is the URL where the report is located. This is not the report manager URL. This is the report server URL.

The parameters will be different at each level.

Year Level Start Date Parameter:

URLEscapeFragment(

“01/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key”)

)

The key for the year is the year, so I am able to just concatenate the date together here for the first day of the year. The last of the year works very similar.

Year Level End Date Parameter:

URLEscapeFragment(

“12/31/” +

[Due Date].[Date].CurrentMember.Properties(“Key”)

)

Month Level Start Date Parameter:

URLEscapeFragment(

[Due Date].[Date].CurrentMember.Properties(“Key1”) + “/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)

Month Level End Date Parameter:

URLEscapeFragment(

Cstr(

DateAdd(“d”,-1,

DateAdd(“M”,1,

DateValue(

[Due Date].[Date].CurrentMember.Properties(“Key1”) + “/01/” +

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)))))

The keys for month are year and month. So Key1 is the month and Key0 is the year. This is typical to avoid duplicate key issues in a date hierarchy.

For the Month end date you need to use the dateadd function to go to the first day of the next month, then dateadd again to step back one day. This will give you the last day of the month.

Now for the hard one, Quarter level. The key for quarter is year and quarter. In this you are multiplying the quarter number by three and then subtracting 2 to get the first month in the quarter. The end date for the quarter is the same as the start date with two DateAdd functions to get to the end of the quarter. First you add 3 months then subtract one day.

Quarter Level Start Date Parameter:

URLEscapeFragment(

Cstr(

(Cint(

[Due Date].[Date].CurrentMember.Properties(“Key1”)

) * 3)-2)

+”/01/”+

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)

Quarter Level End Date Parameter:

URLEscapeFragment(

Cstr(

DateAdd(“d”,-1,

DateAdd(“m”,3,

DateValue(

Cstr(

(Cint(

[Due Date].[Date].CurrentMember.Properties(“Key1”)

) * 3)-2

)+”/01/”+

[Due Date].[Date].CurrentMember.Properties(“Key0”)

)))))

Now in excel, when the users right click on a field, they will see the report action for the clicked level. Here are some images of this in action.

Year Level in Excel

clip_image004

Year date ranged passed to report

clip_image005

Quarter Level in Excel

clip_image006

Quarter date ranged passed to report

clip_image007

Month Level in Excel

clip_image008

Month date ranged passed to report

clip_image009

Posted in SSAS, SSRS, Syndication | Tagged , | Leave a comment

SSAS – Using the Euro or Pound currency Symbol in a measure Format String

If you have a set of measures and some of them are US dollars, some are Euros, and some are UK Pounds, you want to format them correctly in SSAS.

First you will need to know the ASCII command to type a pound and euro symbol. You can find this in the character map in windows. You must use the 10 key number pad to type these in while holding down ALT.

Euro = Alt + 0128 €

Pound = Alt + 0163 £

Next you will need to put a back slash in the format string before the symbol for it to show up in excel.

Here are the images from SSAS:

clip_image001

\£ #,##0.00;(\£ #,##0.00)

clip_image002

#,##0.00 \€;(#,##0.00 \€)

Here is the image from Excel:

clip_image003

Posted in SSAS, Syndication | Tagged | 1 Comment

SSAS Dynamic KPI from a table

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically keep the goals up to do by simply updating the table.

The following example uses the adventure works data warehouse. You will need to create a new table. Here you can see one named KPI Goals and the data on it. This table contains the internet sales goal for each year.

image

Next you will need to add this table to the DSV in SSAS and create a relationship between it and the year column on DimDate. You can map the granularity to any attribute like Quarter or Month.

image

Then create a new measure group in the cube using the KPIGoals Table. You can delete the count measure it creates, you only need the internet sales goal.

image

Next you will create the relationship in the cube under the dimension usage tab, set it to a regular relationship on the year attribute.

image

Now you are ready to create a dynamic KPI. Name the KPI Yearly Internet Sales Goal. Use the Internet Sales Amount as the value expression.

image

You want the goal to only show when the user is at the year level when browsing in excel. A Case statement checking the level will accomplish this. We are also using a tuple to get the goal for each year.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Calendar Year]) then

([Due Date].[Date].CurrentMember,[Measures].[Internet Sales Goal])

Else Null

End

image

The Status will check the goal value, if the goal is empty then the status will be null. This will ensure the status only shows on the year level. Then you compare the value to goal and set the numbers to 1, 0, or -1 based on the comparison.

Case

When Isempty(KPIGoal(“Yearly Internet Sales”)) Then Null

When KPIValue(“Yearly Internet Sales”) > KPIGoal(“Yearly Internet Sales”) Then 1

When KPIValue(“Yearly Internet Sales”) = KPIGoal(“Yearly Internet Sales”) Then 0

Else -1

End

image

Now in Excel you will see the KPI status next to each of the years only. The goals are based on the values from the KPI Goals table.

image

Posted in SSAS, Syndication | Tagged | Leave a comment

SSRS Interactive Sorting a Matrix

Sorting a matrix report can be done interactively. To perform interactive sorting, your report you will need a cell to click on to perform the action. In the image below you can see a basic matrix report with State as the row group, with year and quarter as the column groups. You want to be able to interactively sort the report on all of these groups.

image

There is an empty cell above the state and this can be the location for the sorting actions. First you need to create two more cells though, because you want to sort on all groups.

To do this right click on the black cell and select Split cells.

image

This gives you two cells now. You still need one more. Right click on the row at the top and select Insert Row, outside group above.

image

This will give you three cells as seen below.

image

In the blank text boxes you can now type in a descriptive text like “Sort by Year”.

image

To set up the sorting right click on the sort by year text box and select text box properties. Then select interactive sorting. Place a check next to Enable interactive sorting on this text box. Place the radio button next to Groups and select year in the drop down below then click ok.

image

Repeat these same steps for the other cells and your users will be able to sort the report using these cells.

image

Posted in SSRS, Syndication | Tagged | 1 Comment

SSIS For Loop Skip Files

When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load.

For example, I have a set of files named:

Abc.txt
Mno.txt
Rts.txt
Wln.txt
Xyz.txt

If I want to skip the file that starts with “W” then I will need an expression in my For Each Loop to detect this file.

Inside the For Each loop I am going to place a sequence container. This will give me a place to anchor my expression which I will place on the precedence constraint coming from the sequence container. There are no tasks in the sequence container.

clip_image002

On the precedence constraint line I am going to set it to constraint and expression. The expression will be:

substring(Upper(@strFileName),1,1) != “W”

clip_image004

This is looking at the first letter in the filename and comparing it to the letter “W”. I would place the “W” in a variable and use that instead, I am just showing this way for simplicity. Notice I convert the file name variable to upper case and compare it to an uppercase “W”. That way the case will not matter.

Posted in SSIS, Syndication | Tagged | 4 Comments

Setting up Security on sub sites in SharePoint 2010

A lot of companies have several departments and want to limit the sub sites users can view. For example, the IT department should only see the IT sub site, but the HR department may need to be able to see all sub sites. To set up individual security for sub sites you will need to stop the sub sites from inheriting from the parent.

To do this click on the Site Action menu while viewing the sub site, then select Edit in SharePoint Designer.

clip_image001

On the right hand side of the screen you will see the permissions window. Click the button Stop Inheriting.

clip_image003

The button then changes to a new button.

clip_image005

Now click on the new button. This will bring up the Add Permissions window. Enter in the user name or group name in the top text box and click the check user icon on the right. Select the proper group or set up the custom permissions in the bottom section of this window.

clip_image007

Click ok and you have completed adding users to this sub sites. If you have other sub sites you want them to have access to you will need to repeat these steps for each sub site. Any sub sites that you do leave set to inherit from the parent will use the parent permission settings.

If you change your mind and want to set the site back to inherit you can do this with a button at the top of the SharePoint designer. You may need to close the designer and reopen it for it to update.

clip_image008

Posted in SharePoint, Syndication | Tagged | Leave a comment

SSAS – Setting Attributes as Properties for Excel

When a user is browsing your cube with excel they may not understand how to slice the data and end up using things like “first name” as a hierarchy to slice the data. This does not make for good analysis, since grouping all of the people with the same name would not be useful (Unless your business is in analyzing names).

So a better practice is to set the “Attribute Hierarchy Enabled” property of the attributes to false, for attributes you don’t want the user slicing on, like First name, Last name, Email Address, and phone number. Then the user can see the properties in excel without having to do any slicing.

Below are examples using the employee dimension in Adventure Works. You can ignore the parent child relationship on the dimension.

Here is an example of what NOT to do:

In the below image you can see all of the attributes of the dimension are available and there are no hierarchies built. So if an end user wanted to slice by marital status and see the first and last names of the people in the different statuses, they would have to slice by all three of these attributes.

clip_image001

In Excel it would look like this:

clip_image002

clip_image003

This is confusing and is very bad for query performance.

To fix this you will need to build a hierarchy and let the user see the properties of the employee.

To do this:

1. Drag marital status into the hierarchies window in the center of the dimension editor

2. Drop the employee key under martial status

3. Rename the hierarchy to Marital Hierarchy

Now you can disable the attributes you don’t want the user slicing on like Phone, Login ID, Last name, First Name, and Email Address.

To do this:

1. Hold CTRL and click on each of the attributes listed above

2. In the properties window change the Attribute Hierarchy Enable Property to false

clip_image004

The Dimension Structure should look like the image below.

clip_image005

Click on the Attribute relationships tab and it should look like the below image.

clip_image006

Notice the Grey next to the disabled attributes in the bottom window. Now process and deploy the dimension and the cube.

The user will only need to slice by the Marital Hierarchy in Excel as seen in the image below.

clip_image007

The user can slice the information by marital status and then right click to see the properties of the employee as seen in the image below.

clip_image008

The employee Key is showing in this example because the login ID because the name property of the employee key was changed to the login Id.

The end user can now get the properties of each of the employees by right clicking on the employee and selecting “Show Properties in Report” and selecting the information they want to see.

clip_image009

Now selecting the first and last names do not slice the cube by the names and just show the names on the report as seen in the below image.

clip_image010

This should make your queries faster and allow your users to see the information they want without doing and lot of extra slicing.

To give you a deeper understanding of what is occurring here, imagine that you have millions of rows of data on your fact table and you drag out first name like in the example above of what NOT to do. This divides all of the data into groups by the first names. So anyone with the name Mike would be grouped together. Grouping the Mikes together is not useful. The only reason the user is doing this is because they want to see the name once they get down to the employee level.

By changing this behavior in your user and teaching them how to get the properties, your SSAS queries will run much smoother and give your users a better experience.

Posted in SSAS, Syndication | Tagged | 1 Comment