Singleton is bringing 0 when i ran the Query for 2 company's

[rajua99]

Hello,

In my report i have 100 data items values and i am using 100 singletons in a table to display all those values.
Some of the values will be coming from company 10  and the remaining values will be coming from company 2. So in the variables i hard coded to get the values from company 10 and 2. When i run the report i am seeing values only for company 10 but not for company 2. always company 2 is zero. if i ran the same report just report for company 2 then all the variables with company 2 populated without any issues. When i run the report for both company's i am having this issue.  If i add the company number to the singleton in the same table only 1 company number it is showing. So in a singleton it is passing only 1 company number for the whole table so the data displays only for that company number. Is there a way to force the singletons in a single table to get the data from multiple companies according to the data item value formulas or no...


We are using Cognos report studio 10.1 version

Here are the formulas for your reference with different companies...

IF (( [Month]='01' ) and ([Company Number] <> 2)) THEN
    ([January PY])
ELSE
IF (( [Month]='02' )  and ([Company Number] <> 2)) THEN
    ([February PY])
ELSE
IF (( [Month]='03' )  and ([Company Number] <> 2)) THEN
    ([March PY])
ELSE
IF (( [Month]='04' )  and ([Company Number] <> 2)) THEN
    ([April PY])
ELSE
IF (( [Month]='05' )  and ([Company Number] <> 2))  THEN
    ([May PY])
ELSE
IF (( [Month]='06' )  and ([Company Number] <> 2)) THEN
    ([June PY])
ELSE
IF (( [Month]='07' )  and ([Company Number] <> 2)) THEN
   ([July PY])
ELSE
IF (( [Month]='08' )  and ([Company Number] <> 2)) THEN
    ([August PY])
ELSE
IF (( [Month]='09' ) and ([Company Number] <> 2))  THEN
    ([September PY])
ELSE
IF (( [Month]='10' ) and ([Company Number] <> 2))  THEN
   ([October PY])
ELSE
IF (( [Month]='11' )  and ([Company Number] <> 2)) THEN
   ([November PY])
ELSE
IF (( [Month]='12' ) and ([Company Number] <> 2))  THEN
    ([December PY] )
ELSE
    (0 )


IF (( [Month]='01' ) and ([Company Number] = 2))  THEN
    ([January PY])
ELSE
IF (( [Month]='02' )  and ([Company Number] = 2)) THEN
    ([February PY])
ELSE
IF (( [Month]='03' ) and ([Company Number] = 2)) THEN
    ([March PY])
ELSE
IF (( [Month]='04' ) and ([Company Number] = 2)) THEN
    ([April PY])
ELSE
IF (( [Month]='05' )  and ([Company Number] = 2)) THEN
    ([May PY])
ELSE
IF (( [Month]='06' ) and ([Company Number] = 2)) THEN
    ([June PY])
ELSE
IF (( [Month]='07' )  and ([Company Number] = 2)) THEN
   ([July PY])
ELSE
IF (( [Month]='08' )  and ([Company Number] = 2)) THEN
    ([August PY])
ELSE
IF (( [Month]='09' ) and ([Company Number] = 2)) THEN
    ([September PY])
ELSE
IF (( [Month]='10' )  and ([Company Number] = 2)) THEN
   ([October PY])
ELSE
IF (( [Month]='11' ) and ([Company Number] = 2)) THEN
   ([November PY])
ELSE
IF (( [Month]='12' )  and ([Company Number] = 2)) THEN
    ([December PY] )
ELSE
    (0 )

[Lynn]

If you have two result sets coming from the query, one for company 2 and another row for company 10, and you are trying to use singletons to display the output then you've got the wrong query container. The operative part of "Singleton" is "Single" .... as in sole, lone, one and only one.

The other issue I see here is that each singleton is going to result in a query being fired off to the database so you've got 100 queries happening when you, perhaps, really only need one.

Your if-then-else statement is a mystery to me. The result for a particular month number is exactly the same regardless of company number so what is the point of it? There must be more here that you aren't explaining or else I'm missing it entirely.

Not sure I fully understand what you are trying to achieve but maybe a repeater object is a better container for your purpose, or just associate the query to a page and put stuff wherever it needs to go using a table to organize things.