Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: one-to-many graph problem Post Reply Post New Topic
Author Message
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Topic: one-to-many graph problem
     Posted: 26 May 2009 at 6:54am
Hi,
 
I am haviing a crystal report problem. The problem relates to a one-to-many relationship involving two tables.
 
I have a similar setup to the following:
 
I have 2 database tables: table1 & table2
 
table1 consists of 3 fields:
{id#}, {yes/no},{up/down}
 
table2 consists of 2 fields:
{id#}, {state}
 
the {state} field can contain any of the following values: 'open', 'closed', 'final', 'complete'.
 
The problem is that there exist many states {state} to one id#.
 
I have a bar graph in my report that displays a bar for the count of the{yes/no} field and a bar for the count of the {up/down} field. I want also a bar for the count of when the {state} field = 'final'.
 
What happens when I write a formula for the {state} field in the second table and place it in the 'show values' section of the chart expert, is the count displayed in each bar on the graph increases significantly. I am assuming this is because there are multiple records in table 2 for each record in table 1.
 
What I want to happen is that the graph displays bar 1 and bar 2 correctly and then when I put in the formula for the third bar, it simply displays that bar with the count for when the {state} field = 'final'. I dont want it to affect the whole garph.
 
Has anyone got any suggestions on how I can do this?
 
I have tried messing around with the distinct records setting in crystal and also by writing an sql query specifying DISTINCT keyword instead of using the select expert, but to no avail.
 
Please please can someone help me with this issue.
 
Thank you.
 
J
J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 May 2009 at 7:49am
You will need to make sure your summary is set to a distinct count of the ID# for chart 1 and 2.
For # 3 create a view of that table to only include "complete" records, pull in that view instead of the table and left join it to your other table. Do a dsitinct count of that ID# from the view.
Make sense?
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 26 May 2009 at 8:00am

Firstly, thank you for replying. There is alot of info there and it contains doing things that i havn't done before such as creating a view.

You mentioned in your reply about chart 1 and 2. I only have 1 chart. That chart has 2 bars on it. the first bar for one set of data in table 1. The second bar for another set of data in table 1. However, I want a third bar to reflect a field in table2. However for every records in table1, there is many records with the same ID# in table2.
 
So I want the third bar to display the number of times {field3} = 'final' without affecting the other 2 bars on the graph.
 
 
I hope I explained it better this time. I was just unsure wether you understood my dogged explanation the first time.
 
If you did, would you perhaps be so kind as to explain, your steps in your reply a bit deeper as im a relative newbie to CR.
 
Thanks DBlank. 
J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 May 2009 at 8:45am
Sorry, I should have said bar rather than chart. Although as I am looking this over I don't think this will work. I was thinking of stacked bar charts and this would not work for that any way...
I think the solution here might be using some running totals but I need to understand exactly what you are trying to count per bar. Using Running Totals would also mean that your chart would have to go into the report footer instead of the header (or you could create a subreport that could end up in the header if needed).
What exactly do you want to count for each bar. Your description makes me think these are going to be the same number.
for Bar #1 do you want a count of each record regardless if the answer is Yes or No and are there any NULLS that you want to exclude?
For Bar #2 , same question.
 
What are you using for the "On CHange of" in the chart righ now?
 
 


Edited by DBlank - 26 May 2009 at 8:46am
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 26 May 2009 at 9:28am
Hi,
 
bar#1 will have a number on it. this number will represent the amount of yes values in the {yes/no} field. I created a formula that went something like this:
 
if {yes/no} = 'yes' then 1 else 0, placed this formula in the 'show values' section of the chart expert and got the sum of it in the chart expert.
 
bar #2 will have a number on it too. this number will represent the amount of up values in the {up/down} field. I created a formula that went something like this: 
 
if {up/down} = 'up' then 1 else 0, placed this formula in the 'show values' section of the chart expert and got the sum of it in the chart expert.
 
There is another field in my database called {prod} and this can have one of two values: prod1 or prod2.  This is the field that I placed in the 'on change of' section of the chart expert.
 
So at this stage I have a graph. on the y-axis is a count, on the x-axis is prod1 and prod2. There is 2 bars for prod1 and 2bars for prod2.
 
What I now want to do is to create a third bar which displays a count of the amount of times the field {state} = final.
 
The problem I am facing here is that the field {state} is in table2 and there are multiple state entries for each id# in table1.
 
i.e.
 
id#       state
1            closed
1            closed
1            final
1            open
2            open
2            final
 
etc...
 
So, when I create a formula called sumuptotal which contains the code:
 
if ({table2.state} = 'total') then 1 else 0
 
and place this formula in the 'show values' section of the chart expert, what happens is on all the other bars, their counts increase dramatically. Im assuming this is because its a one-to-many relationship going on here.
 
 
(I hope I explained that ok. I know it may be hard to understand from just reading text.) :) 
 
J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 May 2009 at 9:52am

Thanks for the detailed explanation. Yes you are correct that the one to many exploded your count using the formulas. You cannot use the SUm of your formulas because of this (no way to use a distinct count on that which is what you would need). I think the easiest solution is to use 3 Running totals and display your 3 columns. I think htis will work for your On Change of haveing 2 vuales. I can't test it out so you will have to validate that by going through these steps...

The Running totals can use a condition to get your numbers and then you can display these in the chart. Running Totals do not work in headers so you will need ot display the chart in your report footer. If you must have it in the header create a subreport that uses the same data and select criteria, add your running totals in the subreport and create the chart in it and then display the subreport in your main report header. This will impact performance a little because you have to read the tables twice (once for the main report and once for the subreport).
I would add it to your current report footer first to validate the process works before adding in the subreport if needed.
Here is how to create your 3 Running Totals (RTs):
Right click on the RT field and select NEW.
Enter the name (this will appear in your chart so use something a viewer can understand). I will call it "Yes" for this example.
Field to Summarize=ID from table 1
TYpe of Summary=Distinct Count
Evaluate=Use a formula. Change this formula to match your table.field names
{table1.yes/no_field}="Yes"
Reset=Never
Save and Close
RT #2
Name it "Up" for this example.
Field to Summarize=ID from table 1
TYpe of Summary=Distinct Count
Evaluate=Use a formula. Change this formula to match your table.field names
{table1.up/down_field}="Up"
Reset=Never
Save and Close
RT #3
Call it "State" for this example.
Field to Summarize=ID from table 1 (you may have to change it to table2 ID field but I think table 1 should work OK.)
TYpe of Summary=Distinct Count
Evaluate=Use a formula. Change this formula to match your table.field names
{table2.state}="Final"
Reset=Never
Save and Close
 
In your chart keep the same On change of.
IN "Show value(s)" add all 3 of your RT's in the order you want them to appear (e.g. #Yes, #Up, #Final). They should now be listed as Summary fields in your Available Fields.
Place the chart it on the Report footer.
Check it for accuracy.
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 28 May 2009 at 4:22am

Thanks for that suggested solution DBlank. From just reading it, it appears like it should work no problem. 

Im sorry for taking so long to reply, I have been unable to get on the internet due to complications my way. I am currently in the process of trying out your solution so I'll get back to ya when I'm done to let ya know how I got on.
 
Thanks again for your help.
 
J
J
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 02 Jun 2009 at 3:26am
Hi DBlank. I have just finished my report and though I would let you know that your solutions worked.
I now have a little knowledge on what running totals are and how they operate.
 
Thanks again for all your help.
 
 
J
J
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.