Hi Ye All, I need the user to provide a time period of weeks for my report. That's easy; Parameter @DateFrom and @DateTo. The default for @DateFrom = Monday of next week and for @DateTo = Sunday of next week. As a matter of fact, the user needs to be able to provide more weeks that the default next week. Here my problem arises. In the DatePicker of the Report Manager there is no weeknumber, so how do I provide the user to be able to pick a particular week? One way I tried is another parameter set @WeekFrom and @WeekTo containing ww-yyyy (week of year and year of the week of year). However if the user alters the @WeekFrom or @WeekTo, the @DateFrom or @DateTo should also be redefaulted. This however seems not be possible any more in Sql 2008 R2, where some support was in Sql 2003. If I change the @WeekFrom in my example, the @DateFrom is not changed. One would expect, since the default value of @DateFrom depends on @WeekFrom, the default would be recalculated, but it is not. What am I doing wrong, or how can I make the user able to select a specific week instead of the date of the first/last day of a week? Help would be appriciated. JosI'll be back

Josje, Assign the default values expression to Available values too. And check your report. Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Hi Josje, For furhter assistance, Could you please elaborate your issue with an example and your parameter configuration steps/expressions?Please remember to mark the replies as answers if they help.

Hi Lola, I can elaborate, however as a cannot attach anything, like tables or stored procedures, it is a bit hard to gather the proper coding. Basically I have 4 parameters: @WeekVanaf, @WeekTotMet, @DatVanaf, @DatTotMet. @DatVanaf, @DatTotMet = datatype datetime, @WeekVanaf, @WeekTotMet = datatype int with values yyyyww (yearweeknr) like WeekNr in ICalendar. @WeekVanaf default = FirstDayOfWeek(GetDate()) = 1st Monday of next week. Available values all Mondays from ICalendar. @WeekTotMet default and available values = all Sundays from ICalendar Where WeekNr >= @WeekVanaf. Now the problem is, that say next week is 201137 and then by default @WeekVanaf/TotMet = 201137, then if you select say @WeekVanaf 201135, @WeekTotMet will still be 201137 instead of 201135, because the default value has not being recalculated. Hope I am clear enough, Jos Here is table ICalendar: Datum Jaar Week Dag WeekNr 2011-01-03 2011 1 ma 201101 2011-01-10 2011 2 ma 201102 2011-01-17 2011 3 ma 201103 2011-01-24 2011 4 ma 201104 2011-01-31 2011 5 ma 201105 2011-02-07 2011 6 ma 201106 2011-02-14 2011 7 ma 201107 2011-02-21 2011 8 ma 201108 2011-02-28 2011 9 ma 201109 2011-03-07 2011 10 ma 201110 2011-03-14 2011 11 ma 201111 2011-03-21 2011 12 ma 201112 2011-03-28 2011 13 ma 201113 2011-04-04 2011 14 ma 201114 2011-04-11 2011 15 ma 201115 2011-04-18 2011 16 ma 201116 2011-04-25 2011 17 ma 201117 2011-05-02 2011 18 ma 201118 2011-05-09 2011 19 ma 201119 2011-05-16 2011 20 ma 201120 2011-05-23 2011 21 ma 201121 2011-05-30 2011 22 ma 201122 2011-06-06 2011 23 ma 201123 2011-06-13 2011 24 ma 201124 2011-06-20 2011 25 ma 201125 2011-06-27 2011 26 ma 201126 2011-07-04 2011 27 ma 201127 2011-07-11 2011 28 ma 201128 2011-07-18 2011 29 ma 201129 2011-07-25 2011 30 ma 201130 2011-08-01 2011 31 ma 201131 2011-08-08 2011 32 ma 201132 2011-08-15 2011 33 ma 201133 2011-08-22 2011 34 ma 201134 2011-08-29 2011 35 ma 201135 2011-09-05 2011 36 ma 201136 2011-09-12 2011 37 ma 201137 2011-09-19 2011 38 ma 201138 2011-09-26 2011 39 ma 201139 2011-10-03 2011 40 ma 201140 2011-10-10 2011 41 ma 201141 2011-10-17 2011 42 ma 201142 2011-10-24 2011 43 ma 201143 2011-10-31 2011 44 ma 201144 2011-11-07 2011 45 ma 201145 2011-11-14 2011 46 ma 201146 2011-11-21 2011 47 ma 201147 2011-11-28 2011 48 ma 201148 2011-12-05 2011 49 ma 201149 2011-12-12 2011 50 ma 201150 2011-12-19 2011 51 ma 201151 2011-12-26 2011 52 ma 201152 These are the datasets in the report: <DataSet Name="Datums"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <CommandText>SELECT DATEADD(Day, 7, Datum) AS BeginVolgendeWeek, DATEADD(Day, 13, Datum) AS EindeVolgendeWeek, dbo.GetWeekNr(Datum) AS WeekNr FROM ICalendar WHERE (Datum = dbo.FirstOfWeek(GETDATE()))</CommandText> </Query> <Fields> <Field Name="BeginVolgendeWeek"> <DataField>BeginVolgendeWeek</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="EindeVolgendeWeek"> <DataField>EindeVolgendeWeek</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="WeekNr"> <DataField>WeekNr</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WekenVanaf"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <CommandText>SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'ma')</CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="Week"> <DataField>Week</DataField> <rd:TypeName>System.Byte</rd:TypeName> </Field> <Field Name="Jaar"> <DataField>Jaar</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="WeekNr"> <DataField>WeekNr</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Dag"> <DataField>Dag</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="WeekDatum"> <DataField>WeekDatum</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WekenTotMet"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekVanaf"> <Value>=Parameters!WeekVanaf.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo') AND (WeekNr &gt;= @WeekVanaf)</CommandText> </Query> <Fields> <Field Name="Jaar"> <DataField>Jaar</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Week"> <DataField>Week</DataField> <rd:TypeName>System.Byte</rd:TypeName> </Field> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="WeekNr"> <DataField>WeekNr</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Dag"> <DataField>Dag</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="WeekDatum"> <DataField>WeekDatum</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WeekVanaf"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekVanaf"> <Value>=Parameters!WeekVanaf.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum FROM ICalendar WHERE (WeekNr = @WeekVanaf) AND (Dag = 'ma')</CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WeekTotMet"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekTotMet"> <Value>=Parameters!WeekTotMet.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum FROM ICalendar WHERE (WeekNr = @WeekTotMet) AND (Dag = 'zo')</CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> </DataSet> These are the report parameters <ReportParameter Name="WeekVanaf"> <DataType>Integer</DataType> <DefaultValue> <DataSetReference> <DataSetName>Datums</DataSetName> <ValueField>WeekNr</ValueField> </DataSetReference> </DefaultValue> <Prompt>Week Vanaf</Prompt> <ValidValues> <DataSetReference> <DataSetName>WekenVanaf</DataSetName> <ValueField>WeekNr</ValueField> <LabelField>WeekDatum</LabelField> </DataSetReference> </ValidValues> <UsedInQuery>True</UsedInQuery> </ReportParameter> <ReportParameter Name="WeekTotMet"> <DataType>Integer</DataType> <DefaultValue> <DataSetReference> <DataSetName>WekenTotMet</DataSetName> <ValueField>WeekNr</ValueField> </DataSetReference> </DefaultValue> <Prompt>Week TotMet</Prompt> <ValidValues> <DataSetReference> <DataSetName>WekenTotMet</DataSetName> <ValueField>WeekNr</ValueField> <LabelField>WeekDatum</LabelField> </DataSetReference> </ValidValues> <UsedInQuery>True</UsedInQuery> </ReportParameter> <ReportParameter Name="DatVanaf"> <DataType>DateTime</DataType> <DefaultValue> <DataSetReference> <DataSetName>WeekVanaf</DataSetName> <ValueField>Datum</ValueField> </DataSetReference> </DefaultValue> <Prompt>DatVanaf</Prompt> <Hidden>true</Hidden> <ValidValues> <DataSetReference> <DataSetName>WeekVanaf</DataSetName> <ValueField>Datum</ValueField> <LabelField>Datum</LabelField> </DataSetReference> </ValidValues> <UsedInQuery>True</UsedInQuery> </ReportParameter> <ReportParameter Name="DatTotMet"> <DataType>DateTime</DataType> <DefaultValue> <DataSetReference> <DataSetName>WeekTotMet</DataSetName> <ValueField>Datum</ValueField> </DataSetReference> </DefaultValue> <Prompt>DatTotMet</Prompt> <Hidden>true</Hidden> <ValidValues> <DataSetReference> <DataSetName>WeekTotMet</DataSetName> <ValueField>Datum</ValueField> <LabelField>Datum</LabelField> </DataSetReference> </ValidValues> <UsedInQuery>True</UsedInQuery> </ReportParameter> I'll be back

