Working w/ Singletons in a Large Table Used As a Report

[Cognos_Jan2017]

2 Questions, as shown in attached Excel file.  Pasting that, which will likely
lose formatting ...  Hoping to LEARN from this ....

FY 2018   Target  FY 2017
'Cell A2'     0.91                     
     59     46           57

Am Using a Table as a Report populating Table Cells w/ Singletons from many Queries.

Cell A2 above is a calculation (that runs to a List Report) that works properly.

The Calculation for that 'Current' (in Cell A2) is ...
([Q_TPRR].[CR_CurFY] * 200000) / [Q_TPRR]. [Ho_CurFY] ---> 'Ho_CurFY' works fine.

[Q_TPRR} is the Query, and 'CR_CurFY' (in Cell A3) is a Case When result
based on a Selected Date and selected Business Group(s).

Trying to drag in 'Current' to Cell A2 as a Singleton results in "0"
and changes the values in Cells A3 and C3 to "0".
            
Thoughts on how to correct?
=================================================   
Also, tried to set a Style Variable to Green or Red based on the Singletons in Cells A3 and C3.
                        
Cell A3 is 'Singleton4' and Cell C3 is 'Singleton20'         

Trying a Case when of EITHER ...
Case
When [Singleton4] <= [Singleton20] Then 'Green'
When [Singleton4] > [Singleton20] Then 'Red'
End      FAILS
                        
Case
When [Q_TPRR].[CR_CurFY] <= [Q_TPRR].[Target] Then 'Green'
When [Q_TPRR].[CR_CurFY] > [Q_TPRR].[Target] Then 'Red'      
End      FAILS
      If the item exists in a query but is not referenced in the layout, add it to a property list.
      The Reporting Object is a Table ... can the above items of      
      [Q_TPRR].[CR_CurFY] and [Q_TPRR].[Target] be added to a Table's Property List?

[bdbits]

I think I would probably union the query results and use a regular data container. It is going to be a lot easier to work with and style.

[Cognos_Jan2017]

Thank you.

The Mgr wants the Overall Report to look like an Excel file, and with
the many different Queries we have we cannot use something like
Microsoft Access-to-Excel automation to populate an Excel file.

We have many Lists and Crossabs now.  Visualization is not popular for the
Manager's Summary Report ... he wants an Excel file with one Worksheet summary.

We are a subscriber to a Cloud SQL Server App, which uses Standalone Calculations.
Our code does an excellent job working off a Selected Date, then calculating the
Current FY values (even if one day) and the Previous FY values.

The Table design w/ Singletons formats surprisingly well, and then running the Report
thru Cognos to Excel is ultra cool.

Now if we can just figure out to get around the two potholes we have.

[rteruyas]

....
Also, tried to set a Style Variable to Green or Red based on the Singletons in Cells A3 and C3.
                        
Cell A3 is 'Singleton4' and Cell C3 is 'Singleton20'         
Trying a Case when of EITHER ...
Case
When [Singleton4] <= [Singleton20] Then 'Green'
When [Singleton4] > [Singleton20] Then 'Red'
End      
FAILS
                        
Case
When [Q_TPRR].[CR_CurFY] <= [Q_TPRR].[Target] Then 'Green'
When [Q_TPRR].[CR_CurFY] > [Q_TPRR].[Target] Then 'Red'      
End      
FAILS
      
If the item exists in a query but is not referenced in the layout, add it to a property list.
The Reporting Object is a Table ... can the above items of      
[Q_TPRR].[CR_CurFY] and [Q_TPRR].[Target] be added to a Table's Property List?

Since table is not a data container, you cannot add properties to it.

If [CR_CurFY] and [Target] have the same underlying query (I believe so from your example)...

