More than one parameter on a SSRS report

Question

Post reply

More than one parameter on a SSRS report

stevenplee

SSCommitted

Points: 1626
More actions
July 22, 2010 at 8:20 am

#221628

I have created a SSRS report and need to have more than one parameter for the report. I am using a stored procedure as my base dataset for the report. I have also created a separate dataset for each of the respective parameters. I have assigned the parameter to the appropriate dataset.
When I preview the report and I select the just one parameter I receive an error message that I need to select a parameter for the other available parameters. For each parameter I have checked the allow null checkbox.
What am I overlooking here?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

So if you execute the sp in a query window it will be

EXEC spName Parm1, Parm2, Parm3

but since you are not providing parm 2 or 3 you are essentially using

EXEC spName Parm1

Does this work in a query window?

stevenplee SSCommitted Points: 1626 More actions · Answer 2

Here's the stored procedure that I have in the SQL database. I am calling this procedure with SSRS.

USE [Clearview]

GO

/****** Object: StoredProcedure [dbo].[sp_rpt_Travel_Zone_by_customer] Script Date: 07/22/2010 13:08:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_rpt_Travel_Zone_by_customer]

--@Customer varchar(40) = null,

--@Distance int ,

@Zone varchar (8) = null,

@BillTo varchar (40) = null

AS

BEGIN

SELECT Customer_number, Customer_Name, Distance, branch_dist_uom, Zone, Branch, technician_id, tech_fname, tech_lname, alt_technician_id,

alttech_fname, alttech_lname, active_flag, Customer, technician, bill_to_business_id, biill_to_Cust_Name, Loc_Cust_Code, bill_to_bus_Code, Rate,

record

FROM v_Travel_Zone_by_Customer

WHERE (@Zone = Zone) or (@BillTo = biill_to_Cust_Name)

END

stevenplee SSCommitted Points: 1626 More actions · Answer 3

The sp works from SQL and it also works from the report data tab in visual studio.

Steven

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I am a bit rusty on this, so sorry if I am a bit off the mark. In BIDS 2005 report parameters and query parameters are different. It is possible for them to get out of sync so that the report parameters are not feeding the query parameters as expected. I would try deleting the report parameters and recreating them. Hope this helps. Good luck.