CrossTab Report - Conditional Formatting and nulls

6 posts, 0 answers
  1. Donald McLean
    Donald McLean avatar
    20 posts
    Member since:
    May 2010

    Posted 05 Dec 2012 Link to this post

    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.
  2. IvanY
    Admin
    IvanY avatar
    417 posts

    Posted 11 Dec 2012 Link to this post

    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

    HAPPY WITH TELERIK REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  3. DevCraft banner
  4. Donald McLean
    Donald McLean avatar
    20 posts
    Member since:
    May 2010

    Posted 19 Dec 2012 Link to this post

    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.
  5. IvanY
    Admin
    IvanY avatar
    417 posts

    Posted 20 Dec 2012 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  6. Donald McLean
    Donald McLean avatar
    20 posts
    Member since:
    May 2010

    Posted 21 Dec 2012 Link to this post

    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 });

  7. IvanY
    Admin
    IvanY avatar
    417 posts

    Posted 27 Dec 2012 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

Back to Top
DevCraft banner