VB Crystal Report, SuperTypes - Subtypes tables in Crystal Reports 7

microsoft.public.vb.crystal - Microsoft Visual Basic


VB Crystal Report >> SuperTypes - Subtypes tables in Crystal Reports 7

by Diego Curtino » Thu, 14 Apr 2005 21:52:20 GMT

Dear people:

I have a report in which a use 3 tables. One has common data to all persons,
such as name and TE number. Another one has data for physical persons such
as sex, civil state and other. The last one has data for legal persons.

When one person is added to the DB my vb application generates the person
Id, add the record in Persons table and with that Id it also adds a record
in the table that matches the person type (physical or legal).

Is there anyway in which I could make CR's report that shows data from all
these 3 tables. Concretely I want to make a report that shows person name,
TE (from Persons table), Sex (if it a physical person) and other data if it
a legal person. This report should show all this data together leaving blank
spaces when the field is not applicable to that person. I also would like to
show another data such as person address.

The relation between Persons, Physical Persons and Legal Persons is a
supertype-subtype relation, and it seems to me that CR get confused when
LegalPerson Id and Physical Person Id points to Persons Is. It is like it
doesn know where to look for the data that it has to show and ends showing
up all ceros. Should I use subreports? Is there any website where I can find
information?

Any advice is welcome.

TIA,
Ing. Diego Curtino



Top

VB Crystal Report >> SuperTypes - Subtypes tables in Crystal Reports 7

by Iain » Thu, 14 Apr 2005 22:03:15 GMT


You're looking for outer joins here.

There should be a left outer join from Person to Physical and another from
Person to Legal .

Where you have to be careful is that if you do any selections at all based
on data in Physical or Legal, the selection criteria has to be
(isnull(fieldname) or <selection criteria>) or it will only find results
which have an entry in the relevant table. The isnull HAS to be before the
other selection or it doesn't work.

Regards,

Iain







Top

VB Crystal Report >> SuperTypes - Subtypes tables in Crystal Reports 7

by Diego Curtino » Thu, 14 Apr 2005 22:21:06 GMT

Thanks, Iain

But the problem is more complex. Il tell you what happens.

If I link Person with Address, it works fine (showing the address). But if
then I link Person with Physical to show data of physical persons, the data
of this last one table doesn show at all (they all keep in zero though the
address keeps ok).

I have also tried creating another report to do the inverse proccess linking
Person to Physical and show the data of it. It works ok, but if then I link
Person to Address, it is the address now which keeps in zero.

Do you have any idea of could be happening here? Simplifing, the report
works OK until I add another table, it allows me one or another but not
both.

TIA,



"Iain" < XXXX@XXXXX.COM > escribien el mensaje
>> You're looking for outer joins here. >> >> There should be a left outer join from Person to Physical and another from >> Person to Legal . >> >> Where you have to be careful is that if you do any selections at all based >> on data in Physical or Legal, the selection criteria has to be >> (isnull(fieldname) or<) or it will only find results >> which have an entry in the relevant table. The isnull HAS to be before the >> other selection or it doesn't work. >> >> Regards, >> >> Iain >>

>>>> Dear people: >>>> >>>> I have a report in which a use 3 tables. One has common data to all >>>> persons, >>>> such as name and TE number. Another one has data for physical persons
such >>>> as sex, civil state and other. The last one has data for legal persons. >>>> >>>> When one person is added to the DB my vb application generates the >>>> person >>>> Id, add the record in Persons table and with that Id it also adds a
record >>>> in the table that matches the person type (physical or legal). >>>> >>>> Is there anyway in which I could make CR's report that shows data from
all >>>> these 3 tables. Concretely I want to make a report that shows person >>>> name, >>>> TE (from Persons table), Sex (if it a physical person) and other data if >>>> it >>>> a legal person. This report should show all this data together leaving >>>> blank >>>> spaces when the field is not applicable to that person. I also would
like >>>> to >>>> show another data such as person address. >>>> >>>> The relation between Persons, Physical Persons and Legal Persons is a >>>> supertype-subtype relation, and it seems to me that CR get confused when >>>> LegalPerson Id and Physical Person Id points to Persons Is. It is like
it >>>> doesn know where to look for the data that it has to show and ends >>>> showing >>>> up all ceros. Should I use subreports? Is there any website where I can >>>> find >>>> information? >>>> >>>> Any advice is welcome. >>>> >>>> TIA, >>>> Ing. Diego Curtino >>>> >>>> >> >>



Top

SuperTypes - Subtypes tables in Crystal Reports 7

by Iain » Fri, 15 Apr 2005 15:59:02 GMT

an you cut and paste the SQL?

Iain


"Diego Curtino" <diego@solsoft(punto)com(punto)ar> wrote in message
news: XXXX@XXXXX.COM ...



Top
Similar Threads

1. Crystal report porblem (dataset passing, blank report, and table b - VB Crystal Report

2. Loading Fields Table Crystal Report vs Reporting Services 

I have 2 tables contain fields as below :
- A : f1, f2, f3, f4, f5
- B : f1, f2, f3, f4, f5, f6, f7, f8, f9, f10

I want to create report, Which table that will give best performance?
This question appear because I want to compare Crsytal Report and
Reporting Services. At Crystal, no matter of your query all fields of
table will be load and appear at Field Explorer, meanwhile Reporting
Services only load fields that's included in your query.

3. Crystal Reports - Load Report failed. "Invalid Table Number" - VB Crystal Report

4. Creating a report from a SQL Server temp table in Crystal Reports.NET 

Hi !

Is it possible to create a Crystal Report through a temporary table in
SQL server if the fields in the temporary table aren't known until
run-time ?  I am not going to know every field that needs to be in the
report till run-time as the number of fields for the temporary table
changes each time the app is run. If so, how is it done ? What sort of
template can you create in Crystal Reports .NET if you dont know all
of the columns till run-time ?

Thanks

5. Crystal Reports - Setting up a one-to-many relationship in a crystal report - VB.Net

6. crystal reports 9 and crystal reports.net 

does crystal reports 9 and crystal reports.net have the same wizard,
syntax and any other functionality? if i will learn crystal reports 9
first, will I use this knowledge in crystal reports.net?

coz i don't have books in CR.NET bu i do have in crystal reports 9.

thanks in advance for any help.

7. Crystal Report Print Engine (CRPE) API in Crystal Reports XI - VB Crystal Report

8. Help: How to export crystal report to excel file (Crystal Report 6) 

Hi All,
    How to export crystal report to excel file (Crystal Report 6)?


Kind regards,
Peter