Need help with creating a report

[SLXuser]

I need to create a report for a group "ABC"

with following fields

1.)Createdate

2.)CaseAccount Manager

3.)ClaimAmt

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.

Please advise.

Thanks

[AdamTravers]

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

Message Edited by AdamTravers on 06-21-2010 03:33 PM

[slsl]

What table(s) are you using to run your query?  Or are you actually looking at creating a report via Crystal Reports?

[SLXuser]

Adam,

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

[SLXuser]

Hi,

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.

Thanks

[AdamTravers]

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.

[SLXuser]

Adam,

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.

Regards,

Rajiv

[SLXuser]

Adam,

I have figured out concern(A) but I am still looking for answer for concern(B) in my previous email.

Thanks,

Rajiv

[AdamTravers]

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.

[SLXuser]

All the missing accounts are part of the same group from which other accounts with no notes/history were returned. Is there any way to set the conditions for a particular accounts in a group?