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
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
EMail 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.
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 subqueries 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 :).
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.