suppressing of duplicate values

[ry1633]

In my project, I have a view that is suppressing duplicate values.   When ever I add in three fields from that view (for instance, Establishment ID, Product Serial, and Submission Type) and then filter the report by Establishment ID,  I come up with 1 record for the report.  But when I add the primary key ID field (from the same view) to the report, it returns 15 records including some duplicates.   

My users want to see the duplicates and don't want them to be suppressed.  So I'm wondering what could be wrong.  All the fields in question are from the same view.    What should I be looking at?   Could it be a flaky join in my Cognos model, or is at the view level in the database?

[Lynn]

In my project, I have a view that is suppressing duplicate values.   When ever I add in three fields from that view (for instance, Establishment ID, Product Serial, and Submission Type) and then filter the report by Establishment ID,  I come up with 1 record for the report.  But when I add the primary key ID field (from the same view) to the report, it returns 15 records including some duplicates.   

My users want to see the duplicates and don't want them to be suppressed.  So I'm wondering what could be wrong.  All the fields in question are from the same view.    What should I be looking at?   Could it be a flaky join in my Cognos model, or is at the view level in the database?

Automatic aggregation and summarization is the nature of how the product works and, in fact, is a large part of what makes it very powerful. When you select attributes and a measure the generated query is a going to do a "group by" to show the aggregated measure value for the attributes in question. If only attributes are selected then the generated query is going to do a "select distinct."

There is an auto group and summarize property in report studio which defaults to "Yes" but it can be set to "No" for scenarios where the detail information is required. I would not recommend attempting to do anything in the model that prohibits automatic aggregation.

[ry1633]

This is for an Ad Hoc project - so my users are using Query Studio for right now.  And I see there is a checkbox to turn off "Automatically Suppress Duplicate Values" under the Advanced Options when you run a report in QS.    I discussed this with my users yesterday, and they didn't warm to it because they were concerned that they'd have to go there and uncheck that box every single time they wanted to see duplicates, and that this seemed tedious to them at best.

I can post this over in the Query Studio forum and see what I get.  I'll also do some more reading and searching on my own.   It may well be that this isn't possible.    Just wondering if there was something I should be looking at in the model with Framework Manager.

[Lynn]

If memory serves there is a change you can make to the system.xml to set that checkbox for Query Studio as the default. It was a loooong time ago but I imagine it would still be possible in more recent versions. Not sure if I'm confusing this with setting it to default to no data mode. Maybe someone else will know one way or the other.

Not sure what you could do on the framework side.

[ry1633]

where would that system.xml file be found.  Is it somewhere on my C: drive in the cognos installation folder, or would it be in the cognos project folder I'm working from?

[MFGF]

where would that system.xml file be found.  Is it somewhere on my C: drive in the cognos installation folder, or would it be in the cognos project folder I'm working from?

There are lots of system.xml files in different places. Which specific one do you mean?

MF.

[ry1633]

Not sure actually.... looking for the one that might let me edit default settings Cognos (esp. Query Studio),  I wanted to see if Lynn's answer might work for me.

[Lynn]

I can't find anything about that particular setting so I'm not sure if my memory is simply faulty.

Another option to explore might be to create a custom template that has this property switched off and make it the default for QS. I've never messed with templates but might be worth considering.

https://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cra.10.2.2.doc/t_changethedefaultquerystudiotemplate.html

[ry1633]

I found this info that looks promising. except I can't find any corresponding system.xml file in my cognos install folder or in the project folder itself.

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page533.html

[Lynn]

I found this info that looks promising. except I can't find any corresponding system.xml file in my cognos install folder or in the project folder itself.

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page533.html

I suspect things are different in 10 than they were in 8. I don't use QS so can't really dig in further. Here is an article about templates that is specific to 10 which is very different from what you need to do in 8.

http://www.ibm.com/developerworks/library/ba-pp-reporting-layout_and_formatting-page633/

[ry1633]

I'm kind of forced to use Query Studio right now due to our organization's set up and licensing (for the time being).  I found this too which seems to be a decent workaround:

https://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.ug_cr_qstd.10.1.1.doc/t_turn_off_automatic_summarization.html?lang=en

[MFGF]

I'm kind of forced to use Query Studio right now due to our organization's set up and licensing (for the time being).  I found this too which seems to be a decent workaround:

https://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.ug_cr_qstd.10.1.1.doc/t_turn_off_automatic_summarization.html?lang=en

I'm guessing that's what Lynn saw a while ago and was trying to recall? Good find! :)

MF.

[ry1633]

Just wondering (thinking out loud),  can this be fixed at the package level somehow in the definition/create of the package?

[MFGF]

Just wondering (thinking out loud),  can this be fixed at the package level somehow in the definition/create of the package?

Hi,

There's nothing to fix - it's not a product issue or something that's broken. Automatic grouping and aggregation is the way the product is designed to work. I don't see a way of turning this off in your model.

Cheers!

MF.