Skip to content

Latest commit

 

History

History
57 lines (44 loc) · 4.5 KB

preparing-data-for-display-in-a-tablix-data-region-report-builder-and-ssrs.md

File metadata and controls

57 lines (44 loc) · 4.5 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom
Preparing data for display in a tablix data region in a paginated report
Learn how to customize the paginated report display of data in Report Builder to show either all data retrieved or a subset of the data.
kfollis
kfollis
09/25/2024
reporting-services
report-design
conceptual
updatefrequency5

Preparing data for display in a tablix data region in a paginated report (Report Builder)

[!INCLUDEssrs-appliesto] [!INCLUDE ssrs-appliesto-ssrs-rb] [!INCLUDE ssrs-appliesto-pbi-rb] [!INCLUDE ssrb-applies-to-ssdt-yes]

A tablix data region displays data from a dataset in a paginated report. You can view all the data retrieved for the dataset or you can create filters so that you see only a subset of the data. You can also add conditional expressions to fill in null values or modify the query for a dataset to include columns that define the sort order for an existing column.

Note

[!INCLUDEssRBRDDup]

Working with Nulls and Blanks in Field Values

Data for the field collection in a dataset includes all values retrieved from the data source at run time, including null values and blanks. Normally null values and blanks are indistinguishable. In most cases, this is the desired behavior. For example, Numeric aggregate functions like Sum and Avg ignore null values. For more information, see Aggregate Functions Reference (Report Builder and SSRS).

If you do want to handle null values differently, you can use conditional expressions or custom code to substitute a custom value for the null value. For example, the following expression substitutes the text Null wherever a null value occurs in the field [Size].

=IIF(Fields!Size.Value IS NOTHING,"Null",Fields!Size.Value)

For more information about eliminating nulls in your data before retrieving the data from a [!INCLUDEssNoVersion] data source using [!INCLUDEtsql] queries, see NULL and UNKNOWN (Transact-SQL).

Handling Null Field Names

Testing for null values in an expression is fine as long as the field itself exists in the query result set. From custom code, you can test whether the field itself is present in the collection fields returned from the data source at run time. For more information, see Dataset Fields Collection References (Report Builder and SSRS).

Adding a Sort Order Column

By default, you can alphabetically sort values in a dataset field. To sort in a different order, you can add a new column to your dataset that defines the sort order you want in a data region. For example, to sort on the field [Color] and sort white and black items first, you can add a column [ColorSortOrder], shown in the following query:

SELECT ProductID, p.Name, Color,  
   CASE  
      WHEN p.Color = 'White' THEN 1  
      WHEN p.Color = 'Black' THEN 2  
      WHEN p.Color = 'Blue' THEN 3  
      WHEN p.Color = 'Yellow' THEN 4  
      ELSE 5  
   END As ColorSortOrder  
FROM Production.Product p

To sort a table data region according to this sort order, set the sort expression on the detail group to =Fields!ColorSortOrder.Value. For more information, see Sort Data in a Data Region (Report Builder and SSRS).

Related content