Microsoft Dynamics GP (Archived)

Get AR balances by month for a customer

(1) Share

Report

Posted on by Bob Luedtke

160

Is there a way to get data into Excel from a SmartList that would calculate the monthly AR balance for a customer?

The ideal pivot table would look roughly as follows:

Customer Name	Document Type	January	February	March
Customer 1	Sales / Invoices
	Payments
	Returns
	Credit Memos
	Debit Memos
	Finance Charges
:Monthly Net Subtotal
Monthly Balance

Our customer wants to see the trends of AR balances for certain customers.

Has anyone come across this request? Or know of a solution?

Thanks.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

soma 24,410 on at

Like (0)

Report
Copy link

Link copied!

I think, there is no such option available in smartlist or GP. Either you need to create a own customized report or export the values from smartlist and make it with using pivot.

or Deploy SSRS reports in your GP and check the reports like "Sales per Month" and "Sales per Month Details" under charts and KPI.

Hope this helps!!!

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

I have to agree with Soma that there is not something 'out of the box' in the system that will export to Excel.

However, I have had a similar request from a client and the way I managed it was to create a VIEW in the database that contained the data that we wanted, then in Excel linked to that VIEW. It is getting the data into the VIEW that can be the issue.

From Excel I was then able to SORT and FILTER that data any way I wanted.

Let us know if there is anything else you need.

Was this reply helpful? Yes No
Bob Luedtke 160 on at

Like (0)

Report
Copy link

Link copied!

Would it be possible to get your view?

The standard Accounts Receivables SmartList gives me most of the information, but I can't figure out how calculate the monthly balance within the PivotTable (or PowerPivot). I would need to start with the customer balance, then back calculate the ending balance for each prior month. Maybe your view would present the data in a better fashion.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Sorry, Bob we did this in Payroll (Canadian) but the idea is similar.

To do the summary work, using the table it will have the document date, from that field you should be able to 'filter' and 'sort' the data into the correct monthly buckets.

Get the data into column format, then in Excel use the date field to then 'pull' the data into the column.

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

If you are ready to connect to database directly from an Excel, check this view named "ReceivablesTransaction". This view is available as part of your Smartlist SQL objects. This is a standard GP view, so no customizations are involved.

You need to focus on following fields from this view:

1. Customer Number

2. Customer Name

3. Document Type

4. Document Date

5. Original Trx Amount (Functional Currency)

6. Originating Original Trx Amount (Transaction Currency, if needed)

7. Void Status (filter only records that are "Normal")

You may add anything else you want from this. Please note that you must make the amount negative for appropriate document types (such as Payments, etc.) if you want it that way. If you are simply looking for a monthly pivot to know the amounts per document type, this would satisfy your requirement totally.

Now, the same data is available in Smartlist named "Receivables Transactions". You don't need to look outside of GP. Only caveat here is you won't get a refreshable data. Every time, you must export to Excel and do the pivoting.

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

Oh BTW, why don't you visit Victoria's blog: Victoria Yudin, Ramblings and Musings of a Dynamics GP MVP.

You may already have a tailor-made SQL view. Not just for this requirement, but for many other. Great site with loads of GP SQL stuff, if you are interested in SQL.

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report
Copy link

Link copied!

Addition to the above valuable suggestion mentioned by Vaidy, just use this code (datename(month,[Document Date]) as [Month]) to your view to get the month name and make it a pivot in excel, it will give you the detail as you expected.

Example:

SELECT

[Customer Name],

[SOP Type],

datename(month,[Document Date]) as [Month],

[Document Amount]

FROM SalesTransactions

Hope this helps!!!

Was this reply helpful? Yes No
Bob Luedtke 160 on at

Like (0)

Report
Copy link

Link copied!

I have an Export Solution already set up for this, taking into account the document type.

So the pivot table correctly shows the numbers for the customer. What I can't figure out is how to back calculate to also show the AR balance for each month. I know the SmartList has the current Customer Balance number. I need to figure a way to show the customer balance for each month in the table.

The end result my customer wants is basically a trend line of the monthly AR balance.

Was this reply helpful? Yes No
soma 24,410 on at

Like (0)

Report
Copy link

Link copied!

Have you tried above my mentioned query?

Was this reply helpful? Yes No
Andrew Hall 190 on at

Like (0)

Report
Copy link

Link copied!

When we want to get out reports like that we use a reporting tool called jet reports which is embedded in excel

jetreports.com/.../index.php

Was this reply helpful? Yes No