Graphic in MDX/cube based Crosstab

[Robl]

I hope you can help.
I'm trying to nest an variable image into the dimension section of an MDX based crosstab.

As an example.
A crosstab with ;
Country and Town as the left rows.
Month as the columns.      (Rolling 12 months)
Next to town is an embedded spark line graph showing the 12 months values for the town.
There are a few other prompt based slicers as prompts on the page which the user can select.

This all works just fine.

But, I need to add a simple arrow graphic to show whether the last 2 months have gone up or down.
This needs to sit just next to the spark line.
I imagine it needs to be the tuple of the last month vs the tuple of the last month -1, compared
But, I just can't seem to get a way to make the report do the calculation to allow me to put the graphic in with the dimension structure rather than with the metrics.

(in fact it doesn't even need to be a graphic, a webdings 5 or 6 will work fine)

Any suggestions or pointers would be most welcome.

[Robl]

32 views and no answers.
Either it's a fiendishly difficult question or it's so patheticly obvious you refuse to dignify it with a response. :)

Please let me know which, just to put my kind at rest.

[BigChris]

I have to admit I read that when you posted it and thought "hmmm...that's tricky". I don't know if it's an option, but would you be able to do anything in the cube itself? I don't know how it's built, but I'm sure I've seen comparisons between current month and previous month built into cubes...

[Lynn]

32 views and no answers.
Either it's a fiendishly difficult question or it's so patheticly obvious you refuse to dignify it with a response. :)

Please let me know which, just to put my kind at rest.

I'm not sure the reason for lack of answer is quite so black and white. People here offer assistance freely based on numerous factors including what time they have available and which questions interest them.

Attached is an XML spec against a sample package that does what I think you've described. I did this by creating a calculation subtracting two periods so the default measure will show either a negative or positive value. I then nested a crosstab space (with fact cells) on the rows and put the calculation in there, unlocked the report, and set it's boxtype to none. We don't need to see it, we just need to have it there to control displaying of the arrow images.

Then, with the report still unlocked, I put two image items into the space. One with a red down arrow and one with a green up arrow. Finally I set a conditional style on each image to render green up arrow when the calculation is greater than zero and another style to render the red down arrow when the calculation is less than zero. In the case of a zero value you'd get neither rendered, but if it were me I'd put in a third image for that scenario. Perhaps a yellow horizontal bar or something.

Hopefully this is enough to send you in the right direction. Good luck.

[Lynn]

Ooops..... :o

The arrow logic is flip-flopped because I did the calculation backwards, but I think you can get the idea despite my moment of dyslexia  ::)

[Robl]

That's great.
Thanks very much.

[Robl]

I've made a start with what you suggested but I've hit another issue.
Because my report uses several measures in the crosstab and because the measure is a max measure I've had to use the following calculation for the +- item.

"maximum ([Actual] within set [Performance Cube].[Calendar Date].[Current Month])
-
maximum ([Actual] within set [Performance Cube].[Calendar Date].[Last Month])
"

This works just fine and displays in the crosstab space exactly as I need it to.
The problem is that when I try and use it in any sort of conditional format I get the following error.

Invalid expression [Main Report].[Actual Curr - Last] = 0. CRX-API-0006 The variable value at ( 5,119 ) is not valid.

I'm guessing the error is based on something to do with my calculation using a set or not using the default measure - perhaps?
If you could suggest any other direction to look I'd be most grateful.

[cognos810]

Hello Robl,
A data item that is being referenced in a condition variable, should be in the container. Or, included in the "properties" property. In a crosstab you can try to add it as a property for any one of your Row dimensions. In your case the data item, [Actual Curr - Last] should either be in the crosstab or should be selected as a "properties" property for one of the dimensions.

-Cognos810