sql server reporting services: how to turn off required parameter in reports -- sql server reporting services

how to turn off required parameter in reports

msdninfo
10/25/2005 1:39:09 PM

sql server reporting services:

Trying to learn reporting services and in particular how to cascade
parameters.
Using AdventureWorks200 db made up a report which works fine except when
there are no db entries for the last cascaded parameter.
Its doing category->subcategory->product->vendor
there are vendors in the db for some, but not all products. The report works
fine when there's a vendor but produces nothing when there is no vendor to
choose.
I checked the allow blanks in the report parameters for the vendor but still
no go.
so what do I need to do to allow a report to run when ther is no value for
the vendor?

Thansk for your help.

p.s. here's the main report sql:
------------------------
SELECT Product.Name AS Expr1, ProductInventory.Quantity,
ProductCategory.Name AS Expr3, ProductSubCategory.Name, Product.ListPrice,
Product.ProductLine, Vendor.Name AS Expr2,
ProductModel.Name AS Expr4
FROM Product INNER JOIN
ProductSubCategory ON Product.ProductSubCategoryID =
ProductSubCategory.ProductSubCategoryID INNER JOIN
ProductCategory ON
ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
INNER JOIN
ProductVendor ON Product.ProductID =
ProductVendor.ProductID INNER JOIN
Vendor ON ProductVendor.VendorID = Vendor.VendorID
INNER JOIN
ProductInventory ON Product.ProductID =
ProductInventory.ProductID INNER JOIN
ProductModel ON Product.ProductModelID =
ProductModel.ProductModelID
WHERE (Product.ProductID = @ProdID) AND
(ProductSubCategory.ProductCategoryID = @Cat) AND (ProductVendor.VendorID =
@VendID) AND
(Product.ProductSubCategoryID = @SubCat)

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

thanks.

RE: how to turn off required parameter in reports

Bret
10/25/2005 1:58:02 PM

The reason that you are not getting any data when there is no vendor is
because you have the @VendID in your where clause. You will need to change
the SQL Statement so that it is not limiting by VendorId. You could still do
an outer join if you need to return vendor when it exists.. hope this helps.

Another approach would be to encapsalate the query into a stored procedure
that checks if null was passed in for @VendID.

example:

CREATE procedure getProducts
@Cat int,
@SubCat int,
@ProdID int,
@VendID int
AS
BEGIN

if @VendID is NULL
begin
--select statement that doesn't limit by vendor
end
else
begin
--select statement that limits by vendor
end
END

GO

[quoted text, click to view]

Re: how to turn off required parameter in reports

msdninfo
10/25/2005 4:10:41 PM

Thanks Bret,
I figured it was something like that, but was hoping there was something
already set up in RS to handle this without resorting to code.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.