This is a migrated thread and some comments may be shown as answers.

Cross Tab Reports - Hide Rows where groupname is null

3 Answers 338 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Andrew
Top achievements
Rank 1
Andrew asked on 22 Aug 2011, 07:50 AM
I am running a report off a query that is using a LEFT OUTER JOIN to ensure that dates are included even if there is no data for the dates. (See the bottom of this post for the query).


I am displaying the data in a crosstab report, using the dates as columns. The ideal is that if there is no data for the date then each value in the column displays blank.

This is working. The problem is, if the crosstab has one or more blank columns, then a blank row with no header appears as well..

The attached image shows two crosstabs. The range "11 August to 17 August" has no empty dates, so subsequently there are no empty rows.
But the range "18 August to 21 August" happens to have empty dates. The columns are correctly empty, but there is one group row that has no header.

 I can understand why this is so, but how can I make the blank row go away? My guess is that there's some filtering condition along the lines of "if VideoGroupName IS NOT NULL", but I can't seem to find a way to filter away nullness.




select
    date_range_all.Date,
    values_in_range.VideoGroupName,
    values_in_range.VideoGroupKey,
    values_in_range.NumberOfPlayers,
    values_in_range.AveragePlaysPerHour
from
(
   select date_dimension.date
       from Date_Dimension
           where date_dimension.WeekStartingThuSeqNo IN
          (
                  select distinct Date_Dimension.WeekStartingThuSeqNo
                  from Date_Dimension
                     where Date_Dimension.Date >= @startDate
                    and Date_Dimension.Date < @endDateExclusive
           )
) date_range_all
left outer join
(
   -- More query here
) values_in_range
on date_range_all.Date = values_in_range.Date

3 Answers, 1 is accepted

Sort by
0
Peter
Telerik team
answered on 22 Aug 2011, 04:36 PM
Hi Andrew,

The crosstab is data driven and you are right that you have to use a filter. In your case our suggestion is to use the group explorer to locate the respective crosstab group and add the necessarily filter as the following one:

 Expression Operator  Value 
 =IIF(Fields.Date is not null,true,false)  =  =true

Kind regards,
Peter
the Telerik team

Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's DevProConnections Awards. We are competing in mind-blowing 20 categories and every vote counts! VOTE for Telerik NOW >>

0
Neepa
Top achievements
Rank 1
answered on 21 Aug 2013, 01:54 PM
I've a similar situation where I need to hide/make visible = false if the column has no header - how can I achieve this.  I was able to locate the group filter but how would you make column disappear if there is no header (or null as a header for the column)?   
0
Neepa
Top achievements
Rank 1
answered on 21 Aug 2013, 02:00 PM
Never mind got it - I was able to filter out the column in group properties
Tags
General Discussions
Asked by
Andrew
Top achievements
Rank 1
Answers by
Peter
Telerik team
Neepa
Top achievements
Rank 1
Share this question
or