Solved

formula using crosstab fields

Posted on 2004-09-07
10
665 Views
Last Modified: 2008-03-10
i have 2 crosstabs, both are products x months.

so... i want to make a new field that calculates the sum between the total of the first column crosstab #1 and the first column crosttab #2.

More explained, i have these:

Crosstab#1
Product January Feb. March
x              1         2       3
y              5         6       7
total         6          8      10

Crosstab#2
Product January Feb. March
a              5         5        5
b              6          5       4
Total       11        10       9

And I want these:
Total2      17         18     19

THe month columns today are only 3, but next month could be 4, or 6, so i need the total2 field to present the 12 months if it´s necesary and only 3 or 4 if that´s the case.

Please help. i´m just starting to use crystal.
Thanks in advance.
0
Comment
Question by:perfectshark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 10

Assisted Solution

by:ebolek
ebolek earned 225 total points
ID: 12001701
I know that you want crosstabs but you can achieve this by grouping too

If you have the detail field from the date the sales occure, you can group by that field than by the product id,

You will have two groups.In the desgin view you will roight click the first group and select group by each month. This will group the products by each month.

You will put sums for each group.  Insert , summary,
On chnage of product id, summary
Copy this and paste it to all group footers

These wil lgive you the total of product x for month y
And then you will put a grandtootal to the report footer to find out the total sales for all products for all months

Regards
Emre

0
 
LVL 42

Accepted Solution

by:
frodoman earned 275 total points
ID: 12005350
Can you explain the difference between the two crosstabs?  Normally you'd want to create a single crosstab with two entries in the "row" area and then you'd have what you want in a single crosstab like this:

Product January Feb. March
Crosstab#1
   x              1         2       3
   y              5         6       7
total         6          8      10
Crosstab#2
   a              5         5        5
   b              6          5       4
total       11        10       9
Grand Tota2      17         18     19

The key is putting whatever field defines "crosstab #1" and "crosstab #2" into your row area in the crosstab setup.

HTH

frodoman
0
 
LVL 1

Author Comment

by:perfectshark
ID: 12009977
the problem is that i don´t always want to add in the grand total, the 2 crosstabs could be 1 for sells and 1 for costs, then i want to substract the two values, then i want to multiply this total for other and get other information.

that´s why i do it in parts.

Thanks again.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:frodoman
ID: 12010075
Okay, in that case I have to say that I don't believe what you want isn't directly possible.

You could possibly figure out a way to use formulas to accumulate your totals (although I'm not sure that you could do this, I won't rule it out) but you would need one formula per column which isn't going to work if you have a variable number of columns.  Even techniques such as creating 12 formulas and suppressing the ones you don't need won't work because a crosstab doesn't give you the information you'd need for conditional suppression.

The only way I can think of to achieve the result you want is to create a seperate report that will show only the bottom line totals that you want and then embed this as a subreport in your main report (probably in the report footer).  I don't know if this subreport would be a crosstab or just a normal report - depends on your data structure.

This is an imperfect solution and will likely take tons of effort to make it work correctly but AFAIK that's you're only option.

frodoman
0
 
LVL 1

Author Comment

by:perfectshark
ID: 12010357
ok frodoman, i'll try that and tell you if it works,

Two more questions and i close this thing:

i´m not quite sure if it´s possible (or how it´s possible) to change the totals of the crosstab and put another operacion(add, substract, multiply, divide)

2nd:

using crosstab. in one of my crosstab i have this info.

Product January Feb. March
x              1         2       3
y              5         6       7
total         6          8      10

can i make a condition that make all the values in row of product "y" show "0" instead of their actual values?}

so i get .
Product January Feb. March
x               1         2       3
y               0         0       0
total          1         2       3

Thanks.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12199776
I believe this comment answered the question:

Comment from frodoman
Date: 09/08/2004 03:30PM EDT


Last posting from perfectshark was additional questions that should be posted separately.

frodoman
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12199979
This comment also answers the question. I would recommend split between Frodoman/ebolek
Comment from ebolek
Date: 09/07/2004 03:49PM PDT

Regards
Emre
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12200046
Emre,

I disagree with you on this one.  The original question stated: "THe month columns today are only 3, but next month could be 4, or 6, so i need the total2 field to present the 12 months if it´s necesary and only 3 or 4 if that´s the case.
"

Because of the variable column requirement he needs to use a crosstab.  Your solution would give him totals wanted but would invalidate his stated requirements.

frodoman
0
 
LVL 1

Author Comment

by:perfectshark
ID: 12200485
Ok... I found a lot of help with your comments, but none of you give me a true answer to the problem.

The key, was... using a variable to define the sum field in a third crosstab, and make a condition that makes (x and y) positive values and (a and b) negatives values.

Then just needed to hide all the values in the third crosstab except the grand total. And that was it.

As a note, this was the key to make a report i needed to present, i use like 5 variables and 11 crosstabs... i know it should be a better way, but still don´t know it.

Thanks to everyone, i´m gonna split the points.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question