Understanding Dataset structure in reports and making a preview in Excel

As you probably know, one of major issue when developing reports is to have a preview of the dataset before creating the layout. This is due to the fact that when we develop a multiple dataitems report, what we find in the Report Builder is a unique Dataset containing all the fields (columns) of all the dataitems. In other words, the system performs a sort of denormalization of our data, by repeating dataset records in a one-to-many scenario.

In this post I want to explain how the system converts our dataitem structure and how to make a preview of our dataset.

Suppose we want to print a posted invoice containing comments on each line. The table structure in this case would be this one:

This would be translated into this dataitem structure:

In order to see a preview of our dataset we need to insert some colums into our structure

Now, when we run the report, we have to select Send to, Microsoft Excel Document (data only).

In the downloaded Excel we can see our dataset

As we can see, the inner join is translated into a unique table in which the columns of the first dataitem are repeated on each record of the nested ones.

In this specific example, the first two invoice lines have two comment lines associated, that's why we see the item number repeated two times for each line; on the other side, the last invoice line does not have any comment associated, that's why we see NULL into the Comment and the item number appears just one time.

Frequently on the standard reports we find a first/last dataitem associated to the Integer virtual table filtered in order to retireve just one record. This is tipically used to have a header/footer section to insert, for example, company information or totals.

Also in this case the result will be a unique dataset, but let's see in details what happens. Suppose to have this structure:

The first and the last dataitems are seen by the system as separated queries, so the final result will be this dataset.

Now we can understand that before making the layout it's very important to understand how our data are distributed across the dataset, and then use the grouping functionality or the filter properties of a Tablix to print your data correctly. To simplify the layout construction, a suggestion can be to insert a sort of placeholder into each dataitem, like the colums I inserted in the report above, containing the name of the dataitem or of the section, so that filtering the dataset would be straightforward into Report Builder or Word.