October 25, 2012 at 9:35 am
Firstly, this is my first post so hi everyone... /wave
Please excuse my explination of the problem if it's not clear enough, as I'm pretty new to reporting services.
I've got a report set up to pull weekly data based on a parameter passed as week number, which is taken from the calendar picker.
Our warehousing database uses a Date Dim Table that has a different week number to that of Reporting Services. So when I pass the date parameter to the query, I'm getting last weeks data instead of this weeks.
query has a strtoset(@variable) statement, and the following is passed to it:
="[Date Dim].[Week Of Year].&[" & year(CDate(Parameters!DateDimDate.Value)) & "]&[" & DatePart("ww", Parameters!DateDimDate.Value) & "]"
Is there an easy method of matching the data without editing the Date Dim table? The obvious answer would be to -1 from the week in Reporting Services, but I believe this will not be accurate at all times.
Many thanks in advance.
Gareth.
October 25, 2012 at 11:22 am
It concerns me a bit that you have 2 different week numbers. Personally I would be inclined to change one of them. The business must have definitions for week numbers, you should follow those.
Short of modifiying your data I wonder if you can pass a date or a first day of week date and have the week number fall into line behind that in the actual query.
October 26, 2012 at 1:29 am
Daniel Bowlin (10/25/2012)
It concerns me a bit that you have 2 different week numbers. Personally I would be inclined to change one of them. The business must have definitions for week numbers, you should follow those.Short of modifiying your data I wonder if you can pass a date or a first day of week date and have the week number fall into line behind that in the actual query.
Hi Daniel,
I believe it's partly to do with the starting day of the week. Reporting services starts the week on a Sunday, where as the business starts the week on a Monday. I also know that it changes when the there's a split week at the end of the year.
I have limited knowlege with the queries, but the issue with passing a date is that, yes I do get the week number, but I also only get 1 day's worth of data, by passing week number I returned the data for the full week.
I thought of passing month, but if the first is a Sunday the week would be out again. Pulling a year just seems like overkill.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply