No "Excel CountIf" available, so am writing, IE ...
Example of Data Item for 'Mktg_6Month' ...
Case
When ([Finding_SixMonth] > 179 And [Level 1 Name] = 'Mktg') Then 1 Else 0
End
No problem calculating 'Mktg_6Month' values as 1 or 0.
To calculate for a Singleton (to drag into a Table) as 'Mktg_Total_6Month' ...
Total([Mktg_6Month] for [Level 1 Name]) ... where [Level 1 Name] is a Business Group like "Marketing".
Viewing tabular data does correctly display the "Total" for the above where ...
Detail Aggregation ...... Not Applicable
Summary Aggregation ...... Calculated
As an example the Data Item 'Mktg_Total_6Month' correctly displays, example, as 7.
But, dragging the Data Item 'Mktg_Total_6Month' into a Singleton displays 0. Similar
Data Item calculations always drag in as 0 or 1.
Formatting the Singletons and formatting as Number, 0 decimal places, still appears as 1 or 0.
What are we doing wrong?
TIA, Bob
The logic here is a bit confusing, and I'm having trouble trying to understand what you're trying to do.
If I understand, you're trying to count the number of rows when [Finding_SixMonth] is greater than 179, and only for Mktg?
Use Mktg_6Month, set the aggregation on that to total, and pull that into a singleton. Does your logic also rely on other data items in the query?
Thank you CognosPaul.
I have many years of Microsoft-to-Excel automation experience automation Access data to Excel files. I began studying Cognos on 14 Dec 2016. I always wanted to emulate Access-to-Excel automation within Cognos. Apparently Singletons in a Table does that.
I discussed that possibility in cognoise maybe 2 months ago, and was "encouraged" not to do that.
Actually it works well except for some of the aggregations. It has "too many' queries and have not looked up the max number of queries capable in Cognos. Last week I set
Detail Aggregation as Not applicable
Summary Aggregation as Calculated
to give the correct answer, in View tabular data, but dragging in as a Singleton results in 0 or 1 whenever run.
There are many Business Groups such as Mktg, w/ individual queries to capture correct Singleton values.
Unfortunately I have been using many queries to arrive at a trusted value. I should be able to write more efficient queries as I gain experience.
I cannot test this now as someone is being assigned this project and I need to let him handle that.
Quote from: Cognos_Jan2017 on 20 Nov 2018 12:22:40 AM
I began studying Cognos on 14 Dec 2016.
What time of day did you start? :)
MF.
There are some dates people remember as a sign of respect for being introduced to something. Cognos is one of those days.
CognosPaul, from yesterday ... Just reread and fully understand for to do. Will be most helpful now, and for future Singletons.
I will be sure the other person being assigned this Report understands how to do this.
Just gave you an "Applaud".
Thank you again, and have a Happy Thanksgiving, Bob
From CognosPaul ...
Quote[/If I understand, you're trying to count the number of rows when [Finding_SixMonth] is greater than 179, and only for Mktg?
Use Mktg_6Month, set the aggregation on that to total, and pull that into a singletonquote]
CognosPaul ... I came into the office Thanksgiving afternoon before the "other probable assigned Author"
sees this Report.
It WORKS fine. THANK you again, Bob
Errant quote correction ...
From CognosPaul ...
QuoteIf I understand, you're trying to count the number of rows when [Finding_SixMonth] is greater than 179, and only for Mktg?
Use Mktg_6Month, set the aggregation on that to total, and pull that into a singletonQuote
CognosPaul ... I came into the office Thanksgiving afternoon before the "other probable assigned Author"
sees this Report.
It WORKS fine. THANK you again, Bob