RepServ report 1 main table, 2 subtables - DS structure

Miklos_Hollender

As a simple example - list of customers, below, list of their sales orders, and list of their invoices. In NAV Customer table above, Sales Line table and Sales Invoice below, intended. In RS?

Would you use one Table on the visual designer or two? Or generally how would yo do it?

In other words, what I am actually asking is - what is the the structure of this generated datasource? What is considered one record of the datasource - what is actually iterating on? When you just have one 1 sub-dataitem, I think a record - an iteration step- in the datasource means basically the lowest level body section. But in this case? Is there any way to investigate it's structure?

Can anyone recommend a book that explains this sort of thing fairly well? Not how RS works but how RS with NAV works - the structure of the datasource etc. tips and tricks etc. etc. Thanks.

Slawek_Guzek

Hi,

There is no good answer to your question I'm afraid. It depends on what do you want to achieve.

If you create report directly in SSRS you can create as many data sources as you want. It can be one data source per table, or you can 'flatten' you data and create one datasource with combined information from all 3 tables.

One flat dataport allows to use nice SSRS level grouping with very simple report structure and virtually no coding, but disadvantages are that you need to carefully think about it's structure as number of records returned will i quitre significant.

Three dataports allow in one report will make a little headache when it comes to groupping.

Report created in SSRS (without NAV) can only be launched from NAV using URL function.

if you design SSRS report from NAV you do not have any option. NAV will create simple flat dataport, containing all fields. It will be executed in RTC only, instead of classic report.

Hope this helps a bit.

Regards,
Slawek

Miklos_Hollender

It must be NAV-based because it's the modification of a standard report that will need to maintained version by version.

Could you explain a bit more about the flat data structure? How is it flattened - is it possible to somehow preview that data source or something in order to see what records are being sent and in what order? In the RDLC file there is just a dummy datasource.

Staying at my simple example (the real problem is of course more complicated) if there is a Customer dataitem and a Sales Line and a Sales Invoice Line DataItems properly linked and some fields are displayed on the Sections what will be sent to the RS report? Suppose I have Name on the Customer - Body and Document No. on Sales Line - Body and also Document No. on Sales Invoice Line - Body - and the customer has 2 Orders and 3 Invoices, will the data sent be like:

Name,OrderNo1
Name,OrderNo2
Name,InvoiceNo1
Name,InvoiceNo2
Name,InvoiceNo3

is this so?

If yes, how can Table Rows be filtered to display only the order lines or only the invoice lines?

The problem I cannot find any such reports in standard NAV. Most reports have just 1 main dataitem and 1 sub-dataitem, and some technical ones like Integer which are a different thing.

Miklos_Hollender

OK I think I figure it out. I added a table without any groups or conditions in order to see the raw DataSource and it looked like this:

Customer1,Order1,Empty
Customer1,Order2,Empty
Customer1,Empty,Invoice1
Customer1,Empty,Invoice2
Customer2,Order1,Empty
Customer2,Order2,Empty
Customer2,Empty,Invoice1
Customer2,Empty,Invoice2

Therefore, three different "sections" in order to imitate the original sections can be created in RS by
- setting a Group for the Customer.No. or in general, on the primary key of the Main DataItem, this will imitate the Section of the Main DataItem

- Adding a Row for SubDataItem1 (in this case Sales Invoice Line) will act as a "section" if the Hidden property of the Line is Sales Invoice Line . Document No. =""

- Same way another Row can be added to act as a "section" for SubDataItem2

I think it works however I must test it on more complicated examples.

This should be in the documentation...