Stored Proc into SSRS

chrisssyp82 · Feb 6, 2012

SQL 2008
Report Builder 3.0

Hi

This is probably going to be a stupid question.

I'm converting a load of Crystal Reports over into SSRS. All of the reports are based upon SQL stored proc's. I have this stored proc

Code:

USE [InfoPortal]
GO
/****** Object:  StoredProcedure [dbo].[usp_Report_MonthEnd_NAV]    Script Date: 02/06/2012 16:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Report_MonthEnd_NAV]
	(	
			@RPT_TITL_NME	NVARCHAR(40)	= NULL                              
		,	@ORGID			CHAR(4)			= NULL
		,	@BKID			CHAR(4)			= NULL			  
		,	@ACCT_ID		CHAR(12)		= NULL   	
		,	@INQ_BASIS_NUM	INT				= NULL
		,	@CLS_SET_ID		VARCHAR(15) 	= NULL
		,	@AS_OF_TMS		VARCHAR(30) 	= NULL
		,	@ADJST_TMS		VARCHAR(30) 	= NULL
		,	@ADJUST			SMALLINT    	= NULL
		,	@START_DTE		VARCHAR(30) 	= NULL
		,	@START_ADJDTE	VARCHAR(30) 	= NULL
		,	@DTE_TYPE		SMALLINT    	= NULL
		,	@INV_TYP_FLTR	SMALLINT    	= NULL
		,	@TRN_TYP_NUM	SMALLINT    	= NULL
		,	@MINMAXCODE		SMALLINT    	= NULL
		,	@MINMAXVALUE	FLOAT       	= NULL
		,	@INSTR_ID		CHAR(16)    	= NULL
		,	@LDGR_NME		VARCHAR(40) 	= NULL
		,	@APP_USR_ID		CHAR(8)			= NULL
		,	@SESSION_ID		CHAR(50)		= NULL
		)

AS

BEGIN

SET NOCOUNT ON
/*------------------------------------------------------------------------------------------------------
Declare variables and worktable
------------------------------------------------------------------------------------------------------*/

DECLARE	@Section				VARCHAR(100)

DECLARE @ReportTitle			VARCHAR(40)
DECLARE @EndDateTime			DATETIME
DECLARE @LastWorkingDay			DATETIME
DECLARE @Debug					VARCHAR(1)

DECLARE @Worktable TABLE
	(
			[Portfolio ID]				VARCHAR(12)
		,	[Date]						DATETIME
		,	[All in Market Value]		FLOAT
		,	[MID or BID]				VARCHAR(3)
	)
/*------------------------------------------------------------------------------------------------------
Set default values
------------------------------------------------------------------------------------------------------*/
SET @Section = 'Set default values'
BEGIN

	/*SET @EndDateTime = DATEADD(s, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()),0))	--Defaults to end of last month 23:59:59
	SET @LastWorkingDay = dbo.fn_GetLastWorkDay (@EndDateTime, 'GB')
	SET @Debug = 'Y'
	
	SELECT @EndDateTime AS EndDateTime, @LastWorkingDay AS LastWorkingDay*/  --  Removed to cater for report
	SET @ReportTitle = ISNULL(NULLIF(@RPT_TITL_NME,''),'Month End NAV for Segregated Cash Clients')
	SET @EndDateTime = ISNULL(CONVERT(DATETIME,NULLIF(@AS_OF_TMS,'')), DATEADD(s, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()),0)))--Defaults to end of last month 23:59:59
	SET @LastWorkingDay = DBO.fn_GetLastWorkDay (@EndDateTime,'GB')
	SET @Debug = ISNULL(NULLIF(@LDGR_NME,''),'N')
	
	IF @LDGR_NME = 'TEST' SET @Debug = 'Y'

	IF @@ERROR <> 0 GOTO Error_handler

	IF @Debug = 'Y'
		BEGIN
			SELECT @Section AS Section
			SELECT 
				@ReportTitle AS ReportTitle, @EndDateTime AS EndDateTime, @LastWorkingDay AS LastWorkingDay
		END
