cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 1

Help Needed For Customizing / Creating Custom Report

Hi Folks , I am back with another Query.I am using QB Desktop Enterprises 2014.

I have created some custom data fields for customers & items , relating to my business.

I need to build custom report which should be consist of customers & Items data fields.

For Example :

Report should be showing , Sale By Customers/Job => All Saleable Items => Invoices =>Quantity (Quantity total for all invoices should be displayed in numbers at the end) => CUSTOM DATA FIELDS (which i have created for both customers & Items) =>Sale Price=>Amount & Balance

Its been pretty same as one can run SALE BY CUSTOMER DETAIL Report.

Now i need to show amount/payment received by same customer/job & remaining balance as well within the same report.

Thats mean i want to merge SALE BY CUSTOMER DETAIL & CUSTOMER RECEIVABLE Report together.

I have attached two PDF reports format for better understanding.

I am pointing towards report no - 1

Actually , My customers needed that type of report when i send them ledgers statement , they require all custom data fields displayed their.

Please Guide me, if i can run/create that type of report ?.

Thanks

Solved
Best answer 12-10-2018

Accepted Solutions
Level 15

First, you need to be careful about your expectation for...

First, you need to be careful about your expectation for Custom Fields for reporting purposes.

Second, every report has a perspective. You cannot Mix two perspectives with the reporting; you can export to Excel and do combination reporting there.

You want Customer Sales Detail, and some of these custom fields are part of the Transaction Header, not the detail lines.

You can Sales, but also payments. That is a mix of transaction types; so, you really want Customer Transaction Details, not Sales. Or, you Do want sales, so Payments will be a different report.

You are trying to add "current AR Balance" to a Detail report. Yet, AR balance is a calculation, not Transactions and not details. It is calculated From the transactions, not from the details.

So, you might even three. Here is one thing you can do, though:

"Now i need to show amount/payment received by same customer/job & remaining balance as well within the same report."

For a Transaction-based report for Invoices, you can show Orig Amount and Balance.


View solution in original post

10 Comments
Level 15

First, you need to be careful about your expectation for...

First, you need to be careful about your expectation for Custom Fields for reporting purposes.

Second, every report has a perspective. You cannot Mix two perspectives with the reporting; you can export to Excel and do combination reporting there.

You want Customer Sales Detail, and some of these custom fields are part of the Transaction Header, not the detail lines.

You can Sales, but also payments. That is a mix of transaction types; so, you really want Customer Transaction Details, not Sales. Or, you Do want sales, so Payments will be a different report.

You are trying to add "current AR Balance" to a Detail report. Yet, AR balance is a calculation, not Transactions and not details. It is calculated From the transactions, not from the details.

So, you might even three. Here is one thing you can do, though:

"Now i need to show amount/payment received by same customer/job & remaining balance as well within the same report."

For a Transaction-based report for Invoices, you can show Orig Amount and Balance.


View solution in original post

Level 1

Thanks qbteachmt For responding. So according to your Rec...

Thanks qbteachmt For responding. So according to your Recommendations , Its impossible what i want to create from QB.
Actually 90% of Report is available as per my requirement , Remaining 10% can be availed by importing to Excel.
I have attached New Excel File , for understanding , what i need. Please reply if there is any Possibility of creating Custom Transaction Detail Report like what is displayed in EXCEL Attached File ?.
Level 15

The Custom Fields in your report seem to be related to th...

The Custom Fields in your report seem to be related to the Transaction; typically, your details are the Items, such as Airfare and Room, with Room or lodging as qty and Type = you could use different Items for this and not Custom Fields. We started this topic with the caveat that Custom Fields don't report well.

There is no report to combine Invoice and details and payments against it.

Yes, you can use Excel to get combined reporting.

Level 1

Well Thanks For your all support and help. The End Result...

Well Thanks For your all support and help.
The End Result is to Arrange Customers Sale Report and export it to Excel.
Then Arrange Customers Received Amount and import it to Excel.
Merge the Report by own way and its done.
The Custom Fields For Customers & Items are necessary For me so i need to stick along them.
Level 15

"Well Thanks For your all support and help." This is a Pe...

"Well Thanks For your all support and help."

This is a Peer User text-based forum. Since we cannot see your data with you and cannot work with it ourselves, all we can do is give you direction and guidance for the tools you would need to use on your own.

"The End Result is to Arrange Customers Sale Report and export it to Excel.
Then Arrange Customers Received Amount and import it to Excel.
Merge the Report by own way and its done."

Exactly :)

"The Custom Fields For Customers & Items are necessary For me so i need to stick along them."

As long as they are working for you, fine. And yes, combining reports is pretty much where this discussion started.

