sql server reporting services:

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

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

[quoted text, click to view]

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.

[quoted text, click to view]

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.


[quoted text, click to view]

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









[quoted text, click to view]