I am using Crystal XI with Oracle 10g DB...
My report lists dates along with thier associated measurements and categories in a table called "MEASUREMENT_PERIODS".
Each Date can be specified as "Client", "Management", "Other", no category at all, or any combination of the categories. I have a parameter "rp" that can be set to "Client", "Management", "Other" Or nothing at all. If "Client", "Management", or "Other" is chosen, only the dates that have a "Y" in the category should show. If nothing is chosen, all dates should show.
The data looks like this (The measurements are irrelevant to this problem, so i left them out):
Date Client Management Other
02/19/05 N N N
02/25/05 N Y Y
03/05/05 N N Y
03/10/05 Y Y Y
03/15/05 N N N
03/20/05 Y Y Y
If a user chooses "Management", the follwing dates would display along with their measurements...
02/25/05
03/10/05
03/20/05
If a user chooses nothing, the following dates would dispay along with thier measurements...
02/19/05
02/25/05
03/05/05
03/10/05
03/15/05
03/20/05
I need this to happen within the Command Object... I am using analytical functions to rank the dates, find first and last values, and a few other things...
So far I have tried to use decode in the select statement- it won't let me use the field in the where clause:
SELECT date,
DECODE({?rp}, NULL, 'TRUE',
'CLIENT', DECODE(mp.CLIENT, 'Y', 'TRUE', 'FALSE'),
'MANAGEMENT', DECODE (mp.MANAGEMENT, 'Y', 'TRUE', 'FALSE'),
'OTHER', DECODE(mp.OTHER, 'Y', 'TRUE', 'FALSE'), 'TRUE') SOMETHING
FROM MEASUREMENT_PERIODS mp
WHERE SOMETHING = 'TRUE'
I have also tried to use decode in the where clause - doesnt seem to like that either. Also tried to use 'CASE WHEN' and it doesnt make a difference.
Any suggestions would be great - I think this is more of a SQL problem than anything else, so if there is somewhere else I should post it, please let me know!
Thanks!
- Jayme
My report lists dates along with thier associated measurements and categories in a table called "MEASUREMENT_PERIODS".
Each Date can be specified as "Client", "Management", "Other", no category at all, or any combination of the categories. I have a parameter "rp" that can be set to "Client", "Management", "Other" Or nothing at all. If "Client", "Management", or "Other" is chosen, only the dates that have a "Y" in the category should show. If nothing is chosen, all dates should show.
The data looks like this (The measurements are irrelevant to this problem, so i left them out):
Date Client Management Other
02/19/05 N N N
02/25/05 N Y Y
03/05/05 N N Y
03/10/05 Y Y Y
03/15/05 N N N
03/20/05 Y Y Y
If a user chooses "Management", the follwing dates would display along with their measurements...
02/25/05
03/10/05
03/20/05
If a user chooses nothing, the following dates would dispay along with thier measurements...
02/19/05
02/25/05
03/05/05
03/10/05
03/15/05
03/20/05
I need this to happen within the Command Object... I am using analytical functions to rank the dates, find first and last values, and a few other things...
So far I have tried to use decode in the select statement- it won't let me use the field in the where clause:
SELECT date,
DECODE({?rp}, NULL, 'TRUE',
'CLIENT', DECODE(mp.CLIENT, 'Y', 'TRUE', 'FALSE'),
'MANAGEMENT', DECODE (mp.MANAGEMENT, 'Y', 'TRUE', 'FALSE'),
'OTHER', DECODE(mp.OTHER, 'Y', 'TRUE', 'FALSE'), 'TRUE') SOMETHING
FROM MEASUREMENT_PERIODS mp
WHERE SOMETHING = 'TRUE'
I have also tried to use decode in the where clause - doesnt seem to like that either. Also tried to use 'CASE WHEN' and it doesnt make a difference.
Any suggestions would be great - I think this is more of a SQL problem than anything else, so if there is somewhere else I should post it, please let me know!
Thanks!
- Jayme