| Author |
Message |
Me_Evans
Newbie
Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
  
Topic: Cross Tab
Posted: 11 May 2010 at 10:47am |
I am running Crystal Reports XI.
I have several cross tabs running in the same report and this is working great. I want to be able to do the following:
Total of CrossTab1 - Total of CrossTab2 = New Amount
Is this possible?
Michelle
|
|
Michelle
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 May 2010 at 11:06am |
CT totals are just summarizations of grouped data and can be recreated in other ways.
It really depends on what is being totalled and at what report level and how you need it to appear in the report.
You can create a formula field that replicates this as something like
SUM(field1)-SUM(field2)
|
| IP Logged |
|
Me_Evans
Newbie
Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 11 May 2010 at 11:14am |
In Cross tab 1 - I am using the group option for categories 1, 2, 3, 4 and "amount" field is being summed.
In Cross tab 2 - I am using the group option for categories 3, 5, 6, 7 and "adjust" field is being summed.
I have these in separate cross tabs because of category "3". I am not able to get category 3 to show up in both groupings.
I need to take cross tab 1 total amount and subtract cross tab 2 adjust or get category 3 to work in both groupings.
Either solutions would be great.
Thanks
|
|
Michelle
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 May 2010 at 11:25am |
So you really need the SUM of 1,2,4 - the SUM 5,6,7 (the 3 cancels it self out) correct?
You can create a formul to say
if the field is in 1,2,4 then use amount field else
if the field is in 5,6,7 then use adjust field *(-1)
else 0
then just Sum this formula field
|
| IP Logged |
|
Me_Evans
Newbie
Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 12 May 2010 at 1:53am |
Field 3 does not cancel its self. The value for 3 in amount is not the same as the value for 3 in adjust. Same category, but different values as I am pulling the amount column and adjustment column at the same time.
Michelle
|
|
Michelle
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 May 2010 at 4:27am |
|
Can you show some row level data nad how you need it calculated?
|
| IP Logged |
|
Me_Evans
Newbie
Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 12 May 2010 at 4:39am |
Ledger.Amount
Row 1 = 10
Row 2 = 10
Row 3 = 20
Row 4 = 25
Total = 65
Ledger.Adjust
Row 1 = 7
Row 2 = 3
Row 3 = 5
Row 4 = 10
Total = 25
Ledger.Amount - Ledger.Adjust
I can get the above formula to work, but Row 3 will not appear in both groupings. So, I have separated these into 2 different cross tabs. I either need row 3 to calculate in BOTH groupings for subtract cross tab 1 from cross tab 2.
Thanks
Michelle
|
|
Michelle
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 May 2010 at 4:51am |
I do not understnd your grouping nor why row 3 is in both or why row 3 has 2 values in it?
What is the raw row level data that we are dealing with.
You can likely still do what I was suggesting earlier with a change to deal with '3' being in both but I just do not understand your data yet.
|
| IP Logged |
|
Me_Evans
Newbie
Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 12 May 2010 at 5:23am |
I am trying to group categories with "cost" and "adjustments" in the same cross tab. (No problem). But, most of the categories to not have both cost and adjustments. Category 3 happens to have both.
The cross tab will not calculate the second group with the value of category 3 in it.
|
|
Michelle
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 May 2010 at 8:47am |
I still do not understand your data. A category is a very generic term and does not explain how it is impacting row level data. Please remember I have neevr seen your data set ever so I am working entirely from this dialogue.
If I understand at least the basics of your data set, you will not be able to create one crosstab that can show the categories in any detail in both a cost and adjustment grouping unless you were to run a command that pulls the data twice fromt he same table, once with your cost and once with your adjustments and unions the the results together (basically doubling the "category 3" rows).
However you should still be able to show a single result of the 2 but as to how I cannot say exactly without knowing the data. here is a guess.
Make an if-then fomula field to do:
if the field is in category 1,2,4 then use amount field else
if the field is in category 5,6,7 then use adjust field *(-1) else
if the field is in category 3 then use amount field-cost field
else 0
Now SUM this formula field for your 'difference'
|
| IP Logged |
|
|
|
