06-21-2010 07:20 AM
I need to create a report for a group "ABC"
with following fields
4.)Notes(No notes or history)
I am using SLX version 6.2.3
I have tried editing group lay out but lot of accounts have notes column empty even though they have notes.
06-21-2010 07:30 AM - edited 06-21-2010 07:33 AM
Are you trying to create a GROUP to retrieve Accounts where they have no History items? If so, add the following Condition:
History.Historyid Does Not Contain Data
06-21-2010 07:47 AM
Thank you so much. It worked. Although for some accounts the create date is some time in 2009, but it shows 18/05/2010 as create date for almost all the accounts. 18/05/2010 was the creation date for only new accounts in that group. The old accounts also show the 18/05/2010 as create date
Please let me know
06-21-2010 07:51 AM
I can either create it using Crystal report or by just changing the layout of the group as long as it returns the results.
I am looking to get results for just one specific group with around 900 accounts.
06-21-2010 07:59 AM
So just to clarify, you have a condition where Account.Createdate >= 18/05/2010 but you have several Accounts showing within the group where the Account CreateDate is < 18/05/2010 ?
Can you copy and paste the SQL from within your group (View SQL) and add it to your response please.
06-21-2010 08:36 AM
Here is the SQL for the results I am looking for:
SELECT A1.ACCOUNTID, A1.ACCOUNT, A4.POC_CLAIM_AMT A4_POC_CLAIM_AMT, A2.ACCOUNTMANAGERID A2_ACCOUNTMANAGERID, A5.NOTES A5_NOTES, A2.CREATEDATE A2_CREATEDATE, A2.MODIFYDATE A2_MODIFYDATE, A3.CLAIM_AMOUNT A3_CLAIM_AMOUNT FROM ACCOUNT A1 LEFT OUTER JOIN HISTORY A6 ON (A1.ACCOUNTID=A6.ACCOUNTID) LEFT OUTER JOIN ACTIVITY A5 ON (A1.ACCOUNTID=A5.ACCOUNTID) LEFT OUTER JOIN INF_ADV_MAIN_CASE A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) RIGHT JOIN INF_ADV_DEBTOR_SUBCASE A3 ON (A2.INF_ADV_MAIN_CASEID=A3.INF_ADV_MAIN_CASEID) LEFT OUTER JOIN INF_ADV_SUBCASE_CLAIM A4 ON (A3.INF_ADV_DEBTOR_SUBCASEID=A4.INF_ADV_DEBTOR_SUBCASEID) WHERE (UPPER(A2.MAIN_CASE)='CC STORES, INC.') AND (A6.HISTORYID IS NULL OR A6.HISTORYID = '') AND (A6.NOTES IS NULL OR A6.NOTES = '') ORDER BY A3.CLAIM_AMOUNT DESC
1.) The only conditions that I am using for this group is
Account.History.Historyid does not contains data
& Account.Historynotes does not contain data
2.) The column headings that are part of the SQL above are:
Account,(POC) claimamt,Case Acct Mgr,Notes, Createdate,Modify date
A.)Now, There are some accounts that were created before 18/05/2010 but still in the results it shows 18/05/2010 for all the accounts.
B.) After using the conditions above, I was cross checking and found out that there were some accounts that did not have any history/notes and still they were not returned in the results.
Please let me know if you need more information.
06-21-2010 09:13 AM
Do the Accounts 'Missing' from your group meet the following condition: Where MAIN_CASE ='CC STORES, INC.' ?
If they do then I would look at your joins.
06-21-2010 09:19 AM