Grouping in RoleTailored Client Reports

When moving to the RoleTailored client some people have experienced difficulties with grouping in RDLC reports in cases where the requirements are just a bit more complex than a basic grouping functionality.

I must admit that at the first glance it does not seem simple, but after a little research it does not look dramatically complex either. That motivated me to write this blog post and share my findings with all of you NAV report developers.

So let’s take a look at the task we are trying to accomplish.

I have a master table, which contains a list of sales people – SalesPerson. The sales people sell software partially as on-premises software and partially as a subscription. There are two tables, which contain data for these two types of sale: OnPremisesSale and SubscriptionSale.

The example is artificial and is meant only to show different tricks on how to do grouping. The picture below shows the data for this report:

 For each sales person I need to output both sales of on-premises software and subscription sales and show the total sales. Something that looks like the following:

 

Now we have all required information, let’s start solving the task.

1. First, I create an NAV report. Add all necessary data items, establish data item links for the proper joining of the data, and place all needed fields on the section designer in order to get them inside RDLC report.
See the picture below.

2. Next, I go to RDLC designer in Visual Studio. First I pick a list control, put the SalesPerson_Name field in the list, and set the grouping based on the SalesPerson_SalesPersonId field.
3. After that, I place a row with column captions on top of the list.

Design in Visual Studio as shown below.

4.  Now I need to place two tables inside the list- one for the on-premises software and one for the subscriptions.
A list can display detail rows or a single grouping level, but not both. We can work that around this limitation by adding a nested data region. Here I place a rectangle inside the list and place two tables inside this rectangle, one for On-Premises and one for Subscriptions. In each table, I add header text and add the CustomerName and Amount fields.

5.  I also add two text boxes for the sum of amount – one inside the rectangle to show total sales for the sales person and one outside to show the overall amount. Both contain the same formula: =SUM(Fields!SubscriptionSale_Amount.Value) + SUM(Fields!OnPremisesSale_Amount.Value)

 

The picture below shows the result of this design:

  

6. It looks more or less correct, but there are uneven strange empty spaces between rows. In order to detect the root cause of this problem let’s add visible borders to our tables. Change the BorderStyle property to Solid for one of the tables and to Dashed for another.

So the result will look like this:

 

7. This result indicates that our report has two problems:

• Tables do not filter out the empty rows. The result set contains joined data for both on-premises sales and subscriptions – so each data row has an empty part of it. The Data Zoom tool (Ctrl-Alt-F1) is your best friend when you need to learn how your data looks.
• Empty tables should be hidden from the output.

So I will make two fixes in order to address these two bugs:

• In the table’s Property window, on the Filter tab set a filter: Fields!OnPremisesSale_CustomerName.Value > ” for one table and Fields!SubscriptionSale_CustomerName.Value > ” for another. (Note: In the Value field, you must enter two single quotes.)
• For each table, set the Visibility expression to =IIF(CountRows(“table2”) > 0, False, True). The table name should be replaced with an actual table name. Table names are different, so the visibility expressions should also be different. Please avoid a copy/paste error here and do not forget to wrap the name with quotes.

As an addition, I will make some minor changes to improve the report’s readability: change fonts, font size, font style, and add separator lines.

All these modifications will produce the following output:

 

That is exactly it what I wanted to achieve at the beginning.

I also have some tips, which might be helpful in your future reporting endeavors:

1. Use lists. In many cases it is more convenient than creating a complex grouping inside a table. You can nest a list or any other data region inside the list.
2. Do not forget that a list can display detail rows or a single grouping level, but not both. It can be worked around by adding a nested data region including another list.
3. If there are some issues with layout, then make the invisible elements of the design temporarily visible – set visible style for table borders or change color of the different elements of the layout.
4. Build inside Visual Studio. That can catch layout errors and reduce development time.

 – Yuri Belenky