Need to repeat grouping even if no data

[Toya]

Hi, 

I have a report that I group by a unit name parameter, there can be up to 4 units per report. Right now only a unit where there is data is displayed. But I need all units to display header and message "no data for this unit" displayed under the header if there is no data for the table. How do I make that happen? I am not currently using report header section, everything is in the group header section. The data is coming from a sql stored procedure. 

[Dimitar]

Hello Toya,

If I understand correctly, there is a report group based on the "units" and data for the current unit is displayed in a table that is inserted either in the group header section or in the detail section, is that correct?

In such a scenario, since there is no data for the 4th unit, the engine won't create a new instance of the group or details sections, so it won't get to the point where it is rendered the table in one of those sections as well.

Workaround 1

Without making changes to the report structure, one workaround would be to modify the stored procedure to always return data for all 4 units. Even if the data for the 4th unit is only a single row where the values are all Null, it should still be enough to generate the section and for the table to be rendered. In that case, you would be able to use the "no data message" functionality of the table - Configuring the NoDataMessage property - Telerik Reporting.

The hard part would be modifying the SQL procedure. The way I imagine it would be to do 4 separate SELECTs, for each unit, while applying a workaround on each ensuring that at least one row of data is returned - mysql - Help Me with my SQL Query (Need it to always return at least one row.) - Stack Overflow, then joining the tables.

Workaround 2

If that cannot be implemented in your stored procedure, the other workaround would be to modify the report structure. Instead of having a group based on the "units", create 4 static groups(meaning - without grouping expression):

Then put the table in each of those sections, and create a specific filter for each unit on those tables - Filtering the Table and Crosstab items at a Glance - Telerik Reporting. In this scenario, the Report.DataSource can be (none) since our grouping is now static. This approach would also allow you to use the Configuring the NoDataMessage property - Telerik Reporting functionality.

The downside of this approach is that it will be less performant. To improve its performance, you may split the stored procedure or add a parameter to it, so that you can create 4 SqlDataSource components, where each retrieves the data for a specific unit, so that each one of those can be assigned to their corresponding table. In this case, you wouldn't need to apply a filter at the table since the data will be filtered in the query.

I hope that the suggested approaches will help.

Regards,
Dimitar
Progress Telerik

[Toya]

Thank you I was able to modify my stored procedure to return a null record if a unit does not have data. However, when I configure the no data message it doesn't work, only displays the table with one blank row. Am I missing something?

[Dimitar]

You may try to add a filter to the table so that you check if a specific filed(s) is not Null, for example:

This way, the rows where that field is Null will be filtered out, and in the case of the units with a single row with null data, after it is filtered, there will be 0 rows of data for that table and the "No Data Message" functionality should get triggered.

You may have a look at the Filtering the Table and Crosstab items at a Glance - Telerik Reporting article for more information on how to filter tables.   

 

[Toya]

Thank you, that worked as you described. One more question, is there a way to print the table headers and put the no data message below that?

[Dimitar]

Unfortunately, that is not possible at least out of the box,  because the "No Data Message" functionality replaces the table with a textbox containing the "no data" text.

A workaround may be possible but it won't be easy to implement. You could create a separate report that has a couple of textboxes styled to appear as the header of your table, then you may display this report above the table as a SubReport. 

You may then change the visibility of those textbox items based on manually counting if there is data using the approach from the How to display 'No Data Message' or hide an item in case of No data - Telerik Reporting KB article. It will be necessary to assign the same data source component used on the table to the SubReport's DataSource property so that it can check the count of the same data.

If the table data is set via a binding, you may apply the same binding to the SubReport item.