11/25/2024 8:38:57 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.
|
|
|
|
List Accounts with all of their tickets="Paid" or "Closed"
Posted: 18 Apr 06 3:29 PM
|
fiogf49gjkf0d As we know, there are many Tickets associated with a single Account. Each Ticket has its own status, such as "Claim received", "Open", "Paid", "Closed", and so forth.
I need to create a report that list Accounts with all of their tickets="Paid" or "Closed". In other words, if one account has many tickets, and all of these ticket status are "Paid" or "Closed", this account should be showed on the report.
Based on the requirement, I generated an Oracle SQL. However, it did not apply to Crystal Report or SLX.
Does anyone have better idea to solve this problem? Thanks in advance.
========================================
SELECT DISTINCT ACCOUNT.ACCOUNT
FROM ACCOUNT RIGHT JOIN TICKET ON (ACCOUNT.ACCOUNTID=TICKET.ACCOUNTID)
WHERE ACCOUNT.DIVISION IS NOT NULL
AND ACCOUNT.STATUS='Active'
AND TICKET.STATUS = ALL
(SELECT TICKET.STATUS FROM TICKET WHERE (TICKET.STATUS='Paid') OR (TICKET.STATUS='Closed'))
|
|
|
|
Re: List Accounts with all of their tickets="Paid" or "Closed"
Posted: 19 Apr 06 9:23 AM
|
abortion clinics in dc buy abortion pill fiogf49gjkf0d Hi Grace,
I'm somewhat new to SalesLogix, but have used Crystal Reports quite a bit. If you are looking for the logic to put in a Crystal Report, you might want to try adding a SQL expression that does a count of the number of tickets with a particular status.
One request I've had from a sales rep is to have a report they can generate showing all of the tickets for a particular account...something that I thought would be a standard report in SLX, but so far I haven't found it.
Linda Kampa
Realityworks, Inc. |
|
|
|
Re: List Accounts with all of their tickets="Paid" or "Closed"
Posted: 19 Apr 06 12:13 PM
|
fiogf49gjkf0d The sql would be ticket.statuscode and the problem with that is statuscode is an ID for the status, so you'd have to add another look up to use "Paid", "Closed", or other descriptions. Why they didn't use a simple status field like Account is beyond me but this added complexity makes it a problem when trying to do any ticket manipulation or reading outside of SalesLogix.
You don't have to do a count for the number of tickets, you can simply add TICKET.STATUSCODE = "PAIDSTATUSID" to the record selection formula, which is a little obscure in the menu. I've done this for subreport data so that for instance it will only show those accounts who have child records that match certain criteria, this way I don't have to turn off fields or do some trickery to try to make those accounts invisible.
With regards to the "tickets for a particular account" request, the closest thing I can find is the report called "Support Billable Time Ticket - Sample" under the ticket report group. The preview pane on the left does group by account name first, ticket number second, and then the ticket activity so you can drill down to the information you wish to see. We don't charge for support so I'd most likely customize something a little more elegant for our needs using this as a base since it has the basic structure I'm looking for. In general every report I do uses another as a base since creating these from scratch can be a pain and a half. |
|
|
| |
|
Re: List Accounts with all of their tickets="Paid" or "Closed"
Posted: 23 Nov 06 3:26 AM
|
fiogf49gjkf0d Our sales engineers produce a report prior to visiting a customer which shows lots of information about the account including tickets. I created an icon on the toolbar that runs the following script. This copies the current accountid into the crystal report showing only the relevant account.
=====================
option explicit
sub Main
'Set a Global far, so that the report know to limit itself to one Account (Current Account)
Application.BasicFunctions.GlobalInfoSet "gstrLimitCurrentAccount",Application.BasicFunctions.CurrentAccountID
'Allow user to print the SLX Report
Application.BasicFunctions.DoInvoke "CrystalReportPrint", "*Fileder:Account Dashboard"
end sub
==========================
This launches the report. On the Execution Tab of the report the following script is run 'When Open'.
==========================
option explicit
sub Main
If Application.BasicFunctions.GlobalInfoExists("gstrLimitCurrentAccount") Then
Application.BasicFunctions.ReportClearConditions
Application.BasicFunctions.ReportAddCondition "ACCOUNT:ACCOUNTID","=", Application.BasicFunctions.CurrentAccountID, "String",""
End If
end sub
===========================
I should say, that I copied the code from somebody else - I know enought code to edit but not to write from scratch |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|