Weekly representation of timestamp
hi,
I am using the the date parameter in the report .I now want to divide the timestamps into interval of weeks.How can i do this?
Thanks,
Dipika
June 17th, 2011 9:06am
Hi Dipika,
Here is some code that we use to convert datetime values into weeks. In Reporting Services, under the Report properties, add the following to the Code value:
Public Function ISOWeek(pDate as Date) as String
return "Week " & CStr(DatePart("ww", pDate, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays))
End Function
This uses the ISO Week definition used in Europe where the year is divided into Weeks numbered 1 to 53, and Week 1 starts on a Monday.
You can then call the function ISOWeek in Reporting Services, for example by setting the X-axis label to =ISOWeek(Fields!Timestamp.value), etc.
Here's more info from MSDN about the DatePart function :
http://msdn.microsoft.com/en-us/library/ms127415.aspx
I hope this helps.
Best regards, Christopher Sorensen
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2011 2:55pm
Hi ,
do you want the Date Calender parameter to show in weeks ..rather than months ?
If so its Not Possible in Reporting Services.Rakesh M J
Dont forget to mark it as Answered if found useful
MCTS,MCITP,MCSS
http://mycubeandreports.blogspot.com/
June 20th, 2011 6:25pm
Hi dpkas,
Based on your description, I still need to confirm the scenario, the available values will be displayed like the following way:
2011-1-1
2011-1-8
2011-1-15
…….
If I have misunderstood, please point out and also post an example to show what you want.
Instead of creating a table in the database we may less used, we can use dynamic query in a dataset, which will only create table
at run time and drop it after used:
Declare @WDate Date
Declare @WeeklyDate table(DimDate Date)
Select @WDate='StartTime'
//please supply the beginning date
while (@WDate<='EndTime') //please supply the end date
Begin
Insert into @WeeklyDate
Select @WDate
Set @WDate=DATEADD(DAY,7,@WDate)
End
Select * from @WeeklyDate
Then, bind the dataset mentioned to the date type parameter with the option Get values from a query, to choose the available values
for the parameter.
Any questions, feel free to contact me.
Thanks,
Lola Wang
Please remember to mark the replies as answers if they help.
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2011 7:22pm