- Go to your query [Q_TPRR] and add a new data item [Target] - [CR_CurFY] (let's call it newColumn)
- Select cell "A3" and set [newColumn] as a property for that singleton
- Select cell "C3" and set [newColumn] as a property for that singleton
- Now you should be able to work with styles for those two cells using [newColumn]

Let me know if this works for you
Good luck!

[Cognos_Jan2017]

Since the table is not a data container, you cannot add properties to it.
I have a couple of suggestions, both of them should work I think.

Thank you,.Yes, I was afraid about a Table not being a data container

===============================================
[Singleton4] and [Singleton20] DO both have the same underlying query ...

I will try your recommendation, and let you know its results.

[Cognos_Jan2017]

rteruyas ...
Your Post on working w/ Singletone4 and Singleton20 has disappeared from this thread.
Please try adding that Post again.  thank you.

THANK you for the suggestion on working w/ the Q_TPRR query.
I will also try that and let you know the result.

[Cognos_Jan2017]

I went to the Query Q_TPRR and added a New Data Item 'NewColumn'. Its Expression Definition is ...
[GMVVTarget] - [CR_CurFY]

Cell "A3" is 'Singleton4' which is the Data Item 'Cur_CurFY' dragged into the Singleton.
Selecting 'Singleton4' when managing the Query 'Q_TPRR' selects Singleton4's properties
which allows adding 'NewColumn' to its Data Properties.

Cell "C3" is 'Singleton20' which is the Data Item 'GMVVTarget' dragged into the Singleton.
Selecting 'Singleton20' when managing the Query 'Q_TPRR' selects Singleton20's properties
which allows adding 'NewColumn' to its Data Properties.

Named a Style Vbl 'VTarget' w/ the Expression Definition of ...
Case
When [Q_TPRR].[CR_CurFY] <= [Q_TPRR].[GMVVTarget] Then 'Green'
When [Q_TPRR].[CR_CurFY] > [Q_TPRR].[GMVVTarget] Then 'Red'
End

That Style Vbl validates. I then assigned 'Green' and 'Red' to Cell "A3" in the Table.

All was fine UNTIL when running the Report ...

It resulted in the error ...
The following expression is not valid: Case
When [Q_TPRR].[CR_CurFY] &lt;= [Q_TPRR].[GMVVTarget] Then &#39;Green&#39;
When [Q_TPRR].[CR_CurFY] &gt; [Q_TPRR].[GMVVTarget] Then &#39;Red&#39;
End. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error occurred at or near the position &#39;10&#39;. The variable named &#39;[Q_TPRR].[CR_CurFY]&#39; is invalid.

Data Item [CR_CurFY] does exist in the Query [Q_TPRR].

Thoughts?  TIA, Bob

[Cognos_Jan2017]

Just tried ...
Case
When [Q_TPRR].[NewColumn] >= 0 Then 'Green'
When [Q_TPRR].[NewColumn] <0 Then 'Red'
End

... which results in the error ...
The following expression is not valid: Case
When [Q_TPRR].[NewColumn] &gt;= 0 Then &#39;Green&#39;
When [Q_TPRR].[NewColumn] &lt;0 Then &#39;Red&#39;
End. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error occurred at or near the position &#39;10&#39;. The variable named &#39;[Q_TPRR].[NewColumn]&#39; is invalid.

Hopefully, we are very close to solving this.

Thoughts?  TIA, Bob

[rteruyas]

Just tried ...
Case
When [Q_TPRR].[NewColumn] >= 0 Then 'Green'
When [Q_TPRR].[NewColumn] <0 Then 'Red'
End

... which results in the error ...
The following expression is not valid: Case
When [Q_TPRR].[NewColumn] &gt;= 0 Then &#39;Green&#39;
When [Q_TPRR].[NewColumn] &lt;0 Then &#39;Red&#39;
End. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error occurred at or near the position &#39;10&#39;. The variable named &#39;[Q_TPRR].[NewColumn]&#39; is invalid.

Hopefully, we are very close to solving this.

Thoughts?  TIA, Bob

Yes @Cognos_Jan2017, you're on the right track!
You're just missing one extra step:

You cannot reference a data item outside the scope of the container
In other words, you cannot use NewColumn to style a table cell because the cell you're trying to format cannot know what value is set for NewColumn.
If you look at hierarchy, it should show something like
   table > table cell > singleton > text item

Try to set the style variable "VTarget" for the text item (inside the singleton) instead of the table cell.
That will do the trick

Good luck!

[rteruyas]

Just added a little example
Regards

[Cognos_Jan2017]

rteruyas ...

You cannot reference a data item outside the scope of the container
In other words, you cannot use NewColumn to style a table cell because the cell you're trying to format cannot know what value is set for NewColumn.
If you look at hierarchy, it should show something like
   table > table cell > singleton > text item

Try to set the style variable "VTarget" for the text item (inside the singleton) instead of the table cell.
That will do the trick

S O L U T I O N !!!

That WORKS !!!  THANk you.  I will be Entering an "Applaud" for you !!!

Will post result of the "calculations" test below ...
I will try that technique to see if that will solve the first part of this Topic Question ...

Am Using a Table as a Report populating Table Cells w/ Singletons from many Queries.

Cell A2 above is a calculation (that runs to a List Report) that works properly.

The Calculation for that 'Current' (in Cell A2) is ...
([Q_TPRR].[CR_CurFY] * 200000) / [Q_TPRR]. [Ho_CurFY] ---> 'Ho_CurFY' works fine.

[Q_TPRR} is the Query, and 'CR_CurFY' (in Cell A3) is a Case When result
based on a Selected Date and selected Business Group(s).

Trying to drag in 'Current' to Cell A2 as a Singleton results in "0"
and changes the values in Cells A3 and C3 to "0".
           
Thoughts on how to correct?

[rteruyas]

Glad to hear it worked!
Let me know about the other issue. I might be able to check it tomorrow after work

[Cognos_Jan2017]

The second part.  Example ...
There is a Data Item 'CurTPRR' which has this Expression ...
([Q_TPRR].[CR_CurFY] * 200000) / [Q_TPRR]. [Ho_CurFY]

IF I drag in 'CR_CurFY' OR 'Ho_CurFY' individually (as Singletons),
they work.  Trying to drag in BOTH displays one true value, but the other Singleton value
goes to 0.

Trying to drag in the Data Item 'CurTPRR' results as 0,
and changes an existing Singleton for 'CR_CurFY' to 0.

I set the Data Properties in the Singletons to the appropriate Data Item from the Query
'Q-TPRR'.  Assigning, as an example. 'CR_CurFY' to the Text Item still does not work.

I have plenty of "Single" Data Items (NO calculation) to drag in from many different
Queries to keep me busy until you might be able to examine this tomorrow.

Will DEFINITELY welcome you testing this tomorrow after work.

THANK you again, Bob

[rteruyas]

I did a small test with your scenario and I got the expected results for the singleton (that uses a calculation).
Perhaps you need to check your aggregate functions.
If you right click on the query and select "Show tabular data", do you get the expected result for CurTPRR?

[Cognos_Jan2017]

Thank you.

If you right click on the query and select "Show tabular data", do you get the expected result for CurTPRR?

CurTPRR's Query is being used in both a Full Report on a separate Page, plus the Table on another Page.
See below for ... Trying "View Tabular Data"

Running the Report (and the Query) results in CurTPRR's correct value in the Full Report,

The Table Singleton holding CurTPRR has its data property set to Q_TPRR's CurTPRR, and
the Text Item is set to CurTPRR.

I tried combinations of Default and Calculated for the Detail and Summary Aggregations,
but still the Singleton for CurTPRR displays 0.00 ... and the previously working Singletons
referring to the Numerator in the Expression for CurTPRR in the Table Row directly below
have their Singletons changed to 0.

Trying "View Tabular Data" results in the error ...
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 2,724: Syntax error near "=".
RSV-SRV-0042 Trace back:
RSReportService.cpp(736): QFException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(253): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_Request
.................................

Will continue trying scenarios.

[Cognos_Jan2017]

P O S S I B L E Insight ...

The Query 'Q_TPRR' is an older Query that has worked VERY well for over a year.  We used _maketimestamps by dragging
in lots of different Data Items like [Calendar Year], [Calender Year Month Number], etc, to determine the Number of Months
between the Selected Month Ending Date which retrieves Standalone Calculation values for each month.

In a List, after seeing how each Month's value from the Standalone Calculation displays properly, we then
Cut Data Items until We "Group Totals" of those Standalone Calculations.  That is in a LIST. and
works VERY well.

In our Table utilizing Singletons, there is no reference to Calender Year Date, etc, so we are now working w/ Current_Date,
and adding new Data Items to the 'Q_TPRR" Query so that Query works properly w/ BOTH a List and a Table.

Am fortunate to be working on other Projects BUT will find time to test all this next week.

rteruyas ...  Because your Post earlier today confirmed calculations will work w/ Singletons
we knew we had done something incorrectly.

I expect to solve this, and hopefully give full details by Friday, 5 Oct 2018.

THANK you for your help, anf have a good weekend.

[Cognos_Jan2017]

C U R R E N T   I S S U E ...

The existing code for the Query Q_TPRR utilizes a List's Rows to show
each month's entries for a Standalone Calculation of [Cases].

To show those monthly entries, we add 2 Data Items from Framework Manager of ...
1 - [Calendar Year]
2 - [Calender Year Month #]

We add a Data Item of [LTMonths] which is ...
_months_between ([SelMMYY],[TimeL])

The Data Items of [SelMMYY] and [TimeL] are ...
[SelMMYY] ... _make_timestamp (extract(year,?p_EndDate?),extract(month,?p_EndDate?),1)
[TimeL] .... _make_timestamp ([Calendar Year],[Calendar Year Month #],1)

The Parameter p_EndDate is a Date Prompt.

With these 3 Data Items appearing in the List ...
1 - [Calendar Year]
2 - [Calender Year Month #]
3 - [LTMonths}
... we then have several Data Items, such as ...
[CurFY] ...

Case
When (extract(month,?p_EndDate?)=10 and [Q_TPRR].[LTMonths] = 0)  then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=11 and [Q_TPRR].[LTMonths] between 0 and 1) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=12 and [Q_TPRR].[LTMonths] between 0 and 2) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=1 and [Q_TPRR].[LTMonths] between 0 and 3) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=2 and [Q_TPRR].[LTMonths] between 0 and 4) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=3 and [Q_TPRR].[LTMonths] between 0 and 5) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=4 and [Q_TPRR].[LTMonths] between 0 and 6) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=5 and [Q_TPRR].[LTMonths] between 0 and 7) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=6 and [Q_TPRR].[LTMonths] between 0 and 8) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=7 and [Q_TPRR].[LTMonths] between 0 and 9) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=8 and [Q_TPRR].[LTMonths] between 0 and 10) then [Q_TPRR].[Cases]

