Solved

Report Server report pass parameter to stored procedure

Posted on 2012-03-13
6
457 Views
Last Modified: 2012-03-13
I have a stored procedure that we use to pull information. In it there are some if statements that determine which information to pull based on the supplied parameter.

I am building a report in report builder to run this stored procedure. I have set the parameter in the report to default to one of the possibilities in the IF statements. However, the report is defaulting to the first select statement in the stored procedure, almost like it is skipping the IF statements.

How can I make sure it is going to the correct if statement in the stored procedure?

Thanks
0
Comment
Question by:wiggy353
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 37715031
Use profiler to see what is actually being passed to the SP.  It doesn't matter what is calling the SP, just what parameters are passed.  Make sure the defaults in your report are correct, or allow the user to enter the correct defaults.  You may have to add a dataset to the report to populate the default value based on some criteria such as username.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37715141
Perhaps you could post the rough outline of your stored proc, just to see if it's something silly (syntax, ...)?

I'm not sure how familiar you are with SSRS/SPs, so just in case, here's an article on the particular topic: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_1931-Reporting-On-Data-From-Stored-Procedures-part-1.html
0
 
LVL 1

Author Comment

by:wiggy353
ID: 37715163
Here is part of the stored procedure:

IF @Exception_Code = 'Sudafed'
BEGIN
	SELECT
			RIGHT(ORDERNO, LEN(ORDERNO) +1 - patindex('%[^0]%', ORDERNO))       AS [OrderNo]  ,
			HEALTHPLANNAME                                                      AS [Batch]    ,
			RXNUM                                                               AS [RxNum]    ,
			DRUGDESC                                                            AS [PrintName],
			ORIGINALQTYREQ                                                      AS [Qty]      ,
			DAYSSUPPLY                                                          AS [Days]     ,
			TOTALREFILLS                                                        AS [Refills]  ,
			STATUS                                                              AS [Status]
		FROM
			ORDERDETAILS WITH (NOLOCK)
		WHERE
			(DRUGDESC LIKE '%pseud%'
				OR VA_PRODID IN('P0165', 'P0167', 'P0296', 'P0330')
			)
			AND STATUS           <> 5
			AND STATUS           <> 9
			AND(TOTALREFILLS      > 5
				OR ORIGINALQTYREQ > 120
				OR DAYSSUPPLY     > 30
			)
		ORDER BY
			DRUGDESC DESC
END -- END IF @Exception_Code = 'Sudafed'

--================================================================================================
-- Sudafed
--================================================================================================
IF @Exception_Code = 'POBox' OR @Exception_Code = 'POBoxDetails'
BEGIN
	DECLARE @POBoxes AS VARCHAR(MAX); SET @POBoxes = '1!2@3#4$5%6^7&8*9(0)1!2@3#4$5%6^7&8*9(0)' -- Just something that should not ever match.
	SET @POBoxes = @POBoxes + ', P%O%BOX, H%C%BOX, PO BX, POB'
	SET @POBoxes = @POBoxes + ', BOX [0-9], BOX[0-9], BOX  [0-9], BOX #[0-9], BOX.[0-9]'
	SET @POBoxes = @POBoxes + ', H C R, HCR[0-9], HCR [0-9], HCR-[0-9]'
	SET @POBoxes = @POBoxes + ', GENERAL DELIVERY, GENERALDELIVERY'
	SET @POBoxes = @POBoxes + ', NO UPS DELIVERY'

	SELECT
			RIGHT(od.ORDERNO, LEN(od.ORDERNO) + 1 - PATINDEX('%[^0]%', od.ORDERNO))      AS OrderNo    ,
			od.STORECODE                                                                 AS 'Site'     ,
			CAST(od.STATUS AS VARCHAR(5)) + ' - ' + s.StatusText                         AS 'Status'   ,
			LTRIM(RTRIM(od.RXNUM))                                                       AS RxNum      ,
			od.PRODUCTDESCRIPTION                                                        AS Product    ,
			od.PATIENTNAME                                                               AS Patient    ,
			oh.PATIENTADDRESS                                                            AS [Address 1],
			oh.PATADDSTREETADD1                                                          AS [Address 2],
			RTRIM(oh.CITY)                                                               AS City       ,
			oh.STATE                                                                     AS State      ,
			oh.ZIPCODE                                                                   AS Zip        ,
			oh.PATIENTPHONE                                                              AS Phone
		INTO
			#PoBoxOrders
		FROM
			ORDERDETAILS AS od WITH (NOLOCK)
			INNER JOIN ORDERHEADER AS oh WITH (NOLOCK) ON od.ORDERNO = oh.ORDERNO
			INNER JOIN dbo.gpd_PopulateTable(@POBoxes) AS pt ON oh.PATIENTADDRESS LIKE '%' + pt.TableData + '%' OR oh.PATADDSTREETADD1 LIKE '%' + pt.TableData + '%'
			INNER JOIN gpd_Status s on s.OrderStatus = od.STATUS
		WHERE
			od.REFRIGERATE = 'Y'
			AND od.STATUS <> 5
			AND od.STATUS <> 9
			AND od.STATUS < 800
			AND NOT (oh.CITY IN ('Green Valley', 'Marana', 'Oro Valley', 'Tucson', 'Vail') AND oh.STATE = 'AZ')
		GROUP BY
			od.ORDERNO            , od.STORECODE   , od.STATUS         , od.RXNUM            ,
			od.PRODUCTDESCRIPTION , od.PATIENTNAME , oh.PATIENTADDRESS , oh.PATADDSTREETADD1 ,
			oh.CITY               , oh.STATE       , oh.ZIPCODE        , oh.PATIENTPHONE     ,
			od.DATETIMEPACKED     , oh.PATNAMELAST , s.StatusText
		ORDER BY
			od.STATUS, od.STORECODE

	IF @Exception_Code = 'POBoxDetails'
	BEGIN
		SELECT 
				* 
			FROM 
				#PoBoxOrders 
			ORDER BY 
				[Status], [Site]
	END
	ELSE
	BEGIN
		SELECT 
			OrderNo, RxNum, Product
			FROM 
				#PoBoxOrders
	END
	
	DROP TABLE #PoBoxOrders
END -- END IF @Exception_Code = 'POBox'

Open in new window


I have the dataset in the report set to run the stored procedure and one parameter that I have set at 'POBox'.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37715282
Wow, erm, what is the result when you execute that stored proc manually with the POBox parameter set?

A problem however that I do notice is the following: your stored procedure is not consistent in terms of fields returned.  To be able to make it work in SSRS, your SP should always return a resultset with identical layout (field names, field types).  That is currently not the case.
0
 
LVL 1

Author Closing Comment

by:wiggy353
ID: 37715402
That makes sense. This particular report is set to always use the one parameter. I didn't right the stored procedure, just making the report. What I think I will do is copy out the chunk of code for the particular parameter uses and make it its own procedure.

Thanks for pointing me in the right direction.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37715409
Addendum to my previous statement: what I said in the second paragraph is not entirely true.  Technically it is possible to create a dataset that can handle different sets returned from the SP, but it requires a little cheating.

When you created your dataset, the fields definition would be retrieved from the first SELECT statement in your query.  Other fields that are possibly returned in other SELECT statements will be ignored. To get it to work, you'd need to manually add fields to your dataset.  That is done in the Fields page of the Dataset properties: click the Add button and select Query Field.

Furthermore, when you run your report you'll get some warnings that fields are missing (rsMissingFieldInDataSet, rsErrorReadingDataSetField).  But it'll work...  Whatever fields are available will be shown (well, if you've added them on the report obviously).
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question