Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't be afraid to say "I made a mistake"
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

13
May

How Do I Conditionally Format Cross-tabs in Crystal Reports?

A number of clients wanted to apply dynamic conditional formatting to their cross tab sales reports.

Crystal Reports has a couple of cross-tab function, which came in from version 2008, which allows users to apply conditional formatting.


To Apply Dynamic Conditional Formatting to Cross-tabs:

  1. Create a New Blank Report
  2. Select the Xtreme.mdb database
  3. Add the Employees and Orders tables to the report
  4. Use a text object to add the report Header "Employee Orders Xtab"
  5. Add a Cross tab object to the Report Header section
  6. Go to the Insert Menu and Cross tab
  7. Right click on the Cross tab object and select the Cross-Tab Expert menu option
  8. Add the following fields to the sections of the cross tab:

    • Columns: Employee.Employee ID
    • Rows: Orders.Order Date
    • Summarised Fields: Orders.Order Amount
                                             Orders.Order ID

  9. Select the Employee.Employee ID field in the Columns section
  10. Press the Group Options button
  11. Select the Options Tab
  12. Check the Customise Group Name Fields
  13. Select Use Formula as Group Name
  14. Press the x+2 button
  15. Enter the formula:

    {Employee.First Name} &" "& {Employee.Last Name}

  16. Press the Save and Close button
  17. Press the OK button
  18. Select the Sum of Orders.Order ID field, in the Summarised Fields section
  19. Press the Change Summary button
  20. In the Edit Summary dialog box, select Count from the Calculate this summary drop down
  21. Press the OK button
  22. Click on the Customize Style Tab
  23. Set the following formatting options for the cross tab:

    • Select Repeat Row Labels
    • Select Keep Columns Together
    • Deselect Column Totals on Top
    • Deselect Row Totals on Left

  24. Press the OK button
  25. On the Field Explorer, right click on Parameter Fields
  26. Select the New option
  27. In the Create New Parameter dialog box:

    • Name: Sales Qty Target
    • Type: Static
    • List of Values: Static
    • Prompt Text: Enter Sales Qty Target
    • Default Value: 30

  28. Press the OK button
  29. Create a new parameter:
  30. In the Create New Parameter dialog box:

    • Name: Find Employee
    • Type: Dynamic
    • Value: Employee ID
    • Description Last Name
    • Parameters: Click -> ?Find Employee
    • Prompt Text: Select the required Employee
    • Allow Multiple Values: True

  31. Press the OK button
  32. Right click on the Summary cell, Count of Orders.Order ID
  33. Select the Format Field menu option:
  34. In the Format Editor dialog box, click on the Border tab
  35. Press the conditional format button for the Color -  Background option:
  36. In the Formula Workshop, enter the following formula:

    37. IF CurrentFieldValue > {?Sales Qty Target} then CrLime Else CrNoColor

  37. Press the Save and Close button
  38. Right click on the Summary cell Sum of Orders.Order Amount
  39. Select the Format Field menu option
  40. In the Format Editor dialog box, click on the Font tab
  41. Press the conditional format (x+2) button for the Color
  42. In the Formula Workshop, enter the following formula:

    if GridRowColumnValue ("Employee.Employee ID") ={?Find Employee} then
    crSilver

  43. Press the Save and Close button
  44. Run the Report


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

45 + 41 =