[Solved] Reporting Week Commencing Fact Data using a standard Date Prompt

[cognosjon]

I'm having some aggro  >:( with a standard date prompt and the data being returned.
The data within my fact table is only detailed as a week commencing date.( Monday of each week).
However the users wish to see a standard date prompt and if they select a date within a week that is not a monday it should still display all data for the current week due to the date they have selected belonging to the same week commencing date.

My calendar file has every date within it and for each date has a column titled week commencing date. So for example

Date               Week Commencing Date
14/03/2011           14/03/2011
15/03/2011           14/03/2011
15/03/2011           14/03/2011
16/03/2011           14/03/2011
17/03/2011           14/03/2011
18/03/2011           14/03/2011
19/03/2011           14/03/2011
20/03/2011           20/03/2011
21/03/2011           20/03/2011

The data element of the data within the Fact table is a week commencing date. Currently the join between the Calendar file and Fact table is between the date field and the week commencing field within the fact table. Therefore I get the problem that if they choose any data other than a Monday they dont return a result for that week.

I don't want to join Week commencing date to Week commencing date due to introducing a many to many relationship.


Any one got any ideas or suggestion?
I've tried a bridging table between the calendar and fact table that detailed simply the Week commencing dates but speed of retrieval became an issue.

Oh and  I'm using DMR for the reporting side.

Thanks

[cognostechie]

Took me quite some time to figure this one but since this was an interesting issue, I wanted to find the solution.

I did this using a Relational package. There is a standard Date Prompt in the Report (Not a Date and Time Prompt) and there is no need for a Calendar Table for this. The name of Prompt is pDate

Use this in the filter:

(_day_of_week (?pDate?,1) = 1 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) ) )
OR
(_day_of_week (?pDate?,1) = 2 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 1 ) )
OR
(_day_of_week (?pDate?,1) = 3 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 2 ) )
OR
(_day_of_week (?pDate?,1) = 4 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 3) )
OR
(_day_of_week (?pDate?,1) = 5 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 4) )
OR
(_day_of_week (?pDate?,1) = 6 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 5) )
OR
(_day_of_week (?pDate?,1) = 7 and [Fact Table].[Date] =
_make_timestamp (cast(substring(?pDate?,1,4),integer),  cast(substring(?pDate?,6,2),integer),  cast(substring(?pDate?,9,2),integer) - 6 ) )

[cognostechie]

Sorry, didn't realise that it will only work when the month is not changed within the week. But, you get the idea !

[cognosjon]

cognostechie your a star!!!!  ;) ;) ;)

I've made use of my calendar table and have based it upon DMR using the following code and it's ploughing along lovely.

I owe you a beer....Thanks for pushing me in the right direction.

Here's the code I've ended up with.


(_day_of_week (?pDate?,1)=1 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=?pDate?)

or

(_day_of_week (?pDate?,1)=2 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=_add_days(?pDate?,-1))

or

(_day_of_week (?pDate?,1)=3 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=_add_days(?pDate?,-2))

or

(_day_of_week (?pDate?,1)=4 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date]>=_add_days(?pDate?,-3))

or

(_day_of_week (?pDate?,1)=5 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=_add_days(?pDate?,-4))

or

(_day_of_week (?pDate?,1)=6 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=_add_days(?pDate?,-5))

or

(_day_of_week (?pDate?,1)=7 and [Testing].[Calendar].[Calendar].[Report Date].[Report Date] >=_add_days(?pDate?,-6))

[cognostechie]

Happy to help and thanks for the nice words !

I have used _add_days function before, don't know why it didn't occur to me. That usually
happens to me when the weather is crappy  ;D