synapsevampire,
I looked at your FAQ, and tried changing the order of the sel criteria, and adding parens, but still have not got it to work.
This is the sql:
SELECT
ledger_organizations.organization_description,
budget_lists.approver,
chart_of_accounts.account, chart_of_accounts.account_description,
ledger_balances.balance_name, ledger_balances.period, ledger_balances.ledger_account, ledger_balances.fiscal_year, ledger_balances.period_amount
FROM
{ oj ((((ledger_organizations ledger_organizations INNER JOIN budget_list_accounts budget_list_accounts ON ledger_organizations.fiscal_year = budget_list_accounts.fiscal_year AND ledger_organizations.ledger = budget_list_accounts.ledger) INNER JOIN ledger_accounts ledger_accounts ON ledger_organizations.fiscal_year = ledger_accounts.fiscal_year AND ledger_organizations.ledger = ledger_accounts.ledger AND ledger_organizations.organization_level = ledger_accounts.organization_level AND ledger_organizations.organization = ledger_accounts.organization) INNER JOIN chart_of_accounts chart_of_accounts ON ledger_accounts.fiscal_year = chart_of_accounts.fiscal_year AND ledger_accounts.coa = chart_of_accounts.coa AND ledger_accounts.account = chart_of_accounts.account) INNER JOIN budget_lists budget_lists ON budget_list_accounts.fiscal_year = budget_lists.fiscal_year AND budget_list_accounts.ledger = budget_lists.ledger AND budget_list_accounts.signature_list = budget_lists.signature_list) LEFT OUTER JOIN ledger_balances ledger_balances ON ledger_accounts.fiscal_year = ledger_balances.fiscal_year AND ledger_accounts.ledger = ledger_balances.ledger AND ledger_accounts.ledger_account = ledger_balances.ledger_account}
WHERE
ledger_balances.ledger = 'XX' AND (ledger_balances.balance_name = 'ACTUAL' OR ledger_balances.balance_name = 'BUDGET') AND ledger_balances.fiscal_year = '2004' AND ledger_balances.period <= 12 AND ((chart_of_accounts.account >= '4000' AND chart_of_accounts.account < '4910') OR chart_of_accounts.account > '4910')
These are the selection criteria:
({budget_lists.approver} = {?user}) and
({ledger_balances.fiscal_year} = {?fiscal year}) and
{ledger_balances.period} <= {?period}
The only criterion that is getting passed through is the user. And this is the criterion that I added(also added budget_lists and budget_list_accounts tables) when I modified the report to be run by different users, instead of by the Business Office which can see everything.
The fiscal year and the period don't become part of the sql until the report has run for a little bit. But when they do become part of the sql, the correct fiscal year and period do not get picked up.
Also, although when copied the sql looks neat and cleanly spaced, when I look at it in Crystal the lines in the middle seem to be written on top of each other. I don't know if this is relevant to this particular problem, but it seems strange to me.
I would appreciate any help you could give me.
Thanks,
Sue