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.
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]
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
Year date ranged passed to report
Quarter Level in Excel
Quarter date ranged passed to report
Month Level in Excel
Month date ranged passed to report
