Using Variables from Stored Procedure in SSRS Report
Hi,
I have 3 variables defined in my stored procedure inside the BEGIN like below. I want to display those varibles in my SSRS report but I don't how I can display those variables. PLease help. Thank you.
BEGIN
DECLARE
@MMRATE1 AS
DECIMAL(6,3)
DECLARE @MMRATE2
AS
DECIMAL(6,3)
DECLARE
@CKRATE AS
DECIMAL(6,3)
SET
@MMRATE1=1.2
SET
@MMRATE2=0.92
SET
@CKRATE =0.81
SELECT
DISTINCT
<Query>
END
November 10th, 2010 11:55am
Try this
SELECT @MMRATE1 AS MMRATE1 ,@MMRATE2 AS MMRATE2 ,@CKRATE AS CKRATE
Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 12:51pm
Than variable will come as column in your data set and you can use it where ever you want.Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
November 10th, 2010 12:52pm
Hi Gaurav,
Thank you but I have unions in my man query hence if I use what you said then all of my fields vanishes and I just get the rates. Is there any other workaround. Thank you for your help.
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 1:32pm
can you please share your main query with us .
thanksGaurav Gupta http://sqlservermsbiblog.blogspot.com/
November 10th, 2010 2:00pm
Or try this
SELECT T.*,@MMRATE1 AS MMRATE1 ,@MMRATE2 AS MMRATE2 ,@CKRATE AS CKRATE
FROM (
You main Query which has Union
) AS T
Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 2:01pm
Here is the main query
DECLARE @MMRATE1
AS DECIMAL(6,3)
DECLARE @MMRATE2
AS DECIMAL(6,3)
DECLARE @CKRATE
AS DECIMAL(6,3)
SET @MMRATE1=1.12
SET @MMRATE2=0.99
SET @CKRATE
=0.85
SELECT
DISTINCT
'MM - Deposits > or = $100,000'
AS 'Type',
DA.ACCT_NO AS
'Account No',
DA.ACCT_TYPE AS
'Account Type',
DA.RIM_NO
AS 'RIM #',
DA.TITLE_1
AS 'Customer Name',
DA.BRANCH_NO AS
'Branch No',
AGB.SHORT_NAME AS
'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT))
AS 'Date Opened',
DD.CUR_BAL AS
'Current Balance',
DD.CLASS_CODE AS
'Class Code',
ADC.DESCRIPTION
AS 'Class Description',
DAIO.CURRENT_RATE
AS 'Interest Rate',
DAIO.MARGIN AS
'Margin',
DAIO.MARGIN_TYPE
AS 'Margin Type',
DA.[STATUS] AS
'Status',
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO
= DAIO.ACCT_NO
AND DA.ACCT_TYPE
= DAIO.ACCT_TYPE
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE
DA.STATUS
<>'CLOSED'
AND
DD.CUR_BAL>99999.99
AND
DA.ACCT_TYPE IN
('CK','SV')
AND
DAIO.CURRENT_RATE>@MMRATE1
UNION
ALL
SELECT
DISTINCT
'MM - Deposits < $100,000'
AS 'Type',
DA.ACCT_NO AS
'Account No',
DA.ACCT_TYPE AS
'Account Type',
DA.RIM_NO
AS 'RIM #',
DA.TITLE_1
AS 'Customer Name',
DA.BRANCH_NO AS
'Branch No',
AGB.SHORT_NAME AS
'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT))
AS 'Date Opened',
DD.CUR_BAL AS
'Current Balance',
DD.CLASS_CODE AS
'Class Code',
ADC.DESCRIPTION
AS 'Class Description',
DAIO.CURRENT_RATE
AS 'Interest Rate',
DAIO.MARGIN AS
'Margin',
DAIO.MARGIN_TYPE
AS 'Margin Type',
DA.[STATUS] AS
'Status'
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO
= DAIO.ACCT_NO
AND DA.ACCT_TYPE
= DAIO.ACCT_TYPE
AND DAIO.DEBIT_CREDIT
= 'CR'
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE
DA.STATUS
<>'CLOSED'
AND
DD.CUR_BAL<100000
AND
DA.ACCT_TYPE IN
('CK','SV')
AND
DAIO.CURRENT_RATE>@MMRATE2
UNION
ALL
SELECT
DISTINCT
'Checking Accounts'
AS 'Type',
DA.ACCT_NO AS
'Account No',
DA.ACCT_TYPE AS
'Account Type',
DA.RIM_NO
AS 'RIM #',
DA.TITLE_1
AS 'Customer Name',
DA.BRANCH_NO AS
'Branch No',
AGB.SHORT_NAME AS
'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT))
AS 'Date Opened',
DD.CUR_BAL AS
'Current Balance',
DD.CLASS_CODE AS
'Class Code',
ADC.DESCRIPTION
AS 'Class Description',
DAIO.CURRENT_RATE
AS 'Interest Rate',
DAIO.MARGIN AS
'Margin',
DAIO.MARGIN_TYPE
AS 'Margin Type',
DA.[STATUS] AS
'Status'
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO
= DAIO.ACCT_NO
AND DA.ACCT_TYPE
= DAIO.ACCT_TYPE
AND DAIO.DEBIT_CREDIT
= 'CR'
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE
DA.STATUS
<>'CLOSED'
AND
DA.ACCT_TYPE IN
('CK','SV')
AND
DAIO.CURRENT_RATE>@CKRATE
November 10th, 2010 2:06pm
Try following,
I know it will repeat parameter value through out your result set. but you can use first function in SSRS to just pickup one value
DECLARE @MMRATE1 AS DECIMAL(6,3)
DECLARE @MMRATE2 AS DECIMAL(6,3)
DECLARE @CKRATE AS DECIMAL(6,3)
SET @MMRATE1=1.12
SET @MMRATE2=0.99
SET @CKRATE =0.85
SELECT T.*,@MMRATE1 AS MMRATE1 ,@MMRATE2 AS MMRATE2 ,@CKRATE AS CKRATE
FROM (
SELECT DISTINCT
'MM - Deposits > or = $100,000' AS 'Type',
DA.ACCT_NO AS 'Account No',
DA.ACCT_TYPE AS 'Account Type',
DA.RIM_NO AS 'RIM #',
DA.TITLE_1 AS 'Customer Name',
DA.BRANCH_NO AS 'Branch No',
AGB.SHORT_NAME AS 'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT)) AS 'Date Opened',
DD.CUR_BAL AS 'Current Balance',
DD.CLASS_CODE AS 'Class Code',
ADC.DESCRIPTION AS 'Class Description',
DAIO.CURRENT_RATE AS 'Interest Rate',
DAIO.MARGIN AS 'Margin',
DAIO.MARGIN_TYPE AS 'Margin Type',
DA.[STATUS] AS 'Status',
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO = DAIO.ACCT_NO AND DA.ACCT_TYPE = DAIO.ACCT_TYPE
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE DA.STATUS <>'CLOSED' AND DD.CUR_BAL>99999.99 AND DA.ACCT_TYPE IN ('CK','SV') AND DAIO.CURRENT_RATE>@MMRATE1
UNION ALL
SELECT DISTINCT
'MM - Deposits < $100,000' AS 'Type',
DA.ACCT_NO AS 'Account No',
DA.ACCT_TYPE AS 'Account Type',
DA.RIM_NO AS 'RIM #',
DA.TITLE_1 AS 'Customer Name',
DA.BRANCH_NO AS 'Branch No',
AGB.SHORT_NAME AS 'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT)) AS 'Date Opened',
DD.CUR_BAL AS 'Current Balance',
DD.CLASS_CODE AS 'Class Code',
ADC.DESCRIPTION AS 'Class Description',
DAIO.CURRENT_RATE AS 'Interest Rate',
DAIO.MARGIN AS 'Margin',
DAIO.MARGIN_TYPE AS 'Margin Type',
DA.[STATUS] AS 'Status'
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO = DAIO.ACCT_NO AND DA.ACCT_TYPE = DAIO.ACCT_TYPE AND DAIO.DEBIT_CREDIT = 'CR'
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE DA.STATUS <>'CLOSED' AND DD.CUR_BAL<100000 AND DA.ACCT_TYPE IN ('CK','SV') AND DAIO.CURRENT_RATE>@MMRATE2
UNION ALL
SELECT DISTINCT
'Checking Accounts' AS 'Type',
DA.ACCT_NO AS 'Account No',
DA.ACCT_TYPE AS 'Account Type',
DA.RIM_NO AS 'RIM #',
DA.TITLE_1 AS 'Customer Name',
DA.BRANCH_NO AS 'Branch No',
AGB.SHORT_NAME AS 'Branch Name',
DATEADD(DD,0,DATEDIFF(DD,0,DA.CREATE_DT)) AS 'Date Opened',
DD.CUR_BAL AS 'Current Balance',
DD.CLASS_CODE AS 'Class Code',
ADC.DESCRIPTION AS 'Class Description',
DAIO.CURRENT_RATE AS 'Interest Rate',
DAIO.MARGIN AS 'Margin',
DAIO.MARGIN_TYPE AS 'Margin Type',
DA.[STATUS] AS 'Status'
FROM DP_DISPLAY DD
LEFT JOIN DP_ACCT DA
ON DA.ACCT_NO=DD.ACCT_NO
LEFT JOIN AD_DP_CLS ADC
ON ADC.CLASS_CODE=DD.CLASS_CODE
LEFT JOIN DP_ACCT_INT_OPT DAIO
ON DA.ACCT_NO = DAIO.ACCT_NO AND DA.ACCT_TYPE = DAIO.ACCT_TYPE AND DAIO.DEBIT_CREDIT = 'CR'
INNER JOIN AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE DA.STATUS <>'CLOSED' AND DA.ACCT_TYPE IN ('CK','SV') AND DAIO.CURRENT_RATE>@CKRATE
) AS T
Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 2:14pm
Thank you but there is an issue. I can bring the fields but the main query does not always return value in that case the rates also does not show. I want the rates to be always available regardless of the query return value or not. Thank you.
November 10th, 2010 2:18pm
OK,
Try add this line at the end of above code i had given you
UNION
SELECT NULL,NULL,@MMRATE1 AS MMRATE1 ,@MMRATE2 AS MMRATE2 ,@CKRATE AS CKRATE
because of NULL i think your aggregation wont affect when you have data,
and when you don't have data only this line will come up
Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 2:34pm
Number of NULL columns in above union statement will be equal to No of columns you are getting from your "main union query" result set. Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
November 10th, 2010 2:37pm
Thank you. The last union with null seems to work. I appreciate your help.
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 2:50pm
This topic is archived. No further replies will be accepted.
Other recent topics
