Hi
So, I have a problem where by I have a DMR in FM. Its a pretty straightforward model - Basically a hierarchy for location, and a few measures.
The problem starts because each measure has a RAG banding so that I am able to put in some conditional formatting. For example a measure of GWP may be given a Red background if sales are less that 1m, Amber if between 1 and 2m and Green if over 3m. To this end, a table has been created that they can update, so I have put this table in FM and I try to reference it in the report. I set these up as a lower and upper total for each banding.
Initially I set these up as attributes, but as each measure has different values for each band, it splits each measure onto a different row, which obviously I don't want. Next I set them up as Measures, but then as you drill up and down the Hierarchy the banding changes, whilst the measures do not!
So I suppose the question is, can I stop the Rag numbers from Rolling up - I noticed there is an external rollup in Transformer, but sadly I can't use that software.
I was also surprised that I needed to have the RAG info in the report (not the layout) to use it for conditional formatting. Is there anyway around that? Do I maybe need to set up a data item for each column which returns a value and base by conditional formatting on that?
Any advice would be very gratefully received. Any advice on where I am going wrong.
Cheers
Hi again,
For info I found a solution - I don't like it, but it really was ugly scenes here at Garfunkal Towers and I needed get it done.
So there is not just a problem and no solution (don't you hate it when you do a search and find a problem just like yours but no reply and you are all 'ah man - that is EXACTLY what I need'), here is what I had to do.
I had to set up a prompt page, and put on it a prompt for each of the Rags. The drop down then referenced the Rags. I then stole some javascript to automatically choose the rag needed from the drop down. I then set up the queries using a 'Value between ?rag1? and ?rag2?' type thing. Then I hid the dropdowns so the user thinks the prompt page is just a welcome screen.
Like I say, not very clean but I was desperate.
If anyone has a better solution or idea then I'd love to hear it for future reference.
cheers