Hi Josje, I noticed that there might be something wrong in your dataset. WekenTotMet SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo') AND (WeekNr &gt; = @WeekVanaf) Could you please delete &gt; in the where statement? Then, it will be WHERE(Dag = 'zo') AND (WeekNr = @WeekVanaf) . If it still cannot work for you, could you please paste your user-defined function or procedure structure (dbo.EditWeekDatum (),dbo.GetWeekNr (),dbo.FirstOfWeek ()) here? Thanks, Lola Please remember to mark the replies as answers if they help.

Hi Josje, I noticed that there might be something wrong in your dataset. WekenTotMet SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo') AND (WeekNr &gt; = @WeekVanaf) Could you please delete &gt; in the where statement? Then, it will be WHERE(Dag = 'zo') AND (WeekNr = @WeekVanaf) . If it still cannot work for you, could you please paste your user-defined function or procedure structure (dbo.EditWeekDatum (),dbo.GetWeekNr (),dbo.FirstOfWeek ()) here? Thanks, Lola Please remember to mark the replies as answers if they help.

Hi Lola, The &gt;= is intentional, because the user must be able to select multiple weeks. dbo.EditWeekDatum: USE [Baanstede_Test] GO ALTER FUNCTION [dbo].[EditWeekDatum] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns VarChar(Max) As Begin Declare @EditWeekDatum As VarChar(Max) Return dbo.EditWeekNr(@Datum) + ', ' + dbo.EditDagDatum(@Datum) End Go Declare @Datum As DateTime Set DateFirst 1 Set @Datum = GetDate() Select @Datum As Vandaag , dbo.EditWeekDatum(@Datum) As WeekNr Set @Datum = dbo.FirstOfYear(GETDATE()) Select @Datum As EenJan , dbo.EditWeekDatum(@Datum) As WeekNr Vandaag WeekNr 2011-09-16 09:57:17.417 37-2011, do 16-09-2011 EenJan WeekNr 2011-01-01 00:00:00.000 52-2010, vr 01-01-2011 ALTER FUNCTION [dbo].[EditWeekNr] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns VarChar(Max) As Begin Declare @EdWeekNr As VarChar(Max) Set @EdWeekNr = Cast(dbo.GetWeekNr(@Datum) As VarChar(Max)) Declare @Week As Int , @Jaar As Int Set @Week = Cast(SubString(@EdWeekNr, 5, 2) As Int) Set @Jaar = Cast(SubString(@EdWeekNr, 1, 4) As Int) Return Cast(@Week As VarChar(Max)) + '-' + Cast(@Jaar As VarChar(Max)) End ALTER FUNCTION [dbo].[GetWeekNr] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns Int As Begin Declare @Week As Int , @Jaar As Int Set @Jaar = dbo.F_ISO_YEAR_OF_WEEK(@Datum) Set @Week = dbo.F_ISO_WEEK_OF_YEAR(@Datum) Return @Jaar * 100 + @Week End ALTER Function [dbo].[F_ISO_WEEK_OF_YEAR] /* Function F_ISO_WEEK_OF_YEAR returns the ISO 8601 week of the year for the date passed. */ ( @Date datetime ) Returns Int As Begin Declare @WeekOfYear int Select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @WeekOfYear = (datediff(dd, -- Case finds start of year case when NextYrStart <= @date then NextYrStart when CurrYrStart <= @date then CurrYrStart else PriorYrStart end,@date)/7)+1 From ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4 = dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a Return @WeekOfYear end ALTER Function [dbo].[F_ISO_YEAR_OF_WEEK] /* Function F_ISO_YEAR_OF_WEEK returns the ISO 8601 year of the week for the date passed. */ ( @Date DateTime ) Returns Int As Begin Declare @YearOfWeek Int Select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @YearOfWeek = (datepart(yy, -- Case finds start of year case when NextYrStart <= @date then NextYrStart when CurrYrStart <= @date then CurrYrStart when Jan1PriorYr <= @Date Then Jan1PriorYr else PriorYrStart end)) From ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of prior year Jan1PriorYr = dateadd(yy,datediff(yy,0,@date) -1,0), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4 = dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a Return @YearOfWeek End USE [Baanstede_Test] GO ALTER FUNCTION [dbo].[FirstOfWeek] --====================================== -- Input: een willekeurige @Datum -- Output: 1e maandag van de week tov @Datum -- in DateTime formaat met Time-deel = 0 -- Auteur: Jos vd Vlis, Baanstede -- Datum: 18 juli 2008 --====================================== ( @Datum DateTime ) Returns DateTime As Begin Declare @WeekDay Int , @Wk Int , @Ma DateTime -- @WeekDay = volgorde nr dag (2=ma, 3=di, ..., 7=za, 1=zo) Set @Weekday = DatePart(WeekDay, @Datum) -- @wk = aantal verschildagen tov maandag If @WeekDay = 1 -- Zondag? Set @Wk = -6 -- Dan nog vorige week Else Set @Wk = 2 - @WeekDay -- @Dt = 1e maandag tov @Datum Set @Ma = @Datum + @Wk -- Return @Dt zonder Time deel (wordt 0) Return dbo.EdDat(@Ma) End ALTER FUNCTION [dbo].[EdDat] -- ============================================= -- Author: Jos van der Vlis -- Create date: 20-02-2009 -- Description: @Datum van een DateTime veld -- -> @EdDat (zonder tijdsdeel) -- ============================================= ( @Datum As DateTime ) RETURNS Date AS BEGIN -- Lege (lage) Datum? If IsNull(@Datum, 0) <= '1/1/1900' Set @Datum = '1/1/1900' Else -- Of Lege (hoge) datum? If @Datum >= '9/9/2999' Set @Datum = '9/9/2999' RETURN @Datum END I'll be back

