Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Need help with creating a report

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

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: Need help with creating a report

[ Edited ]

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
Regards, Adam Travers
empath-e Services Limited
Copper Elite Contributor
Posts: 41
Registered: ‎03-09-2009

Re: Need help with creating a report

What table(s) are you using to run your query?  Or are you actually looking at creating a report via Crystal Reports?
Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Re: Need help with creating a report

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

Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Re: Need help with creating a report

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

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: Need help with creating a report

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.

Regards, Adam Travers
empath-e Services Limited
Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Re: Need help with creating a report

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

 

 

Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Re: Need help with creating a report

Adam,

 

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

 

Thanks,

Rajiv

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: Need help with creating a report

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.

Regards, Adam Travers
empath-e Services Limited
Highlighted
Tuned Listener
Posts: 19
Registered: ‎06-11-2010

Re: Need help with creating a report

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?