How to arrange days in coloumn wise in crosstab

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Aditya thanks for your quick response however I have a question.

How am I going to integrate crystal formula for working days and then the jobs which are shown into excel?

Would by formula work in excel workbook? As far as I can see OLE object brings excel workbook across to Crystal report however does not allow any integration of formula unless I am wrong.

Many thanks

Regards

Jehanzeb

[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