Laura2112
asked on
Using stored procedure in SSRS
Hi, I have report parameters being passed to a store procedure dataset. That works fine. I confirm that my SQL table has the results I expect. Then I added a table to the same report, and use the table for my second dataset, querying the table I just propulated in mt first recordset. However, when I run the report, nothing comes back in my table. Suggestions??
ASKER
this is wht I have in the dataset. - spLHPN_LA_QtrFeeRtp. The code itself is taking the parameters and truncating a table and popping it using the passed parameters.
No, I am not using SET NOCOUNT ON. I am wondering tho if I should have brought back the results in the procedure? Does SSRS know that the proc run first and the second dataset next, which refers to the results of the first dataset? Like a refresh on the table?
No, I am not using SET NOCOUNT ON. I am wondering tho if I should have brought back the results in the procedure? Does SSRS know that the proc run first and the second dataset next, which refers to the results of the first dataset? Like a refresh on the table?
please show the procedures code... it's too late (for me) to think abstract :)
ASKER
CREATE PROCEDURE dbo.spLHPN_LA_QtrFeeRtp
@StartDt DateTime,
@EndDt DateTime
AS
TRUNCATE TABLE tblLA_QtrFeeRpt
INSERT tblLA_QtrFeeRpt
SELECT CustomerNumber, vt.PrescriptionID,OrderDat e, CAST(NULL AS VARCHAR (03)) DispenseLocation,
CAST(NULL AS VARCHAR (03))PharmacyLocation, OrderID
FROM
( -- PDS data
SELECT CUSTOMERNUMBER, Convert(VarChar,MAX(P.Orde rDate),101 ) OrderDate,L.PRESCRIPTIONID ,O.OrderID
FROM ORDER700..TBLORDER O WITH (NOLOCK),
ORDER700..TBLORDER_LINE L WITH (NOLOCK),
DATAWAREHOUSE700..TBLPAYME NTTYPE T WITH (NOLOCK),
PHARMACY.DBO.PDSMANIFEST P WITH (NOLOCK)
WHERE O.ORDERID = L.ORDERID AND
T.PAYMENTTYPEID = O.PAYMENTTYPEID AND
O.ORDERID = P.CISORDERNUM AND
ORDERSTATUSID = 4 AND
P.STATE = 'LA' AND
P.ORDERDATE BETWEEN @StartDt AND @EndDt AND P.CUORDERNUM IS NOT NULL AND PrescriptionID IS NOT NULL
GROUP BY CUSTOMERNUMBER,L.PRESCRIPT IONID,O.Or derID
) vt
LEFT JOIN (SELECT DISTINCT PrescriptionID
FROM Pharmacy..Prescription where PrescriptionID IS NOT NULL
GROUP BY PrescriptionID
)RX
ON vt.PrescriptionID = RX.PrescriptionID
--======================== ========== ========== ========== ========== ========== ========== ========== ========== ======
UPDATE LA
SET PharmacyLocation = vt.PharmacyLocation,
DispenseLocation = vt.DispenseLocation
FROM dbo.tblLA_QtrFeeRpt LA
LEFT JOIN (SELECT OrderID,
CASE DispenseLocation WHEN 1 THEN 'FL' ELSE 'NV' END DispenseLocation,
CASE PharmacyLocation WHEN '03' THEN 'OH'
WHEN '15' THEN 'NV' WHEN '99' THEN 'FL'
WHEN '19' THEN 'NJ' ELSE 'UNK' END PharmacyLocation
FROM Pharmacy..PrintLog
WHERE PharmacyLocation <> 0
)vt
ON LA.OrderID = vt.OrderID
GO
@StartDt DateTime,
@EndDt DateTime
AS
TRUNCATE TABLE tblLA_QtrFeeRpt
INSERT tblLA_QtrFeeRpt
SELECT CustomerNumber, vt.PrescriptionID,OrderDat
CAST(NULL AS VARCHAR (03))PharmacyLocation, OrderID
FROM
( -- PDS data
SELECT CUSTOMERNUMBER, Convert(VarChar,MAX(P.Orde
FROM ORDER700..TBLORDER O WITH (NOLOCK),
ORDER700..TBLORDER_LINE L WITH (NOLOCK),
DATAWAREHOUSE700..TBLPAYME
PHARMACY.DBO.PDSMANIFEST P WITH (NOLOCK)
WHERE O.ORDERID = L.ORDERID AND
T.PAYMENTTYPEID = O.PAYMENTTYPEID AND
O.ORDERID = P.CISORDERNUM AND
ORDERSTATUSID = 4 AND
P.STATE = 'LA' AND
P.ORDERDATE BETWEEN @StartDt AND @EndDt AND P.CUORDERNUM IS NOT NULL AND PrescriptionID IS NOT NULL
GROUP BY CUSTOMERNUMBER,L.PRESCRIPT
) vt
LEFT JOIN (SELECT DISTINCT PrescriptionID
FROM Pharmacy..Prescription where PrescriptionID IS NOT NULL
GROUP BY PrescriptionID
)RX
ON vt.PrescriptionID = RX.PrescriptionID
--========================
UPDATE LA
SET PharmacyLocation = vt.PharmacyLocation,
DispenseLocation = vt.DispenseLocation
FROM dbo.tblLA_QtrFeeRpt LA
LEFT JOIN (SELECT OrderID,
CASE DispenseLocation WHEN 1 THEN 'FL' ELSE 'NV' END DispenseLocation,
CASE PharmacyLocation WHEN '03' THEN 'OH'
WHEN '15' THEN 'NV' WHEN '99' THEN 'FL'
WHEN '19' THEN 'NJ' ELSE 'UNK' END PharmacyLocation
FROM Pharmacy..PrintLog
WHERE PharmacyLocation <> 0
)vt
ON LA.OrderID = vt.OrderID
GO
ASKER
This is my dataset for the table after the proc runs
SELECT Period, CalMonthNumofYear,COUNT(DI STINCT PrescriptionID) DistinctRXs,
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL ocation
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
SELECT Period, CalMonthNumofYear,COUNT(DI
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
can you show how you run the proc and the select to fill the dataset, please?
ASKER
The report parameters are providing my StartDt and EndDt
dataset 1
spLHPN_LA_QtrFeeRtp Command Type = StoredProcedure
dataset 2 CommandType = Text
SELECT Period, CalMonthNumofYear,COUNT(DI STINCT PrescriptionID) DistinctRXs,
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL ocation
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
dataset 1
spLHPN_LA_QtrFeeRtp Command Type = StoredProcedure
dataset 2 CommandType = Text
SELECT Period, CalMonthNumofYear,COUNT(DI
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
question: as the procedure does not return any data actually, why do you use a dataset?
just use the sqlcommand and issue the ExecuteNonQuery().
after that, you would then fill the dataset with the select query (which should work just fine)...
just use the sqlcommand and issue the ExecuteNonQuery().
after that, you would then fill the dataset with the select query (which should work just fine)...
ASKER
I was looking at solution ID 23276928 where it used two datasets.
WHere do I use the ExecuteNonQuery() command?
WHere do I use the ExecuteNonQuery() command?
sorry, I was somehow under the impression that you where using .net code and not the reporting services (as I said, it's late for me)...
anyhow, please try 1 dataset:
anyhow, please try 1 dataset:
dataset 1 CommandType = Text
SET NOCOUNT ON
exec spLHPN_LA_QtrFeeRtp
SELECT Period, CalMonthNumofYear,COUNT(DISTINCT PrescriptionID) DistinctRXs,
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyLocation
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
ASKER
It is not getting the expected parameter StartDt. Tried both 'StartDt', 'EndDt' and @StartDt, @EndDt
---CommandType = Text
SET NOCOUNT ON
Exec spLHPN_LA_QtrFeeRtp @StartDt, @EndDt
SELECT Period, CalMonthNumofYear,COUNT(DI STINCT PrescriptionID) DistinctRXs,
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL ocation
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
---CommandType = Text
SET NOCOUNT ON
Exec spLHPN_LA_QtrFeeRtp @StartDt, @EndDt
SELECT Period, CalMonthNumofYear,COUNT(DI
COUNT(PrescriptionID) RXs,
CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END DispenseLocation,PharmacyL
FROM
(
SELECT CustomerNumber, PrescriptionID, OrderDate, DispenseLocation, PharmacyLocation, CalMonthNumofYear,
OrderID, (CALMonthName + ' ' + CalendarYear) Period
FROM dbo.tblLA_QtrFeeRpt L
JOIN dbo.dimTime T ON T.FullDateAlternateKey = L.OrderDate
)vt
GROUP BY Period, CalMonthNumofYear, CASE DispenseLocation WHEN 'NV' THEN 'Medco' ELSE 'Liberty' END,
PharmacyLocation
SQL Reports does not wait for one dataset to finish before executing the next. In other words, your query that retrieves the report data is running before the Truncate/Populate procedure has finished. If you move your select statement to the end of the main stored procedure it will return the results to the report (you only need 1 dataset).
So within the same stored procedure you would perform all three operations:
TRUNCATE TABLE ......
UPDATE LA SET......
SELECT Period, CalMonthNumofYear,.....
If you set it up this way and then run the stored procedure from the "Data" tab, SQL Reports will add update the dataset with the fields being returned by the sproc.
So within the same stored procedure you would perform all three operations:
TRUNCATE TABLE ......
UPDATE LA SET......
SELECT Period, CalMonthNumofYear,.....
If you set it up this way and then run the stored procedure from the "Data" tab, SQL Reports will add update the dataset with the fields being returned by the sproc.
ASKER
Sounds good but not quite yet. I do not have fields from the dataset to use now in an expression. Should I be typing the field names in the Fields tab of the dataset?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, thought I graded this the other day. Thank you!
does the procedure have the following line in the begining:
SET NOCOUNT ON