Report data grouping question

ccbryan63

This seems like it should be simple but it's eluded me so far.

I want to do a report that shows Purch. Inv. Lines, totaled by Vendor and No., for an entered date range. That sounds easy but the Purchase Invoice posting date is on the Purch. Inv. Header. I don't want to display any info from Purch. Inv. Header, nor do I want it involved in the grouping or subtotaling. I just want total amount by Vendor and No..

How is this accomplished? The first filter has to be the date but if I have Purch. Inv. Header as the top record in the report it groups by invoice no matter what I try.

Any help appreciated!

Thanks in advance...

DenSter

You could add a purchase header dataitem and not have any sections for it

ccbryan63

Yes, but if the Header is the top dataitem then it doesn't summarize the groups properly, since the looping sequence is Header - its invoices, Header - its invoices etc. This happens even when I don't have any sections for the Header.

I'll try to show you. Let's say that during our period we have two invoices from Vendor1, both for the same 2 accounts at $100 each. Here's what I want:

Vendor1
. account1 $200
. account2 $200
. Vendor 1 total $400


Here's what I'm getting, with no header sections:

Vendor1
. account1 $100
. account2 $100

. account1 $100
. account2 $100
. Vendor1 total $400

I think I'm using the grouptotals correctly (it totals accounts correctly PER INVOICE) but the issue seems to be that I need all of the invoice lines in a single pool to be able to sort and total them correctly, but what I have now are separate pools for each invoice, if you get my drift.

Savatage

there is a field in mine called "buy from-to Vendor No." Field # 2 on the purchase inv. line and you might find useful

ccbryan63

Again, yes but... I first need to find all the Purch. Inv. Lines for the date range, then sum them on Vendor + account. I can total by vendor just fine, from Buy-From Vendor as you suggest. In SQL I'd just get my invoice lines like this:

Select * from [Purch. Inv. Lines] inner join [Purch. Inv Header] on [Purch. Inv. Line].[Document No.] = [Purch. Inv. Header].[No.] where [Purch. Inv. Header].[Posting Date] >= startdate and [Purch. Inv. Header].[Posting Date] <= enddate

And then build my report with no trouble. But is there a way to do this within Navision?

davmac1

You could define a view in SQL Server then create a Navision table with linked object = yes, give it the view name, and then use this table for Navision reporting. This method works very well and makes Navision reporting easy.
If the view name does not start with company name$, then be sure to set it to data per company = No

DenSter

ccbryan63 wrote:

Yes, but if the Header is the top dataitem then it doesn't summarize the groups properly

Sure it will. You said:

ccbryan63 wrote:

totaled by Vendor and No., for an entered date range.

Dataitems:
- Vendor
-- Purchase Header, linked by vendor number, sorted by vendor and date, which you leave as a filter
--- Purchase Line, linked by document number, at which point you only get the ones that are linked to the right vendor

Put the total by vendor in the header's footer, and group them by date which you put into a group footer.

Done. It kind of works as the join you described, but you need to be creative in how to put them into the NAV report designer.

DenSter

ccbryan63 wrote:

I think I'm using the grouptotals correctly (it totals accounts correctly PER INVOICE) but the issue seems to be that I need all of the invoice lines in a single pool to be able to sort and total them correctly, but what I have now are separate pools for each invoice, if you get my drift.

If you group by invoice number, you are going to get a total for each invoice, so if each invoice has one line, and you have a body section for the lines, you're going to get that it looks like it's repeating itself, when in fact it is doing exactly what you told it to do. If you don't want to see the detail, then don't include a body section, but put your controls into a groupfooter.