Avatar of robinh583
robinh583
Flag for United States of America asked on

Percentage in crystal grouping

I have 2 groups.
Group colors red
     Group numbers one
             x  ; 20   x : %?
             y ;  30   y: %?
             z :  20   z : %?

Group colors blue
     Group numbers two
              x  ;40
             y ;  30
             z :  20
So we have 60 x's in total. And the percentage of x's for group color red, under group number one would be (20/60* 100). How do I do this in Crystal? A running total does not have the overall x count until the end. The fact that there are 2 groups s complicating it as crystal would ordinarily let you find the % of a group total. Any ideas?
Crystal ReportsSQL

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
musalman

If you can get a total of X in SQL as a field, you can easily calculate the Percentages.
Mike McCracken

How are you calculating the X total?

mlmcc
James0628

If those numbers are simple counts (or some other summary), you can create a summary for the "numbers" (or "colors") group and check the option to show it as a % of the grand total.

 James
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
robinh583

ASKER
This is the formula im using to calculate x.

In the report header add a formula
WhilePrintingRecords;
Global NumberVar MyTotal := 0;
''

In the section where you display the summary
WhilePrintingRecords;
Global NumberVar MyTotal;
MyTotal := MyTotal + {summaryFormula);
''

To display
WhilePrintingRecords;
Global NumberVar MyTotal;
MyTotal

and the actual data may make more sence ...

Broker 1                  
      Interaction      Total Interactions      % of Total Interactions
Call      3            
E-Mail      1            
Field Trip / Road Show      1            
Research Material      3            
                  
Broker 2                  
      Interaction      Total Interactions      % of Total Interactions
Management Meeting / Visit      2            
Meeting      3            
                  
Broker 3                  
      Interaction      Total Interactions      % of Total Interactions
Call      2            
Mike McCracken

How many X,Y,Z values are there?

You may be able to use a formula like
If {YourField} = 'X' then
    {ValueField}
Else
     0

That field can be summarized to get the total for the report.
Obviously this works well if you have a few values but if you have lots or the values change between runs it won't work.

mlmcc
robinh583

ASKER
Thanks For your response. I'm trying to avoid hardcoding the value, of x as it might vary from db to db. I'm pretty good with crystal and i'm begining to believe that a sql solution may be the way to go. But i'm not srtong in SQL and do not know how to integrate this
-
select Count(e.INTERACTION_SEQ_NO),i.descr
From BT_Event e
inner join interaction i on i.INTERACTION_SEQ_NO = e.interaction_seq_no
Group by i.INTERACTION_SEQ_NO,i.descr

which gives me what i want into the existing sp that's pulling the data at an "ungrouped" field level. It insists every field should be in the group by clause and that just isn't realsitic as there are a lot of fields, 5 joins but  thankfully no other aggregations but a very detailed where clause.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mike McCracken

That was why I added the caveat about it working if the set of values was small and remained the same between runs.

It would be much better to get the information throught the SP.

mlmcc
robinh583

ASKER
Ok, thanks.. Do you know how in SQL? Specifically i need aggregated values but also need to see the details. Is there a  way to do that?
James0628

Your Count query doesn't seem to make sense.  You're apparently trying to get a count of e.INTERACTION_SEQ_NO for each i.descr, but you include i.INTERACTION_SEQ_NO in the GROUP BY, and e.INTERACTION_SEQ_NO and i.INTERACTION_SEQ_NO are the same (because you're using them to JOIN the tables).  So, the way I read that, you're going to get one row with a count of 1 for each INTERACTION_SEQ_NO.  I would think that the GROUP BY should just include i.descr.

 As for how to incorporate that count into your other data, if your detail query includes that descr field, you could use the Count query and JOIN the results with your detail query.  Something like this:

 <first part of your detail query>

INNER JOIN
(select Count(e.INTERACTION_SEQ_NO) AS seq_no_count,i.descr
From BT_Event e
inner join interaction i on i.INTERACTION_SEQ_NO = e.interaction_seq_no
Group by i.descr) C ON
tableA.descr = C.descr

 <rest of your detail query>

 tableA.descr would be the descr field in some table in your detail query.  And then in the SELECT in the detail query you'd include C.seq_no_count, which is the count from your count query.

 This is untested, of course, but I think the basic idea is sound.

 James
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
robinh583

ASKER
The reference to i.interaction is wrong - you right but it is still pulling out the correct data. Anyway both queries work independetly but im still struggling to put them togetehr. From your sample it looks like i end up treating each quesry as 2 tables that i gave an alias and join but thats not working. So as per your example I have (query 1) A inner join (query 2) C on a.integration_seq_no = c.integration_seq_no. Is that right? The error im getting just says that the syntax is wrong near a and c. Thanks for your help and prompt replies.
musalman

You need to add one more Field in the Final Resultset, Let Say Total_X

SELECT A.* , 0 AS TOTAL_X INTO #TEMPA FROM <FINAL_TABLE>

UPDATE #TEMPA SET TOTAL_X = (SELECT SUM(X) FROM <FINAL_TABLE>)

SELECT * FROM #TEMPA
James0628

It's hard to say without seeing the actual queries, but my idea was that you'd incorporate the count query into your detail query, rather than having the detail and count queries as two separate sub-queries that you try to join together.  Maybe that could work, but it's not what I had in mind.  Anyway, I don't know exactly what would be causing that error.  Maybe if you could post the whole thing ("query 1" and "query 2" is a little vague :-).

 James
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
robinh583

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
James0628

You can accept your own last post as the solution, with 0 points, and then award points to any posts/people that you feel helped.

 James
James0628

Argh.  Forgot to say that your post would be the solution, and the other posts would be "assisted solutions".

 James
musalman

did u follow my technique , it was so simple and fast...
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.