Total for each group calculates for total instead of subtotal

[dlucenario]

Hi I am quite new to Report Studio and I have a problem in where the total for each group (subtotal) displays the total for the whole column. There's an attached picture in where my problem is displayed.

[Francis aka khayman]

got a good idea what is happening but if you can tell us what columns/groups you are using and how you are calculating the total, then we could tell you exactly how to calculate.

Hi I am quite new to Report Studio and I have a problem in where the total for each group (subtotal) displays the total for the whole column. There's an attached picture in where my problem is displayed.

[dlucenario]

Hi thanks for the reply. I apologize for not being detailed.
The query is as follows: it composes of 7 columns; index (master table), port number, record, targets, sources (port table),country (country table), reports (ip address table)

The country table acts like a reference table for ip address table.
The ip adress table and port table has a foreign key that relates to the index (master table)

Thanks again!

[MFGF]

Hi,

We really need to know what you did to generate the total in your report. If you just selected the measure column and used the Aggregate button (on the toolbar) then this points you you having a problem with the way your metadata is defined in your package - which is rather more serious. Without knowing more it's not possible to advise further, though.

Cheers!

MF.

[MFGF]

Hi thanks for the reply. I apologize for not being detailed.
The query is as follows: it composes of 7 columns; index (master table), port number, record, targets, sources (port table),country (country table), reports (ip address table)

The country table acts like a reference table for ip address table.
The ip adress table and port table has a foreign key that relates to the index (master table)

Thanks again!

Ah! You posted as I was writing that last message :) Have you coded this as a SQL query in your report, or are these relationships defined in your underlying FM model? If they are in FM, have you looked at the best practices and remodelled the data to take account of the assumptions the query engine makes about fact/dimension usage based on the cardinalities of the relationships?

MF.

[dlucenario]

The attached picture is the current design in my model framework. As you can see there are four tables that were involved. (Geo_Table, Top_ip_address, date, top_port)

I just want to have a list of an inner join between top_port and top_ip_address table.

Thanks Again

[dlucenario]

It seems I got it all working now. I was grouping it with a wrong column. I was grouping it through Index located at the master table. Instead I used the indexkey (top_ip_address) to group it and it worked.

Thank You for your help. I would'nt realize if you haven't said to recheck my SQL statements and relationships.

[MFGF]

Where is the measure value you are totalling?

The current relationships will lead the query engine to assume that TOP_IP_ADDRESS and TOP_PORT are both dimension tables linking to DATE as a common fact table. You really need to remodel the objects so that your measure is in a query subject representing a fact table and your descriptive data is in query subjects representing dimensions. The image you posted is from "Foundation Objects View" in FM - do you have a higher modelling tier where the data is restructured?

I'm convinced your results are incorrect because the query engine doesn't recognise how to aggregate measures in a dimension. I would wager that if your report contains data from just the table with the measure in it, you will get correct aggregation, but as soon as you bring in data from a different table you get weird aggregations going on. This is a common symptom of a model that is not designed according to IBM's best practice guidelines :)

MF.

Cheers!

MF.

[MFGF]

Ha! You posted again while I was typing :) Ideally it shouldn't make a difference what you group on - you should get accurate, predictable results whatever you decide to do in your report. If this is a throwaway report/model then no further action is required, but if you're going to be modifying the report and/or building other reports from the same model, you really ought to think about changing the model to give you correct and consistent results.

Cheers!

MF.

[dlucenario]

Unfortunately, you were right.  :D
I did some calculations on the report and when I double checked them, there were some abnormalities to it. I am currently remodeling my project. Im going to take into account on what you said about the fact  tables.

Thanks again and if you have any further suggestions feel free to tell them.