RM Detail Historical Aged Trial Balance Crystal Reports

Hello All,

I am attempting to create a crystal reports that utilizes the
historical aged trial balance report but with an outside supplied list
of customer IDs and a user specified aging date. I found the following
query below, can anyone help in modifying the query to meet such
requirements?


SELECT SALSTERR AS Territory, SLPRSNID AS Salesperson, AccountRep,
CUSTNMBR AS CustomerID, CUSTNAME AS CustomerName,
CNTCPRSN AS Contact, PHONE1 AS Phone, PYMTRMID AS Terms,
SUM([1-7]) AS [1-7 Days], SUM([8-30]) AS [8-30 Days], SUM([31-60]) AS
[31-60 Days], SUM([Over 61]) AS [Over 61],
SUM([Total Balance]) AS [Total Balance] FROM
(

SELECT ISNULL(s.SALSTERR, '') AS SALSTERR, c.SLPRSNID,
RTRIM(LTRIM(c.USERDEF1)) AS AccountRep, RTRIM(LTRIM(rm.CUSTNMBR)) AS
CUSTNMBR,
RTRIM(LTRIM(c.CUSTNAME)) AS CUSTNAME, RTRIM(LTRIM(c.CNTCPRSN)) AS
CNTCPRSN, LEFT(c.PHONE1, 10) AS PHONE1,
RTRIM(LTRIM(c.PYMTRMID)) AS PYMTRMID, rm.BCHSOURC, rm.RMDTYPAL,
rm.CSHRCTYP, rm.DOCNUMBR, rm.DOCDATE, rm.POSTDATE, rm.GLPOSTDT,
rm.ORTRXAMT, rm.CURTRXAM,
DATEDIFF(d, rm.GLPOSTDT, GETDATE()) AS DaysOld,
CASE
WHEN DATEDIFF(d, rm.GLPOSTDT, GETDATE()) <= 7 THEN
CASE WHEN rm.RMDTYPAL IN (1, 2, 3, 4, 5) THEN rm.CURTRXAM ELSE -
rm.CURTRXAM END
ELSE 0
END AS [1-7],
CASE
WHEN DATEDIFF(d, rm.GLPOSTDT, GETDATE()) > 7 AND DATEDIFF(d,
rm.GLPOSTDT, GETDATE()) <= 30 THEN
CASE WHEN rm.RMDTYPAL IN (1, 2, 3, 4, 5) THEN rm.CURTRXAM ELSE -
rm.CURTRXAM END
ELSE 0
END AS [8-30],
CASE
WHEN DATEDIFF(d, rm.GLPOSTDT, GETDATE()) > 30 AND DATEDIFF(d,
rm.GLPOSTDT, GETDATE()) <= 60 THEN
CASE WHEN rm.RMDTYPAL IN (1, 2, 3, 4, 5) THEN rm.CURTRXAM ELSE -
rm.CURTRXAM END
ELSE 0
END AS [31-60],
CASE
WHEN DATEDIFF(d, rm.GLPOSTDT, GETDATE()) > 60 THEN
CASE WHEN rm.RMDTYPAL IN (1, 2, 3, 4, 5) THEN rm.CURTRXAM ELSE -
rm.CURTRXAM END
ELSE 0
END AS [Over 61],
CASE
WHEN rm.RMDTYPAL IN (1, 2, 3, 4, 5) THEN rm.CURTRXAM ELSE -rm.CURTRXAM
END
AS [Total Balance]
FROM RM20101 rm
LEFT OUTER JOIN RM00101 c ON c.CUSTNMBR = rm.CUSTNMBR
LEFT OUTER JOIN RM00301 s ON s.SLPRSNID = c.SLPRSNID
WHERE rm.CURTRXAM > 0
and rm.postdate <= '2010-01-18 00:00:00.000'

) AS dt_RMAging
GROUP BY SALSTERR, SLPRSNID, AccountRep, CUSTNMBR, CUSTNAME, CNTCPRSN,
PHONE1, PYMTRMID
ORDER BY SALSTERR, SLPRSNID, CUSTNMBR
0
dhsieh12 (1)
9/23/2011 3:53:47 PM
greatplains 29623 articles. 5 followers. Follow

