Week Startdate and enddate by Week number in MDX

Question

Hi,

I have a cube with the Server Time Dimension. Our client wants a report that will give the measure values week by week. When I started the development of the report, In the Dropdown I have to give the Week Numbers to select, So that By those weeks we will pull the data.

In the label part of the report parameter (the Dropdown Values), I have given the Week numbers Ex: Week 15.
 But they cannot understand the what is the month or what is the daterange that weeknumber is belongs to.. So I tried to display the Report parameter label as Week Number - Startdate of the Week - End Date of the week
Ex: Week 15- 04/05/2009 -04/11/2009

I have tried so many ways but i didnt get the script can anyone please give me solution for this.

Answer 1

Hi Bharadwaj,

Try this... week dropdown in the form of [week#] [week start date] [week end date]

Public

 

Function findWeek(ByVal year As String) As ArrayList

 

Dim sDate As Date = CDate("01/01/" & year.Trim)

 

Dim arr As New ArrayList()

 

Dim yr As Integer = sDate.Year

 

Dim days As Integer = sDate.DayOfWeek()

sDate = sDate.AddDays(-days + 1)

 

Dim week As Integer = 1

 

While sDate.Year <> yr + 1

 

Dim strDate As String = ("[" & week.ToString() & "] [") & Convert.ToString(((sDate.Day & "-") & (sDate.[Date].ToString("dd-MMM-yyyy").Split("-"))(1) & "-") & sDate.Year) & "]"

sDate = sDate.AddDays(6)

strDate = (strDate &

" to " & "[") & Convert.ToString(((sDate.Day & "-") & (sDate.ToString("dd-MMM-yyyy").Split("-"))(1) & "-") & sDate.Year) & "]"

arr.Add(strDate)

sDate = sDate.AddDays(1)

week += 1

 

End While

 

Return arr

 

End Function