on 08-22-2014 6:55 PM
I have a very detailed Crystal report that needs changing. I can open the report and modify fields and formulas on the report and it saves fine. When I open the Database Exoert and click edit command, then OK, I am not able to save the report. I did not even make changes to the command. I get the message "Failed to save document.", then "Failed to save database information" and then "Database Connector Error: The specified object was not saved."
I am using CR Developer Full Edition, Version 11.5.10.1263.
I have tried to make a new connection/updating the datasource and still get the same problem.
Hi Brian,
Can you send the command, we can try that one
Thanks,
DJ
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here is the SQL. By the way, this command works in SQL server and also in the report when I run it. The only issue is I can not save it.
/*---------------------------------------------------------------------------------------------------------------------
Base data for Interest calculation
Get claim data for interest calculation on claims.
Used for Interest Report.rpt
*****CHANGE HISTORY****************************************************************************************************
| Date | Editor | Summary
| 07/05/2012 | D. Heacox | Initial iteration
| 07/27/2012 | D. Heacox | Added adjustment code at request of Rhonda Haggins
| 08/14/2012 | D. Heacox | Added criteria to only include claims created before last Friday
| 09/07/2012 | D. Heacox | Added check to drop any existing temporary tables before creating new ones
| 09/21/2012 | D. Heacox | Removed temp table for interest; replaced with reference to new table in CompanyReports db
| | | Removed temp table for clean dispositions; replaced with table variable
| 07/08/2013 | D. Heacox | Added logic to get linked state for interest calculation from payor ins record and only use the provider address state where there is no state on the payor
(currently includes FIVP, CENTENE, and other misc. records); Changed state label to provider state
***********************************************************************************************************************
*/
--Variables for changing report date or the date the next report date is based on
DECLARE @weekday_of_report as int
DECLARE @next_report_based_on_day as datetime
Set @weekday_of_report = 6; --Report date is Friday
Set @next_report_based_on_day = GETDATE(); --Based on report run date (scheduled for Monday)
--Table variable for clean dispositions
DECLARE @cleandispositions as TABLE (code varchar(100),clean bit)
INSERT INTO @cleandispositions
VALUES
('AAnthemAdjust',0)
,('AAPPEALADJ',0)
,('AAPPEALCC',0)
,('AAPPEALNEW',0)
,('AAPPEALREV',0)
,('AApprCost',1)
,('AAPPTCOM',1)
,('AAVFLOT',1)
,('ABilat',1)
,('ABilatMulti',1)
,('ABillMarkup',1)
,('ABillSched',1)
,('ABirthdate',1)
,('ABundledRate',1)
,('AChange->Add',1)
,('ACOB',1)
,('ACOBCOMM',1)
,('ACOBDISC',1)
,('ACOBMCARE',1)
,('ACOBUsed',1)
,('ACODEREQMR',1)
,('ACOINSAPLID',1)
,('ACopayment',1)
,('ACORRCLAIM',1)
,('ADJUSTED',0)
,('ADMINAPPR',0)
,('ADMINREV',0)
,('AEffUpd',1)
,('AFamDeductible',1)
,('AFamDeductMet',1)
,('AFamOOPMax',1)
,('AFamOOPMaxMet',1)
,('AFREQMN',1)
,('AGlobal',1)
,('AGlobalCap',1)
,('AHISTORICAL',0)
,('AIndDeductible',1)
,('AIndDeductMet',1)
,('AIndOOPMaxMet',1)
,('AINTEREST',0)
,('ALTRMDNEC',1)
,('AMaxCostLife',1)
,('AMaxCostYear',1)
,('AMaxDaysYr',1)
,('AMEDREC',1)
,('AMEMBER',1)
,('AMEMCO',1)
,('AMetCertUnit',1)
,('AModifer24',1)
,('AModifer59',1)
,('AMulti',1)
,('ANegRate',1)
,('ANewProvTerm',1)
,('AOPREPORT',1)
,('APLYDUTIB',1)
,('Approved',1)
,('APPROVEDMAXMET',1)
,('APreCertRate',1)
,('AProvCap',1)
,('AProvDiscnt',1)
,('APtCoinsur',1)
,('APtDeduct',1)
,('APtOOPMax',1)
,('APtPayOverAllow',1)
,('AREFUND',0)
,('AREPROCESS',0)
,('ARetroDelete',0)
,('A-Reversal',0)
,('A-Reversed',0)
,('AServ$/Ben',1)
,('AServCap',1)
,('AServDiscnt',1)
,('ASERVINC',1)
,('AServSched',1)
,('ASUPPLE',0)
,('ATermUpd',1)
,('ATXCINT',0)
,('ATXINTSB418',0)
,('AWRITEOFF',0)
,('AWRITRECON',0)
,('AWRITRECONGA2',0)
,('AWRITRECONOH2',0)
,('CONNIEUSE',0)
,('D76510',1)
,('D92015',1)
,('D92015OD',1)
,('D92225DOC',1)
,('D92225DUP',1)
,('D92225EX',1)
,('DADDON',1)
,('DADDONDEL',1)
,('DADDONOON',1)
,('DADDONPC',1)
,('DApprCost',1)
,('DASNCOV',1)
,('DASPHERIC',1)
,('DASPHLENS',0)
,('DBadClaimDx1',0)
,('DBadClaimDx2',0)
,('DBadClaimDx3',0)
,('DBadClaimDx4',0)
,('DBadClaimDx5',0)
,('DBadClaimDx6',0)
,('DBadClaimDx7',0)
,('DBadClaimDx8',0)
,('DBENINFO',1)
,('DBILL92015',1)
,('DBUNDLED',1)
,('DBUNDLED92015',1)
,('DBUNDLEDIHCP',1)
,('DCatholic',1)
,('DCertAddress',1)
,('DCertIPDates',1)
,('DCertPlaceSv',1)
,('DCertVisits',1)
,('DClaimsRunout',0)
,('DCLMAGE120',1)
,('DCLMAGE150',1)
,('DCLMAGE180',1)
,('DCLMAGE365',1)
,('DCLMAGE365OON',1)
,('DCLMAGE60',1)
,('DCLMAGE90',1)
,('DCLMAGE95',1)
,('DCLMAGE95TX',1)
,('DCLMAGEAPP45',0)
,('DCLMAGEAPP60',0)
,('DCLMREF120TX',1)
,('DCLMREFIL120',1)
,('DCLMREFIL150',1)
,('DCLMREFIL180',1)
,('DCLMREFIL90',1)
,('DCLMREFILYR',1)
,('DCLMREFUND',0)
,('DCLOSEDOFF',0)
,('DCOCLFIT',1)
,('DCODEPUNCTAL',1)
,('DCOMGMT',0)
,('DCORRCLM',0)
,('DCovDepend',1)
,('DCovExpire',1)
,('DCovNone',0)
,('DCovNotBegun',1)
,('DCovPlanTerm',0)
,('DCovSpouse',1)
,('DCovStudent',1)
,('DCovTooOld',1)
,('DCPP',0)
,('DCPTNOTRT',1)
,('DCPTNOTRTOON',1)
,('DCPTNOTRTUPMC',1)
,('DCROWN',1)
,('DCROWNS',1)
,('DDESCRIPT',0)
,('DDEVELOP',1)
,('DDISCOUNTPLAN',1)
,('DDISPFEE',1)
,('DDOSBEFORE',1)
,('DDOSSPAN',1)
,('DDuplClaim',0)
,('DDuplService',0)
,('DDXNOTAPPR',1)
,('DDXNOTAPPRHW',1)
,('DDXNOTAPPRMD',1)
,('DDxNotCertif',1)
,('DDXTEST',1)
,('DEDIPHYLOC',0)
,('DEDIPROV',0)
,('DEMCODEMDDX',1)
,('DEMCODEOON',1)
,('DEMCODERDX',1)
,('DENCOC',1)
,('Denied',1)
,('DENIEDBPR',1)
,('DENIEDCC',1)
,('DENIEDCCKY',1)
,('DENIEDGLBL',1)
,('DENIEDLCP',1)
,('DENIEDNDC',0)
,('DENIEDPOS',1)
,('DENOVPOON:',1)
,('DENRPHW',1)
,('DExpiredDiagCode',0)
,('DExpiredModifier',0)
,('DExpiredServCode',0)
,('DFollowupService',1)
,('DFQHC',1)
,('DFQHCPOS',1)
,('DFREQCEX',1)
,('DFREQIEX',1)
,('DFREQMN',1)
,('DFREQMNAV',1)
,('DFREQMNMR',1)
,('DGCIDISP',1)
,('DGCIHW',1)
,('DGCODENOTCOV',1)
,('DHPLENS',0)
,('DHWDISPFEE',1)
,('DHWORRP',1)
,('DIDPT',0)
,('DInaptPrimaryDx',0)
,('DINCORRTAXID',0)
,('DInfoCPT',0)
,('DInfoDate',0)
,('DInfoDx',0)
,('DInfoPlace',0)
,('DInfoProv',0)
,('DInfoPt',0)
,('DInfoRefer',0)
,('DInfoServCat',0)
,('DINVCPT',0)
,('DINVDX',0)
,('DINVMOD',0)
,('DINVNDC',0)
,('DINVNDCQTY',0)
,('DINVNDCUT',0)
,('DINVNPI',0)
,('DINVOICE',0)
,('DINVPOS',0)
,('DItemizedBill',1)
,('DKERA',1)
,('DLENSCODE',1)
,('DLENSFIT',1)
,('DLOGISTICS',1)
,('DLTRMDNEC',0)
,('DManagedCare',1)
,('DMATCHVER',1)
,('DMAXAGE18',1)
,('DMAXAGE20',1)
,('DMAXAGE22',1)
,('DMAXAGE23',1)
,('DMAXAGE5',1)
,('DMaxCostLife',1)
,('DMaxCostYear',1)
,('DMaxDaysYr',1)
,('DMAXHWOVP',1)
,('DMAXHWUPMC',1)
,('DMAXOPTYR',1)
,('DMaxPerDay',1)
,('DMAXTECH',1)
,('DMAXTEMP',1)
,('DMaxUnitsLif',1)
,('DMaxUnitsYr',1)
,('DMaxVisitsLf',1)
,('DMaxVisitsYr',1)
,('DMCR',0)
,('DMEDASSIGN',1)
,('DMEDREC',1)
,('DMEMMAX',1)
,('DMNCTOON',1)
,('DMOD50',1)
,('DMSCONTRACT',1)
,('DMSTERM',1)
,('DNCSMEDICAID',1)
,('DNCTYPE',0)
,('DNegRate',1)
,('DNEWPTOV',1)
,('DNoAddrCont',0)
,('DNoCert',1)
,('DNOCERT99',1)
,('DNoCertServs',1)
,('DNoContract',0)
,('DNOEANDM',1)
,('DNOEMO',1)
,('DNOEXAM2',1)
,('DNOGRPNPI',0)
,('DNOHICFA',0)
,('DNOHICFANP',0)
,('DNOHICFAPAR',0)
,('DNOHW',1)
,('DNOLONGERAT',0)
,('DNOMATCHGA',0)
,('DNOMATCHIN',0)
,('DNOMATCHMS',0)
,('DNOMATCHOH',0)
,('DNOMATCHPA',0)
,('DNOMATCHTX',0)
,('DNOMATCHWI',0)
,('DNOMBRMCDID',1)
,('DNOMEDICAIDID',0)
,('DNoMedical',1)
,('DNONCAPSERV',1)
,('DNONPAY',1)
,('DNONPI',0)
,('DNoPay',0)
,('DNOPRIORNOTIFY',1)
,('DNOPROVNPI',0)
,('DNOPROVPART',0)
,('DNOREFCLM',0)
,('DNOREFELTX',0)
,('DNORHW',1)
,('DNORPHW',1)
,('DNoServices',0)
,('DNoServiceUnits',0)
,('DNOSHOW',1)
,('DNotAssignedProv',1)
,('DNOTAXID',0)
,('DNotBilled',0)
,('DNotCertOrg',1)
,('DNotCertProv',1)
,('DNOTMEDIND',1)
,('DNOTVISCODE',1)
,('DNPI',0)
,('DOONServNotCert',1)
,('DOPREPORT',0)
,('DOPTICALLABIL',1)
,('DOPTLFIT',1)
,('DOPTOUT',1)
,('DOPTOUTRP',1)
,('DOPTSTD',1)
,('DOverCertUn',1)
,('DPEDX',1)
,('DPHYLOC',0)
,('DPLANSTART',0)
,('DPLANTERM',0)
,('DPOLYMHIN',1)
,('DPQRI',1)
,('DPreBilled',0)
,('DPRGNC',1)
,('DPRIMINS',0)
,('DPOBOX',0)
,('DPROVCL',0)
,('DProvDis',1)
,('DPROVDIS10',1)
,('DPROVDIS15',1)
,('DPROVDIS20',1)
,('DPROVDIS25',1)
,('DPROVDIS35',1)
,('DPROVHDW',0)
,('DPROVMSO',0)
,('DPROVPROF',0)
,('DPROVREQ',0)
,('DPROVROU',0)
,('DPSGACOMGT',1)
,('DPTCTFM',1)
,('DREBILLEMOD',0)
,('DREBILLMOD',0)
,('DREFILEMODRHW',0)
,('DREFILESF',1)
,('DREFILEV',1)
,('DREJECT',0)
,('DREPAIR',1)
,('DRESUBMN',0)
,('DROUTINEVIS',1)
,('DRPFRAME',1)
,('DRPHW',1)
,('DRPLENS',1)
,('DRTNETERM',0)
,('DRUNOUT',0)
,('DRX',0)
,('DRXMEDICAID',1)
,('DS0620',1)
,('DSECOV',1)
,('DSELOPTKY',1)
,('DserExam',1)
,('DServExclude',1)
,('DServExUPMC',1)
,('DServExUPMCHIP',1)
,('DSERVINC',1)
,('DSERVMAC',1)
,('DSERVNOBEN',1)
,('DServNotCert',1)
,('DServNotCov',1)
,('DSERVPROV',0)
,('DSPECEX',1)
,('DSSFLTANGO',1)
,('DSSI',1)
,('DSSISUTX',1)
,('DSUBAVFL',1)
,('DSUBELTX',1)
,('DSUBMHP',1)
,('DSUBPAYOR',1)
,('DSUBPOLYIN',1)
,('DSUBUPMC',1)
,('DSUTXDUAL',1)
,('DTEMPCODE',1)
,('DTINHEROPT',1)
,('DTINVISMRT',1)
,('DTXHIPOW',1)
,('DUPMCCLFIT',1)
,('DUPMCENMN',1)
,('DUPMCMEDCP',1)
,('DUVBUND',1)
,('DV2025RP',1)
,('DVarious',1)
,('DVoid',0)
,('DVOIDWEB',0)
,('DWRITCANCEL',0)
,('DWRITRECON',0)
,('DWRITRECONGA2',0)
,('DWRITRECONOH2',0);
/***********************************************************************************************************************
Get claim data
***********************************************************************************************************************/
With ClaimData As (
Select
claim.systemkey as Claim_Number
,ptins.memberid as Member_ID
,claim.provider as Provider_ID
,prov.name as Provider_Name
,claim.program1 as Benefit_Option
,ben.product as Product_Number
,claim.receiveddt as Received_Date
,claim.billed as Billed_Amount
,claim.allowed as Allowed_Amount
,(Select sum(service.billed) from rmdb.dbo.service where service.parentkey = claim.systemkey and left(ltrim(service.dispositn),1)='a' group by service.parentkey) as Approved_Services_Billed
,claim.dispositn as Claim_Disposition
,claim.payor1 as Payor
,claim.userfield2 as Adj_Code
--determine approved
,Approved=
case
when LEFT(ltrim(claim.dispositn),1)='a' then 1 else 0
end
--determine state based on address of provider
,(Select top 1 provaddr.state from rmdb.dbo.provaddr where provaddr.systemkey = claim.addresskey) As ProviderState
--calc receipt method as electronic or paper
,Submission_Method=
Case
when claim.inventory_id is not null then 'Electronic'
when claim.createdby = 'webclaim' then 'Electronic'
else 'Paper'
End
--calc received date as receiveddt on original claims for Adjusted and A-Reversal dispositions
-- get original claim id from original_id field if a-reversal and parse from adminnote if adjusted
,Adjusted_ReceivedDate=
Case
when claim.dispositn = 'A-Reversal' then (Select claim2.receiveddt from rmdb.dbo.claim claim2 where claim2.systemkey = claim.original_id)
when claim.dispositn = 'Adjusted' then
Case
when substring(claim.adminnote,charindex('2',claim.adminnote,0),12) like '20__________' then (Select claim3.receiveddt from rmdb.dbo.claim claim3 where claim3.systemkey = substring(claim.adminnote,charindex('2',claim.adminnote,0),12))
else null
End
else claim.receiveddt
End
--calc next report date as the next Friday
,Next_ReportDate=
Case
when datepart(dw,@next_report_based_on_day)=@weekday_of_report then @next_report_based_on_day
when datepart(dw,@next_report_based_on_day)<@weekday_of_report then @next_report_based_on_day+(@weekday_of_report -datepart(dw,@next_report_based_on_day))
when datepart(dw,@next_report_based_on_day)>@weekday_of_report then @next_report_based_on_day+(@weekday_of_report -datepart(dw,@next_report_based_on_day)+7)
End
,cleandispositions.clean as CleanClaimIndicator
,LinkedState=
Case
When ben.product = '101' then 'US'
When (SELECT TOP 1 ins.[state] FROM rmdb.dbo.ins where ins.ins = claim.payor1) is not null then (SELECT TOP 1 ins.[state] FROM rmdb.dbo.ins where ins.ins = claim.payor1)
Else (Select top 1 provaddr.state from rmdb.dbo.provaddr where provaddr.systemkey = claim.addresskey)
End
From ncocrmsq03.rmdb.dbo.claim claim
INNER JOIN ncocrmsq03.rmdb.dbo.ptins ptins on ptins.systemkey = claim.ptinskey1
INNER JOIN ncocrmsq03.rmdb.dbo.prov prov on prov.prov = claim.provider
INNER JOIN ncocrmsq03.rmdb.dbo.ben ben on ben.ben = claim.program1
LEFT OUTER JOIN @cleandispositions cleandispositions on ltrim(rtrim(cleandispositions.code)) = ltrim(rtrim(claim.dispositn))
Where
(claim.reportdate is null)
and claim.payor1pmt > 0
and claim.createddt < dateadd(d,-DATEPART(dw,getdate())-1,GETDATE())
)
/***********************************************************************************************************************
Get claims with applicable interest rate segments from the interest table where interest rate segment product value
is null.
***********************************************************************************************************************/
Select *
From ClaimData
INNER JOIN ncocrmsq03.CompanyReports.clm.claim_interest interest on
interest.state = ClaimData.LinkedState
AND
(interest.submission_method = ClaimData.Submission_Method
or interest.submission_method is null)
AND
(interest.product = ClaimData.Product_Number
or interest.product is null)
AND
(interest.effective_date <= ClaimData.Next_ReportDate
and interest.termination_date >= ClaimData.Next_ReportDate)
AND
(ClaimData.Adjusted_ReceivedDate Is Null
Or (ClaimData.Next_ReportDate - ClaimData.Adjusted_ReceivedDate >= interest.age_low))
--dropping records where there is a product match for the state so that only records matching on null product in the interest table will be returned
Where
ClaimData.LinkedState + IsNull(ClaimData.Product_Number,'999') Not In (Select distinct interest.state + IsNull(interest.product,'000') from CompanyReports.clm.claim_interest interest)
union
/***********************************************************************************************************************
Get claims with applicable interest rate segments from the interest table where interest rate segment product value
is not null.
***********************************************************************************************************************/
Select *
From ClaimData
INNER JOIN ncocrmsq03.CompanyReports.clm.claim_interest interest on
interest.state = ClaimData.LinkedState
AND
(interest.submission_method = ClaimData.Submission_Method
or interest.submission_method is null)
AND
(interest.product = ClaimData.Product_Number)
AND
(interest.effective_date <= ClaimData.Next_ReportDate
and interest.termination_date >= ClaimData.Next_ReportDate)
AND
(ClaimData.Adjusted_ReceivedDate Is Null
Or (ClaimData.Next_ReportDate - ClaimData.Adjusted_ReceivedDate >= interest.age_low))
Ah, good idea DJ,
If there is any kind of error it may not allow you to save the report.
Another issue is in R2 we may have had a limit to the length of the SQL in a Command.
Try removing all of the Comments as a test and see if that allows you to save it. The Designer UI may allow you to add but when saving it is going to complain, in this case it simply won't allow you to save the report. ( Maybe ) R2 is end
Doing a character count in Word it show 16,011. It may be too long... I don't know what version we extended it in but original would have been 32K ( divided in half for UNICODE characters ) so may be the limit.
Also, I tried pasting in you SQL and CR XI R2 did not like any of the comment text
Don
Hi Brian,
On the original report make a copy of it and then open it in CR Designer. And/Change the SQL and then start removing objects and whole sections to see if you can find the source of the problem. It could be some object needs to be updated or it is possibly corrupt and the RPT file itself has been corrupted somehow, no way to "fix" it.
If you try to change anything else can you save it?
DJ, what you posted makes no sense...
Don
Hi Brian,
From the errors it looks like there are problems with the Command after you change it.
Unfortunately XI R2 is of life and support so you may want to test it by downloading CR 2011 or 2013 Trial version and see if that works.
Don
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.