Singleton???

[nanoha]

Hi,

Anyone could help me understand this:
Scene 1:
Drag a date member from the cube into the query, say '20130806'. (I'm using DMR)
Create a new data item with the expression 'prevMember([20130806])'.
Drag the two data items above into a singleton and run the report. Nothing would display.
Scene 2:
Create a data item with the expression '333' and name it '333'.
Create a data item with the expression [333] + 1
Drag the two data items into a singleton and run the report. I would display '333334'

Could anyone give me a clue? I'm totally confused :-(
Thx

[Lynn]

A singleton is intended to return a single thing, not two things. It is going to show the first row value of the query (according to the documentation).

If you put only your date member or only your prevMember data item then I imagine it would display one either just fine. Sounds like your 333 and 334 derived data items are both getting in there but just pushed together so the two values appear as one.  Put a text item between (with a space or a hyphen or something) to see if that is what is happening.

What are you trying to achieve?

[nanoha]

Hi Lynn,

Thanks for the quick reply!
1. If anything is put in between the '333' and '334', both items would still appear.
2. I was trying to create a bold status panel showing both the current date and the comparison date. To me singleton was a 'safe' solution, or otherwise I may need to create a master-detail relationship to display my text expression, which I'm not so familiar with. When I tried to reference the data item into the layout using an expression text item, cognos said I need to add it into a layout property.  The problem is that I have only one query for this report and I've no idea how to create a master-detail relationship with... the query itself?
Not sure whether I'm clarifying the situation effectively...
Thanks anyway. Any suggestions are appreciated.

Nanoha

[Lynn]

Still not understanding the purpose of 333 and 334. If you don't want both of them to appear then only include the one you want.

As for the two dates, you can associate your query to the page and then drag the desired items anywhere you like. You can create a master-detail with the query itself. In reality the query is being executed twice. Select the crosstab that is displaying your report data and then click master-detail from there.

Or go with the singleton approach but just use two singletons....one for each date.

Or just create a separate query for your dates and associate that to some layout for display purposes.

[nanoha]

I didn't know master-detail relationship can be applied to the same query. Thank you!
By the way, two singletons in a same query to display two dates won't work.
I was using the 333 example to show a singleton would display two string or integer data items but refuse two date data items. That's it.
Thanks Lynn!

[Lynn]

You aren't using a date data items ... you are using members. Try the same thing with two members from any other dimension in your model and you'll see the same thing.

Singletons are expecting one thing, hence the name singleton. I suspect the generated SQL would be quite different when using actual members from any dimension vs using totally derived/hard coded string or text expressions.

[nanoha]

Ohhh sorry, I used an inappropriate comparison >_<
And you are right, here is the sql generated by Cognos for the '333' example:

select
       333  as  Data_Item1,
       (333 + 1)  as  Data_Item2
from
       (values('all_level')) "Query1.0"(c1)

It's totally different with accessing members in a cube. Thanks a trillion!
I will check the SQL next time when I feel confused.

Nanoha