Missing records

[Davest11]

I'm running Crystal v10, SP3, on a Progress v9 database. Without going into too much detail, I'm writing a fund statement for a community foundation. This is a financial statement that pulls general ledger data from an accounting database. I have two versions of the report - one that runs for single funds, and another that consolidates the data from several funds. Basically, the only real difference between the two reports is that the consolidated report uses a larger data set than the fund-level version. The selection criteria are identical except that the fund-level version pulls records where the fund ID equals a single value, where the consolidated version pulls records where the fund ID is one of several values.

My problem is that, in the consolidated version, records are being skipped. I ran the report with the detail unsuppressed and compared the two sets of output, and there are very clearly records that are included in the fund-level report that are simply missing from the consolidated one. Again, I made no changes to the selection critera in the two reports other than changing which fund ID's are being pulled. I'm getting some records from every fund ID that should be included, so the problem isn't there.

I'd be glad to answer any questions or provide any additional detail that anyone thinks will help them assist me with this.

Thanks,

-Dave

 

[lbass]

Are you using more than one table? If so, how are they linked? Do you have other selection criteria and if so, what are they and what table are the fields drawn from?

-LB

 

[Davest11]

Well, I've found the offending statement, but I've no idea why it's working the way it is.

When I use the statement {Fund.fundid} = {?Pm-?Fund ID}, I get the records that I'd expect based on the statement. When I use the statement {Fund.master_fundid} = {?Pm-?Fund ID}, I also get the results that I'd expect. When I combine these into ({Fund.master_fundid} = {?Pm-?Fund ID} or {Fund.fundid} = {?Pm-?Fund ID}), however, I get all of the records where {Fund.fundid} = {?Pm-?Fund ID} and only some of the records where {Fund.master_fundid} = {?Pm-?Fund ID}.

Thoughts?







({Fund.master_fundid} = {?Pm-?Fund ID} or {Fund.fundid} = {?Pm-?Fund ID})

 

[synapsevampire]

A shame you wouldn't answer LB's question, because as usual, the question was spot on.

Try posting technical information:

what tables are used, and what are the joins?

Crystal does NOT allow for just dropping tables in and making assumptions about what data you want returned.

You may want to build a UNION query to assemble the data, this allows for combining tables into a single recordset, whcih is what Crystal needs.

-k

 

[lbass]

You left an important piece of information--that you are using a subreport. I think you should explain your main report and your subreport structure and explain what you are trying to do. It appears that you are using two tables in the subreport, so they must be linked to each other. Why then are you trying to link the subreport on both table fund IDs? It seems a little odd.

-LB

 

[Davest11]

Hmmm...sorry if I offended, Synapse. I'd be glad to forward the entire report to anyone who's interested, but it's far too complex to describe in a few sentences here. I didn't state that I'm using a subreport because the rest of the report - including the eight or so other subreports in it - is working fine. Also, the ONLY difference between the report that works fine and the one that doesn't is the statement that I described in my last post.

The reason that I'm linking to both fund ID's is that the user is entering a fund ID as a parameter, and I need to pull records that are associated with either the fund entered or any of its subordinates. Thus, either the Fund ID equals the parameter, or the Master Fund ID equals the parameter.

The bottom line, it seems to me, is that when I use either side of the selection criterium described in my last post, the report pulls the appropriate records. When I combine them with an OR, with proper parenthesis, I lose records.

 

[synapsevampire]

No offense taken, but why post if you won't answer people's questions?

It's true that requirements can be complex to describe, but sometimes it's required to understand your scenario.

I had asked what tables are used, and what are the joins, which is easily described by Database->Show SQL Query, depending upon the database.

Since you don't want to supply basic technical information, it seems fruitless to attempt helping you. I guess that you assume that people must know your database and how the tables are or are not joined. The joins are key here I think.

Good luck with it.

-k

 

[Davest11]

Is this what you were looking for?


SQL Queries:

Main report:

SELECT DISTINCT "Fund"."fundid", "Fund"."fund_name", "Fund"."divcode", "Fund_Rep"."Rep_Type", "sql_Profile"."IDCode", "sql_Profile"."OrgName", "Fund"."Asset_Code", "Fund"."polcode", "Fund"."date_inactive", "Fund"."established", "sql_Profile"."LName", "sql_Profile"."Mail_To", "Fund"."master_fundid"
FROM "FOUND"."sql_Fund" "Fund", "FOUND"."sql_Fund_Rep" "Fund_Rep", "FOUND"."sql_Profile" "sql_Profile"
WHERE ("Fund"."fundid"="Fund_Rep"."FundID" (+)) AND ("Fund_Rep"."IDCode"="sql_Profile"."IDCode") AND "Fund"."divcode"<>'' AND "Fund_Rep"."Rep_Type"<>'' AND "sql_Profile"."IDCode"<>'' AND "Fund"."established"<{d '2004-09-30'}


