Cross-Tabs with multiple initial columns

Friday 14 August 2009 @ 11:04 pm

Here is a simple solution to a challenge that came up today from one of my customers.  It has been kicking around in my head for a while but I finally had an excuse to play with it and test it.  I figured some of you might also find it helpful.

If you are like me, you find many uses for cross-tabs in your reports.  So say you a have a cross-tab by Customer (row) and by Month (column).  But you want each Customer row to start with three columns of identifying information rather than just the normal one column.  You could combine the three fields in a formula but then they don’t always align well as columns.  Today I realized that there is a simple way to get true columns, and it works in all versions at least back to v8.5.

So say in the cross-tab I described above I want the row to start with the Customer ID, the Customer Name and the State.  I want all three fields in separate cells or columns.  All I do is add these three fields as row fields in the Cross-tab Expert.  Then I go to the Customize Style tab and highlight the top two fields and check off the box that says “Suppress Subtotal”.  That puts all three fields on one row before the totals start.

Just make sure that each Customer ID has only one Customer Name and one State in the fields you are using.  If there are multiple values for these ‘child’ fields you will get a separate row for each value.  But assuming the Customer ID is tied to the Customer Name and to single primary State, then there will only be one row for the customer and it will have three cells before it starts the cross-tab analysis.








One Response to 'Cross-Tabs with multiple initial columns'

  1. Crystal Quebec - August 29th, 2009 at 8:58 am

    Greetings! Crystal Reports Cross-Tabs are a great validation test tool. Very often I will ‘discover’ miscellaneous elements in a client’s database that would create erroneous results — hence, it’s acts like an auditing tool.

    Thanks
    James
    _____________________________________
    James Keep, B. Admin/Econ, PMP, CRCC
    Crystal Reports® 8.5 Certified Consultant (CRCC)
    Authorized Crystal Engineer (ACE)
    Member of the Project Management Institute® (PMI – Montreal) (PMBOK® Guide)
    Member of Microsoft® Project User Group (MPUG – Montreal)


Leave a Reply

Recrystallize Pro