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:
So I will make two fixes in order to address these two bugs:
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:
- Yuri Belenky
Thanks for your information...