0

I have a Cognos Dashboard with table visualization and my database structure will be like this image. I want to display data like this image by using distinct value. but what I got is sum of all values like this image.

Vigneshwaran B
  • 13
  • 3

2 Answers2

0

Your table has 3 columns, each representing a level of detail, plus your price column. The levels of detail are, from highest to lowest level of abstraction, Name, code, and SLNO.

There are 3 records in the table. One record is A.A1.1, with a value for price of 10. The second is A.A1.2, also with a value of 10. The third is A.A2.3, with a value of 20 for price. Price is probably set as a measure with an aggregate of total.

Your report uses name and price. Because of what has been modeled, you get the 4 records summed to 40.

You do not think this is correct but you need to understand that, as it has been modeled, it is.

Subsequently, given that you say that this is not what the nature of the situation, you need to understand what the nature of the objects in question are and, thus armed, then define this nature, which will then allow us to determine how to model it.

Why do you want the value for price for A to be 30? What business rule determines this? What is the nature of price? Why are the values for SLNO 1 and SLNO 2 not supposed to be aggregated? Is it that the fact grain for price is code and not SLNO?

Is the price an attribute or measure? Without more information, I would say that it is the former rather than the latter, as usually the word price is associated as a fixed (at least in the time frame but possibly variable in a longer time frame) value assigned to an object.

(you can use attributes in measures, for example you could use price * quantity to determine the revenue from a sale)

Assuming that the fact grain of price is code and not SLNO then you ought to consider modelling an appropriate column dependency chain of Name--->Code--->SLNO with price in the code group. You would also need to think about the nature of the keys.

This is tentative, given the uncertainties.

C'est Moi
  • 326
  • 1
  • 2
  • 8
0

Try adding a determinant to control how the data is grouped

In the example, by name and code

You could test this in a report with the following expression

Total([price] for [name],[code])

Then make the appropriate changes to the Framework Manager model

VAI Jason
  • 534
  • 4
  • 14