Customize Report Parameter Properties

Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.No, this is not possible.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> Is it possible to modify the XML code at runtime? I want to control the
> report parameter properties multi-value setting of True/False during
> runtime
> in the XML behind the RDL file. Set it to True if I want the parameters to
> be
> multi-value or False for single-value. This is determined based on the
> value
> selected in parameter one. Parameter one and two are City,State.
> If City is selected in parameter one then I want Parameter two to be a
> single-valued list, if State is chosen in Parameter One then I want the
> list
> in Parameter two to be a multi-valued select list.
> Any ideas or guidance would be appreciated.
>|||Looking at my example, could you recommend an approach to tackle this issue?
"Bruce L-C [MVP]" wrote:
> No, this is not possible.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> > Is it possible to modify the XML code at runtime? I want to control the
> > report parameter properties multi-value setting of True/False during
> > runtime
> > in the XML behind the RDL file. Set it to True if I want the parameters to
> > be
> > multi-value or False for single-value. This is determined based on the
> > value
> > selected in parameter one. Parameter one and two are City,State.
> >
> > If City is selected in parameter one then I want Parameter two to be a
> > single-valued list, if State is chosen in Parameter One then I want the
> > list
> > in Parameter two to be a multi-valued select list.
> >
> > Any ideas or guidance would be appreciated.
> >
>
>|||It is a nice concept but this is not an area you have control over if you
are using the portal (report manager) that ships with Reporting Services. If
you want to create your own web page and then integrate with RS using either
web services or url integration this is certainly possible. RS 2005 ships
with a webform and a winform control for integrating RS reports into your
applications.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> Looking at my example, could you recommend an approach to tackle this
> issue?
> "Bruce L-C [MVP]" wrote:
>> No, this is not possible.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> > Is it possible to modify the XML code at runtime? I want to control the
>> > report parameter properties multi-value setting of True/False during
>> > runtime
>> > in the XML behind the RDL file. Set it to True if I want the parameters
>> > to
>> > be
>> > multi-value or False for single-value. This is determined based on the
>> > value
>> > selected in parameter one. Parameter one and two are City,State.
>> >
>> > If City is selected in parameter one then I want Parameter two to be a
>> > single-valued list, if State is chosen in Parameter One then I want the
>> > list
>> > in Parameter two to be a multi-valued select list.
>> >
>> > Any ideas or guidance would be appreciated.
>> >
>>|||Thank you for your reply. So if I create a separate web page, can I reference
this page as a link from Report Manager going out and launching this custom
page/report? Would I find the winform/webform via Visual Studio?
"Bruce L-C [MVP]" wrote:
> It is a nice concept but this is not an area you have control over if you
> are using the portal (report manager) that ships with Reporting Services. If
> you want to create your own web page and then integrate with RS using either
> web services or url integration this is certainly possible. RS 2005 ships
> with a webform and a winform control for integrating RS reports into your
> applications.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> > Looking at my example, could you recommend an approach to tackle this
> > issue?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, this is not possible.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> >> > Is it possible to modify the XML code at runtime? I want to control the
> >> > report parameter properties multi-value setting of True/False during
> >> > runtime
> >> > in the XML behind the RDL file. Set it to True if I want the parameters
> >> > to
> >> > be
> >> > multi-value or False for single-value. This is determined based on the
> >> > value
> >> > selected in parameter one. Parameter one and two are City,State.
> >> >
> >> > If City is selected in parameter one then I want Parameter two to be a
> >> > single-valued list, if State is chosen in Parameter One then I want the
> >> > list
> >> > in Parameter two to be a multi-valued select list.
> >> >
> >> > Any ideas or guidance would be appreciated.
> >> >
> >>
> >>
> >>
>
>|||Well, the only way you could do this is to have a report that just has
links. You would need to have your own website. Usually people either use
Report Manager or provide their own front end via their own application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:EFC6D25C-E313-4398-92F6-1C5651FABFB9@.microsoft.com...
> Thank you for your reply. So if I create a separate web page, can I
> reference
> this page as a link from Report Manager going out and launching this
> custom
> page/report? Would I find the winform/webform via Visual Studio?
> "Bruce L-C [MVP]" wrote:
>> It is a nice concept but this is not an area you have control over if you
>> are using the portal (report manager) that ships with Reporting Services.
>> If
>> you want to create your own web page and then integrate with RS using
>> either
>> web services or url integration this is certainly possible. RS 2005 ships
>> with a webform and a winform control for integrating RS reports into your
>> applications.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
>> > Looking at my example, could you recommend an approach to tackle this
>> > issue?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No, this is not possible.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> >> > Is it possible to modify the XML code at runtime? I want to control
>> >> > the
>> >> > report parameter properties multi-value setting of True/False during
>> >> > runtime
>> >> > in the XML behind the RDL file. Set it to True if I want the
>> >> > parameters
>> >> > to
>> >> > be
>> >> > multi-value or False for single-value. This is determined based on
>> >> > the
>> >> > value
>> >> > selected in parameter one. Parameter one and two are City,State.
>> >> >
>> >> > If City is selected in parameter one then I want Parameter two to be
>> >> > a
>> >> > single-valued list, if State is chosen in Parameter One then I want
>> >> > the
>> >> > list
>> >> > in Parameter two to be a multi-valued select list.
>> >> >
>> >> > Any ideas or guidance would be appreciated.
>> >> >
>> >>
>> >>
>> >>
>>