0 Replies
505 Views

Similar Articles

[PageSpeed] 9

Reply:

Similar Artilces:

ORDER DETAIL SUBFORM
Iam using the customer order database which I downloaded from the microsoft website. I have a order detail subform which has a combo box which retrieves the ProductID from Products. I also want the unit price to be retreived from the products table. I don't want the customer to change the unit price in the order detail. Please help me to create this. Thank you PON ...

Money 2004 trial uninstall
After intalling Money 2004 trial version about 2-weeks | ago, I finally read the small print stating that data | entered in that version won't be available in Money 2003. | Foolishly (I think) I unintalled the 2004 version. Now | the 2003 version cannot locate the money files. The error | reads "Money cannot locate 'C:/documents and | settings/victor/my documents/my money.mny' or cannot open | it, possibly because it is a read only file or you do not | have permission to change it, or your disk drive is write | protected. If you have chosen the correct file and it | cannot be...

Detailed Sales Report
Hi, When I run my detailed sales report, i include columns profit and profit margin. I have noticed that I am getting incorrect figures. The Cost, Profit and Sales Tax do not equal the Total Sales. On further investigation, I found that where I have sold an item more than once, the cost shown is only for 1 item, thus giving an incorrect COST total. Has anyone come across this issue? hi you might be using the cost field only. you have to make the formula to show the total cost I am sure the RMS is showing the unit cost modify the report as you like. There is no bug at all in report. &q...

