Problem with NAV report

sandman · 2010-01-25

Here's what i'm trying to do... I come from the Crystal Reports world so NAV reports have a strage and complicated feel to me...

I have 2 tables.
-Table 1: Customers
-Table 2: Sales

The sales table refers to the customer table with the customer #. However, i need to run a report that will show me the sales (total amounts by product) for the Master customers (or Bill-to) only, which is another field in the customer table.

I.E.
Master Customer
--> Product 1..... total sales
--> Product 2..... total sales
--> Total sales for Master customer

Now, my report has 2 data items, first one being the customer table, the other being the sales table.

The customer data item has the master cust field as DataItemTableView and as GroupTotalFields and it does show me the grand total for what I need but i can't figure out a way to get the totals by product.

I've also tried to go the SSRS way which makes the creation of the report a breeze but, the number of different parameters the users need to run the report make this option a little too complicated and long to execute (compared to NAV).

I hope this is clear enough...
Thanks

matttrax · 2010-01-25

It's a little confusing. First off, Sales is not a table. I assume you mean "Sales Invoice Line"? Or "Sales Invoice Header"?

You will probably need to add a new key along with a SumIndex field to the Sales Invoice Line table. Then you can group based on that. The C/SIDE Help from the client or the Application Designer's Guide from the product CD should get you started if you're not familiar with how to do that.

David_Singleton · 2010-01-25

Please never use Sales Invoice Line or Sales Invoice Header in reports. They should never be used in reports ever. Use the Customer ledger entry or item ledger entry tables.

sandman · 2010-01-25

Sorry for the confusion... The table that i want to use for the 'detail' section is "Value Entry"... I used the name "sales" as an example only... I have to get used to the fact that this site is for a specific software and not general programming...

I figured I would have to add a field to that table with the info I need but i wanted to minimize the impact on other objects.

Thanks

sandman · 2010-01-25

And why not use the Sales Invoice Line or Header in reports? Locks records?

Thx

matttrax · 2010-01-25

sandman wrote:

And why not use the Sales Invoice Line or Header in reports? Locks records?

When it comes to NAV the ledger entry tables are the final word. Any sort of financial reporting should really come from them. Value Entry is included in that set.

There can be problems when you don't go through the ledgers. This might be a bad example, but say you add a Discount % on a purchase line, but you don't post discounts separately to the G/L. Then, instead of using the amount field on the line, you manually calculate "Unit Cost" * Quantity as the amount spent for your report. Now you would be saying you owe the vendor more than the ledger says they do.

Maybe someone else can come up with a better example

David_Singleton · 2010-01-25

Its all about performance really. When ever a new client tells me that Navision is getting slow, I look to the Document tables ot see if they have keys added, and generally they do and its most often a major reason for the system being slow.

When you have finished with them you should delete data from all the document tables anyway, so reports written using them wont work.

As Mattrax says, reports should be written ONLY using Entry tables. Basically the tables that have an integer as the primary key.

DenSter · 2010-01-26

sandman wrote:

And why not use the Sales Invoice Line or Header in reports? Locks records?

You can't rely on the information in the posted document tables. Users can delete posted documents, so totals may not add up to actual financial numbers. Reporting on financial numbers should always be done on ledger entry tables.

Howdy, Stranger!

Quick Links

Categories

Problem with NAV report

Answers