| Author |
Message |
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
  
Topic: Crosstab report
Posted: 17 Jun 2008 at 1:45pm |
Is there any way i can add a row in a crosstab right below the total for the columns. That would be the formula field.
I would use the colum total value in the formula.
Dept X Y z Tot
acc 1 2 0 3
mkt 2 2 3 7
HR 0 1 1 2
sales 1 2 4 7
4 7 8 19
FTE 4/19*.5 7/19*.5 8/19*.5 This is the row that I need
to add below the total of columns.
If this is possible, Can anyone explain How?
Thank you.
|
 IP Logged |
|
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 17 Jun 2008 at 3:00pm |
|
Ugh. Not that I know of.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
Posted: 17 Jun 2008 at 3:09pm |
Thanks Bryan.
So how can I get what I want?
Is there a way using SQL to do this? My values for the columns such as X, Y, Z changes. User does not want to go and change the SQL everytime a values has been added.
Thanks again.
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 17 Jun 2008 at 3:50pm |
|
There is a way to use SQL to have it create the cross-tab data for you and then you just report from that data. I've never done it before, but I've seen it in Google. Also, I know that MS Access has a way of creating cross-tab data using SQL. If you have Access there should be a way to get it to create the data and then look at its SQL code and create your own from there.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
Posted: 18 Jun 2008 at 11:39am |
Thanks Bryan. I do know how to create a crosstab using SQL but the problem with that is the column names should be hard coded in SQL. Everytime a value changes, the code has to be changed. http://www.orafaq.com/wiki/SQL_FAQ SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40,
sum(sal) TOTAL
FROM emp
GROUP BY job
In this scenario, when the dept gets added such as
dept 50, dept 60, dept 70, dept 80 etc. each of them
has to be added to the code manually.
The change is not automatic
Thanks.
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 18 Jun 2008 at 6:25pm |
|
I did some research and it appears that it can't be done in SQL (strange that I thought this was possible). The other option, which is quite complex but you are probably up for it, is to use the code in my book to copy the totals from cross-tab into an array. Then create a formula for the max number of columns you could have (as long as it doesn't expand onto a second page) and put them into a new section and line each one up below the cross-tab where a column would be. If the column doesn't have a value (the array element is zero), then return an empty string. If the array has a value, perform the formula on it and convert it to string and print it on the report.
Does this make any sense? If not I can try to explain it better. It's definitely a 'unique' solution, but I think it will work as long as the cross-tab stays on one page.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
Posted: 19 Jun 2008 at 8:17am |
Bryan,
I am using only crystal reports. How is it possible to copy the totals of the cross tab in an array.
The other thing is the Crosstab expands to next page even though I set the page as Legal.
Let me know if this can be done.
Thank you.
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 19 Jun 2008 at 11:46am |
If it expands to the next page then you can't do this. Sorry.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
Posted: 19 Jun 2008 at 3:35pm |
|
Bryan,
How can I capture the column totals in a variable?
After I click on the column total field, right click and go to the format editor,
select the common tab and select the Display String formula field,
I was able to capture one value by using the following code.
Shared stringvar t := ToText(currentfieldvalue);
I then created a formula. I declared the variable in that formula.
shared stringvar t;
I inserted this value on the section below
I could only view one value.
Any ideas on how all the values can be displayed?
Thanks
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 19 Jun 2008 at 4:43pm |
Did you ever get a copy of my book (I forget...)? I have a whole section dedicated to this topic with sample code. Anyway, you create an array and you put each value into the next element of the array. Every time the formula is called, increment the index counter by one and use that to insert the value into the array. When the cross-tab is finished, the array will have every value you need and then you can use them in other locations on your report. Very cool!
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
|
|
