Trying to create week grouping - Report Studio 8.2 - Help!

[cdndesi]

Hi folks,

A calculation was created beforehand to group 7 days together and create a weekly grouping for a Cognos Report Studio 8.2 report.

Here are the formulas it follows. Both formulas are in Framework Manager 8.2:

WEEK NUMBER

if (CONVERT(VARCHAR(10), ([Physical Layer].[Package].[DateTimeField]), 112)='20100101')
then (1)
else
(
datepart({wk},dateadd({day},-1, [Physical Layer].[Package].[DateTimeField] ) ))

Output: 1 to 53

What "Week Number" does is simply says that week 1 of this report begins on 2010-01-01. However, the physical week begins on December 28, 2009. The next formula creates the weekly range:

WEEK PERIOD

if (convert({VARCHAR(10)},dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) + 1 -6-
         datepart({dw}, dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) ),111)='2009/12/28')

then

('2010/01/01' ||' - ' || convert({VARCHAR(10)},dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) + 1 -
         datepart({dw}, dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) ),111))

else
(
convert({VARCHAR(10)},dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) + 1 -6-
         datepart({dw}, dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) ),111) ||' - ' || convert({VARCHAR(10)},dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) + 1 -
         datepart({dw}, dateadd ({week}, [Package].[Package].[Week Number], dateadd ({year}, 110, 0)) ),111)

\\The statement above says "If the DateTime field is equal to December 28, 2009 then start the week on January 1st, 2010.

\\The week then goes regularly from January 4th to January 10th, January 11th to 17th etc...for the remainder of 2010.

\\Sample Output:
2010/01/01 - 2010/01/03
2010/01/04 - 2010/01/10
2010/01/11 - 2010/01/17
2010/01/18 - 2010/01/24

The problem
The problem is WEEK NUMBER works only up to "53", which corresponds to the last week in 2010.
Week Period output: 2010/12/27 - 2011/01/02

This data is being pulled from a SQL Server 2000 Database.

I need this calculation to work for 2011! Unfortunately this is really racking the brain. Any help you can provide would be highly, highly appreciated!

[Lynn]

Sorry, I must ask the obvious first....do you have a date dimension? If not, can you have one created to store these attributes?

If the answer to both of those questions is "no" then (aside from taking the DBA out for a sound thrashing) you can modify the week number formula to look for month and day regardless of the year. This would give you 1 to 52 or 53 for each year. If you were thinking consecutive numbering instead of re-starting the week count every year you can just use both year and week together which, might achieve the same purpose (or return to my earlier comment about the date dimension).

As for the week period, yikes!  :o

The problem there is trying to identify the latest monday in december as the basis for the condition. This gets me thinking along the lines of day of the week with max functions and then spinning out of control from there.

You might try to reverse your thinking to make it generic. Haven't quite thought this through, but perhaps pseudo-code something like:

Code Select Expand


If ( date is less than 7 days before the first day of the subsequent year )
then
( set the range beginning with the first day of the subsequent year )
else
( regular translation as you have currently )


If it helps any further I feel your pain!

[cdndesi]

I do have a date dimension - but unfortunately the DBA that assigns access me to to write to the database isn't here....so all I have is Cognos FM and Report Studio at my disposal.

And yeah, it's not the DBA...it's a dude who left the company that designed this work of art. Now I know why!!

I think they would be willing to accept a 2nd report starting with 2011. Just trying my hand at that now, will let you guys know how it goes.

Serenity now....

[Lynn]

I was tempted to make a not-so-kind remark about the hardcoding of dates in those expressions, but I didn't want to offend in case it was you who did it originally :)

Glad you have some options available.

[cdndesi]

Am I doing something wrong here?

I am trying to create a couple of different measures for a 2011 report, and have coded it as follows:

Week Number

if (CONVERT(VARCHAR(10), ([Physical Layer].[MEDITECH_EDRS_WEB_MONTHLY_DETAIL].[RegistrationDateTime]), 112)='20101227')
then (1)
else
(
datepart({wk},dateadd({day},-1, [Physical Layer].[MEDITECH_EDRS_WEB_MONTHLY_DETAIL].[RegistrationDateTime] ) ))

Week Period

if (convert({VARCHAR(10)},dateadd ({week}, [Physical Layer].[WeekNo]  , dateadd ({year}, 110, 0)) + 1 -6-
         datepart({dw}, dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) ),111)='2010/12/27')

then
('2010/12/27' ||' - ' || convert({VARCHAR(10)},dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) + 1 -
         datepart({dw}, dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) ),111))

else
(
convert({VARCHAR(10)},dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) + 1 -6-
         datepart({dw}, dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) ),111) ||' - ' || convert({VARCHAR(10)},dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) + 1 -
         datepart({dw}, dateadd ({week}, [Physical Layer].[WeekNo] , dateadd ({year}, 110, 0)) ),111)
)

I'm still coming out with the date results from the previous year. Can anybody help?