Monday, April 12, 2010

Overcoming Calendar Limitations in PerformancePoint Planning

Some financial analysts may require to be able to present their budget and forecasts in both fiscal and calendar years. This becomes somewhat of a design challenge when it comes to PPS Planning since the application is set to handle only one Time dimension which is built at the onset of the application’s creation. Moreover, once generated, the time dimension that was created using the “calendar” wizard, is then very hard if not impossible to change.

Nevertheless, if a company does have a valid business reason to plan their budget using the calendar as well as the fiscal year, or important business requirements could not be accommodated using the existing application-generated time dimension, then the PPS Planning developer could consider the design solution suggested below.

In this example, the initial time dimension was built based on a fiscal calendar with July as the first month. However, the business user’s requirements are to create an alternative hierarchy where the months would be arranged in the order suggested below (a calendar year). Using the existing application’s Fiscal Calendar, this is not an easy task to be achieved. Therefore, we need to create a new dimension with the Calendar specification below using SSAS.

Using SSAS the following settings had been applied to create the additional time dimension for the budget application:

The figure below shows the relationship configured between time dimension (created using SSAS) and measure Group Tables in the PPS Planning application database.

Tuesday, April 6, 2010

URL Encoding in Reporting Services

Handling Special Characters Using HttpUtility.URLEncode()

By Dan Meyers

Reporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action type. For example, you can embed some javascript into the link so that the report opens up in a new window that is a specific size in a specific location on the screen. You cannot do this using the Go To Report action type.

Of course there are disadvantages too. When using the Go To URL action type you have to manually handle any special characters in you URL string. This is something that the Go To Report action type handles for you when it comes to the parameter values passed to the report. In my opinion, the advantages outweigh the disadvantages. So I always use the Go To URL action type and manually handle the special characters using the HttpUtility.URLEncode() function.

Before we can use the the HttpUtility.URLEncode() function we have to first add a reference to System.Web assembly.

Go to Report –> Properties –> References. Click [Add].


Scroll down a select ‘System.Web’ in the list and click [OK].


You should now see that the reference has been added.


Now that we have added the reference to System.Web, we now have to create a function in the report’s Code window that uses the URLEncode() function. Click on ‘Code’ in the left panel and enter the code below.

Public Function URLEncode (ByVal inURL As String) As String
Dim outURL As String
outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString
Return outURL
End Function


We can now use our new report function in an expression anywhere in our report.


As you can see in the screenshot below, the function will convert any special charters and encode the URL. In this example, I am passing in a string for a MDX member for Barnes & Noble. You can see the before and after values below.


A Common Mistake
One common mistake that many make when taking this approach is that they try to use the HttpUtility.URLEncode() function directly from an expression. The expression will basically ignore the reference and not work. You have to use the function in the report’s Code window and then reference the report’s function in the expression for it to work.