Report Builder : select multiple tables in one report

Hello,
I'm trying to create a report on Microsoft CRM on the following tables
dbo.FilteredAccount
dbo.FilteredNew_internatsamenwerking
dbo.FilteredNew_ImpExpCountry
I have created a data source view in with the following relations :
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
I can create a report with fields from dbo.FilteredAccount and
dbo.FilteredNew_internatsamenwerking and another report with fields from
dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
Is this relationship 1 to N a restriction in Report Builder?
Do you have a solution?
Thanks in advanceIs all your tables connected with a key , then it should not be a problem
Amarnath
"carolineb" wrote:
> Hello,
> I'm trying to create a report on Microsoft CRM on the following tables
> dbo.FilteredAccount
> dbo.FilteredNew_internatsamenwerking
> dbo.FilteredNew_ImpExpCountry
> I have created a data source view in with the following relations :
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
>
> I can create a report with fields from dbo.FilteredAccount and
> dbo.FilteredNew_internatsamenwerking and another report with fields from
> dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> Is this relationship 1 to N a restriction in Report Builder?
> Do you have a solution?
> Thanks in advance
>
>|||Hi,
Table dbo.FilteredAccount has a primary key accountid
Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
dbo.FilteredAccount 1 to many relation on
dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
the field accountid in both tables)
I'm I missing something?
Thanks
"Amarnath" wrote:
> Is all your tables connected with a key , then it should not be a problem
> Amarnath
>
> "carolineb" wrote:
> > Hello,
> >
> > I'm trying to create a report on Microsoft CRM on the following tables
> > dbo.FilteredAccount
> > dbo.FilteredNew_internatsamenwerking
> > dbo.FilteredNew_ImpExpCountry
> >
> > I have created a data source view in with the following relations :
> >
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> >
> >
> > I can create a report with fields from dbo.FilteredAccount and
> > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> >
> > Is this relationship 1 to N a restriction in Report Builder?
> > Do you have a solution?
> >
> > Thanks in advance
> >
> >
> >|||Report Model can be designed in such a way that one table will act as storing
all the PK and FK's of the table.
ie taking your example.
dbo.FilteredAccount 1
account ID as PK
dbo.FilteredNew_internatsamenwerking
intsamenwerkingid PK
accountid as FK
dbo.FilteredNew_ImpExpCountry
impexpid as PK
accountid as FK
If you can design in such a way that both the other tables PK should be in
FilteredAccount Table then it should work. Since you are refereing just
account ID for all the tables it assumes 1 to 1 relation.
You can try this and see.
Amarnath
"carolineb" wrote:
> Hi,
> Table dbo.FilteredAccount has a primary key accountid
> Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> dbo.FilteredAccount 1 to many relation on
> dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> the field accountid in both tables)
> I'm I missing something?
> Thanks
> "Amarnath" wrote:
> > Is all your tables connected with a key , then it should not be a problem
> >
> > Amarnath
> >
> >
> > "carolineb" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to create a report on Microsoft CRM on the following tables
> > > dbo.FilteredAccount
> > > dbo.FilteredNew_internatsamenwerking
> > > dbo.FilteredNew_ImpExpCountry
> > >
> > > I have created a data source view in with the following relations :
> > >
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > >
> > >
> > > I can create a report with fields from dbo.FilteredAccount and
> > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > >
> > > Is this relationship 1 to N a restriction in Report Builder?
> > > Do you have a solution?
> > >
> > > Thanks in advance
> > >
> > >
> > >|||Do you mean that i have to add the PK's as a field in the table
dbo.FilteredAccount 1
intsamenwerkingid PK
impexpid as PK
This is not possible since the relation is 1 to n in both tables?
Thanks
"Amarnath" wrote:
> Report Model can be designed in such a way that one table will act as storing
> all the PK and FK's of the table.
> ie taking your example.
> dbo.FilteredAccount 1
> account ID as PK
> dbo.FilteredNew_internatsamenwerking
> intsamenwerkingid PK
> accountid as FK
> dbo.FilteredNew_ImpExpCountry
> impexpid as PK
> accountid as FK
> If you can design in such a way that both the other tables PK should be in
> FilteredAccount Table then it should work. Since you are refereing just
> account ID for all the tables it assumes 1 to 1 relation.
> You can try this and see.
> Amarnath
> "carolineb" wrote:
> > Hi,
> >
> > Table dbo.FilteredAccount has a primary key accountid
> > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> >
> > dbo.FilteredAccount 1 to many relation on
> > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > the field accountid in both tables)
> >
> > I'm I missing something?
> >
> > Thanks
> >
> > "Amarnath" wrote:
> >
> > > Is all your tables connected with a key , then it should not be a problem
> > >
> > > Amarnath
> > >
> > >
> > > "carolineb" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > dbo.FilteredAccount
> > > > dbo.FilteredNew_internatsamenwerking
> > > > dbo.FilteredNew_ImpExpCountry
> > > >
> > > > I have created a data source view in with the following relations :
> > > >
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > >
> > > >
> > > > I can create a report with fields from dbo.FilteredAccount and
> > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > >
> > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > Do you have a solution?
> > > >
> > > > Thanks in advance
> > > >
> > > >
> > > >|||Sorry, I mean that two id's should be there in your account table not as PK
as FK.
Amarnath
"carolineb" wrote:
> Do you mean that i have to add the PK's as a field in the table
> dbo.FilteredAccount 1
> intsamenwerkingid PK
> impexpid as PK
> This is not possible since the relation is 1 to n in both tables?
> Thanks
>
> "Amarnath" wrote:
> > Report Model can be designed in such a way that one table will act as storing
> > all the PK and FK's of the table.
> > ie taking your example.
> > dbo.FilteredAccount 1
> > account ID as PK
> > dbo.FilteredNew_internatsamenwerking
> > intsamenwerkingid PK
> > accountid as FK
> > dbo.FilteredNew_ImpExpCountry
> > impexpid as PK
> > accountid as FK
> >
> > If you can design in such a way that both the other tables PK should be in
> > FilteredAccount Table then it should work. Since you are refereing just
> > account ID for all the tables it assumes 1 to 1 relation.
> > You can try this and see.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > Hi,
> > >
> > > Table dbo.FilteredAccount has a primary key accountid
> > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > >
> > > dbo.FilteredAccount 1 to many relation on
> > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > the field accountid in both tables)
> > >
> > > I'm I missing something?
> > >
> > > Thanks
> > >
> > > "Amarnath" wrote:
> > >
> > > > Is all your tables connected with a key , then it should not be a problem
> > > >
> > > > Amarnath
> > > >
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > dbo.FilteredAccount
> > > > > dbo.FilteredNew_internatsamenwerking
> > > > > dbo.FilteredNew_ImpExpCountry
> > > > >
> > > > > I have created a data source view in with the following relations :
> > > > >
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > >
> > > > >
> > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > >
> > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > Do you have a solution?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > >
> > > > >|||This is not possible because one account can have many records
dbo.FilteredNew_ImpExpCountry and many records in
dbo.FilteredNew_internatsamenwerking.
"Amarnath" wrote:
> Sorry, I mean that two id's should be there in your account table not as PK
> as FK.
> Amarnath
> "carolineb" wrote:
> > Do you mean that i have to add the PK's as a field in the table
> > dbo.FilteredAccount 1
> > intsamenwerkingid PK
> > impexpid as PK
> > This is not possible since the relation is 1 to n in both tables?
> >
> > Thanks
> >
> >
> > "Amarnath" wrote:
> >
> > > Report Model can be designed in such a way that one table will act as storing
> > > all the PK and FK's of the table.
> > > ie taking your example.
> > > dbo.FilteredAccount 1
> > > account ID as PK
> > > dbo.FilteredNew_internatsamenwerking
> > > intsamenwerkingid PK
> > > accountid as FK
> > > dbo.FilteredNew_ImpExpCountry
> > > impexpid as PK
> > > accountid as FK
> > >
> > > If you can design in such a way that both the other tables PK should be in
> > > FilteredAccount Table then it should work. Since you are refereing just
> > > account ID for all the tables it assumes 1 to 1 relation.
> > > You can try this and see.
> > >
> > > Amarnath
> > >
> > > "carolineb" wrote:
> > >
> > > > Hi,
> > > >
> > > > Table dbo.FilteredAccount has a primary key accountid
> > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > >
> > > > dbo.FilteredAccount 1 to many relation on
> > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > the field accountid in both tables)
> > > >
> > > > I'm I missing something?
> > > >
> > > > Thanks
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Is all your tables connected with a key , then it should not be a problem
> > > > >
> > > > > Amarnath
> > > > >
> > > > >
> > > > > "carolineb" wrote:
> > > > >
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > dbo.FilteredAccount
> > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > >
> > > > > > I have created a data source view in with the following relations :
> > > > > >
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > >
> > > > > >
> > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > >
> > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > Do you have a solution?
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > >
> > > > > >|||You should connect a PK to PK and not a non PK key. in your case it is
account id in the other two tables.
Amarnath
"carolineb" wrote:
> This is not possible because one account can have many records
> dbo.FilteredNew_ImpExpCountry and many records in
> dbo.FilteredNew_internatsamenwerking.
> "Amarnath" wrote:
> > Sorry, I mean that two id's should be there in your account table not as PK
> > as FK.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > Do you mean that i have to add the PK's as a field in the table
> > > dbo.FilteredAccount 1
> > > intsamenwerkingid PK
> > > impexpid as PK
> > > This is not possible since the relation is 1 to n in both tables?
> > >
> > > Thanks
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Report Model can be designed in such a way that one table will act as storing
> > > > all the PK and FK's of the table.
> > > > ie taking your example.
> > > > dbo.FilteredAccount 1
> > > > account ID as PK
> > > > dbo.FilteredNew_internatsamenwerking
> > > > intsamenwerkingid PK
> > > > accountid as FK
> > > > dbo.FilteredNew_ImpExpCountry
> > > > impexpid as PK
> > > > accountid as FK
> > > >
> > > > If you can design in such a way that both the other tables PK should be in
> > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > You can try this and see.
> > > >
> > > > Amarnath
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > >
> > > > > dbo.FilteredAccount 1 to many relation on
> > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > the field accountid in both tables)
> > > > >
> > > > > I'm I missing something?
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > >
> > > > > > "carolineb" wrote:
> > > > > >
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > dbo.FilteredAccount
> > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > >
> > > > > > > I have created a data source view in with the following relations :
> > > > > > >
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > >
> > > > > > >
> > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > >
> > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > Do you have a solution?
> > > > > > >
> > > > > > > Thanks in advance
> > > > > > >
> > > > > > >
> > > > > > >|||Sorry, i cannot follow anymore.
Can you please say which key's in these tables has to be a PK and FK and
what relationship i have to design?
Thanks
"Amarnath" wrote:
> You should connect a PK to PK and not a non PK key. in your case it is
> account id in the other two tables.
> Amarnath
> "carolineb" wrote:
> > This is not possible because one account can have many records
> > dbo.FilteredNew_ImpExpCountry and many records in
> > dbo.FilteredNew_internatsamenwerking.
> >
> > "Amarnath" wrote:
> >
> > > Sorry, I mean that two id's should be there in your account table not as PK
> > > as FK.
> > >
> > > Amarnath
> > >
> > > "carolineb" wrote:
> > >
> > > > Do you mean that i have to add the PK's as a field in the table
> > > > dbo.FilteredAccount 1
> > > > intsamenwerkingid PK
> > > > impexpid as PK
> > > > This is not possible since the relation is 1 to n in both tables?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Report Model can be designed in such a way that one table will act as storing
> > > > > all the PK and FK's of the table.
> > > > > ie taking your example.
> > > > > dbo.FilteredAccount 1
> > > > > account ID as PK
> > > > > dbo.FilteredNew_internatsamenwerking
> > > > > intsamenwerkingid PK
> > > > > accountid as FK
> > > > > dbo.FilteredNew_ImpExpCountry
> > > > > impexpid as PK
> > > > > accountid as FK
> > > > >
> > > > > If you can design in such a way that both the other tables PK should be in
> > > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > > You can try this and see.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "carolineb" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > > >
> > > > > > dbo.FilteredAccount 1 to many relation on
> > > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > > the field accountid in both tables)
> > > > > >
> > > > > > I'm I missing something?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > >
> > > > > > > "carolineb" wrote:
> > > > > > >
> > > > > > > > Hello,
> > > > > > > >
> > > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > > dbo.FilteredAccount
> > > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > > >
> > > > > > > > I have created a data source view in with the following relations :
> > > > > > > >
> > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > > >
> > > > > > > >
> > > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > > >
> > > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > > Do you have a solution?
> > > > > > > >
> > > > > > > > Thanks in advance
> > > > > > > >
> > > > > > > >
> > > > > > > >|||Ok, What I did is I created 3 dummy tables, same as yours,
ie
t1 aid PK
t2 tid2 PK ,aid FK
t3 tid3 PK, aid Fk
When I created these tables and created the model I got to see all the 3
tables in the report builder, So what I think you are missing is that you
have not defined the FK to other two tables
ie
dbo.FilteredNew_internatsamenwerking
dbo.FilteredNew_ImpExpCountry
Have you created Fk in the above tables which referes the FilteredAccount
table "accountid" ? then it should work, so basically it all works with
proper key and relations.
Amarnath
"carolineb" wrote:
> Sorry, i cannot follow anymore.
> Can you please say which key's in these tables has to be a PK and FK and
> what relationship i have to design?
> Thanks
> "Amarnath" wrote:
> > You should connect a PK to PK and not a non PK key. in your case it is
> > account id in the other two tables.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > This is not possible because one account can have many records
> > > dbo.FilteredNew_ImpExpCountry and many records in
> > > dbo.FilteredNew_internatsamenwerking.
> > >
> > > "Amarnath" wrote:
> > >
> > > > Sorry, I mean that two id's should be there in your account table not as PK
> > > > as FK.
> > > >
> > > > Amarnath
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Do you mean that i have to add the PK's as a field in the table
> > > > > dbo.FilteredAccount 1
> > > > > intsamenwerkingid PK
> > > > > impexpid as PK
> > > > > This is not possible since the relation is 1 to n in both tables?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > Report Model can be designed in such a way that one table will act as storing
> > > > > > all the PK and FK's of the table.
> > > > > > ie taking your example.
> > > > > > dbo.FilteredAccount 1
> > > > > > account ID as PK
> > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > intsamenwerkingid PK
> > > > > > accountid as FK
> > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > impexpid as PK
> > > > > > accountid as FK
> > > > > >
> > > > > > If you can design in such a way that both the other tables PK should be in
> > > > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > > > You can try this and see.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "carolineb" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > > > >
> > > > > > > dbo.FilteredAccount 1 to many relation on
> > > > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > > > the field accountid in both tables)
> > > > > > >
> > > > > > > I'm I missing something?
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > "Amarnath" wrote:
> > > > > > >
> > > > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > > > >
> > > > > > > > Amarnath
> > > > > > > >
> > > > > > > >
> > > > > > > > "carolineb" wrote:
> > > > > > > >
> > > > > > > > > Hello,
> > > > > > > > >
> > > > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > > > dbo.FilteredAccount
> > > > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > > > >
> > > > > > > > > I have created a data source view in with the following relations :
> > > > > > > > >
> > > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > > > >
> > > > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > > > Do you have a solution?
> > > > > > > > >
> > > > > > > > > Thanks in advance
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >|||Hi CarolineB,
did you ever resolve your issues?
I have the same problem:
I have three tables:
Table A: PK A_ID
Table B: PK is A_ID, B_ID, FK is A_ID into Table A
Table C: PK is A_ID, C_ID, FK as A_ID into Table A.
I can see all three tables in Report Builder.
I can add fields from Table A and Table B at the same time to a single report.
I can add fields from Table A and Table C at the same time to a single report.
I cannot add fields from Table A and Table B and Table C at the same time,
yet this is a simple requirement.
Think of it as Customers, Orders & Payments.
A Customer may have 0, 1 or more Orders. Each Order has one and one only
Customer. A Customer may have 0, 1 or more Payments. Each Payment is for
one and one only Customer.
Payments and Orders are not directly related.
I'd like to list all customers with outstanding orders, and they payments
they've made. I can't do this using report builder.
"carolineb" wrote:
> Hello,
> I'm trying to create a report on Microsoft CRM on the following tables
> dbo.FilteredAccount
> dbo.FilteredNew_internatsamenwerking
> dbo.FilteredNew_ImpExpCountry
> I have created a data source view in with the following relations :
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
>
> I can create a report with fields from dbo.FilteredAccount and
> dbo.FilteredNew_internatsamenwerking and another report with fields from
> dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> Is this relationship 1 to N a restriction in Report Builder?
> Do you have a solution?
> Thanks in advance
>
>