When (extract(month,?p_EndDate?)=9 and [Q_TPRR].[LTMonths] between 0 and 11) then [Q_TPRR].[Cases]
End

When we cut the Data Items ...
1 - [Calendar Year]
2 - [Calender Year Month #]
3 - [LTMonths}
... from the List that "Adds Together" all the [Cases] values.

A Singleton shows the "First Value" in a Query.  Here is THE problem ... I have many
years programming Microsoft Access Queries and then automating a Result to an Excel Cell
Similar to what we need to do by adding a Cognos Singleton to a Cognos Table Cell.

Learning time for me.  How do we "Group By" to arrive at a "Total" which
becomes a single value for a Singleton?

Before I test, that could be a Summary Filter, but we need to add that to code,
as an example ...
When (extract(month,?p_EndDate?)=9 and [Q_TPRR].[LTMonths] between 0 and 11) then [Q_TPRR].[Cases]

Perhaps this is what needs to be replaced??
... [Q_TPRR].[LTMonths] between 0 and 11)

Hopefully, this is simple?

TIA, Bob

[Cognos_Jan2017]

Before I test, that could be a Summary Filter, but we need to add that to code,
as an example ...
When (extract(month,?p_EndDate?)=9 and [Q_TPRR].[LTMonths] between 0 and 11) then [Q_TPRR].[Cases]

