How do I use DECLARE in SSRS?

  • Using the Query Analyzer the SQL statement below runs just fine. When I try to use it in SSRS (in Visual Studio 2005), I get errors telling me that I cannot use "DECLARE" But if I take it out, then the code doesn't work. If I replace Declare with Dim, then it simply tells me that it cannot parse my query. Any ideas on how to proceed to have this query work in SSRS as it has no problem at all in QA?

    Declare @startDate SMALLDATETIME, @endDate SMALLDATETIME, @range INT, @holidaycnt INT

    Declare @HolidayTbl table (holidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +

    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +

    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    insert into @HolidayTbl (holidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (holidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (holidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (holidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)

    insert into @HolidayTbl (holidayDate)values ('20081127') -- Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (holidayDate)values ('20081128') -- Day After Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (holidayDate)values ('20081225') -- Christmas

    SET @holidaycnt = (select count(*) from @holidayTbl where holidayDate between @startDate and @endDate)

    SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

    SELECT left(SOMast.FOrderDate,11) as EntryDate, SOMast.fSONo, SOMast.fCompany, upper(SOMast.fStatus) AS fcSOStatus,

    SOItem.fac, SOItem.fProdCl, SOItem.fSource, SOItem.fINumber,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN round(SORels.fNetPrice,2)

    ELSE 0000000000.00

    END

    ELSE round(SORels.fNetPrice,2)

    END AS fNetPrice,

    fNetPrice / ( SELECT

    ( @range / 7 * 5 + @range % 7 -

    ( SELECT COUNT(*)

    FROM

    ( SELECT 1 AS d

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    UNION ALL SELECT 6

    UNION ALL SELECT 7

    ) weekdays

    WHERE d <= @range % 7

    AND DATENAME

    ( WEEKDAY, @endDate - d + 1

    )IN

    ( 'Saturday', 'Sunday'

    )

    )

    ) - @holidaycnt

    ) as AvgPrice,

    left(SORels.fDueDate,11) as DateDue,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fRelease = '000' THEN

    (SOItem.fTotPTime + SOItem.fTotSTime)

    ELSE 0.00 END

    ELSE (SOItem.fTotPTime + SOItem.fTotSTime) END AS fnMakeTime,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN

    (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost + SORels.fSetupCost + fSubCost + fToolCost)

    ELSE 0000000000.00 END

    ELSE (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost) END AS fnEstCost,

    0000000.00 as fnActCost, 0000000.00 as fnCostDiff,

    left(CAST('01/01/1900' as datetime), 11) as fActSchDfn,

    CASE WHEN SOItem.fSource = 'B' THEN SoRels.fPOStatus

    ELSE SPACE(10) END AS fcPOStatus, SORels.fRelease,

    CASE WHEN SOItem.fSource = 'M' THEN SORels.fStatus

    ELSE space(10) END AS fJobNo,

    CASE WHEN SOItem.fSource = 'B' THEN 1

    ELSE 0 END as flAllBuy, SPACE(20) AS fcSOFac, SPACE(2) AS fcSOProdCl, SPACE(1) AS fcSOSource,

    upper(SOMast.fStatus) AS fcShoStats, SoRels.fMasterRel,

    SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost as fnCost1,

    SORels.fSetupCost + fSubCost + fToolCost as fnCost2, SoItem.fPartNo, SoItem.fPartRev, SoMast.FCustNo,

    SoRels.FPOStatus, SoMast.fEuroFctr, SoMast.fcCurID,

    case when soitem.fprodcl = '01' or soitem.fprodcl = '02' or soitem.fprodcl = '04' or soitem.fprodcl = '06' or

    soitem.fprodcl = '08' or soitem.fprodcl = '10' or soitem.fprodcl = '12' or soitem.fprodcl = '14' or

    soitem.fprodcl = '16' or soitem.fprodcl = '18' or soitem.fprodcl = '19' then 'Perf'

    else case when soitem.fprodcl = '20' then 'Instr'

    else case when soitem.fprodcl = '30' or soitem.fprodcl = '31' then 'Tek-Co'

    else case when soitem.fprodcl = '40' or soitem.fprodcl = '42' or soitem.fprodcl = '44' or

    soitem.fprodcl = '46' or soitem.fprodcl = '48' or soitem.fprodcl = '52' or

    soitem.fprodcl = '54' or soitem.fprodcl = '56' or soitem.fprodcl = '58' then 'Expl'

    else 'Rental'

    end

    end

    end

    end as ProductCode

    FROM somast

    JOIN soitem ON SOMast.fsono = SOItem.fsono

    JOIN sorels ON Soitem.fsono = Sorels.fsono

    AND SOItem.fINumber = SORels.fINumber

    WHERE SOITEM.fsource IN ('M','S','B')

    AND (SOItem.fSource = 'M'

    OR (SOItem.fSource = 'B'

    AND SORels.fMasterRel = 0)

    OR (SOItem.fSource = 'S'

    AND SORels.fMasterRel = 0))

    AND SOMast.fStatus <> 'STARTED'

    AND ( 1) = 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) >= 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) <= day(getdate())

    AND 1 = 1

    AND 1 = 1

    AND somast.fstatus <> 'CANCELLED'

    AND somast.fstatus <> 'ON HOLD'

    ORDER BY fprodcl

    I am generating a number of reports, and this one happens to be used for generating the "Average Sales Per Workday in the Current Month".

    **I have no clue why the code above has the winking emoticons in there, they are all just closing parenthesis.**

  • SSRS tries to build/use metadata from the query you type in, and it has problems with variables (because it thinks you're trying to pass them as parameters?). i have a document that explains the problem/issue, but i've misplaced it.

    when i ran into this, i just wrapped the query in a stored proc and called the proc from the report. presto, bango, it worked fine.

  • If you want this EXACT set of sql statements to generate a report for you will need to encapsulate it in a stored procedure.

    Parameters in SSRS are defined in the report and your SQL statement can only be stuff like:

    Select name from names where id = @id

  • i use declare in all of my SSRS 2005 reports so it definitely can be used. have you defined any of the declared variables as report parameters? if so, that will cause a problem parsing the query since SSRS declares the parameters prior to query.

  • antonio.collins (8/7/2008)


    i use declare in all of my SSRS 2005 reports so it definitely can be used. have you defined any of the declared variables as report parameters? if so, that will cause a problem parsing the query since SSRS declares the parameters prior to query.

    I stand corrected. Of course I would still put this complex a query in a stored procedure.

    1 issue I encountered with your current code is that it appears SSRS is Case-Sensitive for the parameters/variables you are declaring. I copied the code from the top down through the Set @range line and I had to fix the reference to @holidayTbl to match the declaration @HolidayTbl or SSRS asked me to fill in the parameter.

  • I get an error report when I stick the code in a SProc.

    wrote:

    An error occurred while retrieving the parameters in the query.

    SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "Declare @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @Range INT, @HolidayCnt INT

    Declare @HolidayTbl table (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' + cast(datepart(day,dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' + cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    -- Fill Holiday dates based on facily/State/Country

    insert into @HolidayTbl (HolidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (HolidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (HolidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)

    insert into @h...", the current limit of "4" is insufficient.

    It then gives me "Additional information:" if you think that might help figure out what's wrong with it too.

    This is my first time using SSRS, and I have only used a SProc one time, and it was written by another employee that no longer works here, so my knowledge in this area is limited. Probably something incredibly easy, but it does a fellow good to feel like an idiot every now and then.

    Thank you all for the help so far.

  • Jason,

    If you post the entire stored procedure I'm sure we can give you some help. As you said it is probably something simple. My signature line used to say "It's too late to tell me not to make a fool of myself". I can't tell you how many times I've posted something that had a simple fix. Or posted something with an error.

  • Like Jack said, the problem is your variable names are changing case.

    [font="Courier New"]select @holidaycnt = (select count(*) from @holidayTbl[/font]

    should be

    [font="Courier New"]select @holidaycnt = (select count(*) from @HolidayTbl[/font]

  • I took from the replies above that I should just grab the code and chuck it into a SProc, so that's what I did with it (everything in my first post).

    Should I just take all of the Declare..., Set..., Insert Into... parts and put those in the SProc and then leave the Select portion in a regular query?

    Like I said, I've only dealt with a stored procedure once before, and I wasn't the one that wrote it, I just made updates to it when needed. I'm about as clueless a noob as I can be right now.

    Declare @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @Range INT, @HolidayCnt INT

    Declare @HolidayTbl table (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +

    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +

    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    insert into @HolidayTbl (HolidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (HolidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (HolidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081127') -- Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081128') -- Day After Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081225') -- Christmas

    SET @HolidayCnt = (select count(*) from @HolidayTbl where HolidayDate between @StartDate and @EndDate)

    SET @Range = DATEDIFF(DAY, @StartDate, @EndDate)+1;

    SELECT left(SOMast.FOrderDate,11) as EntryDate, SOMast.fSONo, SOMast.fCompany, upper(SOMast.fStatus) AS fcSOStatus,

    SOItem.fac, SOItem.fProdCl, SOItem.fSource, SOItem.fINumber,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN round(SORels.fNetPrice,2)

    ELSE 0000000000.00

    END

    ELSE round(SORels.fNetPrice,2)

    END AS fNetPrice,

    fNetPrice / ( SELECT

    ( @range / 7 * 5 + @range % 7 -

    ( SELECT COUNT(*)

    FROM

    ( SELECT 1 AS d

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    UNION ALL SELECT 6

    UNION ALL SELECT 7

    ) weekdays

    WHERE d <= @range % 7

    AND DATENAME

    ( WEEKDAY, @EndDate - d + 1

    )IN

    ( 'Saturday', 'Sunday'

    )

    )

    ) - @HolidayCnt

    ) as AvgPrice,

    left(SORels.fDueDate,11) as DateDue,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fRelease = '000' THEN

    (SOItem.fTotPTime + SOItem.fTotSTime)

    ELSE 0.00 END

    ELSE (SOItem.fTotPTime + SOItem.fTotSTime) END AS fnMakeTime,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN

    (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost + SORels.fSetupCost + fSubCost + fToolCost)

    ELSE 0000000000.00 END

    ELSE (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost) END AS fnEstCost,

    0000000.00 as fnActCost, 0000000.00 as fnCostDiff,

    left(CAST('01/01/1900' as datetime), 11) as fActSchDfn,

    CASE WHEN SOItem.fSource = 'B' THEN SoRels.fPOStatus

    ELSE SPACE(10) END AS fcPOStatus, SORels.fRelease,

    CASE WHEN SOItem.fSource = 'M' THEN SORels.fStatus

    ELSE space(10) END AS fJobNo,

    CASE WHEN SOItem.fSource = 'B' THEN 1

    ELSE 0 END as flAllBuy, SPACE(20) AS fcSOFac, SPACE(2) AS fcSOProdCl, SPACE(1) AS fcSOSource,

    upper(SOMast.fStatus) AS fcShoStats, SoRels.fMasterRel,

    SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost as fnCost1,

    SORels.fSetupCost + fSubCost + fToolCost as fnCost2, SoItem.fPartNo, SoItem.fPartRev, SoMast.FCustNo,

    SoRels.FPOStatus, SoMast.fEuroFctr, SoMast.fcCurID,

    case when soitem.fprodcl = '01' or soitem.fprodcl = '02' or soitem.fprodcl = '04' or soitem.fprodcl = '06' or

    soitem.fprodcl = '08' or soitem.fprodcl = '10' or soitem.fprodcl = '12' or soitem.fprodcl = '14' or

    soitem.fprodcl = '16' or soitem.fprodcl = '18' or soitem.fprodcl = '19' then 'Perf'

    else case when soitem.fprodcl = '20' then 'Instr'

    else case when soitem.fprodcl = '30' or soitem.fprodcl = '31' then 'Tek-Co'

    else case when soitem.fprodcl = '40' or soitem.fprodcl = '42' or soitem.fprodcl = '44' or

    soitem.fprodcl = '46' or soitem.fprodcl = '48' or soitem.fprodcl = '52' or

    soitem.fprodcl = '54' or soitem.fprodcl = '56' or soitem.fprodcl = '58' then 'Expl'

    else 'Rental'

    end

    end

    end

    end as ProductCode

    FROM somast

    JOIN soitem ON SOMast.fsono = SOItem.fsono

    JOIN sorels ON Soitem.fsono = Sorels.fsono

    AND SOItem.fINumber = SORels.fINumber

    WHERE SOITEM.fsource IN ('M','S','B')

    AND (SOItem.fSource = 'M'

    OR (SOItem.fSource = 'B'

    AND SORels.fMasterRel = 0)

    OR (SOItem.fSource = 'S'

    AND SORels.fMasterRel = 0))

    AND SOMast.fStatus <> 'STARTED'

    AND ( 1) = 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) >= 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) <= day(getdate())

    AND 1 = 1

    AND 1 = 1

    AND somast.fstatus <> 'CANCELLED'

    AND somast.fstatus <> 'ON HOLD'

    ORDER BY fprodcl

  • when you're done, all the code that was in your report (all the DECLAREs, SELECTs, etc) should be in a proc. then, the report should contain only one line: NameOfYourStoredProcedure.

    be sure to set the command type of your report's dataset to stored procedure, then in the query string box, type the name you gave the proc. go to the parameters tab (if your proc needs parameters), type in the names of your proc's parameters on the left and match them up with the report's parameters on the right.

    if everything was done properly, you should be able to preview your report.

    oh, and don't forget... you may have to GRANT EXECUTE on your proc to the user that's defined in the data source.

  • I split the two up as I asked about above, just to see what that would give me, and at least the error message changed (though I don't know if it was a change for better or for worse):

    SProc Code:

    Declare @StartDate SMALLDATETIME

    Declare @EndDate SMALLDATETIME

    Declare @Range INT

    Declare @HolidayCnt INT

    Declare @HolidayTbl table (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +

    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +

    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    insert into @HolidayTbl (HolidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (HolidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (HolidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081127') -- Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081128') -- Day After Thanksgiving (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20081225') -- Christmas

    SET @HolidayCnt = (select count(*) from @HolidayTbl where HolidayDate between @StartDate and @EndDate)

    SET @Range = DATEDIFF(DAY, @StartDate, @EndDate)+1;

    Query Code:

    SELECT left(SOMast.FOrderDate,11) as EntryDate, SOMast.fSONo, SOMast.fCompany, upper(SOMast.fStatus) AS fcSOStatus,

    SOItem.fac, SOItem.fProdCl, SOItem.fSource, SOItem.fINumber,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN round(SORels.fNetPrice,2)

    ELSE 0000000000.00

    END

    ELSE round(SORels.fNetPrice,2)

    END AS fNetPrice,

    fNetPrice / ( SELECT

    ( @Range / 7 * 5 + @Range % 7 -

    ( SELECT COUNT(*)

    FROM

    ( SELECT 1 AS d

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    UNION ALL SELECT 6

    UNION ALL SELECT 7

    ) weekdays

    WHERE d <= @Range % 7

    AND DATENAME

    ( WEEKDAY, @EndDate - d + 1

    )IN

    ( 'Saturday', 'Sunday'

    )

    )

    ) - @HolidayCnt

    ) as AvgPrice,

    left(SORels.fDueDate,11) as DateDue,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fRelease = '000' THEN

    (SOItem.fTotPTime + SOItem.fTotSTime)

    ELSE 0.00 END

    ELSE (SOItem.fTotPTime + SOItem.fTotSTime) END AS fnMakeTime,

    CASE WHEN SOItem.fSource = 'M' THEN

    CASE WHEN SORels.fMasterRel = 0 THEN

    (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost + SORels.fSetupCost + fSubCost + fToolCost)

    ELSE 0000000000.00 END

    ELSE (SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost) END AS fnEstCost,

    0000000.00 as fnActCost, 0000000.00 as fnCostDiff,

    left(CAST('01/01/1900' as datetime), 11) as fActSchDfn,

    CASE WHEN SOItem.fSource = 'B' THEN SoRels.fPOStatus

    ELSE SPACE(10) END AS fcPOStatus, SORels.fRelease,

    CASE WHEN SOItem.fSource = 'M' THEN SORels.fStatus

    ELSE space(10) END AS fJobNo,

    CASE WHEN SOItem.fSource = 'B' THEN 1

    ELSE 0 END as flAllBuy, SPACE(20) AS fcSOFac, SPACE(2) AS fcSOProdCl, SPACE(1) AS fcSOSource,

    upper(SOMast.fStatus) AS fcShoStats, SoRels.fMasterRel,

    SORels.fLabCost + SORels.fMatlCost + SORels.fOvhdCost + SORels.fOthrCost as fnCost1,

    SORels.fSetupCost + fSubCost + fToolCost as fnCost2, SoItem.fPartNo, SoItem.fPartRev, SoMast.FCustNo,

    SoRels.FPOStatus, SoMast.fEuroFctr, SoMast.fcCurID,

    case when soitem.fprodcl = '01' or soitem.fprodcl = '02' or soitem.fprodcl = '04' or soitem.fprodcl = '06' or

    soitem.fprodcl = '08' or soitem.fprodcl = '10' or soitem.fprodcl = '12' or soitem.fprodcl = '14' or

    soitem.fprodcl = '16' or soitem.fprodcl = '18' or soitem.fprodcl = '19' then 'Perf'

    else case when soitem.fprodcl = '20' then 'Instr'

    else case when soitem.fprodcl = '30' or soitem.fprodcl = '31' then 'Tek-Co'

    else case when soitem.fprodcl = '40' or soitem.fprodcl = '42' or soitem.fprodcl = '44' or

    soitem.fprodcl = '46' or soitem.fprodcl = '48' or soitem.fprodcl = '52' or

    soitem.fprodcl = '54' or soitem.fprodcl = '56' or soitem.fprodcl = '58' then 'Expl'

    else 'Rental'

    end

    end

    end

    end as ProductCode

    FROM somast

    JOIN soitem ON SOMast.fsono = SOItem.fsono

    JOIN sorels ON Soitem.fsono = Sorels.fsono

    AND SOItem.fINumber = SORels.fINumber

    WHERE SOITEM.fsource IN ('M','S','B')

    AND (SOItem.fSource = 'M'

    OR (SOItem.fSource = 'B'

    AND SORels.fMasterRel = 0)

    OR (SOItem.fSource = 'S'

    AND SORels.fMasterRel = 0))

    AND SOMast.fStatus <> 'STARTED'

    AND ( 1) = 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) >= 1

    AND month(SOMAST.FORDERDATE) = month(getdate())

    and year(somast.forderdate) = year(getdate())

    and day(somast.forderdate) <= day(getdate())

    AND 1 = 1

    AND 1 = 1

    AND somast.fstatus <> 'CANCELLED'

    AND somast.fstatus <> 'ON HOLD'

    ORDER BY fprodcl

    Error Message Text

    TITLE: Microsoft Report Designer

    ------------------------------

    An error occurred while retrieving the parameters in the query.

    The stored procedure 'Declare @StartDate SMALLDATETIME

    Declare @EndDate SMALLDATETIME

    Declare @Range INT

    Declare @HolidayCnt INT

    Declare @HolidayTbl table (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +

    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +

    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    insert into @HolidayTbl (HolidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (HolidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (HolidayDate)values ('20080901') -- Labor Day (UPDATE...' doesn't exist.

    ------------------------------

    ADDITIONAL INFORMATION:

    The stored procedure 'Declare @StartDate SMALLDATETIME

    Declare @EndDate SMALLDATETIME

    Declare @Range INT

    Declare @HolidayCnt INT

    Declare @HolidayTbl table (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +

    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +

    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    insert into @HolidayTbl (HolidayDate)values ('20080101') -- New Years

    insert into @HolidayTbl (HolidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)

    insert into @HolidayTbl (HolidayDate)values ('20080704') -- 4th of July

    insert into @HolidayTbl (HolidayDate)values ('20080901') -- Labor Day (UPDATE...' doesn't exist. (System.Data)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • I'd take the whole thing and stick it in a stored procedure:

    [font="Courier New"]CREATE PROCEDURE report_name

    AS

    DECLARE @StartDate SMALLDATETIME,

               @EndDate SMALLDATETIME,

               @Range INT,

               @HolidayCnt INT  

    DECLARE @HolidayTbl TABLE (HolidayDate SMALLDATETIME)

    -- Fill Start/End dates to obtain date range

    SET @StartDate = CAST(CAST(DATEPART(MONTH, DATEADD(mm, -0, GETDATE())) AS VARCHAR) + '/' +

                     CAST(DATEPART(DAY, DATEADD(DAY, -DATEPART(DAY, GETDATE())+1, GETDATE()))AS VARCHAR) + '/' +

                     CAST(DATEPART(YEAR, DATEADD(mm, -1, GETDATE())) AS VARCHAR) AS DATETIME)

    SET @EndDate = GETDATE()-1    -- Stops today from being counted as one of the days. Remove the -1 to include today

    --Fill Holiday dates based on facility/State/Country

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20080101')   -- New Years

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20080526')   -- Memorial Day            (UPDATE YEARLY)

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20080704')   -- 4th of July

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20080901')   -- Labor Day               (UPDATE YEARLY)

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20081127')   -- Thanksgiving            (UPDATE YEARLY)

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20081128')   -- Day After Thanksgiving  (UPDATE YEARLY)

    INSERT INTO @HolidayTbl (HolidayDate)VALUES ('20081225')   -- Christmas

    SELECT

        @HolidayCnt = COUNT(*)

    FROM

       @HolidayTbl

    WHERE

       HolidayDate BETWEEN @StartDate AND @EndDate

    SET @Range = DATEDIFF(DAY, @StartDate, @EndDate)+1;

    SELECT

       LEFT(SOMAST.FORDERDATE,11)

       AS ENTRYDATE,

       SOMAST.FSONO,

       SOMAST.FCOMPANY,

       UPPER(SOMAST.FSTATUS) AS FCSOSTATUS,

       SOITEM.FAC,

       SOITEM.FPRODCL,

       SOITEM.FSOURCE,

       SOITEM.FINUMBER,

       CASE

           WHEN SOITEM.FSOURCE = 'M' THEN CASE

                                                                           WHEN SORELS.FMASTERREL = 0 THEN ROUND(SORELS.FNETPRICE,2)

                                                                           ELSE 0000000000.00

                                                                       END

           ELSE ROUND(SORELS.FNETPRICE,2)

       END AS FNETPRICE,

       FNETPRICE / ( SELECT

       ( @RANGE / 7 * 5 + @RANGE % 7 - ( SELECT

                                                               COUNT(*)

                                                           FROM

                                                               ( SELECT

                                                               1 AS D UNION ALL SELECT

                                                               2 UNION ALL SELECT

                                                               3 UNION ALL SELECT

                                                               4 UNION ALL SELECT

                                                               5 UNION ALL SELECT

                                                               6 UNION ALL SELECT

                                                               7 ) WEEKDAYS

                                                               WHERE

                                                                   D <= @RANGE % 7 AND

                                                                   DATENAME( WEEKDAY, @ENDDATE - D + 1) IN ( 'SATURDAY', 'SUNDAY') ))

           -@HOLIDAYCNT) AS AVGPRICE,

       LEFT(SORELS.FDUEDATE,11) AS DATEDUE,

       CASE

           WHEN SOITEM.FSOURCE = 'M' THEN CASE

                                                                           WHEN SORELS.FRELEASE = '000' THEN (SOITEM.FTOTPTIME + SOITEM.FTOTSTIME)

                                                                           ELSE 0.00

                                                                       END

           ELSE (SOITEM.FTOTPTIME + SOITEM.FTOTSTIME)

       END AS FNMAKETIME,

       CASE

           WHEN SOITEM.FSOURCE = 'M' THEN CASE  

                                                                           WHEN SORELS.FMASTERREL = 0 THEN (SORELS.FLABCOST + SORELS.FMATLCOST +

                                                                                       SORELS.FOVHDCOST + SORELS.FOTHRCOST + SORELS.FSETUPCOST +

                                                                                       FSUBCOST + FTOOLCOST)

                                                                           ELSE 0000000000.00

                                                                       END

           ELSE (SORELS.FLABCOST + SORELS.FMATLCOST + SORELS.FOVHDCOST + SORELS.FOTHRCOST)

       END AS FNESTCOST,

       0000000.00 AS FNACTCOST,

       0000000.00 AS FNCOSTDIFF,

       LEFT(CAST('01/01/1900' AS DATETIME), 11) AS FACTSCHDFN,

       CASE

           WHEN SOITEM.FSOURCE = 'B' THEN SORELS.FPOSTATUS

           ELSE SPACE(10)

       END AS FCPOSTATUS,

       SORELS.FRELEASE,

       CASE

           WHEN SOITEM.FSOURCE = 'M' THEN SORELS.FSTATUS

           ELSE SPACE(10)

       END AS FJOBNO,

       CASE

           WHEN SOITEM.FSOURCE = 'B' THEN 1

           ELSE 0

       END AS FLALLBUY,

       SPACE(20) AS FCSOFAC,

       SPACE(2) AS FCSOPRODCL,

       SPACE(1) AS FCSOSOURCE,

       UPPER(SOMAST.FSTATUS) AS FCSHOSTATS,

       SORELS.FMASTERREL,

       SORELS.FLABCOST + SORELS.FMATLCOST + SORELS.FOVHDCOST + SORELS.FOTHRCOST AS FNCOST1,

       SORELS.FSETUPCOST + FSUBCOST + FTOOLCOST AS FNCOST2,

       SOITEM.FPARTNO,

       SOITEM.FPARTREV,

       SOMAST.FCUSTNO,

       SORELS.FPOSTATUS,

       SOMAST.FEUROOFCTR,

       SOMAST.FCCURID,

       CASE /*You can eliminate all the nested cases by using CASE WHEN x = x THEN

                                                                                                       WHEN y=y THEN, etc...*/

           -- change these casees to use SOITEM.FPRODCL IN () where there is a list of OR's

           WHEN SOITEM.FPRODCL = '01' OR SOITEM.FPRODCL = '02' OR SOITEM.FPRODCL = '04' OR

                       SOITEM.FPRODCL = '06' OR SOITEM.FPRODCL = '08' OR SOITEM.FPRODCL = '10' OR

                       SOITEM.FPRODCL = '12' OR SOITEM.FPRODCL = '14' OR SOITEM.FPRODCL = '16' OR

                       SOITEM.FPRODCL = '18' OR SOITEM.FPRODCL = '19' THEN 'PERF'

           ELSE

                   CASE

                       WHEN SOITEM.FPRODCL = '20' THEN 'INSTR'

                       ELSE

                           CASE

                                   WHEN SOITEM.FPRODCL = '30' OR SOITEM.FPRODCL = '31' THEN 'TEK-CO'

                                   ELSE

                                       CASE

                                           WHEN SOITEM.FPRODCL = '40' OR SOITEM.FPRODCL = '42' OR

                                                       SOITEM.FPRODCL = '44' OR SOITEM.FPRODCL = '46' OR

                                                       SOITEM.FPRODCL = '48' OR SOITEM.FPRODCL = '52' OR

                                                       SOITEM.FPRODCL = '54' OR SOITEM.FPRODCL = '56' OR

                                                       SOITEM.FPRODCL = '58' THEN 'EXPL'

                                           ELSE 'RENTAL'

                                       END

                           END

                       END

       END AS PRODUCTCODE

    FROM

       SOMAST JOIN

       SOITEM ON

           SOMAST.FSONO = SOITEM.FSONO JOIN

       SORELS ON

           SOITEM.FSONO = SORELS.FSONO AND

           SOITEM.FINUMBER = SORELS.FINUMBER

    WHERE

       SOITEM.FSOURCE IN ('M','S','B') AND

       (

           SOITEM.FSOURCE = 'M' OR

           (SOITEM.FSOURCE = 'B' AND SORELS.FMASTERREL = 0) OR

           (SOITEM.FSOURCE = 'S' AND SORELS.FMASTERREL = 0)

       ) AND

       SOMAST.FSTATUS <> 'STARTED' AND

       (1) = 1 AND

       MONTH(SOMAST.FORDERDATE) = MONTH(GETDATE()) AND

       YEAR(SOMAST.FORDERDATE) = YEAR(GETDATE()) AND

       DAY(SOMAST.FORDERDATE) >= 1 AND

       MONTH(SOMAST.FORDERDATE) = MONTH(GETDATE()) AND

       YEAR(SOMAST.FORDERDATE) = YEAR(GETDATE()) AND

       DAY(SOMAST.FORDERDATE) <= DAY(GETDATE()) AND

       1 = 1 AND

       1 = 1 AND

       SOMAST.FSTATUS <> 'CANCELLED' AND

       SOMAST.FSTATUS <> 'ON HOLD'

    ORDER BY

       FPRODCL

    [/font]

    I made some comments within the code where I think you could clean it up a little. In your where clause you have 1=1 3 times and I have to ask why as it always is true? I think there are several areas where the query could be improved as well, but hesitate to comment on all because I'm not exactly sure why it is all in there. Another benefit to writing it as an sp is that you can more easily include comments and format it for easier reading.

    On thing I will recommend is having either a real holidays table or a dates table with an IsHoliday flag that I could index and join on. In 2009 you will need to re-write this query to work for 2009 or have a new report for 2009 while adding the actual table will make this table work perpetually.

  • Lenny (8/8/2008)


    when you're done, all the code that was in your report (all the DECLAREs, SELECTs, etc) should be in a proc. then, the report should contain only one line: NameOfYourStoredProcedure.

    be sure to set the command type of your report's dataset to stored procedure, then in the query string box, type the name you gave the proc. go to the parameters tab (if your proc needs parameters), type in the names of your proc's parameters on the left and match them up with the report's parameters on the right.

    if everything was done properly, you should be able to preview your report.

    oh, and don't forget... you may have to GRANT EXECUTE on your proc to the user that's defined in the data source.

    Code from the report is now in the proc, report says only the name of the proc "PercAvg", and command type is set to Stored Procedure.

    I don't have any parameters, so that's out. I have all of that done, but I get a new error message now (see below).

    Not sure how to go about the GRANT EXECUTE thing, but is there a way to grant execute to everybody?

  • Since you are asking I will tell you, but it is generally not the best idea to give everybody rights on things.

    Grant Execute on [procname] to Public

    Everyone that has access to the database is in the public role. What you should do is just grant rights to the user defined in your datasource.

  • The query was part of an existing report that we have in our ERP system that needs drastic changes made to it. I took the query from there and started making changes as needed. The 1=1 statements were in there when I got it, and while I didn't understand their need to be there either, I didn't see it hurting anything so I went ahead and left it.

    Feel free to make whatever comments and suggestions you feel like making. I'm well aware that this thing needs help, so whatever we can do to make it better is fine by me.

    Making the table is something that has been suggested before, so I set up an Excel macro that generated the calendar for me to cover the next 60 years worth of dates. I haven't bothered importing it into the main database yet since I wasn't sure how to make it work, but it's something I could implement pretty easily.

    The route I took here seemed like the quickest and simplest way to go about it, so that's what I went with. I'm definitely open to "better".

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply