Crystal XI REL 2 Record Selection changes after linking another table

[dkollmann]

Hello,

Here is my challenge I am trying to do a comparison of a financial statement Current Period Debit /credit to a budget. I am linking 3 tables together GL period balance,
GL Budget and GL account. I have tried this 2 ways first
Starting with the GL Period linked to the GL Account this returns the correct amount for the Period when I try to join the GL Budget Account using a left outer join I get the error "Cannot retrieve data from database" , I tried the inner join and it doesn't return all the records because in that period not all GL accounts had transactions. I will post my selection formula i am not sure if it is linking or the selection formula.

Selection Formula:
{GLAcct.Type} = "I" and
{GLPerBal.FiscalYear} = 2007 and
{GLPerBal.FiscalPeriod} = {?Fiscal Period} and
{Company.Name} = "03"

SQL Query:
SELECT "GLPerBal1"."FiscalPeriod", "GLPerBal1"."DebitAmt",
"GLPerBal1"."CreditAmt", "GLAcct1"."Type", "GLPerBal1"."FiscalYear",
"GLAcct1"."Chart", "GLAcct1"."GLDept", "GLPerBal1"."Company",
"Fiscal1"."StartDate", "Fiscal1"."EndDate", "GLBudget1"."BudgetAmt",
"Company1"."Name", "GLAcct1"."AcctDesc", "GLBudget1"."FiscalPeriod"
FROM ((("PUB"."GLPerBal" "GLPerBal1" INNER JOIN "PUB"."GLAcct" "GLAcct1"
ON ((("GLPerBal1"."Division"="GLAcct1"."Division") AND
("GLPerBal1"."GLDept"="GLAcct1"."GLDept")) AND
("GLPerBal1"."Chart"="GLAcct1"."Chart")) AND
("GLPerBal1"."Company"="GLAcct1"."Company")) INNER JOIN "PUB"."Fiscal"
"Fiscal1" ON (("GLPerBal1"."Company"="Fiscal1"."Company") AND
("GLPerBal1"."FiscalYear"="Fiscal1"."FiscalYear")) AND
("GLPerBal1"."FiscalPeriod"="Fiscal1"."FiscalPeriod")) INNER JOIN
"PUB"."GLBudget" "GLBudget1" ON
((((("GLPerBal1"."Company"="GLBudget1"."Company") AND
("GLPerBal1"."Division"="GLBudget1"."Division")) AND
("GLPerBal1"."GLDept"="GLBudget1"."GLDept")) AND
("GLPerBal1"."Chart"="GLBudget1"."Chart")) AND
("GLPerBal1"."FiscalYear"="GLBudget1"."FiscalYear")) AND
("GLPerBal1"."FiscalPeriod"="GLBudget1"."FiscalPeriod")) INNER JOIN
"PUB"."Company" "Company1" ON "GLAcct1"."Company"="Company1"."Company"
WHERE "GLAcct1"."Type"='I' AND "GLPerBal1"."FiscalYear"=2007 AND
"Company1"."Name"= "03" AND "GLPerBal1"."FiscalPeriod"=1 AND
"GLBudget1"."FiscalPeriod"=1


Thank You

Don






Thank you in advance for your help

 

[synapsevampire]

Try posting your database type, version and the connectivity being used.

The error states it can't get data, so you you need to make sure that you're accessing it in an allowed manner.

-k

 

[dgillz]

Please turn auto smart linking off as well. I suspect you have a linking problem. Do you know the keys of each table?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[dkollmann]

Thank You Dgilz,

I do have auto linking off.

I am starting with these 3 tables linking with inner joins
GLPeriod > GlAcct GLBudget

Company Company Company
Div Div Div
Dept Dept Dept
Chart Chart Chart
Fiscal Year Fiscal Year
Fiscal period Fiscal period


The data gets compromised when I add the budget table to the report. It eliminates records because there is not a match on all the accounts in every Period.

 

[Turkbear]

Hi,
If you use INNER joins, that is the normal outcome when no matching record exists in ANY of the linked tables..

Use a Left Outer Join from the main to the linked tables...
( and do not apply any criteria to the left-outer joined tables or the join will be re-interpreted to an EQUAL/ INNER join...)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dkollmann]

What table should I start with and is my record selection
impacting the results?

Maybe I need to look at doing a running total to get the correct results?

 

[dkollmann]

How would you link this scenario if you wanted results
to show GLPeriodAmount by fiscal year compared to GLBudget amount by fiscal year. Understanding there may not be records for in all of the amount accounts.