Need To Better Understand Singletons

[Cognos_Jan2017]

We have a 500 row List Report where we want to summarize parts.

An example is to find the number of rows where a date is at least one year older
than Today.  That Data Item, 'Ops_OneYr' has an Expression Definition of ...
Case
When (([BusinessGroup]='Ops') and ([< 1 Year]=1)) Then 1
End

Of the 500 records, 15 rows do display 1's properly meaning we want to report 15 in a Singleton.

An attempted Data Item 'T_Ops_OneYr" has an Expression Definition of ...
Total([Ops_OneYr] for report)

The above does display 15 in every row (View tabular data) under the Column Header
'T_Ops_OneYr'.  Dragging that Data Item into a Singleton does NOT display 15.  Trying
various Detail and Summary Aggregations result in either a Blank, 0, 1, or 539 ... none
of those are correct.

Gotta be a simple way of dragging that Data Item ('T_Ops_OneYr' value of 15)
into a Singleton?

H E L P.  TIA, Bob

[Cognos_Jan2017]

Need to add.  I had a similar question 2 weeks ago and was helped here.  THANK you CognosPaul.

In the other scenario, I expected an answer of 6, and dragging that in as a Total resulted in 18.  Examined it further and realized the vendor's App was assigning 3 wherever it should have been 1 ... therefore 3 times 6 does equal 18.  Yesterday I sent an email to the Vendor asking why it recorded 3 instead of a 1 assigned by a Case Where ... no reply yet.

Trying the 1 times 15 from today and dragging in as a Total does not result in 15.  That's why I am trying the descriptive in the opening Post.

[Lynn]

A query may result in different SQL for different layout containers. When you view tabular data the SQL will be generated based on all the data items present in your query whereas a layout container on your page that references fewer of the data items present in the query will be different SQL and potentially a different result. Looking at the generated SQL can help identify what is happening.

It looks like you are using calculations on top of calculations so there are multiple elements in the query that are necessary to achieve the result you really are after but they are not present in your singleton layout. You can try to add some of the necessary elements to the singleton properties to see if that addresses the problem.

Another area to investigate is the aggregate property for your calculated data items. The figure shows correctly when all the query data items are in the "select" list (e.g., your tabular data) but is not aggregating properly when those items are not present in the layout container (e.g., your singleton). Try creating a list that includes all the items in your query and then remove items one by one to see at what point aggregation goes awry.

You can also try to simplify your approach by creating a single calculation that encompasses all the necessary logic and uses a fully qualified reference to the package data items. That should allow it to come up with the correct result regardless of what other data items are or are not present.

Code Select Expand



Total(
    case
        when [Your Package].[Your Query Subject].[BusinessGroup] = 'Ops'
          and [Your Package].[Your Query Subject].[< 1 Year] = 1  then 1
        else 0
    end
for report)



[Cognos_Jan2017]

Excellent reply Lynn.  I gave that an Applaud from my tablet several hours ago,
but my Reply wouldn't save.  At work now.  THANK you.

I will try your recommendations, and hopefully find a solution ... I will then report
back here so all may benefit if encountering similar scenarios in the future.

Have always thought ... what is the possibility Cognos could have a feature like
Excel's "Paste As Value"?

[Cognos_Jan2017]

Lynn ... Your simplify approach (much fewer Data Items in another Query) has worked
on six (6) Singletons so far.  I would expect that to continue to work ... always aware
selected Aggregations might need modification.

Here is an example Data Item ...
Total(
    case
        when (([Query1].[Level 1 Name] = 'Ops')
          and ([Query1].[< 1 Year] = 1))  then 1
        else 0
    end
for report)

The Source is a Data Module, and it was not necessary to qualify it
preceding [Query 1].

[Query 1] is a UNION having a Summary filter to find Maximum Date
where a Site has multiple entries based on a Date.

Looking good.  THANK you again.