Technical Questions: Group summary/formula problem

Group summary/formula problem

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=172
Printed Date: 03 May 2024 at 12:55pm

Topic: Group summary/formula problem

Posted By: Valmont
Subject: Group summary/formula problem
Date Posted: 29 Jan 2007 at 11:24am

Hi reader,

I'm very new to reporting (and using CRXI) yet I'm immediatly thrown in the middle of a crucial reporting-stage. We have NO experts. So I am alone... Cry

I'll show you the screeny of my current report and then I'll ask my questions.

My questions in order of importance (using crystal formula notation):
1) (**crucial**) : A customer (like Anthony) has 4 or more "test" in the "Action" field. I want to count the number of customers with 4 (or more) "call".
This needs to be a formula (or something) because I'll add that result in the report-footer.

2) (very high, not crucial) : Do I have to group it like this? I just need the formula-oject (or running total field!) to insert it in the report-footer. I may want to choose to setup the report in a different way.
Yet I do need the summary as mentioned in "1".

3) (moderate) : What if I want to summarize per user how many "call" he/she got?
Like:

NAME | CALLs | TESTs
Antho. | 1 | 4
Carla. | 3 | 2

Etc.
Like I mentioned, this is a crucial state of the reporting process. Once I get through this, I can handle the rest of the report most likely!

Replies:

Posted By: BrianBischof
Date Posted: 29 Jan 2007 at 12:28pm

1) Can you use a global variable and only increment it when the record has "call" in it?

2) You can hide whatever sections you don't want to view.

3) Use a cross-tab object. I have a chapter on it here on the website.

-------------
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>

Posted By: Valmont
Date Posted: 29 Jan 2007 at 10:26pm

[quote] 1) Can you use a global variable and only increment it when the record has "call" in it?[/qoute]

Not skillwise. That's why I am here.
For the sake of consistancy with the report, I need a formula and then drag the formula to the reportfooter. Question is that I only can make very basic formula's.

Thank you for the reply by the way. This project important.

Posted By: BrianBischof
Date Posted: 30 Jan 2007 at 9:04am

Here you go...


Global NumberVar RTotal
If {table.action field} = "call" Then
   RTotal = RTotal + 1;

-------------
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>

Posted By: Valmont
Date Posted: 30 Jan 2007 at 4:07pm

Thank you for your time but that is not what I meant. I could have been clearer.

Say a database has a record of 2 tables: Customer, Action.
Say we have 10 customers. Say we have 2 types of actions (AOne and ATwo).

Say customer "John" can have up to 50 actions. I am only interested if John has at least 4 times action "AOne". If he has then "CustomerActionOne_4Count" = 1.

Mary has at least 4 times action "AOne" as well!
So my variable becomes:
"CustomerActionOne_4Count" = 2.

That's because 2 customers have at least 4 x performed a certain action.
I don't care who they are, all I want to know how many users have that amount of that specific action.

Now the tricks is that this has to be a formula that does not depend on what is to be seen on the report: I may not want to display any field of any table in the report. I just want to put formulas in the report. And this very formula is the most important one.

So the formula gets complex alright. Once again I totally used up my spare time. (1.05 am here). I'll try until I drop down from tireness but without help I'm lost.

Posted By: hilfy
Date Posted: 31 Jan 2007 at 12:14pm

Alright, I think I understand what you're trying to do. You want a count of all customers who have 4 or more "Call" actions.

I think you can do this with a couple of formulas (I haven't tested this...)

 mailto:%7b@CustomerCallCount - {@CustomerCallCount }
Global NumberVar CustomerCallCount;
if (PreviousIsNull({table.customer id})) or ({table.customer id} <> previous({table.customerID} then 
  CustomerCallCount := 0;  //reset to 0 for each customer
If {table.action field} = 'Call' then 
  CustomerCallCount := CustomerCallCount + 1;
CustomerCallCount

 mailto:%7b@Has4Calls - {@Has4Calls }
if  mailto:%7b@CustomerCallCount - {@CustomerCallCount } >= 4 then 1 else 0

On your report, in the footer where you want to display this information, insert a summary to SUM mailto:%7b@Has4Calls - {@Has4Calls } - Go to the Insert menu, select Summary, select your field and then "Sum". If this is a total for the report, click on OK. If it is a total for a group, select the group and then click on OK.

In order for this to work, your group or sort MUST have the customer id at a top level so that all records for a given customer are read consecutively before going on to the next customer.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Valmont
Date Posted: 01 Feb 2007 at 12:49pm

Jolly awesome! This looks promising!

Thank you. Deadline tomorrow (my time, it's evening here now).
It's funny to see how a certain skillset with Crystal Reports has direct influence on attracting more customers sometimes. This is one of these times. Tomorrow afternoon the potential customers comes to see if we can report decently!

That's the story behind this CR project :)

Once again, thank you all for your help.