Record Selection Question

[w1mnk]

I'm having a problem (I think) with a record selection forumula:

If IsNull({?FunctionalArea}) Then
(
({APPROVAL.GROUP_ID} In 'XXX-XXXXXXX','yyy-yyyyyyy'] Or
{CHANGE.ASSIGNEE_GROUP} In 'XXX-XXXXXXX','yyy-yyyyyyy'] Or
{CHANGE.REQUESTER_GROUP} In 'XXX-XXXXXXX','yyy-yyyyyyy') And
(({CHANGE.RECORD_STATUS} = 'CLOSED' And
({CHANGE.START_DATE} In LastFullMonth) Or
{CHANGE.CLOSED_DATE} In LastFullMonth)) Or
({CHANGE.RECORD_STATUS} <> 'CLOSED' And
{CHANGE.START_DATE} In LastYearMTD)
)

... there are additional if statements after this ...

The portion of the formula in bold does not seem to work correctly. I know there are records where the status is other than 'CLOSED', yet they don't show up. For some reason, the last 2 lines don't appear to be processed. What am I doing wrong? Any ideas/hints/help is greatly appreciated.

Thank you, Jon

 

[synapsevampire]

Try:

(
(
{APPROVAL.GROUP_ID} In 'XXX-XXXXXXX','yyy-yyyyyyy'] Or
{CHANGE.ASSIGNEE_GROUP} In 'XXX-XXXXXXX','yyy-yyyyyyy'] Or
{CHANGE.REQUESTER_GROUP} In 'XXX-XXXXXXX','yyy-yyyyyyy'
)
And
(
{CHANGE.RECORD_STATUS} = 'CLOSED' And
(
{CHANGE.START_DATE} In LastFullMonth
)
Or
{CHANGE.CLOSED_DATE} In LastFullMonth
)
)
Or
(
{CHANGE.RECORD_STATUS} <> 'CLOSED' And
{CHANGE.START_DATE} In LastYearMTD
)

Also remove the {CHANGE.START_DATE} In LastYearMTD
for testing purposes.

-k

 

[w1mnk]

synapsevampire, thank you for the speedy reply. It is greatly appreciated. I do have a question. I have record that is

CHANGE.RECORD_STATUS = 'CLOSED'
CHANGE.START_DATE = 2003/08/29
CHANGE.CLOSE_DATE = 2003/09/01

I'm not sure why it isn't selected by

{CHANGE.RECORD_STATUS} = 'CLOSED' And
(
{CHANGE.START_DATE} In LastFullMonth
)
Or
{CHANGE.CLOSED_DATE} In LastFullMonth

Thanks again for the help.
Jon

 

[synapsevampire]

The best thing to do is to check the Database->Show SQL Query.

This will show you what the record selection parentheticals are producing for the SQL pass through.

Try removing the date criteria for this and see if it shows up, if not, remove the Closed criteria, perhaps you're not getting an exact match?

-k

 

[w1mnk]

Very Interesting. NONE of the selection criteria, other than the group selections, is being passed through to the SQL. I guess that means that the selection criteria is being applied after the SQL retrieves the records? Can this be changed. I seem to remember that Database -> Show SQL query -> reset will return the SQL to its original state? Thanks again for the help, synapsevampire.

 

[synapsevampire]

That's my take on it too.

You say that the SQL is demonstrating Grouping?

Consider posting the full record selection formula(s), and the SQL being produced.

-k

 

[w1mnk]

I apologize in advance for the size of this. Here is the record selector:

If IsNull({?FunctionalArea}) Then
(
({APPROVAL.GROUP_ID} In ['XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL'] Or
{CHANGE.ASSIGNEE_GROUP} In ['XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL'] Or
{CHANGE.REQUESTER_GROUP} In ['XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL']) And
(
{CHANGE.RECORD_STATUS} = 'CLOSED' And
(
{CHANGE.START_DATE} In LastFullMonth
)
Or
{CHANGE.CLOSED_DATE} In LastFullMonth
)
)
Or
(
{CHANGE.RECORD_STATUS} <> 'CLOSED' And
{CHANGE.START_DATE} In LastYearMTD

)