So, congratulations; and I am glad to help.


Level 1

Thanks For Responding to my query once again I need a lit...

Thanks For Responding to my query once again
I need a little information for which i do not want to start another conversation.
I am using 2 A/R Accounts . One for my Ticketing & Second For Umrah Services.
I want to Customize & Create 2 Different Invoices and set one A/R Account default for first invoice,
second A/R Account as Default for second customized Invoice.
Hope U understand my Query.
Level 15

"Both A/R Accounts are for Service Items" AR account = am...

"Both A/R Accounts are for Service Items"

AR account = amounts Owed and not "what they owe because of the charges."

In other words, an invoice with Two things on it = One AR amount, not split because of two Unpaid Items. You can run reports to see Items on Unpaid invoices. There is no Splitting an invoice.

A partial payment on an invoice is against an AR Balance; it is never against any specific entry on the invoice.

You would need to be creating individual Invoices, with only One Item on each, to split AR like this.

"and my boss needs daily report for Tickets Receivable & Umra Receivable separately."

You can run a report on Sales by Item for Accrual Basis and filter for Unpaid. Or, Sales by Customer Summary, Columns by Item Detail, filter for only those two items, and on Paid Status = Open.

"At the end of month , we calculate the total amount received for both services separately."

You don't have to do this; you have a program with Reports. Sales by Customer Summary, Cash Basis = Payments this date range. Filter on those two items, and/or select to see Columns by Item Type or Detail.

Or, look at Sales by Item reports.

You have a computer program; there isn't much need to do anything manually, for calculations. Run the reports.

"Both Service Items contain Sub Service items categorically."

That is not what Subitem means.

In the Items list, you can Organize that list for convenience, and for Collapsed and Expanded reporting. If you list the Parent level item as the charge, there is no Activity for the Subitem, and the opposite also is true. Charges for the Subaccount do not "roll up" to the parent. This is what you see for Chart of accounts, but not for items.

Example:

I stock Tshirts; I have a subaccount for each color. I sell the Top level, and there is no activity for each color. Or, I sell the subitem Colors, and the Parent Level has No sales at all, because it is its Own Separate Item. That is why you often out a parent level item with the description "DO NOT USE."

"That is why , I created Two A/R to track Receivable easily , without going through report and filtering."

You will need to do individual invoicing, then.

Let me remind you about Class Tracking. If you listed Both Items on one invoice, for One AR account, and Class track the two lines differently, such as Labor Vs Materials, you can see this already in the Balance Sheet by Class = AR falls into Each Column on that report. P&L by Class = the one Sales Account has values in each Class column, as well.

We refer to this as Divisional reporting, or Departments, or for Product Lines.

Level 15

"One for my Ticketing & Second For Umrah Services." Are y...

"One for my Ticketing & Second For Umrah Services."

Are you certain these are not supposed to be your Items? Is this something you Charge? If so, there is no need for additional AR accounts. AR simply represents funds not yet paid; the Items control what is charged and why.

Or, if these are Divisions or Departments, your Program has Class Tracking and you don't need separate AR. You can run Balance Sheet by Class.

If these are different Entities, FEIN, you need a separate file for each legal entity.

"I want to Customize & Create 2 Different Invoices"

I think you mean Templates = design or Layout.

"and set one A/R Account default for first invoice,
second A/R Account as Default for second customized Invoice."

There is no Account assigned at all. The Templates are like your Pads of Paper, with letterhead, etc. This is a Design. When you create a Transaction Type, in this example, an Invoice, you select which Design you want = the look and layout, for what to reveal or not to show. It still is just a sale, no matter which Template or design.


Level 1

Both A/R Accounts are for Service Items and my boss needs...

Both A/R Accounts are for Service Items and my boss needs daily report for Tickets Receivable & Umra Receivable separately.
At the end of month , we calculate the total amount received for both services separately.
Both Service Items contain Sub Service items categorically.
That is why , I created Two A/R to track Receivable easily , without going through report and filtering.
Hope u understand , what i said
Level 1

Re: Help Needed For Customizing / Creating Custom Report

Don't know what kind of bus. this is, but I found a pretty good, "only" way to represent with a report, expenses and income all in one.  It's not ideal, but maybe qb will work a little harder to make this easier.  "Class Your Customers".  It's a bit redundent but you will get what your looking for in the long run.  You can modify, (add), specifics to your chart of accounts to negotiate the difference...depending on your business.  But, you do not need to touch COA if your main goal is to put customer expenses with their invoices----record by customer and cusomter classing-----.  Other accounting programs think outside of the box.  Until then, if one's COA is set accordingly, this does work.

Need to get in touch?

Contact us