Solved

Report Server report pass parameter to stored procedure

Posted on 2012-03-13
6
452 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

12 Experts available now in Live!

Get 1:1 Help Now