Else If {?FunctionalArea} = 'TERAS' Then
(
({APPROVAL.GROUP_ID} In ['XXX-TERASSUP','XXX-TERASAPPROV'] Or
{CHANGE.ASSIGNEE_GROUP} In ['XXX-TERASSUP','XXX-TERASAPPROV'] Or
{CHANGE.REQUESTER_GROUP} In ['XXX-TERASSUP','XXX-TERASAPPROV']) And
(
{CHANGE.RECORD_STATUS} = 'CLOSED' And
(
{CHANGE.START_DATE} In LastFullMonth
)
Or
{CHANGE.CLOSED_DATE} In LastFullMonth
)
)
Or
(
{CHANGE.RECORD_STATUS} <> 'CLOSED' And
{CHANGE.START_DATE} In LastYearMTD

)

Here is the SQL:
SELECT
CHANGE.&quot;CHANGE_ID&quot;, CHANGE.&quot;RISK_CODE&quot;, CHANGE.&quot;NAME&quot;, CHANGE.&quot;PLAN_ST_DATE&quot;, CHANGE.&quot;PLAN_ST_TIME&quot;, CHANGE.&quot;START_DATE&quot;, CHANGE.&quot;START_TIME&quot;, CHANGE.&quot;ASSIGNEE_GROUP&quot;, CHANGE.&quot;CLOSED_DATE&quot;, CHANGE.&quot;CLOSED_TIME&quot;, CHANGE.&quot;ESTIMATED_DURATION&quot;, CHANGE.&quot;RECORD_STATUS&quot;, CHANGE.&quot;REQUESTER_GROUP&quot;,
PEOPLE.&quot;LAST_NAME&quot;, PEOPLE.&quot;FIRST_NAME&quot;,
APPROVAL.&quot;APPROVAL_DATE&quot;, APPROVAL.&quot;APPROVAL_TIME&quot;, APPROVAL.&quot;GROUP_ID&quot;
FROM
&quot;IGSVIEW&quot;.&quot;CHANGE&quot; CHANGE INNER JOIN &quot;IGSVIEW&quot;.&quot;APPROVAL&quot; APPROVAL ON
CHANGE.&quot;CHANGE_ID&quot; = APPROVAL.&quot;CHANGE_ID&quot; LEFT JOIN &quot;IGSVIEW&quot;.&quot;PEOPLE&quot; PEOPLE ON
CHANGE.&quot;ASSIGNED_TO&quot; = PEOPLE.&quot;PEOPLE_ID&quot;
WHERE
(CHANGE.&quot;ASSIGNEE_GROUP&quot; In ('XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL') Or APPROVAL.&quot;GROUP_ID&quot; In ('XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL') Or CHANGE.&quot;REQUESTER_GROUP&quot; In ('XXX-CHGCOM','XXX-BWSUPPORT','XXX-BWAPPROVAL','XXX-DrillingSup','XXX-DrillApprov','XXX-FISUPPORT','XXX-FIAPPROVAL','XXX-HRSUPPORT','XXX-HRAPPROVAL','XXX-MMPMSUPPORT','XXX-MMPMAPPROVE','XXX-PRASUPPORT','XXX-PRAAPPROVAL','XXX-PROJAPPROVE','XXX-SECSUPPORT','XXX-SECAPPROVAL','XXX-TECHSUPPORT','XXX-TECHAPPROVE','XXX-TERASSUP','XXX-TERASAPPROV','XXX-TOBINAPPSUP','XXX-TOBINAPPROV','XXX-TOWSUPPORT','XXX-TOWAPPROVAL'))
ORDER BY
CHANGE.&quot;START_DATE&quot; ASC,
CHANGE.&quot;START_TIME&quot; ASC,
CHANGE.&quot;CHANGE_ID&quot; ASC

Thanks again for your help.

 

[synapsevampire]

Remark everything that isn't working (such as just leaving what's at the end), and run the report, and then move upwards unremarking each segment.

This is fairly complex, but I've had much worse.

As for the SQL being passed, once you get the data that you want, try formatting your sections one at a time to make sure each gets passed, sometimes a date vs. a datetime can do you in, I'd copy out the whole record selection formula and start pasting it back one piece at a time and learn what isn't being passed.

-k