Hi Lola, The &gt;= is intentional, because the user must be able to select multiple weeks. dbo.EditWeekDatum: USE [Baanstede_Test] GO ALTER FUNCTION [dbo].[EditWeekDatum] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns VarChar(Max) As Begin Declare @EditWeekDatum As VarChar(Max) Return dbo.EditWeekNr(@Datum) + ', ' + dbo.EditDagDatum(@Datum) End Go Declare @Datum As DateTime Set DateFirst 1 Set @Datum = GetDate() Select @Datum As Vandaag , dbo.EditWeekDatum(@Datum) As WeekNr Set @Datum = dbo.FirstOfYear(GETDATE()) Select @Datum As EenJan , dbo.EditWeekDatum(@Datum) As WeekNr Vandaag WeekNr 2011-09-16 09:57:17.417 37-2011, do 16-09-2011 EenJan WeekNr 2011-01-01 00:00:00.000 52-2010, vr 01-01-2011 ALTER FUNCTION [dbo].[EditWeekNr] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns VarChar(Max) As Begin Declare @EdWeekNr As VarChar(Max) Set @EdWeekNr = Cast(dbo.GetWeekNr(@Datum) As VarChar(Max)) Declare @Week As Int , @Jaar As Int Set @Week = Cast(SubString(@EdWeekNr, 5, 2) As Int) Set @Jaar = Cast(SubString(@EdWeekNr, 1, 4) As Int) Return Cast(@Week As VarChar(Max)) + '-' + Cast(@Jaar As VarChar(Max)) End ALTER FUNCTION [dbo].[GetWeekNr] --=============================================================== -- Input: een willekeurige @Datum -- Output: Week + '-' + Jaar van @Datum -- Auteur: Jos vd Vlis, Baanstede -- Datum: 8 sep 2011 --=============================================================== ( @Datum As DateTime ) Returns Int As Begin Declare @Week As Int , @Jaar As Int Set @Jaar = dbo.F_ISO_YEAR_OF_WEEK(@Datum) Set @Week = dbo.F_ISO_WEEK_OF_YEAR(@Datum) Return @Jaar * 100 + @Week End ALTER Function [dbo].[F_ISO_WEEK_OF_YEAR] /* Function F_ISO_WEEK_OF_YEAR returns the ISO 8601 week of the year for the date passed. */ ( @Date datetime ) Returns Int As Begin Declare @WeekOfYear int Select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @WeekOfYear = (datediff(dd, -- Case finds start of year case when NextYrStart <= @date then NextYrStart when CurrYrStart <= @date then CurrYrStart else PriorYrStart end,@date)/7)+1 From ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4 = dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a Return @WeekOfYear end ALTER Function [dbo].[F_ISO_YEAR_OF_WEEK] /* Function F_ISO_YEAR_OF_WEEK returns the ISO 8601 year of the week for the date passed. */ ( @Date DateTime ) Returns Int As Begin Declare @YearOfWeek Int Select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @YearOfWeek = (datepart(yy, -- Case finds start of year case when NextYrStart <= @date then NextYrStart when CurrYrStart <= @date then CurrYrStart when Jan1PriorYr <= @Date Then Jan1PriorYr else PriorYrStart end)) From ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of prior year Jan1PriorYr = dateadd(yy,datediff(yy,0,@date) -1,0), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4 = dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a Return @YearOfWeek End USE [Baanstede_Test] GO ALTER FUNCTION [dbo].[FirstOfWeek] --====================================== -- Input: een willekeurige @Datum -- Output: 1e maandag van de week tov @Datum -- in DateTime formaat met Time-deel = 0 -- Auteur: Jos vd Vlis, Baanstede -- Datum: 18 juli 2008 --====================================== ( @Datum DateTime ) Returns DateTime As Begin Declare @WeekDay Int , @Wk Int , @Ma DateTime -- @WeekDay = volgorde nr dag (2=ma, 3=di, ..., 7=za, 1=zo) Set @Weekday = DatePart(WeekDay, @Datum) -- @wk = aantal verschildagen tov maandag If @WeekDay = 1 -- Zondag? Set @Wk = -6 -- Dan nog vorige week Else Set @Wk = 2 - @WeekDay -- @Dt = 1e maandag tov @Datum Set @Ma = @Datum + @Wk -- Return @Dt zonder Time deel (wordt 0) Return dbo.EdDat(@Ma) End ALTER FUNCTION [dbo].[EdDat] -- ============================================= -- Author: Jos van der Vlis -- Create date: 20-02-2009 -- Description: @Datum van een DateTime veld -- -> @EdDat (zonder tijdsdeel) -- ============================================= ( @Datum As DateTime ) RETURNS Date AS BEGIN -- Lege (lage) Datum? If IsNull(@Datum, 0) <= '1/1/1900' Set @Datum = '1/1/1900' Else -- Of Lege (hoge) datum? If @Datum >= '9/9/2999' Set @Datum = '9/9/2999' RETURN @Datum END I'll be back

