sql server reporting services: Hello,

I have a stored procedure that asks for 2 parameters: ProductID or Class. I
want it set up so if the user does not enter either one then the query
selects and returns all records.
In RS Report Designer=> Data View, I executed this stored procedure however
it gives me an error:

{An error occurred while executing the query.
'Procedure ' rptDXUnitsNetSales' Expects parameter '@productId', which was
not supplied.}

How can I work around this situation? Please help!!
----------------------------------------------------------------------------
----------
Here is my Stored Procedure :
Create PROCEDURE dbo.rptDXunitsNetSales
@ProductID Int,
@Class varchar (80)
As



If @ProductID Is Null AND @Class Is Null

Begin

SELECT c.Product, c.ReportGroup1, c.Class, i.Region, f.Description AS
Study, f.StudyID, g.Description AS Promotion, g.PromotionalProgramID,
h.ReportType,
b.YearNumber, b.MonthNumber, d.PayorType,
COUNT(DISTINCT a.OrderTestID) AS TestOrders, SUM(CASE WHEN isbilled = 0 AND
a.productid != 44 THEN standardfee * diagnostic WHEN
isbilled = 0 AND a.productid = 44 THEN standardfee * pathology WHEN isbilled
= 1 AND
a.productid != 44 THEN (chargedpayoramount +
chargedpatientamount) * diagnostic ELSE (chargedpayoramount +
chargedpatientamount)
* pathology END) AS Total1, SUM(a.ChargedPayorAmount +
a.ChargedPatientAmount) AS Total2, SUM(a.ReceivedPayorAmount) AS Total3,
SUM(a.ReceivedPatientAmount) AS Total4
FROM OrderTestRevenue a INNER JOIN
DateDimension b ON a.ReportedDateID = b.DateID INNER
JOIN
ProductDefinition c ON a.ProductID = c.ProductID INNER
JOIN
PayorBillingLocation d ON a.PayorBillingLocationID =
d.PayorBillingLocationID INNER JOIN
PayorTypeReimbursement e ON
ISNULL(d.FinancialClassMID, 0) = e.FinancialClassMID AND a.ReportedDateID >=
e.EffectiveFromDateID AND
a.ReportedDateID <= e.EffectiveToDateID INNER JOIN
Study f ON a.StudyID = f.StudyID INNER JOIN
PromotionalProgram g ON a.PromotionalProgramID =
g.PromotionalProgramID INNER JOIN
Person h ON a.OrderingPersonID = h.PersonID INNER JOIN
TerritoryCurrent i ON h.TerritoryID = i.territoryid
WHERE (b.YearNumber > 2001) AND (a.isvoid = 0) AND (a.isnocharge = 0)
AND (a.StudyID IN
(SELECT studyid
FROM promotionalprogram))
GROUP BY c.Product, c.ReportGroup1, c.Class, i.Region, f.Description,
f.StudyID, g.Description, g.PromotionalProgramID, h.ReportType,
b.YearNumber,
b.MonthNumber, d.PayorType
End