Find Statement Type Sub:


SELECT "sql_Fund_Rep"."FundID", "sql_Fund_Rep"."Rep_Type"
FROM "FOUND"."sql_Fund_Rep" "sql_Fund_Rep"
WHERE "sql_Fund_Rep"."FundID"='23342'


Check for Assets Sub:

SELECT "GLYR"."FundId", "GLYR"."GLYear", "GLYR"."AcctType", "GLYR"."AcctDescr", "GLYR"."Actual__1", "GLYR"."Actual__2", "GLYR"."Actual__3", "GLYR"."Actual__4", "GLYR"."Actual__5", "GLYR"."Actual__6", "GLYR"."Actual__7", "GLYR"."Actual__8", "GLYR"."Actual__9", "GLYR"."Actual__10", "GLYR"."Actual__11", "GLYR"."Actual__12", "GLYR"."Actual__13", "GLYR"."BeginActBal", "GLYR"."sg6", "Fund"."fundid", "Fund"."master_fundid"
FROM "FOUND"."sql_GLYR" "GLYR", "FOUND"."sql_Fund" "Fund"
WHERE ("GLYR"."FundId"="Fund"."fundid") AND "GLYR"."GLYear"=2004 AND "GLYR"."AcctType" LIKE 'A%' AND ("Fund"."master_fundid"='23342' OR "Fund"."fundid"='23342')


Display Assets Sub:


SELECT "GLYR"."GLYear", "GLYR"."AcctType", "GLYR"."AcctDescr", "GLYR"."Actual__1", "GLYR"."Actual__2", "GLYR"."Actual__3", "GLYR"."Actual__4", "GLYR"."Actual__5", "GLYR"."Actual__6", "GLYR"."Actual__7", "GLYR"."Actual__8", "GLYR"."Actual__9", "GLYR"."Actual__10", "GLYR"."Actual__11", "GLYR"."Actual__12", "GLYR"."Actual__13", "GLYR"."BeginActBal", "GLYR"."sg6", "Fund"."fundid", "Fund"."master_fundid"
FROM "FOUND"."sql_GLYR" "GLYR", "FOUND"."sql_Fund" "Fund"
WHERE ("GLYR"."FundId"="Fund"."fundid") AND "GLYR"."GLYear"=2004 AND "GLYR"."AcctType" LIKE 'A%' AND ("Fund"."master_fundid"='23342' OR "Fund"."fundid"='23342')


Liabilities Sub (The one with the problem):

SELECT "GLYR"."FundId", "GLYR"."GLYear", "GLYR"."AcctType", "GLYR"."AcctDescr", "GLYR"."Actual__1", "GLYR"."Actual__2", "GLYR"."Actual__3", "GLYR"."Actual__4", "GLYR"."Actual__5", "GLYR"."Actual__6", "GLYR"."Actual__7", "GLYR"."Actual__8", "GLYR"."Actual__9", "GLYR"."Actual__10", "GLYR"."Actual__11", "GLYR"."Actual__12", "GLYR"."Actual__13", "GLYR"."BeginActBal", "GLYR"."sg6", "Fund"."Stmnt_Style", "GLYR"."natural_acct", "Fund"."fundid", "Fund"."master_fundid"
FROM "FOUND"."sql_GLYR" "GLYR", "FOUND"."sql_Fund" "Fund"
WHERE ("GLYR"."FundId"="Fund"."fundid") AND "GLYR"."GLYear"=2004 AND "GLYR"."AcctType" NOT LIKE 'A%' AND ("Fund"."master_fundid"='23342' OR "Fund"."fundid"='23342')


Fund Activity Sub:



