Skip to Content

Cross Tab Report

I am trying to create a cross tab report that is based off one table and should be easy to constuct, but I have not used crystal for a while and this report is needed for the business.

The table is running off an SQL database and has the following fields I am trying to use:
TestAccount (true or false)
CreateDate (system create date from SQL)
DeleteFlag (true or false) - I am only interested in those where this is True - these are records that have been transferred and processed - if False the record has not been processed)

The report I need is across the top the month current months in order from Jan to Dec - in order so Jan first, then Feb etc.
Down the report (rows) I need to see:
TestAccount (if this is true display "Actual Company" if false display "Test Account"

I am using the month of the "create date" to give me the break by month across the page (Columns) and want the Actual Company/Test Company down the page as rows.

It would be nice to do the report to do weeks as well - i.e. 1st to 7th, 8th to 14th, 15th to 21st, 22nd to 28th, 29th to 31st. So within each month, it splits by the week period. (I am not bothered on the fact the 1st might be Sunday, Monday, Friday or Saturday)

Any help here would be most useful as I am struggling!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 07 at 08:36 AM

    Hi Chris,

    I assume you need a count of Actual Company and Test Account by Month?

    If yes, try these steps:

    1. Create a formula called 'Row' 'with this code:

    If {TestAccount} = True then "Actual Company" Else "Test Account"

    2. Add the Date field to the Crosstab's column and set it to print for 'Each Month' from the Group Options for the column.

    3. Add the Row formula field to the Crosstab's Row.

    4. Add the Row formula field to the Summarized Fields area and set the summary to 'Count'.

    5. If you only need the report to return rows where DeleteFlag = True, then add this to the Record Selection Formula:

    {DeleteFlag} = True 

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Yes that was fine, however is it possible to increase the space for the row label, as its it is only printing Actual Compan

      Probably as simple tweak to the text Row #1 Name, but not sure which is the one.

      Can it do "weeks" like listed above if I need to or is that more complicated :)

  • Feb 07 at 12:40 PM

    The width of the crosstab's Row Label is increased in the same manner as a normal text box. Just highlight the cell and position the mouse cursor on the 'dot' at the end of the cell and keep dragging to the right.

    If you need the crosstab to also show by week, just add the 'Date' field as the second column and set it to print 'For Each Week'.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Abhilash - thats very helpful and I am starting to get to grips with this.

      If I am doing a cross tab say for 12 months, and say "week end totals" how does Crystal handle the page split when it fills up across the page? I have changed it to Landscape.

      Chris

  • Feb 07 at 02:59 PM

    Using "For Each Week" will give you weeks based on the week starting day you have set - usually either Sunday or Monday. To get the weeks like you defined them in your original post, you would do something like this:

    Switch(
      day({date field}) in [1..7], 1,
      day({date field}) in [8..14], 2,
      day({date field}) in [15..21], 3,
      day({date field}) in [22..28], 4,
      day({date field}) in [29..31], 5
    )

    A Switch statement is like stringing together a bunch of if..then..else if statements. In the example, the statement on the left is the condition you're checking for true/false and the number on the right is the return value if the condition is true.

    Use this formula as the second column in your crosstab to get the week breakdown by month instead of using the date and "For Each Week".

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Dell

      I must be doing something wrong as when I try and use this switch in the code it reports a ] bracket error after the 7]

      I checked my code carefully and also brackets and curly brace but still get the error.

      Chris

  • Feb 11 at 03:34 PM

    That's my fault... Replace the brackets ([ and ]) with parentheses.

    -Dell

    Add comment
    10|10000 characters needed characters exceeded