Duplicate values

[funkmonsteruk]

I'm having a slight problem with obtaining a sum value in a BO report. I have several fields in the report including a <description> for each <value> that forms part of an <estate> i need to sum all the values for each estate, which is a relatively simple task. But i need to do this without including the individual <value> and <description> of each item within the estate. I have tried deleting the values and descriptions from the report output, however this seems to make the report exclude any duplicate <value>, i'm guessing this is because the description differentiates values which are identicle and if the description is not included then any duplicate values are ignored (have checked the date for each report and this does seem to fit in with the figures i'm getting)

Any ideas how i can get the report to not ignore duplicate values?

 

[blom0344]

Hello Funkmonsteruk,

Have you played yet with functions like CountAll and extended context with the ForAll syntax? Or the setting 'Avoid duplicate rows aggregation'? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[funkmonsteruk]

Cheers for the poiter Blo0344, have tried playing with 'Avoid Duplicate Rows Aggregation' and alas still no joy. I have also tried creating a new variable - Sum((<Value>) for all <Description>) but this still does not work. Do you have any more suggestions.

I have found some VBA coding to exports single variables from the report, so i might try this approach if nothing else works.

 

[blom0344]

Hello Funkmonsteruk,

Did you set the aggregate behaviour of the measures you use in the universe to 'sum' instead of 'none' . If you do not set them to sum BO does not autoaggregate at report level. I can foresee then strange effects if you leave out dimensions causing identicale rows not to be taken into account (forgive the english, I just blundered in another forum) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[skrandel]

Any chance of you posting a really brief example? What you are describing doesn't make sense to me. Are you describing a Fan trap or are you simply not getting the right values? Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[funkmonsteruk]

Hello Skrandel,

Here is a brief example of the problem.

Each report contains a selection of asset values for an estate (i work in a probate office), if i list all of the asset values individually and include there discriptions then values of (all in pounds): 1, 1, 88.96, 11010, 250 are listed which add up to 11350.96. However if i remove the description then values of 1, 88.96, 11010, 250 are reported giving a sum of 11349.96. As you can see the duplicate value of 1 has been ignored when i removed the description.

The same problem has happened in all estates with duplicate amounts listed under values

 

[blom0344]

Hello Funkmonsteruk,

The fact that when you remove the dimension that gives you the individual asset values still gives you multiple lines for a given estate points at the aggregating behaviour of the measure ' asset value ' as set to 'none' instead of as set to 'sum' in the properties of the object.

Try this:

Set the property of the measure asset value (in Designer) to sum and then save , export universe. Run the report again, if you leave out the description at report level, there should remain just one (aggregated ) total for an estate.

Also check the dataset (data --> view data) to see if the query returns individual lines with the measures as it should. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[skrandel]

Although TBlom's answer is likely correct, I'm from the US and probably can't understand his perfect use of the Queen's English.

As I was suspecting, your Asset value object is a dimension. If you want numbers to add up, they should be measures. What I would do is this:

Open the object in designer and change the SQL for Asset Object to say: sum(table.assetcolumn). Obviously, you need to substitute the correct SQL for the &quot;table.assetcolumn&quot;.

The problem you're having is that dimensions cannot be totalled. When you sum them, BO will only sum the distinct values. SO, 1+1=1 when you use dimensions.

Measures should almost always have the &quot;sum&quot; done in the select portion of the object. When you make this change EVERYTHING else will fall into place. The object will turn itself into a measure and the aggregation function will be automatically set to &quot;sum&quot;. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

Hey stevie,

We dutchmen are known for our excellent English. In fact one of the greatest compliments we always hear from you tourists , is that &quot;every one you meet speaks English as well&quot;. And that for a home language that some say is even more difficult than chinese.
Watch out or I'll post a thread in THE OTHER FORUM in my native language which will cause you to ban me for taking me as a ...-smoking .... (I just applied some self-censure here

P.s. We have a queen too , you know and don't try to insult her, cause today is what you guys would call the &quot;state of the union&quot; for the dutch, and her English is REALLY something.... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com