NAV 2013 reports filtering problem

davmac1

I have been going through the NAV 2013 Reports training and worked on the long report in Chapter 2 (also included in msdn: http://msdn.microsoft.com/en-us/library/jj551758(v=nav.70).aspx
It has a document style report with customer information, then a table with customer ledger entries plus detailed customer ledger entries, then another table with sales orders.
Since the dataset results are flat, each row will either have customer ledger data or sales header data.
To avoid printing blank lines, you apply a filter to the group properties of the customer ledger table - entry no. > 0.
This works fine.
Then I tried doing the same thing to the sales header table, and no combination I tried worked - I either got blank lines or no lines.
Finally I put in some logic to hide the row using the Hidden property (=IsNothing(Fields!SH_Document_Type.Value), and that hides the row. (The fields are not available when I right click and bring up the group property box.)
I am still coming up to speed on Visual Studio for reporting, and I am hoping someone can tell me what I am doing wrong. Why does the filter on the first table work, but not the second?

(BTW - I really love the new report designer - NAV reports has long been due for an overhaul, and this provides us with a lot more flexibility and options. Plus every NAV release we will get the benefits of new VS enhancements.)

clausl

Can you share your "Report.rdlc" and "Dataset.xml" as I explained here:

http://mibuso.com/blogs/clausl/2012/06/24/offline-preview-and-debug-reports-in-microsoft-dynamics-nav-2013/

Then I will have look at what it is.

/Claus Lundstrøm

davmac1

Thanks,
I am using Cronus, so I have no worries about data confidentiality.
I have a setting for row visibility in the detail row of the last group, but I get a lot of white space for customer 10000. I think it is applying the filter in the last group to the first group as well.
My 2 files are 350K which exceeds the Mibuso limit and the upload only seems to accept images.
Do you have a place I can email or post them to?

clausl

I just send you a pm, with my e-mail address. I will place the files on my SkyDrive so everybody can see them.

/Claus Lundstrøm

davmac1

Thanks - I sent you the files.
Currently the second group does not have a filter because I could not get it to work. I added hide row instead to the detail row of the second group.
(I also tried having the group filter of the second block be Cust ledg entry no = 0, but then none of the rows in either groups printed.)

davmac1

Thanks Claus for pointing out the problem.
I had a bad group by field.

In the training material it has the following:
To add the sales header data

1.
From the Toolbox, drag a Table control to the List control, and then put the table control under the table that contains the Cust. Ledger Entry table

2.
Right-click a column and add columns to create five columns for the table.

3.
Delete the first header row from the table.

4.
Right-click the data row, choose Tablix Properties, verify that the DataSet name is set to DataSet_Result, and then choose the OK button.

5.
Right-click the data row, choose Add Group, and then choose Parent Group to open the Tablix group window.

6.
Select the Group by: option and then choose the fx button to open the Expression window.

7.
In the Category column, select Parameters, and then in the Values, column double-click Sales_Document_Caption. Verify that the Set expression for: Value box contains the following value: =Parameters!Sales_Document_Caption.Value. Choose the OK button.

Claus changed it to group by “[SH_Document_Type]”. I made the change in my copy, added the sort filter to only select “[SH_Document_Type]” = Order, and everything works.
In real life, I would include Credit Memos, etc. too. The training guide recommends adding an integer field with the value of the option field, which I would do for convenience in filtering.

I will let Claus have the final word to wrap it up.

Thanks again Claus!
:thumbsup:

clausl

Thanks David,

This is a bug in the training material. When I think of it, the training material is based on the Hands On Lab(HOL) material provided at Directions some years back for NAV 2009.
As the material has been updated to use Captions as Parameters, the grouping of course has to be changed as well. In NAV 2009 it was fine to group on the caption because it existed in your dataset, but when we start using Parameters they don't show in your dataset for each row. (This is a good thing because it will reduce the dataset size and make your report faster).
The report training material needs to be updated to correct this mistake.

I have placed the files we discussed here, if somebody would interested:
http://sdrv.ms/RlfZ9z

/Claus Lundstrøm