Stored Proc for SSRS Report

Hello, thanks for taking the time to look at my question.

I’m working with an SSRS report that uses a shared data source and a shared data set. The shared data set is a stored procedure that queries the Live10Reports database (the database that has temp tables for the reports RDD).

My issue is that the report is appearing blank (labels appear but no data) even though the temp table has data in it (matching the GUID to the temp tables shows I have a row with the data I want to see). However when I execute my stored procedure inside SSMS and use the GUID as the parameter I get zero rows returned.

This same stored procedure script was used to create the same stored proc in other databases (Test10Reports/Dev10Reports) and those work. I have changed the the database in the script so it is pointing to correct database. Comparing the code in each environment they look the same with the exception of which database they are pointing to.

Another user created the ones that are working so could permissions be an issue here? If I write a query that is just SELECT * FROM MTLTAGS_[GUID] and run that script I get the rows back, so it seem my user can query that table, that would lead me to believe a stored proc I made could as well.

Any information would be appreciated.

There are permissions for stored procedure’s but I always thought they were to execute the procedure not specifically select access inside the procedure. You can also get differences from the SELECT statement to the procedure depending on how it’s written. You can paste the sp and we could take a look.

This doesn’t help, but why use the procedure at all? You can add a SSRS subreport that has SQL to fetch the matching Reporting DB records using the GUID as a report parameter.

1 Like

Thanks for the reply John. The person before me implemented a set of reports using the method of stored procedure and shared data sets using those procedures. I’m trying to migrate the report set to a new environment. Below is the stored procedure created by running the script left by the previous person. When I compare this to the other environments it looks identical except for the environment I changed to point to the newly created one.