Perhaps this is what needs to be replaced??
... [Q_TPRR].[LTMonths] between 0 and 11)

Thought about at Lunch ...
Should be able to write a Data Item utilizing timestamps to "between".
Will test and report results.

[Cognos_Jan2017]

It looks like new Data Items, which are dragged in as Singletons MAY work.

Have tested two so far.  An example of one is ...
Case
When ([Q_TPRR].[Month Begin Date] >= [G_CFY_StartDate] and [Q_TPRR].[Month End Date] <= ?p_EndDate?) Then [Q_TPRR].[Cases]
End

Our Fiscal Year begins on 1 October ... coincidentally that is today.
The key is the Data Item [G_CFY_StartDate] ...
Case
When (extract(year,?p_EndDate?) = [Fiscal Year]) Then
_Make_timestamp (extract (year, _add_years(?p_EndDate?, -1)) ,10,1)
End

We need to "From - To" from [Month Begin Date] to [Month End Date].

We have other Data Items to filter for one of three Business Groups.
Data Item example for that is ...
Case
When (([Q_TPRR].[Month Begin Date] >= [G_CFY_StartDate] and [Q_TPRR].[Month End Date] <= ?p_EndDate?) and [BusinessGroup] = 'AAA') Then [Q_TPRR].[Cases]
End

Will begin doing more of the MANY Data Items tomorrow.  Optimistic this WILL work.

[Cognos_Jan2017]

N E W   P R O B L E M ...

A Data Item such as ...
Case
When ([Q_TPRR].[Month Begin Date] >= [G_CFY_StartDate] and [Q_TPRR].[Month End Date] <= ?p_EndDate?) Then [Q_TPRR].[Cases]
End
... does NOT work and results in 0.

The Data Item is intended to Total ]Cases] (a Standalone Calculation) between Fiscal Years Dates of a Start Date of 10/1/2017 and an End Date of 9/30/2018, but it does NOT.

I will further examine the design of the 3rd Party design of the Standalone Calculation of [Cases] and drag in all Date Fields.  It is possible their design parses the values based on [Calendar Year] and [Calendar Month].  If so, actual Dates of 10/1/2017 and 9/30/2018 will not work.  The previously mentioned "List Design" I utilized worked based on [Calendar Year] and [Calendar Month].

Thinking simple SQL, wouldn't something like (Microsoft Access SQL) "SELECT [Cases] From [The Table Name] Between [Month Begin Date] and [Month End Date];" ... work?

More later ...

TIA, Bob

[Cognos_Jan2017]

Attached Excel file shows structure of Date-type Fields and the Standalone Calculation of [Cases].

The Query "may" work if I can successfully convert the _make_timestamp to a Date.  Have tried toDate() and Cast([TheDate], Date)_ but not working.

Sample paste from the attached file ...
Level 1 Name   Month Name   Month Begin Date   Month End Date   Calendar Year   Calendar Year Month #   Fiscal Year   Cases
AAA   October   Oct 1, 2017   Oct 31, 2017   2017   10   2018   2
AAA   November   Nov 1, 2017   Nov 30, 2017   2017   11   2018   4

Ideas?

TIA, Bob