Crosstab Set Within and BottomCount

[markcognos]

Hi,

Why is it ,when i Use a Total([measures] set within ([Data Item])  in crosstab.
the BottomCount not functioned properly??

[MFGF]

Could it be the solve order which is wrong? Try setting the solve order of the bottomcount() to be higher than the total()

I'm not convinced about your expression for the total, though. Is it from a dimensional package? I would expect to see  total([Measure] within set [your set of members]) as the expression for a dimensional total. If your package is relational, then I would not expect bottomcount() to work anyway.

Regards,

MF.

[markcognos]

Hi my example is this.

------------------------------------------------------
           NEW YORK  | JAPAN         | INDIA         | total
item     case |count|case|count  | case|count |  count
--------------------------------------------------------
book    | 100| 1     | 10   |   1     |  0     |  0    |   2
cup      |   0  | 0     |  8    |   1     |  0     |  0    |   1
pencil   |   9   | 1     |  0   |   0     |  0     |  0    |   1



My data Item in Count is -name of data item is [COUNT] --my query is

if([case]<>0) then
(count(distinct([case])))
else
(0)


My total count Is - name of data item is [Total Count]  -my query is
total([COUNT])

The total([COUNT]) is not working so i used
total([COUNT] within set [area])


If i used  total([COUNT] within set [area])
...my bottomcount not working properly..

Any idea to total the count without using  total([COUNT] within set [area])??
because in my other report im not using within a set becouse my other report has no count,ill just only total the case.

[MFGF]

Sorry - I'm still confused. Are you using a dimensional reporting package or a relational reporting package? Or are these two different questions about reports from different packages?

MF.

[markcognos]

Hi,

Im using relational package.

Thanks

[MFGF]

Bottomcount is a dimensional function and will not give predictable results with relational packages. I'm actually surprised you're getting it to work at all.

MF.

[CognosPaul]

From what I've seen, dimensional functions on relational sources forces Cognos to act as if the package was a DMR. But not well, in fact, I'd say avoid dimensional functions like the plague.

Consider the following query:

Top 2 Ticket Status
Total Count

topCount(TicketStatus,2,TotalCount) works well, and the list is limited to two rows. The SQL (native and cognos) does not contain any filters on  status or count. So my guess is there is some local processing going on.


Status     | Count
-----------+---------
Done       |  12345
Done (Late)|    123


It looks good, but what happens if I add another attribute:

Status     | Factory |  Count
-----------+---------+--------
Done       |  Fac A  | 12,345
Done (Late)|  Fac B  |    123


It is not possible that Factory A opened all of the Done tickets. Further testing shows that it doesn't matter which attribute I use, it will never work.

From my tests it looks like dimensional functions that work with sets and measures only will have very limited functionality, but they will need to be checked and rechecked with every change of the data container. Functions that work with individual members (tuple) or hierarchies (currentMember) do not work at all.

Slicers are ignored and do not effect the query.

The bottom line - when using a relational package, try to use relational concepts to achieve your goal. In my example I would probably write another query that only returns the top two statuses (possibly with the Rank function), and reference that in the main query with NS.Table.Status in (Query2.Status).