Update on master record when click onto subform of detail
Hello, I am using Access 2000 (my company doesn't believe in upgrades :) ) and using Oracle as a back-end. I have a form with a subform representing a master detail relationship where Attribute table attrib_id, Name, Mandatory, default_ AttributeValues table attribval_id, attrib_id, Value_ These are referenced by a projects table. I won't get into those tables. For attributes which are mandatory I need to allow the user to assign a default. The way I have done this on the form is to place a unbound radio button on each record of subform. Then place a tran...

Integration Manager
Hello Hope someone can help me with this. I am trying to create an integration into RM. The source file I have contains the Goods and the Tax nominal accounts and values. These will always be credits. The system I am integrating from has no concept of Debtors Control, so effectively the source file I have only has the Credit amounts, no Debit amounts. The Debtors Control (Receivables) will always be the same account, and the value will be the full invoice value that appears in my header record. I can't see a way to use the source file I have to get the Credit amounts in, and...

Reporting Services with CRM
Is anybody using reporting services with CRM 1.2. I'm using RS for all my applications and I would to be able to integrate this great product with CRM. David, Well, you can't integrate it so that users can get reports off the reports menu, so I think everybody is still writing Crystal reports. BUT - the good news is that CRM Next.0 will completely support RS... HTH, Dave ------------------------------------------------- David L. Carr, President Visionary Software Consulting, Inc. Main #: 971-327-6944 Come by our new Booth #806, across from the MSCRM pavilion at Microsoft's Wor...

Deleted timesheet resists in Reporting DB
On the one hand I hope for all of you that you can’t answer my question since you never ran into it. On the other hand I hope that anyone has an idea what I could try: I have the situation that a timesheet was created at some day. A few days later this timesheet was deleted. We don’t know exactly when that was done, but it could be during a timeframe when disk space was full. The issue is now, that we have the old timesheet data in Reporting DB, but no information for that timesheetUID in Published DB. When resource create a new timesheet for that period, there is an error in...

Extract email account details?
Hi all, I have to do a clean install as my machine is cluttered with useless crap. My wife uses the PC to work from home using remote desktop, but she also uses outlook locally. Originally the outlook account was configured for her by the then company IT guy who is no longer around. What I want to do is get the email account password so I can re set up the account after I do a format and clean install. Any pointers? I'm guessing just copying the .pst will not suffice? -- Regards Jon The details are stored in the registry. Save my settings wizard might work, but I dont think it sa...

Reporting Schedule error
Atter migrating from DPM 2007 to Server 2008 64bit- The reporting services Server cannont connect to the DPM database A single report does work, but can schedule anything? ...

detail sections same height different row count
I use a report to print a list of people and a variable number of rows in each person's detail section. How do I make each person's section the same height, regardless of the number of rows in the detail section, adding white space to fill. Thanks, Eric Do you actually need the section to be the same height or do you just need it to appear to be (eg you want to put a box around it and have the box appear the same size for each person)? Will your report be one person per page? Do you have to allow for anyone having more details than your desired height? Are you using subforms? Evi ...

Payment details on customer statement
How can I include payment details (i.e. check number, credit card number) on customer statements? They appear on receipts and invoices, but I can't seem to get the tender details to show up on the statements I send to account customer. All that shows up now is a message that reads "Payment Received - Thank you". I would really like to add the method of payment and payment details to the statement as well. Any suggestions? Thanks, Paul Arenson Anderson Plywood Sales Culver City, CA ...

Reporting on custom fields
I'm having an issue where I cannot find my custom fields in Report Designer when I create reports. If I look at the SQL Management Studio (MS SQL 2005) I can find the fields under the 'Account' view, but not under the 'Accounts' Table. If I pull up a sample set of data based on the view from within the Management Studio or VS2005, I can see the data that I am wanting, but Report Designer doesn't look at Views, it wants tables. Is there an easy way to accomplish this? Can someone point me somewhere where I solve this? Our setup is a SBS2k3 network (STD) on one serve...

Sub Report Repeating Details
With On My Report I Have a Sub-Report, The Main Report Shows The The Project Number, Project Title and Enquiry Type: IE SUPPLY DUCTWORK ONLY The Sub-Report Shows The Drawings That Are Needed To Be Sent For that Enquiry. The Sub Report Is A Contineuous Type, What The Problem Is That Some Times I May Decide to go out 3 Ductwork Companys Etc, When I Print The Report, it Repeats The Drawings Depending How Many Companys Ive Gone To for that Enquiry, Ive Tried Going Into The SubReport Properties and Selecting The Fields Hide Duplicates No Difference. Is there a simple way of Stopping it, or ...

Age Calculation
I have the following sample field and record Age ApprovedDate 40 15-05-2000 Age-number field that shows the current age in 2010 Approveddate - date field when the application was approved. How to calculate the age of the applicants at the point of approval. In the above example the age would be 30... Thanks On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus@discussions.microsoft.com> wrote: >I have the following sample field and record > >Age ApprovedDate >40 15-05-2000 > >Age-number field that shows the current age in 2010 >A...

Managing headers across forms and reports
I am creating a DB with several forms and reports and would like all the forms to have the same header section with a logo and nearly all the reports to have another one. The idea is that changes to the logo and title would only have to be made once and in one place (hope that makes sense!) I’m using Access 2003 and have not found anyway of creating master template to do this and am thinking the best way would be to define a common Word object and paste the same object to each form/ report. Is there a better way? "sevilla10" <u58162@uwe> wrote in message news:a38...

Calculate age
I have an Excel Spreadsheet that contains a column of dates of birth in the format of dd/mm/yy. Across row 1 I have the current year in column "C" followed by 2011 in column "D" etc. What I am trying to do is calculate the ages of all the people using the year of their birth and the years that appear in row 1. I have posted this previously to this discussion group, but I do not seem able to find the posting now. Thanks in advance. If you use Jan-20xx for the value in row 1, you can then subtract the date in column A from the value in Row 1 Column B, and divi...

Exchange 2003
Is there a way in exchange 2003, to query all mailboxes for: ASX/ASF/MPG/MPEG/AVI/QT/RM/RAM - movie file formats. And have exchange 2003 which we run . . to generate a detailed report showing how much data of the total information store that movie files are taking up(eg: if employees are using the mail system appropriately). Also if it can, once it queries which users have movie format files in their inbox, to then list how much of the overall information store is being used by each employee(movie formats). ie: user1: 2% of the organizations total storage data is from this users total ...

RM-Statement
Is it possible to get the originating currency on a Statement? -- Toni Toni Can you give more info on which statement you want the originating currency. To the best of my knowledge any report you print in GP will be printed in functinal/originating currency only. Regards Reddu On Sep 28, 6:08=A0pm, Toni <T...@discussions.microsoft.com> wrote: > Is it possible to get the originating currency on a Statement? > -- > Toni Print Receivables Statements. -- Toni "reddy" wrote: > Toni > > Can you give more info on which statement you want the originating...

Write off small multicurrency balances
The sales write off screen only supports the functional currency, and won't find pennies left in a customer account in an originating currency when the functional balance is 0. Is there another way to only write off an originating currency tiny balance? Create a credit memo. However it sounds that you are not revaluing the customer account, hence the differences you are experiencing. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Rich" wrote: > The sales write off screen only supports the functi...

Balance views
In the register I see the ending balance. Is there a way to get the "Cleared Balance"? This is available through Ultrasoft Money for my palm pilot. I like this so I can verify if my acct is balanced. ...

detail form
I have a form that opens when a user selects a client that collects specific information about the client. For performance reasons, the form is invisible until invoked, then becomes visible. I see the options for populating that form as 1) apply filter to the form with the selection criteria, 2) programmatically change Recordsource of the form to the specific record, or 3) move to correct record with the .FindFirst function. Is there a performance or any other advantage to any of these approaches? I am thinking in terms of many users editing the data, all using this form. The 2nd optio...