IF @ProductID IS NOT Null
Begin
SELECT c.Product, c.ReportGroup1, c.Class, i.Region, f.Description AS
Study, f.StudyID, g.Description AS Promotion, g.PromotionalProgramID,
h.ReportType,
b.YearNumber, b.MonthNumber, d.PayorType,
COUNT(DISTINCT a.OrderTestID) AS TestOrders, SUM(CASE WHEN isbilled = 0 AND
a.productid != 44 THEN standardfee * diagnostic WHEN
isbilled = 0 AND a.productid = 44 THEN standardfee * pathology WHEN isbilled
= 1 AND
a.productid != 44 THEN (chargedpayoramount +
chargedpatientamount) * diagnostic ELSE (chargedpayoramount +
chargedpatientamount)
* pathology END) AS Total1, SUM(a.ChargedPayorAmount +
a.ChargedPatientAmount) AS Total2, SUM(a.ReceivedPayorAmount) AS Total3,
SUM(a.ReceivedPatientAmount) AS Total4
FROM OrderTestRevenue a INNER JOIN
DateDimension b ON a.ReportedDateID = b.DateID INNER
JOIN
ProductDefinition c ON a.ProductID = c.ProductID INNER
JOIN
PayorBillingLocation d ON a.PayorBillingLocationID =
d.PayorBillingLocationID INNER JOIN
PayorTypeReimbursement e ON
ISNULL(d.FinancialClassMID, 0) = e.FinancialClassMID AND a.ReportedDateID >=
e.EffectiveFromDateID AND
a.ReportedDateID <= e.EffectiveToDateID INNER JOIN
Study f ON a.StudyID = f.StudyID INNER JOIN
PromotionalProgram g ON a.PromotionalProgramID =
g.PromotionalProgramID INNER JOIN
Person h ON a.OrderingPersonID = h.PersonID INNER JOIN
TerritoryCurrent i ON h.TerritoryID = i.territoryid
WHERE (@ProductID = c.Productid) And (b.YearNumber > 2001) AND (a.isvoid
= 0) AND (a.isnocharge = 0) AND (a.StudyID IN
(SELECT studyid
FROM promotionalprogram))
GROUP BY c.Product, c.ReportGroup1, c.Class, i.Region, f.Description,
f.StudyID, g.Description, g.PromotionalProgramID, h.ReportType,
b.YearNumber,
b.MonthNumber, d.PayorType
End

IF @Class Is not Null
Begin

SELECT c.Product, c.ReportGroup1, c.Class, i.Region, f.Description AS
Study, f.StudyID, g.Description AS Promotion, g.PromotionalProgramID,
h.ReportType,
b.YearNumber, b.MonthNumber, d.PayorType,
COUNT(DISTINCT a.OrderTestID) AS TestOrders, SUM(CASE WHEN isbilled = 0 AND
a.productid != 44 THEN standardfee * diagnostic WHEN
isbilled = 0 AND a.productid = 44 THEN standardfee * pathology WHEN isbilled
= 1 AND
a.productid != 44 THEN (chargedpayoramount +
chargedpatientamount) * diagnostic ELSE (chargedpayoramount +
chargedpatientamount)
* pathology END) AS Total1, SUM(a.ChargedPayorAmount +
a.ChargedPatientAmount) AS Total2, SUM(a.ReceivedPayorAmount) AS Total3,
SUM(a.ReceivedPatientAmount) AS Total4
FROM OrderTestRevenue a INNER JOIN
DateDimension b ON a.ReportedDateID = b.DateID INNER
JOIN
ProductDefinition c ON a.ProductID = c.ProductID INNER
JOIN
PayorBillingLocation d ON a.PayorBillingLocationID =
d.PayorBillingLocationID INNER JOIN
PayorTypeReimbursement e ON
ISNULL(d.FinancialClassMID, 0) = e.FinancialClassMID AND a.ReportedDateID >=
e.EffectiveFromDateID AND
a.ReportedDateID <= e.EffectiveToDateID INNER JOIN
Study f ON a.StudyID = f.StudyID INNER JOIN
PromotionalProgram g ON a.PromotionalProgramID =
g.PromotionalProgramID INNER JOIN
Person h ON a.OrderingPersonID = h.PersonID INNER JOIN
TerritoryCurrent i ON h.TerritoryID = i.territoryid
WHERE (@Class = c.Class) And (b.YearNumber > 2001) AND (a.isvoid = 0) AND
(a.isnocharge = 0) AND (a.StudyID IN
(SELECT studyid

We do this all the time. Try the following:

1) Verify that you are passing the parameters to the DataSet:

@OfficeNumber =Parameters!OfficeNumber.Value

2) Set a default value on the parameter to the stored procedure:

@OfficeNumber LookupType = '',

3) Query:

where (t.OfficeNumber = '' or t.OfficeNumber = @OfficeNumber)

Good Luck,
Mike

[quoted text, click to view]