Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: different formula for each row in crosstab Post Reply Post New Topic
Author Message
aleplgr
Newbie
Newbie


Joined: 09 Dec 2006
Online Status: Offline
Posts: 23
Quote aleplgr Replybullet Topic: different formula for each row in crosstab
    Posted: 09 Dec 2006 at 12:44am

Hi, I was asked to display this data in a tab and I'm wondering if this is possible:
We have regions and clients, the clients classified according different services they take  and depending on this services  I have to totalize in a different way and show those totals in the crosstab.
The crosstab should look like this:


     Reg1  Reg2  Reg3   Totals
S1
S2
S3
S4

For the S1 row I have to count the number of different clients that take service S1
For the S2 row I have to count  the $ amount those clients pay for ( I have to do some calculations with this money so I need to use a formula)
For the S3 row I have to take 2 dates (d1 and d2)  and show the difference d1-d2 (using another formula because it's not just substracting the dates, I have to do some other calculations.

I know I'm showing different kinds of values in each row (and the general total will have no meaning) but I was asked to show them all together in the same table because they are related in some way and they need  to see them together.

Could I do something like this?

 

 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 12 Dec 2006 at 10:23pm
This is entirely possible. As Crystal Reports is generating the cross-tab object, you have access to the data fields that are being process. Thus, you can look at the value of the data field and determine whether it is equal to S1 or S2 or S3, etc. If so, then display another value as the summary field. The key to making this work is that the cross-tab summary field has to be based on a formula field. Within this formula field you can check to see which row you are on and return the appropriate summary function.

For example, you would create a function called "CrossTab Function" and then use the following code for it (of course, use your own custom formulas that the report requires). After building this function, create a cross-tab report and use this function as the summarized field.


if {Customer.Service} = "S1" then
    //Sum the number of customers by giving each a value of 1
    1
else if {Customer.Service} = "S2" then
    //Sum the total order amount
    {Orders.Order Amount}
else
    CurrentDate - {Orders.Order Date};
IP IP Logged
aleplgr
Newbie
Newbie


Joined: 09 Dec 2006
Online Status: Offline
Posts: 23
Quote aleplgr Replybullet Posted: 14 Dec 2006 at 1:33am
Thanks!!!!! Great!!! I'll be working on  it
 
 
IP IP Logged
jackjack
Newbie
Newbie


Joined: 05 Jan 2009
Location: Philippines
Online Status: Offline
Posts: 2
Quote jackjack Replybullet Posted: 05 Jan 2009 at 4:21am
How about this

CustomerID Lastname TotalOrder
001              Denver     50        
002              Pitt           41
003              Wallace    42

The total order value is based on the count of line items for that customer in another line items table (something like SELECT COUNT("CustomerID") FROM SalesOrderLineItems).. Please help..
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.030 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.