Age Calculation Flaw?
I am trying to calculate the age of pre-school students at start of school year, and have a "flaw" somewhere if the birth month is the same as start of year month. The first formula below gives me the years, the second the months, can anyone spot the error??? =DateDiff("yyyy",[StudDOB],[Year_Start])+Int(Format([Year_Start],"mmdd")<Format([StudDOB],"mmdd")) =DateDiff("m",[StudDOB],[Year_Start]) mod 12 So, a student whose birth date is 4th January 2003, and school year start is 1st January 2008, I get 4 years and 0 months as his age (sh...

Help for newbie balancing accounts
I've switched over to Money 2k5 from Quicken, and am trying to balance my checking account for the first time. During the month I've downloaded tranx from the bank into Money. Now when I am in the reconciliation I cannot balance. I have a bunch of tranx already marked with a checkmark. How do I know if I've cleared a tranx? Should it have a checkmark in the box, an E, or be blank? Why do my tranx for the next month already show a checkmark? It's very confusing. ...

Parameter driver report
Hi, I have created a parameter driver report from account form which opens a quote which works superb. I've been asked to create a parameter driver report opening a report showing all cases to a selected account. I think this would be quite similar but I can manage it to work. The .aspx file in the quote report is like: <%@ LANGUAGE="JScript"%> <% if(Request.QueryString("oType")=="1084") Response.Redirect("/reports/viewer/html/viewer.aspx?id=1890&promptex-Quote="+Request.QueryString("oId")) } %> But this is not a quo...

where can I find details about "AfxGetThreadState()" ?
Hi, folks There are many usage with AfxGetThreadState(...) function, but I dont know how and where use it. So, please give some details or webaddress about it. :-p Thnx :-) Frank "Frank Han" <winsays@hotmail.com> wrote in message news:%23U3yn%23vhDHA.1952@TK2MSFTNGP10.phx.gbl... > Hi, folks > > There are many usage with AfxGetThreadState(...) function, but I dont know > how and where use it. > So, please give some details or webaddress about it. :-p It's an MFC internal function. The documentation is the MFC source code. Cheers Richard. See the fol...