on 10-03-2008 11:03 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
82 | |
10 | |
10 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.