Custome code not working in Report manager

Hi all,
I have Written the Custom code to compare two different dates and
show a message Box depending on their values .It was Working Fine in
preview tab.But it is not working when i deploy to web.Only rhe message
box part is not working in Web
I do have the Custom code like this
Public Function Errorbox(ByVal s as Date,ByVal s1 as Date) As String
If s>s1 Then
System.Windows.Forms.MessageBox.Show("From Date is greater than
To Date,Please Click Ok" )
End if
Return "a"
End Function
and i gave a text box (Just a text box)
To call this custome code and i gave the expression
=Code.Errorbox(Parameters!FROM_DATE.Value,Parameters!TO_DATE.Value)
i added this Assembly also
"System.Windows.Forms"
Its not working in Report manager(web) .Can any body help me out to
knowwhat would be the Problem.i am very new to this VB Coding
Thank you
Raj Deep.AYou need to add the assembly to the following folders:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin
Hope this helps.
"RajDeep" wrote:
> Hi all,
> I have Written the Custom code to compare two different dates and
> show a message Box depending on their values .It was Working Fine in
> preview tab.But it is not working when i deploy to web.Only rhe message
> box part is not working in Web
> I do have the Custom code like this
> Public Function Errorbox(ByVal s as Date,ByVal s1 as Date) As String
> If s>s1 Then
> System.Windows.Forms.MessageBox.Show("From Date is greater than
> To Date,Please Click Ok" )
> End if
> Return "a"
> End Function
> and i gave a text box (Just a text box)
> To call this custome code and i gave the expression
> =Code.Errorbox(Parameters!FROM_DATE.Value,Parameters!TO_DATE.Value)
> i added this Assembly also
> "System.Windows.Forms"
> Its not working in Report manager(web) .Can any body help me out to
> knowwhat would be the Problem.i am very new to this VB Coding
> Thank you
> Raj Deep.A
>

Custome code & Parameters

Hi
Can someone please tell me why =Code.MyConversion (Globals!ReportFolder)
does not work but =Code.MyConversion ("123456789") does work. '
the argument is of type string
Thanks
FrancoisIf you're viewing the report in preview, Globals!ReportFolder will return
nothing, so you don't see any output. Deploy the report and view it in
report manager at which point Globals!ReportFolder will return the report
folder name (excluding URL), so your code should then work.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Francois" <Francois@.discussions.microsoft.com> wrote in message
news:5E4CEF9E-6C92-47FC-AC9E-DF4BFA85FC2E@.microsoft.com...
> Hi
> Can someone please tell me why =Code.MyConversion (Globals!ReportFolder)
> does not work but =Code.MyConversion ("123456789") does work. '
> the argument is of type string
> Thanks
> Francois|||Should Globals!ReportFolder be Globals!ReportFolder.Value?
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OmHN$CWpEHA.2612@.TK2MSFTNGP15.phx.gbl...
> If you're viewing the report in preview, Globals!ReportFolder will return
> nothing, so you don't see any output. Deploy the report and view it in
> report manager at which point Globals!ReportFolder will return the report
> folder name (excluding URL), so your code should then work.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Francois" <Francois@.discussions.microsoft.com> wrote in message
> news:5E4CEF9E-6C92-47FC-AC9E-DF4BFA85FC2E@.microsoft.com...
> > Hi
> >
> > Can someone please tell me why =Code.MyConversion (Globals!ReportFolder)
> > does not work but =Code.MyConversion ("123456789") does work. '
> >
> > the argument is of type string
> >
> > Thanks
> >
> > Francois
>sql

custome code - global?

Is the custom code you write in the Properties section of one report
accessible to other reports within the same project? if so, how is it
accessed?
Thanks,
PMNo it is not. Consider code behind reports. If you want to do this you have
to use custom assemblies. I.e. create the code outside of RS and then
reference it from RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PacMan" <PacMan@.discussions.microsoft.com> wrote in message
news:32547C22-21CA-4EB6-9482-6DDB4A21A630@.microsoft.com...
> Is the custom code you write in the Properties section of one report
> accessible to other reports within the same project? if so, how is it
> accessed?
> Thanks,
> PM

Custom Weeks

In CODE section below, I'm grouping the Sum of Freight by the w/year. We
have a custom year begin and end year table that the DDL section will
create. Thus, I use the t_w table to calculate our custom ws with w
1 of any year starting with the date in the w_beginyeardate field and the
last w of the year ending on the w_endyeardate date value.
Is there a way I could return the same results created by my CODE section in
Northwind except use custom ws derived from my t_w table? Also, I also
need a fix to properly sort the w/year field from start to end w/year
field.
CODE:
SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
sumFreight
FROM Orders
GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
DDL ****************************************
*****
USE [Northwind]
GO
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')A sub-query, like
SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
would get you the custom w number for a given date, but you can't use
sub-queries in a GROUP BY clause. The solution here is to create a function.
The function below formats OrderDate into yyyy-ww
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
You can then use the function to both group and order by
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
"Scott" wrote:

> In CODE section below, I'm grouping the Sum of Freight by the w/year. W
e
> have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with we
ek
> 1 of any year starting with the date in the w_beginyeardate field and t
he
> last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
in
> Northwind except use custom ws derived from my t_w table? Also, I al
so
> need a fix to properly sort the w/year field from start to end w/yea
r
> field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
>
>
>|||You almost have it. Please run my DDL below (I simulated my production db).
The only thing wrong is for example at the end of 2004, your w 53 2004
should be w 1 2005. If you run my example below, you'll see no w 1
2005. otherwise, the wly sums are correct. 2004 should only have 52
ws.
I think this is happening because you aren't taking the year ending dates
into consideration.
DDL *********************************
CREATE TABLE [dbo].[test_prod](
[prod_id] [int] IDENTITY(1,1) NOT NULL,
[prod_date] [datetime] NULL,
[log_count] [int] NULL
) ON [PRIMARY]
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')
CREATE FUNCTION udfWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
insert into test_prod (prod_date, log_count) values
('20041220', '6456')
insert into test_prod (prod_date, log_count) values
('20041221', '5830')
insert into test_prod (prod_date, log_count) values
('20041222', '3480')
insert into test_prod (prod_date, log_count) values
('20041227', '6542')
insert into test_prod (prod_date, log_count) values
('20041228', '6669')
insert into test_prod (prod_date, log_count) values
('20041229', '6524')
insert into test_prod (prod_date, log_count) values
('20041230', '6541')
insert into test_prod (prod_date, log_count) values
('20050104', '6370')
insert into test_prod (prod_date, log_count) values
('20050105', '6926')
insert into test_prod (prod_date, log_count) values
('20050106', '5002')
insert into test_prod (prod_date, log_count) values
('20050107', '6736')
insert into test_prod (prod_date, log_count) values
('20050108', '5822')
-- execute to see results
SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
FROM dbo.test_prod
GROUP BY dbo.udfWFormat(prod_date)
ORDER BY dbo.udfWFormat(prod_date)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>A sub-query, like
> SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
> w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
> would get you the custom w number for a given date, but you can't use
> sub-queries in a GROUP BY clause. The solution here is to create a
> function.
> The function below formats OrderDate into yyyy-ww
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> You can then use the function to both group and order by
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> "Scott" wrote:
>|||Yep, I realized my mistake about 15 minutes after my post, but couldn't get
back to it until after a few hours. This should do it:
DROP FUNCTION udfCustomWFormat
GO
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
The function should now pick up the dates in late December correctly. For
example,
select dbo.udfCustomWFormat('19951228') results in 1996-01
Your example t_w table only goes to 2000, but you are running it against
a table that has dates in 2005; is the table cyclic?
"Scott" wrote:

> You almost have it. Please run my DDL below (I simulated my production db)
.
> The only thing wrong is for example at the end of 2004, your w 53 2004
> should be w 1 2005. If you run my example below, you'll see no w 1
> 2005. otherwise, the wly sums are correct. 2004 should only have 52
> ws.
> I think this is happening because you aren't taking the year ending dates
> into consideration.
> DDL *********************************
> CREATE TABLE [dbo].[test_prod](
> [prod_id] [int] IDENTITY(1,1) NOT NULL,
> [prod_date] [datetime] NULL,
> [log_count] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
> CREATE FUNCTION udfWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> insert into test_prod (prod_date, log_count) values
> ('20041220', '6456')
> insert into test_prod (prod_date, log_count) values
> ('20041221', '5830')
> insert into test_prod (prod_date, log_count) values
> ('20041222', '3480')
> insert into test_prod (prod_date, log_count) values
> ('20041227', '6542')
> insert into test_prod (prod_date, log_count) values
> ('20041228', '6669')
> insert into test_prod (prod_date, log_count) values
> ('20041229', '6524')
> insert into test_prod (prod_date, log_count) values
> ('20041230', '6541')
> insert into test_prod (prod_date, log_count) values
> ('20050104', '6370')
> insert into test_prod (prod_date, log_count) values
> ('20050105', '6926')
> insert into test_prod (prod_date, log_count) values
> ('20050106', '5002')
> insert into test_prod (prod_date, log_count) values
> ('20050107', '6736')
> insert into test_prod (prod_date, log_count) values
> ('20050108', '5822')
>
> -- execute to see results
> SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
> FROM dbo.test_prod
> GROUP BY dbo.udfWFormat(prod_date)
> ORDER BY dbo.udfWFormat(prod_date)
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>
>|||First, thanks big for solving this head-hurter. My t_w table does have
the last 5 years plus always 1 year in advance. I had the old years so it'd
work in northwind.
what did you mean by the table being "cyclic"?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
> Yep, I realized my mistake about 15 minutes after my post, but couldn't
> get
> back to it until after a few hours. This should do it:
> DROP FUNCTION udfCustomWFormat
> GO
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> The function should now pick up the dates in late December correctly. For
> example,
> select dbo.udfCustomWFormat('19951228') results in 1996-01
> Your example t_w table only goes to 2000, but you are running it
> against
> a table that has dates in 2005; is the table cyclic?
> "Scott" wrote:
>|||Just curious - what kind of ws start on TUES?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||That the start and end year dates repeat in a pattern, every 5 years or so.
I
got from your second post that you were adjusting your data to fit Northwind
so a solution could be tested (and that's appreciated!)
I noticed when I ran the query against Northwind, one of the years (1996 I
think) shows up as having 53 ws in it. The difference between the year
start date and end date for 1996 in your sample is 370 days, so that does go
beying 52 ws. However, in a "normal" year, my math shows the last day of
the year being in w 53. It needs some twing. If I have time, I'll post
an adjusted version later.
"scott" wrote:

> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Should have been this way from the start.
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
Now the last (364th) day of the year shows up as being in w 52. For years
that go beyond 364 days, you will still end up with a w 53 out of this
function.
"scott" wrote:

> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Are these calendar ws starting with Sunday?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||what's the difference in this 2nd correct function and the 1st or previous
correct function?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4583358-03D7-4EAD-9B4C-F1E69B884861@.microsoft.com...
> Should have been this way from the start.
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
> AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> Now the last (364th) day of the year shows up as being in w 52. For
> years
> that go beyond 364 days, you will still end up with a w 53 out of this
> function.
> "scott" wrote:
>