join two views for report purpose

hello, Weird problem in front of me : i have two views and I want to use the innerjoin to join them and create a report So I take DataSource : Table1, its datasource : Table2 and in the relations I enter all my properties which carry equalities… creation of my report. Table1 has 11 records, Table2 has 11 records. If I run my report, I get one record from Table1, followed by 11 records from Table2. If I try this in pure SQL (inner join), then I get my 11 joined records. Anyone, any clue? thx in advance, Kathleen

Hi Kathleen, I have done a similar test combining two views (each one created from 2 tables, and having first - 11 records and the 2nd - 7 records). The relation between the views is 1-1, so the inner joining should display 11 records. I had specified the relation between them (in Relations node of View2 in report’s query datasources), and it printed as expected - 1 record from View1 followed by 1 rec. from View2. If I omit specifing the relation, I also get 1 record from V1, followed by 7 (all) from V2. Have you done this setting already? Hope this helps. Ciprian

yes, I did set the relations, but the error was mine : I didn’t put all the properties in the inner body. But my joining problems are not over :frowning: Now I have a second problem : I used to have this : while select sum(precostprice),sum(presalesprice),sum(postcostprice),sum(postsalesprice) from m_projectCalculation group by SubNumber,ProjCategoryId where (m_projectCalculation.ProjId == m_projTable.ProjId) { code } now the one table m_projectCalculation is replaced by two views who have to be joined to get the same result, and something goes wrong. I should be abled to set the group by on the full join, but that doesn’t seem to be correct X++. I tried this : while select sum(SumOfPreCostPrice),sum(SumOfPreSalesPrice) from g_aduprecalc group by SubNumber, ProjCategoryId where g_aduprecalc.ProjId == m_projTable.ProjId join sum(SumOfPostCostPrice),sum(SumOfPostSalesPrice) from g_adupostcalc group by SubNumber,ProjCategoryId where ( g_aduprecalc.ProjId == g_adupostcalc.ProjId && g_aduprecalc.ItemId == g_adupostcalc.ItemId && g_aduprecalc.InventTransId == g_adupostcalc.InventTransId && g_aduprecalc.ProjCategoryId == g_adupostcalc.ProjCategoryId && g_aduprecalc.TransType == g_adupostcalc.TransType && g_aduprecalc.SubNumber == g_adupostcalc.SubNumber && g_aduprecalc.UnitId == g_adupostcalc.UnitId && g_aduprecalc.EmplId == g_adupostcalc.EmplId && g_aduprecalc.SalesId == g_adupostcalc.SalesId ) This doesn’t give me a correct result. Suggestions? thx in advance, Kathleen

And now I feel really stupid … I’m sorry, once again I was looking at the wrong place. The new query does do the trick. Sorry for the inconvenience. Kathleen

Hi, Above all of these, another option for Axapta reports is to use SQL Server statements directly from Axapta (using “Statement” class), instead of using the query of the report. The result is keeped in a ResultSet object. You will also need to overwrite the report’s Fetch() method, to extract and print the needed fields. Programable sections can also be used. Regrads, Ciprian