asked on

How to change the dataset code in SSRS to a store procedure

Hi Experts,

My manager wants me to use a store procedure instead of dataset code in SSRS. This is the original dataset code in SSRS below. I tried to change it to a store procedure, but the data did not show through the store procedure. Can you help me on it? Thanks.

Set DATEFIRST 1
Declare @BeginDate datetime 
Declare @MyTable table(
	StoreID int
)
IF @AMID is null
BEGIN
	Insert into @MyTable Select StoreID from tblStores
END
ELSE
BEGIN
	Insert into @MyTable Select StoreID from tblStores Where AMID=@AMID
END


Set @BeginDate=DateAdd(d,-DatePart(dw,@EndDate),@EndDate)

SELECT  @BeginDate as BeginDate,@EndDate as ReportDate,  
	tblStores.StoreID, tblStores.StoreName, ContractString,
	UPPER(vps.UserFirstName) + ' ' + UPPER(vps.UserLastName) as VPName,
	UPPER(ams.UserFirstName) + ' ' + UPPER(ams.UserLastName) as AMName,
	tblLoans.LoanID, 
	tblCustomers.CustomerFirstName + ' ' + tblCustomers.CustomerLastName as CustomerName, 
         tblUsers.UserFirstName + ' ' + tblUsers.UserLastName as Employee, 
	tblLoanTransactions.TransactionDateTime,  
         tblLoanTransactions.TransactionAmt, 
	tblLoanTransactions.TransactionNotes, 
	tblLoanTransactions.TransactionID as ReceiptNumber, 
	tblStores.vpID, 
	tblStores.amID,TransactionPrincPaid,TransactionInterPaid,SettlementReason,SettlementAmount
FROM         tblLoanTransactions INNER JOIN
                      tblLoans ON tblLoanTransactions.LoanID = tblLoans.LoanID INNER JOIN
                      tblReportingTransactions ON tblLoanTransactions.TransactionID = tblReportingTransactions.TransactionID INNER JOIN
                      tblStores ON tblLoans.LoanStoreID = tblStores.StoreID INNER JOIN
                      tblUsers ON tblLoanTransactions.TransactionUserID = tblUsers.UserID INNER JOIN
                      tblCustomers ON tblLoans.CustomerID = tblCustomers.CustomerID
			INNER JOIN tblUsers ams
			on tblStores.AMID=ams.UserID

			INNER JOIN tblUsers vps
			on tblStores.VPID=vps.UserID
WHERE              (tblLoanTransactions.TransactionDateTime between @BeginDate and @EndDate+' 23:59:59') and tblLoanTransactions.TransactionTypeID in (9, 10)
 AND tblloans.LoanStoreID  IN (Select StoreID from @MyTable)
AND tblLoanTransactions.TransactionAmt>0
ORDER BY AMName, tblstores.storeid

Open in new window

tanj1035

ASKER

Can you help me work on the code of store procedure? Many Thanks!

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ValentinoV

I've got a couple of articles on that, may be good to have a read through. Here they are:

Reporting On Data From Stored Procedures (part 1)
Reporting On Data From Stored Procedures (part 2)

kannanpdx

if the data is not returned from the stored procedure there may be some thing wrong with your joins or data..

try troubleshooting.. you can use print and select statements on stored procedures to see the expected values are returned.

like make sure you have data on @MyTable .. do select * from @MyTable after the below block of statement in your stored procedure..

"IF @AMID is null
BEGIN
Insert into @MyTable Select StoreID from tblStores
END
ELSE
BEGIN
Insert into @MyTable Select StoreID from tblStores Where AMID=@AMID
END"

Take the query from data set and execute by passing the required parameters.. if this is returning records..

create a stored procedure with same query and same parameter..This must match the rows that you are getting on data set.. check on the link provided by ValentinoV ...

tanj1035

ASKER

Thank Valentino and kannanpdx's sharing. I will read the articles.

Vadim Rapp

little cosmetic improvement: instead of

IF @AMID is null
BEGIN
Insert into @MyTable Select StoreID from tblStores
END
ELSE
BEGIN
Insert into @MyTable Select StoreID from tblStores Where AMID=@AMID
END

we can use

Insert into @MyTable Select StoreID from tblStores where AMID=isnull(@AMID,AMID)