Hi Josje, Could you please change the where clause use IN operator to select multiple weeks. SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo' )AND (WeekNr In (@WeekVanaf)) Thanks, LolaPlease remember to mark the replies as answers if they help.

Hi Josje, Could you please change the where clause use IN operator to select multiple weeks. SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo' )AND (WeekNr In (@WeekVanaf)) Thanks, LolaPlease remember to mark the replies as answers if they help.

Hi Lola, I see you are trying to get a work around. I have made a work around, by switching the order of the parameters to: @WeekTotMet, @WeekVanaf and altering the datasets: <DataSet Name="WekenVanaf"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekTotMet"> <Value>=Parameters!WeekTotMet.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'ma') AND (<strong>WeekNr &lt;= @WeekTotMet</strong>) <strong>ORDER BY Datum DESC</strong></CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="Week"> <DataField>Week</DataField> <rd:TypeName>System.Byte</rd:TypeName> </Field> <Field Name="Jaar"> <DataField>Jaar</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="WeekNr"> <DataField>WeekNr</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Dag"> <DataField>Dag</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="WeekDatum"> <DataField>WeekDatum</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WekenTotMet"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <CommandText>SELECT Datum, Jaar, Week, Dag, WeekNr, dbo.EditWeekDatum(Datum) AS WeekDatum FROM ICalendar WHERE (Dag = 'zo') <strong>ORDER BY Datum DESC</strong></CommandText> </Query> <Fields> <Field Name="Jaar"> <DataField>Jaar</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Week"> <DataField>Week</DataField> <rd:TypeName>System.Byte</rd:TypeName> </Field> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="WeekNr"> <DataField>WeekNr</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Dag"> <DataField>Dag</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="WeekDatum"> <DataField>WeekDatum</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WeekVanaf"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekVanaf"> <Value>=Parameters!WeekVanaf.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum FROM ICalendar WHERE (WeekNr = @WeekVanaf) AND (Dag = 'ma')</CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="WeekTotMet"> <Query> <DataSourceName>Baanstede_Test</DataSourceName> <QueryParameters> <QueryParameter Name="@WeekTotMet"> <Value>=Parameters!WeekTotMet.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Datum FROM ICalendar WHERE (WeekNr = @WeekTotMet) AND (Dag = 'zo')</CommandText> </Query> <Fields> <Field Name="Datum"> <DataField>Datum</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> Introducing your suggestion in dataset WekenVanaf would only give the same week to select from, instead of the same week and all previous weeks of @WeekTotMet. Regards Jos I'll be back