END
/*------------------------------------------------------------------------------------------------------
Worktable
------------------------------------------------------------------------------------------------------*/
SET @Section = 'Worktable' 
BEGIN
	--Traded EOD BID, last working day
	INSERT INTO @Worktable
		SELECT
				dgc.PortfolioID						AS [Portfolio ID]
			,	@LastWorkingDay						AS [Date]		
			,	dgc.AcctMarketValue					AS [All in Market Value]
			,	'BID'								AS [MID or BID]

		FROM dbo.fn_Generic_DGControl ('', '', '$LCLAUTH_BID', 11, @LastWorkingDay, @LastWorkingDay, 'D', 'M', 'POSITION') AS dgc
		
			/*INNER JOIN ivw_acct AS a
				ON a.acct_id = dgc.PortfolioID
				AND a.acct_id like 'R%'			--local authority funds begin with R, except for the following...
				AND a.acct_id NOT IN ('RCALCSHZ', 'RCALDERZ', 'RWELLCO', 'RWELLINV', 'RWELLSA')
				AND a.acct_id NOT IN ('RDARLIN','RTTHORN')		--Use traded EOD MID for these local authorities*/  -- Remove as Portfolio Group added to Netik Global
				

	--Traded EOD MID, month end
	INSERT INTO @Worktable
		SELECT
				dgc.PortfolioID						AS [Portfolio ID]
			,	@EndDateTime						AS [Date]		
			,	dgc.AcctMarketValue					AS [All in Market Value]	
			,	'MID'								AS [MID or BID]

		FROM dbo.fn_Generic_DGControl ('', '', '$LCLAUTH_MID', 1, @EndDateTime, @EndDateTime, 'D', 'M', 'POSITION') AS dgc
		
			/*INNER JOIN ivw_acct AS a
				ON a.acct_id = dgc.PortfolioID
				AND a.acct_id IN ('RDARLIN','RTTHORN','RCHESHI2','IBROADDC')*/  -- Remove as Portfolio Group added to Netik Global
			
	IF @@ERROR <> 0 GOTO Error_handler

	IF @Debug = 'Y'
		BEGIN
			SELECT @Section AS Section
			SELECT * FROM @Worktable ORDER BY [Portfolio ID]
		END
				

END
/*------------------------------------------------------------------------------------------------------
Final Select
------------------------------------------------------------------------------------------------------*/
SET @Section = 'Final Select' 
BEGIN
	IF @Debug <> 'Y'
	BEGIN
	
		SELECT * FROM @Worktable
			
			LEFT OUTER JOIN vw_report_rpt_def AS rrd
			  ON rrd.[Report Business Name] = @ReportTitle
	END

	IF @@ERROR <> 0 GOTO Error_handler

END

------------------------------------------------------------------------------------------------------
RETURN(0) -- Return success

/*------------------------------------------------------------------------------------------------------
Error handler
------------------------------------------------------------------------------------------------------*/
Error_handler:

	DECLARE @errormsg varchar(400)
	--Generate error message
	SELECT @errormsg = 'Error occured at "' + @Section + '" in Stored Procedure '  + (SELECT name FROM sysobjects WHERE ID = @@PROCID)
	--Raise error to the user
	RAISERROR(@errormsg,16/*severity*/,1/*state*/)
	--Return error indicator
	RETURN (1)

END

However when this appears in SSRS, in my list of fields under the dataset all i have is section? Why is this? Will have to remove all of the error handling and debugging to allow the @Worktable fields to be shown?

Thanks

MarkSweetland · Feb 6, 2012

You can include the @Section in the return recordset:

Code:

SELECT @Section AS Section, * FROM @Worktable ORDER BY [Portfolio ID]

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

xlbo · Feb 6, 2012

If that doesn't work, the alternative is to use something like:

IF 1=2
SELECT a,b,c,d,e,f
FROM table

as the 1st line of your sp where a,b,c,d,e,f is your field list

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

chrisssyp82 · Feb 7, 2012

I've tried all of the above and i still get only "Section" under field names. It doesn't make any sense to me, surely i should be getting all the columns from @Worktable in my list of fields?

chrisssyp82 · Feb 7, 2012

Do you think i need to restructure the stored proc in any way for the fields to be shown in report builder?

xlbo · Feb 7, 2012

where did you put the code I suggested?

AS

BEGIN

IF 1=2
SELECT All, Field, Names, Here
FROM TABLE

--rest of code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

xlbo · Feb 7, 2012

if the table isn't available, you can use

SELECT '' as Stringfield1, '' as Stringfield2, 0 as ValueField1 etc etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

chrisssyp82 · Feb 8, 2012

Well your original way brought back errors of "invalid column name".

Your second way i added:

Code:

IF 1=2 
   SELECT '' as Stringfield1, '' as Datefield1, 0 as ValueField1, '' as Stringfield2

I added this like this:

Code:

AS

BEGIN

SET NOCOUNT ON
/*------------------------------------------------------------------------------------------------------
Declare variables and worktable
------------------------------------------------------------------------------------------------------*/
IF 1=2 
   SELECT '' as Stringfield1, '' as Datefield1, 0 as ValueField1, '' as Stringfield2  
DECLARE	@Section				VARCHAR(100)

This excuted ok, but i still get only "Section" in my list of fields. Any ideas?

Thanks

chrisssyp82 · Feb 8, 2012

Right sorry, i've got the list now in my list of fields in SSRS from the select i've added above. So how can i get this to link to my @Worktable table?

xlbo · Feb 8, 2012

the list of fields in your IF 1=2 section should mirror the field names being returned from your worktable

obviously, the section never gets used by the stored proc as 1 cannot = 2 but bedause there is a select list there SSRS uses it as its list of fields

btw - really should never use select * anywhere - i's just asking for trouble

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

chrisssyp82 · Feb 13, 2012

Tanks for that mate, makes sense.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Stored Proc into SSRS

chrisssyp82

MIS

MarkSweetland

MIS

xlbo

MIS

chrisssyp82

MIS

chrisssyp82

MIS

xlbo

MIS

xlbo

MIS

chrisssyp82

MIS

chrisssyp82

MIS

xlbo

MIS

chrisssyp82

MIS

Similar threads

Part and Inventory Search

Sponsor