Solved

Using stored procedure  in SSRS

Posted on 2008-06-19
15
995 Views
Last Modified: 2010-04-21
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??
0
Comment
Question by:Laura2112
  • 8
  • 5
  • 2
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826644
can you show the procedure code?
does the procedure have the following line in the begining:

SET NOCOUNT ON
0
 

Author Comment

by:Laura2112
ID: 21826685
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?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826702
please show the procedures code... it's too late (for me) to think abstract :)
0
 

Author Comment

by:Laura2112
ID: 21826727
CREATE  PROCEDURE dbo.spLHPN_LA_QtrFeeRtp
      @StartDt DateTime,
      @EndDt DateTime

      AS

      TRUNCATE TABLE  tblLA_QtrFeeRpt
      INSERT tblLA_QtrFeeRpt

      SELECT CustomerNumber, vt.PrescriptionID,OrderDate, CAST(NULL AS VARCHAR (03)) DispenseLocation,
      CAST(NULL AS VARCHAR (03))PharmacyLocation,  OrderID
      FROM
      ( -- PDS data
      SELECT  CUSTOMERNUMBER, Convert(VarChar,MAX(P.OrderDate),101) OrderDate,L.PRESCRIPTIONID,O.OrderID
      FROM  ORDER700..TBLORDER O WITH (NOLOCK),
       ORDER700..TBLORDER_LINE L WITH (NOLOCK),
       DATAWAREHOUSE700..TBLPAYMENTTYPE 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.PRESCRIPTIONID,O.OrderID
      ) 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
0
 

Author Comment

by:Laura2112
ID: 21826734
This is my dataset for the table after the proc runs

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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826755
can you show how you run the proc and the select to fill the dataset, please?
0
 

Author Comment

by:Laura2112
ID: 21826791
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(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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826820
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)...
0
 

Author Comment

by:Laura2112
ID: 21826852
I was looking at solution ID 23276928 where it used two datasets.
WHere do I use the ExecuteNonQuery() command?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826871
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:
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

Open in new window

0
 

Author Comment

by:Laura2112
ID: 21826941
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(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
 
0
 
LVL 12

Expert Comment

by:jgv
ID: 21827194
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.
0
 

Author Comment

by:Laura2112
ID: 21827274
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?
0
 
LVL 12

Accepted Solution

by:
jgv earned 500 total points
ID: 21827441
No. If you have added the select statement to the end of the main stored procedure then SSRS will update the dataset for you but you have to requery the sproc.

Click on the Data tab and select the dataset where you are calling the stored procedure from. Execute the procedure directly by clicking the Run button on the toolbar (red exclamation mark). SSRS should prompt you to supply the input parameters defined in the stored procedure. After the procedure has finished running you should see results in the grid on the lower half of the window. The dataset should be updated with the fields that were returned. If it isn't, click the Refresh button on the toolbar (next to the Run button)
0
 

Author Closing Comment

by:Laura2112
ID: 31468975
Sorry, thought I graded this the other day. Thank you!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now