Use of Date Range in PPS Reports

Its very common scenario when customers ask us to develop a report based on a particular date range.
Like list of students join between 12th January 2009 and 2nd February 2009.

These kind of reports are easy to make using SQL Server Reporting Services.
But what if we want to use Microsoft Performance Point.

Following are the screen shot and steps require building a report using a date range.

1. Creating a simple report (in screen shot I am taking [Time].[Year - Half Year - Quarter - Month - Date].[Date] on the Y-axis so that we can see that filters are working properly.)





2. On the Query tab adding two parameter Range1 and Range2




3. Editing MDX as shown in Screenshot



Changing the [Time].[Fiscal Day].Allmembers to [Time].[Fiscal Day].[Fiscal Day] so that we don’t have column named “all” in the report.
Inserting where clause -
WHERE {<> :<>}

4. setting default values for the parameter Range1 and Range2



Here I am giving [Time].[Date].&[2008-05-21T00:00:00] as default value for one of the parameter.
The reason of taking different attributes in parameter and on Y-axis is that for same attribute it won’t work.

5. Now on the Design tab we can see that it is showing only those columns which comes under Date Range.

sss

6. Now creating two filters named From and To in dashboard, Filters are member selection filter based on [Time].[Date]




7. Linking the reports to the filter. Remember that’s Dashboard item end point for from is Range1 and for To is Range2. Source value should be Member Unique Name.




8. Publish dashboard and Preview.




In preview we can see that it is showing data between a date ranges.


But taking Tree or Drop down Box is not useful as its not have ease of use.
We can even use calendar instead of it.



For using calendar in filters. We have to use Time Intelligence Post Formula Filter.

For using any Time intelligence filter you have to configure the time settings of data source.
Go to data Source --> Time Tab ... See the screen shot.






Configure it in a way it is showing in Screen shot.

(For more information on configuring time Intelligence Filters Please refer http://office.microsoft.com/en-us/help/HA102408431033.aspx )


After configuring data source. We create two Time intelligence Filter From and To.
Then link the filter as per the screen shot



After that click on the Filter Link Formula…



And insert Formula by typing “Day“.

Formula “Day” means we want day level data. Other options like Month, Quarter can be used.

After it publish and Preview it.




In the Screen shot you can see that how can a calendar control works.






Thanks,
Gaurav Gupta

Using Script Component Instead of Cursor

There are many scenarios where we use cursor in our DW building logic.
But as all we know that cursor is nothing but a curse for a developer as its impact on performance is huge.
We always try to avoid it. But cant refused to use it.

Here I am taking a scenario which I faced in a project.

Supposed you have to dates like Order date and Ship Date and you have to count the no of days between order and shipment.
Yes you can simply use DateDiff Function,
But the problem with this is like If someone want to see the no of days on the date which is in between Order and Shipment date. Then??

For cases like these where you need to insert dates in between two dates I used cursor.
But the performance of cursor was too bad.

Then I thought of alternative approach …which brought me to the Script Component.
Yes I tried Script Task instead of Cursor and I found it very fast and well resource utilized.


Following is the script you can use:-

Here I am taking Script Component as Source.
Output records can be stored in any table by using any of the SSIS destination components.

Imports System

Imports System
Imports System.Data
Imports System.DateTime
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.String
Imports System.Text


Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim dateinsrt As DateTime
Dim loopend As DateTime
Dim Sqlconn As SqlConnection
Dim Sqlcmd As SqlCommand


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim connectionString As String
connMgr = Me.Connections.STGConnection
Sqlconn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub

Public Overrides Sub CreateNewOutputRows()
Sqlcmd = New SqlCommand("SELECT SalesOrderID,OrderDate,ShipDate FROM Sales.SalesOrderHeader", Sqlconn)
Dim reader As SqlDataReader
reader = Sqlcmd.ExecuteReader()
Do While reader.Read
With MyOutputBuffer
dateinsrt = CDate(reader.Item(1))
If IsDBNull(reader.Item(2)) Then 'In Case if shipment date is NUll ie not yet shipped
loopend = Today()
Else
loopend = CDate(reader.Item(2))
End If
While dateinsrt <= loopend  'loop period 
.AddRow()
'Output Records
.SalesOrderID = CInt(reader.Item(0))
.OrderDate = CDate(reader.Item(1))
.ShipDate = CDate(reader.Item(2))
.NoOfDays = 1
.DateKey = dateinsrt
dateinsrt = dateinsrt.AddDays(1) 'Adding days
End While
End With
Loop
reader.Close()
End Sub
End Class

Your Feedbacks are valuable ............ :-)


Thanks,
Gaurav Gupta