Skip to Content
avatar image
Former Member

How to arrange days in coloumn wise in crosstab

Dear all, I have a report which counts number of days and number of jobs with their percentages.

Now number of days are shown like this

Day --- Jobs --- Percentage

1 --- 23 --- 2%

2 --- 45

3 --- 56

4 --- 78

5 --- 100

....

The above is formatted vertically, what I would like to have is horizontally so the records should be like this

Day1 --- Day2 --- Day3 --- Day4 --- Day 5

23 --- 45 --- 56 --- 78 --- 100

2% --- 35% --- 45% --- 65% --- 100%

Can this be done in this format in Crosstab?

Many thanks

Regards

Jehanzeb

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 06, 2008 at 08:44 PM

    Here is an example of how we accomplish this.

    Created a formula field TESTDAYS= ceiling(DateDiff ("d", {@StartDate}, {[TIME_DATE_FIELD_NAME]))

    We also group our data by days so new Group TESTDAYS and then set Sort Order to specific Order and set order.

    From Crosstab expert add TESTDAYS as a column and then set Sort Order as required.

    Enjoy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 03, 2008 at 11:02 AM

    Hi Jehanzeb,

    I think this would not be possible using a Cross tab as Cross tab shows a summary value common to a row and a column value.

    Instead you can try to insert a MS Excel Worksheet as an OLE Object from Insert menu.

    This way the purpose will be served very easily.

    Hope it would help.

    Regards,

    Aditya Joshi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Jehanzeb

      I could not find any way to use Cross tab and even could not use calculated fields, formulas created in Crystal Reports into the Excel worksheet I inserted as an OLE object.

      However I tried some steps as below, quite a long way but you can try this if there is no other work around.

      1) Created a report 1 inserted fields Day, Jobs, Percentage( Percentage is a formula filed)

      2) These are shown vertically as you mentioned in first case. (we want it horizontal)

      Exported this report to a Excel Worksheet.

      1) In the Excel Worksheet selected all the columns and rows which contains data, and copied it. (we have 3 columns here)

      2) Selected an empty cell, right clicked and selected Paste Special from the contextual menu.

      3) Check the check box called as Transpose click ok and converted 3 columns in to 3 rows respectively.

      4) The Worksheet is saved with only these 3 rows.

      5) A new report is created and inserted this worksheet as an OLE object.

      This object contains data calculated by our formulas in crystal reports and in a transpose of the initial one.

      Regards,

      Aditya Joshi

  • avatar image
    Former Member
    Oct 03, 2008 at 12:36 PM

    Hi..

    I hope this can be done using crosstab.

    --Write a formula to get DAY 1..

    "Day "+{@formula}/databasefield. to get day1.. day2.. etc.

    place this formula in CrossTab columns and place jobs and percentage on summary fields.

    Hope this will give you the required format.

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded