CR XI - Is it possible to do Venn Diagrams in Crystal

[ReportDr00]

I would like to represent my data in Venn Diagram, is it possible to do so in Crystal.

Any ideas would be appreciate greatly

Armani

 

[ReportDr00]

Guyss....any ideas would be greatly appreciated?
Really we cannot do venn diagrams in Crsytal??

Anyone????

 

[lbass]

It doesn't appear to be one of the chart options. I'm not sure of what you want your display to look like, but you could potentially create the appearance of a Venn diagram by using separate pie charts that are then overlaid on each other, and then formatting grids, etc., to be transparent.

-LB

 

[ReportDr00]

Yea, i thought so to do something like that, but really the intersection of two circle represents a different value and it is hard to plot it using pie charts.

Like for example

I have clients living in County A or County B, but some clients live in both counties and how would i show this intersection of clients living in both counties using modified pie charts

Clients
County A 10
County B 15

County A & B 5

thanks for your ideas

 

[rosemaryl]

Dear ReportDr00,

You could easily represent this data in one pie chart. You would create 3 separate formulas to test the data (the formulas below are for example sake only as I don't know how the data is stored, that indicates Both Counties.

Create 3 formulas:

If {Table.Count} = 'County A'
then 1 else 0y

If {Table.County} = 'County B'
then 1 else 0

If {Table.County} in ['County A','County B]
then 1 else 0

Create your chart, select pie chart and on the data tab where it says On Change of Records, Select For All Records.

Place your 3 formulas in the value section (sum) and click ok. Your chart will show County A, County B, and Both.

You could format the colors so that County A is Blue, County B is Yellow and Both = Green.

Of course, I don't know how your data is stored, but this will show the values.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Interesting solution, but I think it would misrepresent the data, since the clients in both counties would also appear in both individual county calculations. You could adapt the formulas like this:

if distinctcount({table.county},{table.client}) = 1 and
{table.county} = "County A" then 1

if distinctcount({table.county},{table.client}) = 1 and
{table.county} = "County B" then 1

if distinctcount({table.county},{table.client}) = 2 then 1

This worked when I tested it.

-LB

 

[rosemaryl]

Dear Lbass,

You are right. Great solution! As, in a traditional Venn Diagram the overlapped records are not presented twice.

It would be nice if Crystal could show this relationship ... I did a search at the CR KB and not one item came up.

Best,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ReportDr00]

Thank you Rose and LBass, if your solutions work it would be great. I will try and let you know.

Again Thank you so much

Regards
Armani

 

[ReportDr00]

Hi again and thank you all for your suggestions,

Here are my tables and i am having bit difficulty in join these to get all the rows from both tables so i could do the formulas mentioned and create pie chart representing venn diagram.

Table A Table B
ClientFullName ClientFullName
ClientLName ClientLName
ClientFName ClientFName
SSN SSN
Gender Gender
DateOfBirth DateOfBirth
Service Date
LabID

Table A and Table B are joined on SSN, DateBirth, Gender and ClientLastName

Table A represents data from System A and Table B represents data from System B.
I am building another table c that contains the clients who are in System A and also in System B. Table C will only have those clients that exists in both systems.
I am not sure whether we need Table C for this purpose or not but for our business it is needed as it makes our queries much faster.

In my report i pull Table A and Table B and if i do inner join i get only those records from A and B that exists in both tables, but i think i need all records from A and B to create the pie chart that i am interested to represent the data as in venn diagram so i get total clients in A and total clients in B and clients in both A and B

 

[rosemaryl]

Dear ReportDr00,

I would think that you would need a full outer join and that you only need to join on SSN as it would be unique.

A full outer join pulls all combinatio of the data and you shouldn't need to create table c.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ReportDr00]

Thanks Rose

After i do full outer join on the tables, how could i modify below formulas to add it to pie chart to represent as venn diagram

if distinctcount({table.county},{table.client}) = 1 and
{table.county} = "County A" then 1

if distinctcount({table.county},{table.client}) = 1 and
{table.county} = "County B" then 1

if distinctcount({table.county},{table.client}) = 2 then 1

 

[lbass]

I think you would be better off using a command that uses a Union All statement like this:

Select 'A' as Table, `ClientFullName`, `SSN`
from TableA
union all
Select 'B' as Table, `ClientFullName`, `SSN`
from TableB

Add additional fields as necessary. Then you will be able to group on {command.SSN} and then use a distinctcount on {command.Table}. The formulas would then look like this:

if distinctcount({command.Table},{command.SSN}) = 1 and
{command.Table} = "A" then 1

//etc. This assumes that each table is for a separate county.

-LB

 

[rosemaryl]

Dear ReportDr00,

Lbass solution is truly better. But, when writing Union All statements it is important to know that the number of fields (and field type at each position) must match as only one column is returned for each of the two columns from the table. I would also suggest adding a text field, as shown below, to identify from which table a particular record is from

Select A.ClientFullName, A.SSN, 'Table A' as 'Table Org'
From Owner.TableA A
union all
Select B.ClientFullName, B.SSN, 'Table B' as 'Table Org'
From Owner.TableB B

The result of the above statement would be:

ClientFullName SSN Table Org
John Smith 111-22-3333 Table A
John Smith 111-22-3333 Table B
Jane Smith 111-22-4444 Table A
June Smith 111-22-5555 Table A


This will allow you to also check the table the record came from since I don't know if it is possible for you to have duplicates in either table.

regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Hi Ro,

That was what I was intending to do with 'A' as Table and 'B' as Table. Or am I missing something?

-LB

 

[rosemaryl]

Dear lbass,

There is nothing wrong with your concept at all.

However, Table is a keyword in sql server and in Oracle and cannot be declared that way. Select 'A' as Table should be Select 'A' as 'Table'. I don't know what backend you use - so it may be valid in that.

But the main point I wanted to make was that the columns selected from each table had to "match" in number returned and type in position. I wasn't sure if there were other fields that they were going to be selecting from the tables than the ones referenced in the example and I wanted to make sure that part was understood.

Warm regards,
ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Ro,

I hadn't thought about the keyword issue, thanks! And yes, it was important to explain the need for matching fields on each side of the union all.

-LB