| Groups | Blog | Home |
sql server reporting services : Report Builder : select multiple tables in one report
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
Is all your tables connected with a key , then it should not be a problem
Amarnath [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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. [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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. [quoted text, click to view] "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 > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |