Count Function (ability to reference Member Set in Column

[godawgs85]

Hi Everyone,

I have a dilemma I am facing.  Ultimately I am trying to create a count function in a crosstab column which will count the descendants of whatever member is in the applicable row.  For example, here is the function set up as a data item in a column:

count (1 within set descendants ([Management Company],1))

So if have Management Company as the specified member in the applicable row, it gives me the appropriate # of descendants.  If I have another member from a separate heirarchy, however, such as [Brand], the function gives me an error obviously.  My question is is there a way to reference the member in the row rather than having to explicitly define the member within the count function?

Thanks!

[MFGF]

Hi,

The big question here is how you are substituting the member in the row - is it a fixed member dragged in from the package tree, or are you using some sort of prompt to drive the member?  If you're using a prompt, you could change approach to make it a macro-driven expression which builds the MUN of the desired member, then use this calculated member in your count function?

Just a thought...

MF.

[godawgs85]

That's a good idea, but the row values are static (from the package tree) and won't typically change.  I could look into this though.  Do you think there any way to get the count function to reference different members using a case or if/function?

Thanks!

[CognosPaul]

Can you post the report XML or a screenshot of how your crosstab is set up?

[godawgs85]

Attached is a screenshot.  As you can see, the # of Hotels Column is the data item in question in which I'm using the count function.  Obviously I get the correct count for whatever member I use in the function (i.e. [Management Company]), but I would like for the column to have the ability to refer to whatever member is in the row and perform the count of descendants off of that member.  My conflict has been to have the ability to refer to multiple members or something similar to currentMeasure but in regards to members.

Thanks!

[godawgs85]

Attached is a screenshot.  As you can see, the # of Hotels Column is the data item in question in which I'm using the count function.  Obviously I get the correct count for whatever member I use in the function (i.e. [Management Company]), but I would like for the column to have the ability to refer to whatever member is in the row and perform the count of descendants off of that member.  My conflict has been to have the ability to refer to multiple members or something similar to currentMeasure but in regards to members.

Thanks!

Does anyone know if this can be done?

Thanks!

[CognosPaul]

 :-[ I forgot about this...

Unfortunately Cognos doesn't have decent enough context awareness to easily support something like this. Depending on the cube technology, maybe you can make a custom measure for this?

One possible solution:

Create a data item for each hierarchy. Drag them into the columns as peers. Select all of the intersections, except for the correct intersections, and set box type to none. Select all of the column nodes and do the same. This will push all of the elements to the left, giving the appearance of the functionality you need.

But if you need to reference that in another calculation, we'll need to think of something else.

[godawgs85]

Thanks Paul,


That's a good idea but would it have the same functionality when exported to excel?

I think what I might move forward with is to create multiple crosstabs for each hierarchy, that way I can use similar "# Hotels" data items in each crosstab, just with different members in each count function.  It will also give me the ability to refer to it in other calculations if necessary.  These overall hierarchies won't ever change (the underlying # of hotels might), so I should be ok in doing that.  It's a little more work than one crosstab but would give me better efficiencies in getting the count function to work.  I was just wondering if it could be acheived in one crosstab.  I think it ultimately can but might be a lot harder than necessary.

I appreciate your insight.  Thanks

[CognosPaul]

Formatting, such as setting the box-type to none, will carry over to Excel. Unless you do need to reference the # Hotels in another expression, making separate crosstabs won't improve the efficiency at all.

See what happens when you try my suggestion, it should work.