Bug report RS with SP1

Microsoft gang,
I have the following query. if I use @.Shipping instead of @.CustomerShipTo as
a parameter name, at the preview stage, I receive @.StartDate not defined
error.
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
To)
GROUP BY ProductType,Product,UnitofMeasure,DDate
Regards,
CemSome more information about the parameter bug.
When I add the parameter manually (Report > Report Parameters), the query
below blows at run time, works perfect at query designer.
The way I get around this problem was to define the parameter in the result
query first. The moment I add it to the function below, it shows up in
report parameters. After configuration works perfect.
/*Main query*/
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
,@.BrandName,@.CanSize)
GROUP BY ProductType,Product,UnitofMeasure,DDate
/*Parameter query*/
SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM fn_CreateCanList()
UNION
SELECT Null , '--Select All--'
Regards,
Cem
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>
> Microsoft gang,
> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
as
> a parameter name, at the preview stage, I receive @.StartDate not defined
> error.
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
>
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> To)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> Regards,
> Cem
>|||Thanks. We'll take a look and see if we can repro here.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Could you send me definitions of your functions fn_CreateBlankSalesCases and
fn_CreateCanList?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Lev,
Below you may find the functions you requested. Thanks.
Regards,
Cem
CREATE FUNCTION fn_CreateBlankSalesCases (
@.StartDate as DateTime,
@.EndDate as DateTime,
@.Broker as VarChar(50),
@.Customer as VarChar(50),
@.CustomerShip as VarChar(50),
@.BrandName as VarChar(50),
@.CanSize as VarChar(50))
RETURNS @.DateTable TABLE (
ProductType VarChar(150),
Product VarChar(150),
TotalCases Int,
UnitofMeasure VarChar(10),
DDate DateTime)
/*Table created in memory*/
AS
BEGIN
DECLARE @.StartTempDate as DateTime
DECLARE @.ProductType as VarChar(150)
DECLARE @.Product as VarChar(150)
DECLARE @.TotalCases as Int
DECLARE @.UnitofMeasure as VarChar(10)
DECLARE @.TDate as DateTime
SET @.StartTempDate = @.StartDate
/* String manupilation for the brandname */
If (@.CanSize IS NULL)
SET @.CanSize = '%'
ELSE
SET @.CanSize = '%' + @.CanSize + '%'
If (@.BrandName IS NULL)
SET @.BrandName = '%'
ELSE
SET @.BrandName = @.BrandName + '%'
DECLARE SalesActualProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesActualProducts
FETCH NEXT FROM
SalesActualProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,@.TotalCases,@.UnitofMeasure,@.TDate)
FETCH NEXT FROM SalesActualProducts INTO @.ProductType, @.Product,
@.TotalCases, @.UnitofMeasure, @.TDate
END
CLOSE SalesActualProducts
DECLARE SalesProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesProducts
FETCH NEXT FROM
SalesProducts INTO @.ProductType, @.Product, @.TotalCases, @.UnitofMeasure,
@.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
WHILE (@.StartDate < @.EndDate)
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,0,@.UnitofMeasure,@.StartDate)
SET @.StartDate = DATEADD(month, 1, @.StartDate)
END
SET @.StartDate = @.StartTempDate
FETCH NEXT FROM SalesProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
END
CLOSE SalesProducts
/*Return Table*/
RETURN
END
CREATE FUNCTION fn_CreateCanList()
RETURNS
@.CanTable TABLE (
CanSize VarChar(150),
CanSizeOld VarChar(150)
)
/*Table created in memory*/
AS
BEGIN
DECLARE @.CanSizeTemp as VarChar(50)
DECLARE CanSizeList CURSOR FOR
SELECT DISTINCT can_size
FROM can_typ1
WHERE can_size IS NOT NULL
/*Start to insert data into the table */
OPEN CanSizeList
FETCH NEXT FROM
CanSizeList INTO @.CanSizeTemp
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF CHARINDEX('/',@.CanSizeTemp) > 0
INSERT INTO @.CanTable
VALUES(RIGHT(RTRIM(@.CanSizeTemp),LEN(@.CanSizeTemp)-CHARINDEX('/',@.CanSizeTem
p)) ,@.CanSizeTemp)
ELSE
INSERT INTO @.CanTable VALUES(@.CanSizeTemp, @.CanSizeTemp)
FETCH NEXT FROM CanSizeList INTO @.CanSizeTemp
END
CLOSE CanSizeList
/*Return Table*/
RETURN
END
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:uQ8Q1hnfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Could you send me definitions of your functions fn_CreateBlankSalesCases
and
> fn_CreateCanList?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> >
> >
> > Some more information about the parameter bug.
> >
> > When I add the parameter manually (Report > Report Parameters), the
query
> > below blows at run time, works perfect at query designer.
> >
> > The way I get around this problem was to define the parameter in the
> > result
> > query first. The moment I add it to the function below, it shows up in
> > report parameters. After configuration works perfect.
> >
> > /*Main query*/
> > SELECT
> > ProductType,Product,
> > SUM(TotalCases) as TotalCases,
> > UnitofMeasure,
> > DatePart(yyyy, DDate) as YDate,
> > DateName(mm,DDate) as MDate
> >
> > FROM
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> > ,@.BrandName,@.CanSize)
> > GROUP BY ProductType,Product,UnitofMeasure,DDate
> >
> > /*Parameter query*/
> > SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> > fn_CreateCanList()
> > UNION
> > SELECT Null , '--Select All--'
> >
> > Regards,
> > Cem
> >
> > "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> > news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
> >>
> >>
> >> Microsoft gang,
> >>
> >> I have the following query. if I use @.Shipping instead of
@.CustomerShipTo
> > as
> >> a parameter name, at the preview stage, I receive @.StartDate not
defined
> >> error.
> >>
> >> SELECT
> >> ProductType,Product,
> >> SUM(TotalCases) as TotalCases,
> >> UnitofMeasure,
> >> DatePart(yyyy, DDate) as YDate,
> >> DateName(mm,DDate) as MDate
> >>
> >> FROM
> >>
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> >> To)
> >> GROUP BY ProductType,Product,UnitofMeasure,DDate
> >>
> >> Regards,
> >> Cem
> >>
> >>
> >
> >
>