crystal 8 selection criteria not working with sql statement

[sue1127]

I am using Crystal 8.0 and Sql Server 7. I have a report that is not incorporating the selection criteria into the sql sentence. This report works in a slightly different form, with fewer tables. But when I add a couple tables to limit the people who can view the information, it does not work.

I've had this problem before when modifying this report. It seems as though every time I need to modify, the relationship between sql and the sel criteria is destroyed.

Does anyone have any suggestions?

Thanks, Sue

 

[synapsevampire]

I would suggest that you provide an example of the record selection and the resultant SQL, with what you believe to be the error.

I have a FAQ on record selection criteria:

faq767-3825

You'll need to post specifics if you want specific assistance.

SQL isn't always passed to the database without effort, you need to work with it a bit, especially that version.

-k

 

[sue1127]

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