SELECT "GLYR"."FundId", "GLYR"."GLYear", "GLYR"."AcctType", "GLYR"."natural_acct", "GLYR"."sg6", "GLYR"."Actual__1", "GLYR"."Actual__2", "GLYR"."Actual__3", "GLYR"."Actual__4", "GLYR"."Actual__5", "GLYR"."Actual__6", "GLYR"."Actual__7", "GLYR"."Actual__8", "GLYR"."Actual__9", "GLYR"."Actual__10", "GLYR"."Actual__11", "GLYR"."Actual__12", "GLYR"."Actual__13", "GLYR"."BeginActBal", "GLYR"."AcctDescr", "Fund"."established", "sql_fm_fund"."Fund_Statement_Affiliation", "Fund"."divcode", "Fund"."fundid", "Fund"."master_fundid"
FROM "FOUND"."sql_GLYR" "GLYR", "FOUND"."sql_Fund" "Fund", "FOUND"."sql_fm_fund" "sql_fm_fund"
WHERE ("GLYR"."FundId"="Fund"."fundid") AND ("Fund"."fundid"="sql_fm_fund"."fundid") AND "GLYR"."GLYear"=2004 AND ("GLYR"."AcctType" LIKE 'Q%' OR "GLYR"."AcctType" LIKE 'R%' OR "GLYR"."AcctType" LIKE 'T%' OR "GLYR"."AcctType" LIKE 'X%') AND ("Fund"."master_fundid"='23342' OR "Fund"."fundid"='23342')


Contribution Detail Sub:



SELECT "sql_FundDetailHistory"."tran_date", "sql_FundDetailHistory"."trantype", "sql_FundDetailHistory"."refno", "sql_FundDetailHistory"."tran_amt", "sql_Gifthistory"."GiftTypeCode", "sql_Gifthistory"."GiftComment", "sql_Gifthistory"."GiftNum", "sql_Gifthistory"."Ack_Name", "sql_Gifthistory"."Ack_OrgName", "sql_Profile"."OrgCode", "sql_Fund"."master_fundid", "sql_Fund"."fundid"
FROM "FOUND"."sql_FundDetailHistory" "sql_FundDetailHistory", "FOUND"."sql_Profile" "sql_Profile", "FOUND"."sql_Gifthistory" "sql_Gifthistory", "FOUND"."sql_Fund" "sql_Fund"
WHERE ("sql_FundDetailHistory"."don_idcode"="sql_Profile"."IDCode") AND ("sql_FundDetailHistory"."refno"="sql_Gifthistory"."GiftNum") AND ("sql_FundDetailHistory"."fundid"="sql_Fund"."fundid") AND "sql_FundDetailHistory"."trantype"='gi' AND ("sql_FundDetailHistory"."tran_date">={d '2004-07-01'} AND "sql_FundDetailHistory"."tran_date"<={d '2004-09-30'}) AND "sql_Gifthistory"."GiftTypeCode"<>'ZP' AND ("sql_Fund"."master_fundid"='23342' OR "sql_Fund"."fundid"='23342')
ORDER BY "sql_FundDetailHistory"."tran_date", "sql_FundDetailHistory"."refno"


Expense Detail Sub:



SELECT "GLYR"."FundId", "GLYR"."GLYear", "GLYR"."sg6", "GLYR"."Actual__1", "GLYR"."Actual__2", "GLYR"."Actual__3", "GLYR"."Actual__4", "GLYR"."Actual__5", "GLYR"."Actual__6", "GLYR"."Actual__7", "GLYR"."Actual__8", "GLYR"."Actual__9", "GLYR"."Actual__10", "GLYR"."Actual__11", "GLYR"."Actual__12", "GLYR"."Actual__13", "GLYR"."AcctDescr", "Fund"."fundid", "Fund"."master_fundid"
FROM "FOUND"."sql_GLYR" "GLYR", "FOUND"."sql_Fund" "Fund"
WHERE ("GLYR"."FundId"="Fund"."fundid") AND "GLYR"."GLYear"=2004 AND ("GLYR"."sg6">='67080' AND "GLYR"."sg6"<='89999') AND ("Fund"."master_fundid"='23342' OR "Fund"."fundid"='23342')

 

[Davest11]

Just to clarify a bit what my question is (it seems that it may have gotten lost in some digression yesterday). Has anyone ever experienced a situation where including either side of an OR statement in a set of selection criteria returns the expected results but, when both sides are included, some records are excluded, seemingly at random? It's NOT a linking issue, as some of the correct records are being pulled.

I haven't used this forum much yet, but I've been somewhat surprised that it seems only a few of the many users out there respond to messages. I've answered a couple times, and I've always tried to be friendly to those I'm trying to help. Perhaps if more of us send replies, this could become a friendlier place to be.

Thanks,

-David