Good Evening,
Happy New Year!
I have come across a problem using Crystal Reports 10, now hold onto your horses because my question is a SQL question but it may have an answer in Crystal.
I am in a high production environment where I have built a report for use in Crystal Reports 10. The database is a MS-SQL 2000 database is based on a many to many schema and I have had the ahrdest time trying to get at the data for reporting purposes.
I have tryed sql queries using different types of joins but I can not get the data into the format that Crystal will accept and not create a bunch of pages in my report. Here is a sample of at least four tables.
Linking table Type Table Detail1 Detail2
Lnk_ID Typ_ID Det1_ID Det2_ID
Lnk_Det1_ID Typ_Name Det1_Typ_ID Det2_Typ_ID
Lnk_Det2_ID Typ_Value Det1_A Det2_A
Lnk_Typ_ID Det1_B Det2_B
Det1_C Det2_C
I have abbreviated some of the tables names.
What I need is to come up with a sql query and a stored procedure that will extract the data based on a parameter that will be passed in when the report run. I need to put the data into maybe a temp table that resmebles this;
Row1 - ID TypName Det1_A Det2_A Det1_B Det2_B and so on
Row2 - ID TypName Det1_C Det1_D Det1_E Det2_C etc.
Where each row represents a page of data. Here is a kicker, I have tried to do this but the best that I have come up with is to flaten out the data in order to report on it. By the way, a row will complete an entire page of data in my Crystal repeort.
Thanks for your help in advance.
James
Happy New Year!
I have come across a problem using Crystal Reports 10, now hold onto your horses because my question is a SQL question but it may have an answer in Crystal.
I am in a high production environment where I have built a report for use in Crystal Reports 10. The database is a MS-SQL 2000 database is based on a many to many schema and I have had the ahrdest time trying to get at the data for reporting purposes.
I have tryed sql queries using different types of joins but I can not get the data into the format that Crystal will accept and not create a bunch of pages in my report. Here is a sample of at least four tables.
Linking table Type Table Detail1 Detail2
Lnk_ID Typ_ID Det1_ID Det2_ID
Lnk_Det1_ID Typ_Name Det1_Typ_ID Det2_Typ_ID
Lnk_Det2_ID Typ_Value Det1_A Det2_A
Lnk_Typ_ID Det1_B Det2_B
Det1_C Det2_C
I have abbreviated some of the tables names.
What I need is to come up with a sql query and a stored procedure that will extract the data based on a parameter that will be passed in when the report run. I need to put the data into maybe a temp table that resmebles this;
Row1 - ID TypName Det1_A Det2_A Det1_B Det2_B and so on
Row2 - ID TypName Det1_C Det1_D Det1_E Det2_C etc.
Where each row represents a page of data. Here is a kicker, I have tried to do this but the best that I have come up with is to flaten out the data in order to report on it. By the way, a row will complete an entire page of data in my Crystal repeort.
Thanks for your help in advance.
James