Solved

Report Server report pass parameter to stored procedure

Posted on 2012-03-13
6
456 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 29
Count with a subquery showing details 10 43
query execution hang 5 29
always on switch back after failover 2 33
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 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