CrossTab Report - Conditional Formatting and nulls

[Donald McLean]

I have a crosstab report which shows stats for permits submitted over a 6 year period. I have them broken down by several different parameters. I have the latest year column set to be shaded a dark grey and the 2nd, 4th, and 6th years shaded a light grey. I sort them into the years based on a date permit was approved. My problem is that if here is no permits that match one of the categories it doesn't shade that box. It doesn't make the report look good with breaks in the shading. I have it filling 0's for the null cells by checking for nulls but it still doesn't shade the cells.

Any help would be greatly appreciated.

[IvanY]

Hi Donald,

It is not very clear for us what your current setup is so we will need some more information. Therefore we will need a more detailed explanation and it will be best if you can provide us with a step by step guide that describes how to reproduce the undesired effect.

We have tested at our end filling a Crosstab and a table with different data but we were not able to reproduce such an issue. In case you are using Conditional Formatting and the formatting rule is based on the field it will respect the actual field from the database and not the replaced field in the textbox (using IsNull) and this behaviour is by design.

Regards,
IvanY
the Telerik team

[Donald McLean]

Here is a snapshot of my report I did some searching and you have a demo similar to mine, using orders instead of permits. It was used in the "Working with Tables Lists and Cross Tabs" webinar. I did ask Robert Shoemate the presenter of the webinar about how to show the missing years that don't show up. The report is pretty close to what the client wants, only a few things I would like to do make it perfect. I would fix the shading for the null values cells, also get the report to show other years with either blanks or zeros filled in. If I am able to show all years I would like to hide the Year rows for last three crosstab reports and move them up. So it looks like the rows are part of the same table.

[IvanY]

Hello Donald,

As mentioned in the previous post you can check the Conditional Formatting rules that make the coloring of your table since this is the most probable reason for the missing background color in these cells. 

Also what do you mean by "show the missing years that don't show up" - are these years in the database or are they missing or possibly they are in the database but there are no records for them?

Please have in mind that a detailed explanation of your setup and a step by step guide will help us resolve your issue faster and advise you accordingly.

Regards,
IvanY
the Telerik team

[Donald McLean]

If you look at your cross tab demo showing the orders broken down into years and quarters based on the order date. I noticed that for a for one the years it is not showing data for Qtr 1 and Qtr 2 because there is no orders whose dates that fall into those quarters. Is it possible for those Qtrs to see zeros. In my report there is no information for 2007 I would like it to show up with zeros in the columns or  blanks in the columns that should be blanks.similarly with the 2nd through 4th crosstab reports there are few years where is no data for those years that match the criteria it would be nice if were possible 2 show the years and show counts of zero or blanks if text in the case of Last Entered Permit.

In terms of shading I am doing it dynamically in code however I tried applying them directly to the report and it does the same thing. I have a starting year passed into the report and I use that year vs. ApprovalGrantedDate.Year to group them then shading if the Year = starting year, or if year = starting year - 1, -3 or -5 using seperate formating rules for each one. I am including and excerpt of my formatting code.

// Rule 1 - formating cell for ApprovalGrantedDate.Year = year

Telerik.Reporting.Drawing.FormattingRule formattingRule1 = new Telerik.Reporting.Drawing.FormattingRule();

formattingRule1.Style.BackgroundColor = System.Drawing.ColorTranslator.FromHtml("#BFBFBF");

formattingRule1.Filters.AddRange(new Telerik.Reporting.Filter[]

{

    new Telerik.Reporting.Filter("= Fields.ApprovalGrantedDate.Year", Telerik.Reporting.FilterOperator.Equal, "= " + startingYear)

});

// Rule 2 - formating cell for ApprovalGrantedDate.Year = year - 1

Telerik.Reporting.Drawing.FormattingRule formattingRule2 = new Telerik.Reporting.Drawing.FormattingRule();

formattingRule2.Style.BackgroundColor = System.Drawing.ColorTranslator.FromHtml("#F2F2F2");

formattingRule2.Filters.AddRange(new Telerik.Reporting.Filter[]

{

    new Telerik.Reporting.Filter("= Fields.ApprovalGrantedDate.Year", Telerik.Reporting.FilterOperator.Equal, "= " + startingYear + " - 1")

});

// Rule 3 - formating cell ApprovalGrantedDate.Year = year - 3

Telerik.Reporting.Drawing.FormattingRule formattingRule3 = new Telerik.Reporting.Drawing.FormattingRule();

formattingRule3.Style.BackgroundColor = System.Drawing.ColorTranslator.FromHtml("#F2F2F2");

formattingRule3.Filters.AddRange(new Telerik.Reporting.Filter[]

{

    new Telerik.Reporting.Filter("=Fields.ApprovalGrantedDate.Year", Telerik.Reporting.FilterOperator.Equal, "= " + startingYear + " - 3")

});

// Rule 4 - formating cell for ApprovalGrantedDate.Year = year - 5

Telerik.Reporting.Drawing.FormattingRule formattingRule4 = new Telerik.Reporting.Drawing.FormattingRule();

formattingRule4.Style.BackgroundColor = System.Drawing.ColorTranslator.FromHtml("#F2F2F2");

formattingRule4.Filters.AddRange(new Telerik.Reporting.Filter[]

{

    new Telerik.Reporting.Filter("=Fields.ApprovalGrantedDate.Year", Telerik.Reporting.FilterOperator.Equal, "= " + startingYear + " - 5")

});
//1st row - Year row
txtYear.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] { formattingRule1, formattingRule2, formattingRule3, formattingRule4 });

//2nd row - Column Title row
textBox03.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] { formattingRule1, formattingRule2, formattingRule3, formattingRule4 });
textBox04.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] { formattingRule1, formattingRule2, formattingRule3, formattingRule4 });

[IvanY]

Hello Donald,

Regarding your first question - this depends entirely on your database. For example, if there are no records for these years, nothing will show up. If there are records for these years but there are no sales for that period and you are using inner join to retrieve your data from the tables the years will not show up either.

What you can do in the first case is to add the years to the database and use full, left or right outer join (depending on your database and query) or to use the ObjectDataSource for example and add the values manually before sending them to your table. If you already have the records (the second case) you can simply use full, left or right outer join. However be aware that empty sections may appear when using right, left and full join.

Regarding your second issue - as stated in the previous ticket your formatting might be based on a field that returns null. For example have a look at the following conditional formatting:

formattingRule1.Filters.AddRange(new Telerik.Reporting.Filter[] {
new Telerik.Reporting.Filter("= Fields.AddressLine2", Telerik.Reporting.FilterOperator.Equal, "=\"Unit E\"")});
formattingRule1.Style.BackgroundColor = System.Drawing.Color.BlueViolet;
formattingRule2.Filters.AddRange(new Telerik.Reporting.Filter[] {
new Telerik.Reporting.Filter("= Fields.AddressLine2", Telerik.Reporting.FilterOperator.NotEqual, "=\"Unit E\"")});
formattingRule2.Style.BackgroundColor = System.Drawing.Color.Gold;

This will color in blue all textboxes that are equal to "Unit E" and in gold all of the rest. However if the field is null it will not be colored - it will be left white. Therefore although your code is correct you have to cover the cases when the field is null.

Greetings,
IvanY
the Telerik team