USE [EpicorLive10_2Reports]
GO
/****** Object:  StoredProcedure [dbo].[MtlTags]    Script Date: 3/1/2019 10:56:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[MtlTags] @TableID CHARACTER(33)	
AS
BEGIN

--DECLARE @TableID AS CHARACTER(33) = 'F79357CE951C41CC9EBD7FD698F4FF22'
	   
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
	DROP TABLE #Results;
END
CREATE TABLE #Results(
	ItemQty			INT,
	PartNum			NVARCHAR(MAX),
	PartDesc		NVARCHAR(MAX),
	LotNum			NVARCHAR(MAX),
	XPartNum		NVARCHAR(MAX),
	OurSupplierCode NVARCHAR(MAX),
	BoxPalletSerial NVARCHAR(MAX),
	MfgDate			DATETIME,
	XRevisionNum	NVARCHAR(MAX),
	UM				NVARCHAR(MAX),
	PONum			NVARCHAR(MAX),
	POLine			NVARCHAR(MAX),
	WhseCode		NVARCHAR(MAX),		

	AsmSeq			NVARCHAR(MAX),
	TagNum			NVARCHAR(MAX),
	BCAsmSeq		NVARCHAR(MAX),
	BCBinNum		NVARCHAR(MAX),
	BCJobNum		NVARCHAR(MAX),
	BCLotNum		NVARCHAR(MAX),
	BCOprSeq		NVARCHAR(MAX),
	BCPartNum		NVARCHAR(MAX),
	BCWhseCode		NVARCHAR(MAX),
	BinNum			NVARCHAR(MAX),
	JobNum			NVARCHAR(MAX),
	LaborNote		NVARCHAR(MAX),
	NCComment		NVARCHAR(MAX),
	NonConfTranID	NVARCHAR(MAX),
	OpCode			NVARCHAR(MAX),
	OprSeq			NVARCHAR(MAX),
	PORel			NVARCHAR(MAX),
	Reason			NVARCHAR(MAX),
	ReasonDesc		NVARCHAR(MAX),
	ResGrpID		NVARCHAR(MAX),
	TagFormat		NVARCHAR(MAX),
	TagTitle		NVARCHAR(MAX),		
	VendID			NVARCHAR(MAX),
	VendName		NVARCHAR(MAX),		
	QtyNum			NVARCHAR(MAX),		
	RptLanguageID	NVARCHAR(MAX),
	Company			NVARCHAR(MAX),		
	CustNum			NVARCHAR(MAX),
	GrossWeight		DECIMAL(15, 5),
	GrossWeightUOM	NVARCHAR(MAX),
	PartType		NVARCHAR(MAX),
	LongProductID	NVARCHAR(MAX),
	CustID			NVARCHAR(MAX),
	ShipToNum		NVARCHAR(MAX)

);
	

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND  TABLE_NAME = 'MtlTags_' + @TableID)
)
BEGIN

	DECLARE @SQL AS VARCHAR(MAX);
	SET @SQL = 
	N'SELECT  DISTINCT
		CAST(ISNULL(MtlTags.ItemQty, 0) AS INT) AS ItemQty,
		CAST(MtlTags.PartNum AS NVARCHAR(MAX)) AS PartNum,
		CAST(MtlTags.PartDesc AS NVARCHAR(MAX)) AS PartDesc,
		CAST(MtlTags.LotNum AS NVARCHAR(MAX)) AS LotNum,
		CAST(ISNULL(CustXPrt.XPartNum, MtlTags.PartNum) AS NVARCHAR(MAX)) AS XPartNum,
		CAST(ISNULL(Customer.OurSupplierCode, '''') AS NVARCHAR(MAX)) AS OurSupplierCode,
		CAST(BoxData.Key1 AS NVARCHAR(MAX)) AS BoxPalletSerial,
		CAST(ISNULL(BoxData.Date01, '''') AS DATE) AS MfgDate,
		CAST(ISNULL(CustXPrt.XRevisionNum, '''') AS NVARCHAR(MAX)) AS XRevisionNum,
		CAST(MtlTags.UM					AS NVARCHAR(MAX)) AS UM,
		CAST(MtlTags.PONum				AS NVARCHAR(MAX)) AS PONum,
		CAST(MtlTags.POLine				AS NVARCHAR(MAX)) AS POLine,
		CAST(MtlTags.WhseCode			AS NVARCHAR(MAX)) AS WhseCode,
		CAST(MtlTags.AsmSeq				AS NVARCHAR(MAX)) AS AsmSeq,
		CAST(MtlTags.TagNum				AS NVARCHAR(MAX)) AS TagNum,		
		CAST(MtlTags.BCAsmSeq			AS NVARCHAR(MAX)) AS BCAsmSeq,
		CAST(MtlTags.BCBinNum			AS NVARCHAR(MAX)) AS BCBinNum,
		CAST(MtlTags.BCJobNum			AS NVARCHAR(MAX)) AS BCJobNum,
		CAST(MtlTags.BCLotNum			AS NVARCHAR(MAX)) AS BCLotNum,
		CAST(MtlTags.BCOprSeq			AS NVARCHAR(MAX)) AS BCOprSeq,
		CAST(MtlTags.BCPartNum			AS NVARCHAR(MAX)) AS BCPartNum,
		CAST(MtlTags.BCWhseCode			AS NVARCHAR(MAX)) AS BCWhseCode,
		CAST(MtlTags.BinNum				AS NVARCHAR(MAX)) AS BinNum,
		CAST(MtlTags.JobNum				AS NVARCHAR(MAX)) AS JobNum,
		CAST(MtlTags.LaborNote			AS NVARCHAR(MAX)) AS LaborNote,
		CAST(MtlTags.NCComment			AS NVARCHAR(MAX)) AS NCComment,
		CAST(MtlTags.NonConfTranID		AS NVARCHAR(MAX)) AS NonConfTranD,
		CAST(MtlTags.OpCode				AS NVARCHAR(MAX)) AS OpCode,
		CAST(MtlTags.OprSeq				AS NVARCHAR(MAX)) AS OprSeq,
		CAST(MtlTags.PORel				AS NVARCHAR(MAX)) AS PORel,
		CAST(MtlTags.Reason				AS NVARCHAR(MAX)) AS Reason,
		CAST(MtlTags.ReasonDesc			AS NVARCHAR(MAX)) AS ReasonDesc,
		CAST(MtlTags.ResGrpID			AS NVARCHAR(MAX)) AS ResGrpID,
		CAST(MtlTags.TagFormat			AS NVARCHAR(MAX)) AS TagFormat,
		CAST(MtlTags.TagTitle			AS NVARCHAR(MAX)) AS TagTitle,
		CAST(MtlTags.VendID				AS NVARCHAR(MAX)) AS VendID,
		CAST(MtlTags.VendName			AS NVARCHAR(MAX)) AS VendName,
		CAST(MtlTags.QtyNum				AS NVARCHAR(MAX)) AS QtyNum,
		CAST(MtlTags.RptLanguageID		AS NVARCHAR(MAX)) AS RptLanguageID,
		CAST(MtlTags.Company			AS NVARCHAR(MAX)) AS Company,
		CAST(ISNULL(CustXPrt.CustNum, '''') AS NVARCHAR(MAX)) AS CustNum,

		CAST(ISNULL(Part.GrossWeight, 0) AS DECIMAL(15,5)) AS GrossWeight,
		CAST(ISNULL(Part.GrossWeightUOM, '''') AS NVARCHAR(MAX)) AS GrossWeightUOM,
		CAST(ISNULL(Part.TypeCode, ''Unknown'') AS NVARCHAR(MAX)) AS PartType,
		CAST(ISNULL(Part.LongProductID_c, '''') AS NVARCHAR(MAX)) AS LongProductID,
		CAST(ISNULL(Customer.CustID, '''') AS NVARCHAR(MAX)) AS CustID,
		CAST(ISNULL(ShipTo.ShipToNum, '''') AS NVARCHAR(MAX)) AS ShipToNum
		
	FROM    MtlTags_'+@TableID +' MtlTags WITH(NOLOCK)
	LEFT JOIN Part_' + @TableID + ' Part WITH(NOLOCK) ON MtlTags.Company = Part.Company AND MtlTags.PartNum = Part.PartNum
	LEFT JOIN CustXPrt_' + @TableID + ' CustXPrt WITH(NOLOCK) ON Part.Company = CustXPrt.Company AND Part.PartNum = CustXPrt.PartNum
	LEFT JOIN Customer_' + @TableID + ' Customer WITH(NOLOCK) ON CustXPrt.Company = Customer.Company AND CustXPrt.CustNum = Customer.CustNum
	LEFT JOIN ShipTo_' + @TableID + ' ShipTo WITH(NOLOCK) ON Customer.Company = ShipTo.Company AND Customer.CustNum = ShipTo.CustNum

	LEFT JOIN UD21_' + @TableID + ' BoxData WITH(NOLOCK) ON MtlTags.Company = BoxData.Company AND MtlTags.PartNum = BoxData.Key2 AND MtlTags.LotNum = BoxData.Character01	
	LEFT JOIN UD20_' + @TableID + ' PalletData WITH(NOLOCK) ON BoxData.Company = PalletData.Company AND BoxData.Key1 = PalletData.Key1
	'
	INSERT INTO #Results
	EXEC(@SQL);
END

--Convert the Part Type to something readable.
UPDATE #Results SET PartType = 'Purchased' WHERE PartType = 'P'
UPDATE #Results SET PartType = 'Manufactured' WHERE PartType = 'M'
UPDATE #Results SET PartType = 'Sales Kit' WHERE PartType = 'S'

SELECT TOP 1
	 ItemQty
	,PartNum
	,PartDesc
	,LotNum
	,XPartNum
	,OurSupplierCode
	,BoxPalletSerial
	,MfgDate
	,XRevisionNum
	,UM
	,PONum 
	,POLine
	,WhseCode
	,AsmSeq			
	,TagNum			
	,BCAsmSeq		
	,BCBinNum		
	,BCJobNum		
	,BCLotNum		
	,BCOprSeq		
	,BCPartNum		
	,BCWhseCode		
	,BinNum			
	,JobNum			
	,LaborNote		
	,NCComment		
	,NonConfTranID	
	,OpCode			
	,OprSeq			
	,PORel			
	,Reason			
	,ReasonDesc		
	,ResGrpID		
	,TagFormat		
	,TagTitle		
	,VendID			
	,VendName		
	,QtyNum	
	,GrossWeight	AS GrossWeightPerPart
	,GrossWeightUOM	AS GrossWeightUOMPerPart
	,PartType
	,Company
	,CustNum
	,LongProductID
	,CustID
	,ShipToNum

FROM #Results
WHERE (ShipToNum IS NULL OR ShipToNum <> '')
ORDER BY BoxPalletSerial DESC

END

If you run the SP and then:

USE [EpicorLive10_2Reports] SELECT * FROM #Results

Do you get data? Also, I would look at what’s causing the label to print. It might be pointing to the wrong server or db to fetch the results. You can also fill the results temp table with just some dummy data and see if it prints.