Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

More than one parameter on a SSRS report Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:05 AM
Points: 71, Visits: 282
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

Post #957218
Posted Thursday, July 22, 2010 9:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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?
Post #957275
Posted Thursday, July 22, 2010 11:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:05 AM
Points: 71, Visits: 282
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
Post #957372
Posted Thursday, July 22, 2010 11:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:05 AM
Points: 71, Visits: 282
The sp works from SQL and it also works from the report data tab in visual studio.

Steven
Post #957383
Posted Friday, July 23, 2010 7:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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.
Post #957972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse