Regarding rolling up of data

[asmfloyd]

Hi,
  I am joining 2 queries; each having rows for fund names  01,02,03,04 and 05 for a particular year along with the measure value as fundnav.
For the individual queries, when I give a filter for 01,02 and 03 and then keep Year and fundnav in the report (with detail aggregation as Total), I get one row which has the total of funds 01,02 and 03.
  But when I join the queries and then pull year and fundcost into a report, it shows as 3 separate rows (even though aggregation as Total). Is there anything I am doing incorrect?

Thanks,

[cognostechie]

What your report shows depends on what columns you have put into the data container (list/crosstab etc.)

[asmfloyd]

Thanks for your reply @Cognostechie..
  I am doing a simple list report. But is wondering why my rollup is not happening after the join and shows as 3 separate rows?

[cognostechie]

Can't answer it unless you post the output. Like I said, the rollup is one thing and what is displayed is another, that depends on what columns you have in the list report.

[asmfloyd]

Thanks again for the reply. Below is a sample of my scenario. I am joining table A and B (though B is a join of 2 other tables) and just pulling Year and the measure in a list report; where the total works in the direct query. But not after join. 

   Table A            Table B            
Year   Code   Fundnav1         Year   Code   Fundnav2      
2023   01   1                2023    01       4      
2023   02   2                2023    02       5      
2023   03   3                2023    03       6      
                           
                           
List report from Table A Query         List report after join   query
Year   FundNav1                          Year   FundNav1
2023   6                                   2023      1
                                    2023      2
                                    2023      3

[dougp]

That's not possible.  There is not a row on Table A where Fundnav1 = 6.  Where is that output really coming from?

What is your measure in this scenario?  Is anything being aggregated?  ...deliberately?  It looks like maybe FundNav1 is a dimension that is being summed (1+2+3=6).  But when you join Table A to Table B (assuming you are joining on Year -- but you didn't specify) then it is not being aggregated.

[bus_pass_man]

You have two queries.  Each has some attributes and a measure.   

Is Fundnav1 and Fundnav2 the same measure?   Do you really want to do a union?

A join is an artifact which defines the relationship between the entities of two tables.  Are you sure that is what you want to do?  If so, why wasn't it built into the model which you are using?    What keys are you using?  What are the usage of the columns?   What does the SQL look like?  The post-join query results looks like what you would see if the query is grouping on code so it would look like it is doing exactly what you asked it to do, which I don't think is what you think you asked it to do.

What is the business intention on what you want to do?   

Why do you think you need to create these queries in the first place?

What are you using for your report?  A FM package?  An OLAP cube?  A data module?   What is the metadata structure of it?   (which loops back to the questions of why do you think you need to create these queries and what business intention.).