April 22, 2016 at 2:41 pm
I can get my code to return results like I want in SQL Studio however in SSRS
I can not get results they just come back blank. Any ideas by looking at the code?
Select
pay.fullname,
p.description 'Program',
bp.description 'Plan',
ek.carriermemid 'Member ID',
m.fullname 'Member Name',
et.county 'Member County',
c.controlnmb 'Patient Account',
cd.claimid,
cd.claimline,
c.status 'Status',
Cast(cd.dosfrom As date) 'StartD',
Cast(cd.dosto As date) 'EndD',
Cast(c.paiddate As date) 'Paid Date',
cd.location,
c.facilitycode + c.billclasscode + c.frequencycode 'BillType',
cd.revcode 'Revenue',
cd.servcode 'Procedure',
cd.modcode 'Modifier',
cd.servunits 'Quanity',
cd.claimamt 'Charged Amount',
cd.copay 'Copay',
cd.deductible 'deducible',
cd.memamt 'Cost Share',
cd.claimamt - cd.contractpaid 'Ineligible',
cd.ineligibleamt 'Misc Inelig',
crm.overridemessage 'Remit Message',
cd.amountpaid 'Paid Amount',
cd.paydiscount 'Interest',
cd.ProvMedicareMandatedAdjust 'Sequestration',
pay.fedid 'Tax Id',
pay.fullname 'Payto Name',
rend.fullname 'Service Provider',
rend.npi 'Servicing NPI',
con.description 'Contract Name',
ci.contracted 'Contracted?',
pt.description 'Provider Type',
spec.description 'Specialty',
cpcp.fullname 'PCP',
netw.Network,
cpcp.PCPCounty,
q.description 'Fund',
pc.checknbr,
pc.advanceapplied 'Advance Per Check',
c.reimbursemember 'Member Reimbursement Flag',
diag.codeid 'Diag Code',
pay.fedid 'Provider'
From
claimdetail cd Left Join
claimremit crm With(NoLock)
On cd.claimid = crm.claimid And cd.claimline = crm.claimline Left Join
claimdiag diag
On diag.claimid = cd.claimid And diag.sequence = '1' Left Join
benefitplan bp With(NoLock)
On bp.planid = cd.planid Left Join
claim c With(NoLock)
On c.claimid = cd.claimid Left Join
enrollkeys ek With(NoLock)
On ek.enrollid = c.enrollid Left Join
member m With(NoLock)
On m.memid = c.memid Left Join
entity et
On m.entityid = et.entid Left Join
program p With(NoLock)
On p.programid = ek.programid Left Join
provider rend With(NoLock)
On rend.provid = c.provid Left Join
providertype pt With(NoLock)
On rend.provtype = pt.provtype Left Join
provspecialty ps With(NoLock)
On rend.provid = ps.provid And ps.spectype = 'PRIMARY' And
Cast(ps.termdate As date) >= '2078-12-31' Left Join
specialty spec With(NoLock)
On spec.specialtycode = ps.specialtycode Left Join
affiliation a With(NoLock)
On a.affiliationid = c.affiliationid Left Join
(Select
cpcp.claimid,
pcp2.fullname,
pcp2.provid,
Cast(apcp.effdate As date) 'pcpeffdte',
etp.county 'PCPCounty'
From
claim cpcp With(NoLock) Left Join
enrollkeys ekpcp With(NoLock)
On ekpcp.enrollid = cpcp.enrollid Left Join
memberpcp mp With(NoLock)
On ekpcp.enrollid = mp.enrollid Left Join
affiliation apcp With(NoLock)
On apcp.affiliationid = mp.affiliationid Left Join
provider pcp2 With(NoLock)
On apcp.provid = pcp2.provid Left Join
entity etp
On pcp2.entityid = etp.entid
Where
Cast(cpcp.startdate As date) Between Cast(mp.effdate As date) And
Cast(mp.termdate As date)) cpcp
On cpcp.claimid = c.claimid Left Join
(Select
netw.provid,
network.fullname 'Network',
p.fullname 'PCP'
From
affiliation netw With(NoLock) Left Join
affiliation apcp With(NoLock)
On apcp.affiliationid = netw.affiliationid Left Join
provider network With(NoLock)
On netw.affiliateid = network.provid Left Join
provider p With(NoLock)
On netw.provid = p.provid
Where
netw.affiltype = 'NETWORK' And
Cast(apcp.effdate As date) Between Cast(netw.effdate As date) And
Cast(netw.termdate As date)) netw
On cpcp.provid = netw.provid Left Join
provider pay With(NoLock)
On a.affiliateid = pay.provid Left Join
qfund q With(NoLock)
On cd.fundid = q.fundid Left Join
payvoucher pv With(NoLock)
On pv.claimid = c.claimid Left Join
paycheck pc With(NoLock)
On pc.paymentid = pv.paymentid And cd.fundid = pc.fundid Left Join
contractinfo ci With(NoLock)
On ci.affiliationid = a.affiliationid And ek.programid = ci.programid And
Cast(c.startdate As date) Between Cast(ci.effdate As date) And
Cast(ci.termdate As date) Left Join
contract con With(NoLock)
On ci.contractid = con.contractid
Where
pay.fedid = @Provider And
cd.dosfrom = @StartD And
cd.dosto = @EndD
April 22, 2016 at 2:44 pm
The only difference that could happen between running this in SSMS and via SSRS would be in the parameter values. I'd start your troubleshooting efforts there. Make sure what you think you're passing in via the SSRS report is what is being executed against the SQL box.
April 22, 2016 at 3:04 pm
John Rowan (4/22/2016)
The only difference that could happen between running this in SSMS and via SSRS would be in the parameter values. I'd start your troubleshooting efforts there. Make sure what you think you're passing in via the SSRS report is what is being executed against the SQL box.
The code being run is the same and the Parameters were auto created by the report builder from the code. Never really had an issue with any other reports
April 25, 2016 at 2:19 am
If you're sure the code is the same, have you tried running a trace to double check this? Sometimes you can be "sure" that the report parameters are set up correctly, but then when you see the SQL SSRS is running you notice it isn't quite.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 6, 2016 at 4:15 pm
Are the databases that SSMS/SSRS pointing to the same or are they different?
It wouldn't hurt to double-check the parameters in Report Manager for the report. I have seen Report Manager set properties on parameters that are not set in BIDS/SSDT before.
Joie Andrew
"Since 1982"
May 6, 2016 at 6:45 pm
SilverBack (4/22/2016)
I can get my code to return results like I want in SQL Studio however in SSRSI can not get results they just come back blank. Any ideas by looking at the code?
Selectpay.fullname,
p.description 'Program',
bp.description 'Plan',
ek.carriermemid 'Member ID',
m.fullname 'Member Name',
et.county 'Member County',
c.controlnmb 'Patient Account',
cd.claimid,
cd.claimline,
c.status 'Status',
Cast(cd.dosfrom As date) 'StartD',
Cast(cd.dosto As date) 'EndD',
Cast(c.paiddate As date) 'Paid Date',
cd.location,
c.facilitycode + c.billclasscode + c.frequencycode 'BillType',
cd.revcode 'Revenue',
cd.servcode 'Procedure',
cd.modcode 'Modifier',
cd.servunits 'Quanity',
cd.claimamt 'Charged Amount',
cd.copay 'Copay',
cd.deductible 'deducible',
cd.memamt 'Cost Share',
cd.claimamt - cd.contractpaid 'Ineligible',
cd.ineligibleamt 'Misc Inelig',
crm.overridemessage 'Remit Message',
cd.amountpaid 'Paid Amount',
cd.paydiscount 'Interest',
cd.ProvMedicareMandatedAdjust 'Sequestration',
pay.fedid 'Tax Id',
pay.fullname 'Payto Name',
rend.fullname 'Service Provider',
rend.npi 'Servicing NPI',
con.description 'Contract Name',
ci.contracted 'Contracted?',
pt.description 'Provider Type',
spec.description 'Specialty',
cpcp.fullname 'PCP',
netw.Network,
cpcp.PCPCounty,
q.description 'Fund',
pc.checknbr,
pc.advanceapplied 'Advance Per Check',
c.reimbursemember 'Member Reimbursement Flag',
diag.codeid 'Diag Code',
pay.fedid 'Provider'
From
claimdetail cd Left Join
claimremit crm With(NoLock)
On cd.claimid = crm.claimid And cd.claimline = crm.claimline Left Join
claimdiag diag
On diag.claimid = cd.claimid And diag.sequence = '1' Left Join
benefitplan bp With(NoLock)
On bp.planid = cd.planid Left Join
claim c With(NoLock)
On c.claimid = cd.claimid Left Join
enrollkeys ek With(NoLock)
On ek.enrollid = c.enrollid Left Join
member m With(NoLock)
On m.memid = c.memid Left Join
entity et
On m.entityid = et.entid Left Join
program p With(NoLock)
On p.programid = ek.programid Left Join
provider rend With(NoLock)
On rend.provid = c.provid Left Join
providertype pt With(NoLock)
On rend.provtype = pt.provtype Left Join
provspecialty ps With(NoLock)
On rend.provid = ps.provid And ps.spectype = 'PRIMARY' And
Cast(ps.termdate As date) >= '2078-12-31' Left Join
specialty spec With(NoLock)
On spec.specialtycode = ps.specialtycode Left Join
affiliation a With(NoLock)
On a.affiliationid = c.affiliationid Left Join
(Select
cpcp.claimid,
pcp2.fullname,
pcp2.provid,
Cast(apcp.effdate As date) 'pcpeffdte',
etp.county 'PCPCounty'
From
claim cpcp With(NoLock) Left Join
enrollkeys ekpcp With(NoLock)
On ekpcp.enrollid = cpcp.enrollid Left Join
memberpcp mp With(NoLock)
On ekpcp.enrollid = mp.enrollid Left Join
affiliation apcp With(NoLock)
On apcp.affiliationid = mp.affiliationid Left Join
provider pcp2 With(NoLock)
On apcp.provid = pcp2.provid Left Join
entity etp
On pcp2.entityid = etp.entid
Where
Cast(cpcp.startdate As date) Between Cast(mp.effdate As date) And
Cast(mp.termdate As date)) cpcp
On cpcp.claimid = c.claimid Left Join
(Select
netw.provid,
network.fullname 'Network',
p.fullname 'PCP'
From
affiliation netw With(NoLock) Left Join
affiliation apcp With(NoLock)
On apcp.affiliationid = netw.affiliationid Left Join
provider network With(NoLock)
On netw.affiliateid = network.provid Left Join
provider p With(NoLock)
On netw.provid = p.provid
Where
netw.affiltype = 'NETWORK' And
Cast(apcp.effdate As date) Between Cast(netw.effdate As date) And
Cast(netw.termdate As date)) netw
On cpcp.provid = netw.provid Left Join
provider pay With(NoLock)
On a.affiliateid = pay.provid Left Join
qfund q With(NoLock)
On cd.fundid = q.fundid Left Join
payvoucher pv With(NoLock)
On pv.claimid = c.claimid Left Join
paycheck pc With(NoLock)
On pc.paymentid = pv.paymentid And cd.fundid = pc.fundid Left Join
contractinfo ci With(NoLock)
On ci.affiliationid = a.affiliationid And ek.programid = ci.programid And
Cast(c.startdate As date) Between Cast(ci.effdate As date) And
Cast(ci.termdate As date) Left Join
contract con With(NoLock)
On ci.contractid = con.contractid
Where
pay.fedid = @Provider And
cd.dosfrom = @StartD And
cd.dosto = @EndD
As Thom mentioned, a SQL trace is the way to go. Copy/paste the query passed by SSRS and run it in SSMS. This will also help you determine if your dataset is pointing to the right place. Ihave seen SSRS convert/ format parameters data in a way that could change your result set. A trace would help see if that's happening.
A couple other things to note. You may want to consider losing the nolock table hints if getting correct data every time you run that report is important. Lastly, you really should turn that query into a stored proc. This for many reasons, one of which you'll discover when running a trace.
-- Itzik Ben-Gan 2001
May 25, 2016 at 2:52 pm
I agree with the others, try verifying that you are not running the code in two different servers. There is a possibility that, the data value does not exists in one server.
May 26, 2016 at 12:22 pm
IF you have ascertained that you are indeed working off the exact same database in SSMS and in your SSRS report, move on to step 2:
Whittle down (simplify) your T-SQL and see where it starts working. Put in a TOP 1 after the SELECT keyword to limit what you end up with and remove the last condition in your last WHERE statement. Run. If no results, remove one more condition. Lather, rinse, repeat. When it finally works, check to why the last (blocking) filter was based on data that is not in the database.
If you run out of conditions and it still does not work, bite the bullet and remove the derived tables from your select and re-insert them one at a time - check if it still works ...
When you finally get it to work, you can look at improving the T-SQL and try using CTE's and windowing functions to avoid the hit of derived tables related to the rows of the main table. This is SS2K12. If you get the report to run fast then you could lose the NOLOCK hint.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply