Solved

Report Server report pass parameter to stored procedure

Posted on 2012-03-13
6
453 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 98
SQL Syntax join to include values from first table 3 28
Chrome SSRS print Functionality 1 20
Updating a table from a temp table 4 29
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
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.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now