 | |
Re: Selected Items
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Selected Items -
11-15-2005
, 07:29 PM
|
Hi, I am developing a new calculated measure in ProClarity. That measure must contemplate the following scenario: With the selected items in the time dimension, get the first and last item, and subtract the "last measure value" with "first measure value". Example: If in ProClarity I select moths 10,11,12 it must subtract (value in month 12) - (value in month 10). My problem really is how to obtain the selected items, to apply head and tail and do the calculation. (I am using SQL Server 2000 + ProClarity 6) Can anybody help? Thanks. Fernando Marçal |
#2
| |||
| |||
|
|
Have you tried posting this on the ProClarity community? I find them very helpful. Although admittidly your question is an MDX one and it is irrelevant that your frontend is ProClarity. Anyway, sorry I can't help more but to direct you to the community. "Fernando Marçal" <FernandoMaral (AT) discussions (DOT) microsoft.com> wrote in message news:0540B816-6ACC-4FD0-AC66-B9B8777C8724 (AT) microsoft (DOT) com... Hi, I am developing a new calculated measure in ProClarity. That measure must contemplate the following scenario: With the selected items in the time dimension, get the first and last item, and subtract the "last measure value" with "first measure value". Example: If in ProClarity I select moths 10,11,12 it must subtract (value in month 12) - (value in month 10). My problem really is how to obtain the selected items, to apply head and tail and do the calculation. (I am using SQL Server 2000 + ProClarity 6) Can anybody help? Thanks. Fernando Marçal |
#3
| |||
| |||
|
|
There's no problem with proclarity. What I really wanted is a way to specify in MDX the various selected items. I've solved in Proclarity by defining a set that is referenced in the measure, but I would like to skip the set part and get directly the selected items in the mdx query. (It's no desirable that the users have to edit the set everytime...) The problem is that currentmember is not enough! "Darren Gosbell" wrote: In fact I think it may a proclarity issue. When you say "Selected" do you mean rows/columns highlighted on the screen? If so this is probably a proclarity issue as the MDX would be unaware of this selection. If this is the case I think the proclarity community would be your best bet. -- Regards Darren Gosbell [MCSD] Blog: http://www.geekswithblogs.net/darrengosbell In article <#ca##0k6FHA.3592 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, ccpatriot12 @yahoo.com.au says... Have you tried posting this on the ProClarity community? I find them very helpful. Although admittidly your question is an MDX one and it is irrelevant that your frontend is ProClarity. Anyway, sorry I can't help more but to direct you to the community. "Fernando Marçal" <FernandoMaral (AT) discussions (DOT) microsoft.com> wrote in message news:0540B816-6ACC-4FD0-AC66-B9B8777C8724 (AT) microsoft (DOT) com... Hi, I am developing a new calculated measure in ProClarity. That measure must contemplate the following scenario: With the selected items in the time dimension, get the first and last item, and subtract the "last measure value" with "first measure value". Example: If in ProClarity I select moths 10,11,12 it must subtract (value in month 12) - (value in month 10). My problem really is how to obtain the selected items, to apply head and tail and do the calculation. (I am using SQL Server 2000 + ProClarity 6) Can anybody help? |
#4
| |||
| |||
|
|
And the set is in the background so axis wont do it. |
This probably means that it is being passed in the WHERE clause as a
filter condition. In AS2k you can only put single members in the WHERE
clause, the only way to filter on multiple members in AS2k is to
aggregate the set into a single calculated member and use this in the
where clause. AS2k5 will allow you to put a set in the WHERE clause, but
it basically does an automatic aggregate on the set so you can't really
do a .CurrentMember or equivalent there either.
Unfortunately I am pretty confident that there is no MDX solution to
this issue.
Can you investigate the Proclarity angle further? I don't that much
about it, but if you could intercept and pre process the MDX before it
was sent to AS you might be able to build a set dynamically. Or could
you change your single date drop down into 2 single select date lists
that you can use to build the date range? As I said, I don't know much
about Proclarity and these questions are probably better asked on their
forums
HTH
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <8B510029-912F-4DD5-8E58-2B578C596219 (AT) microsoft (DOT) com>,
FernandoMaral (AT) discussions (DOT) microsoft.com says...
Quote:
|
And the set is in the background so axis wont do it. thx. "Fernando Marçal" wrote: When you select a member, say 1997, in ProClarity, that becomes the currentmember in MDX. The problem is that I want to select multiple items, say Jan, Feb, Mar, and I wanted to get that multiple selection inside MDX. As you imagine, CurrentMember wont solve it. thx. "Darren Gosbell" wrote: I we may be using the word "selected" differently here. When you say "selected", do you mean members a user clicks on to highlight? If you do then you need to get the user interface to tell you what is "selected". If by "selected" you mean the set of members that is visible on the rows or columns have you had a look at the Axis() function? HTH -- Regards Darren Gosbell [MCSD] Blog: http://www.geekswithblogs.net/darrengosbell In article <D7CF64F3-470F-4FBB-B872-0466C06B3575 (AT) microsoft (DOT) com>, FernandoMaral (AT) discussions (DOT) microsoft.com says... There's no problem with proclarity. What I really wanted is a way to specify in MDX the various selected items. I've solved in Proclarity by defining a set that is referenced in the measure, but I would like to skip the set part and get directly the selected items in the mdx query. (It's no desirable that the users have to edit the set everytime...) The problem is that currentmember is not enough! "Darren Gosbell" wrote: In fact I think it may a proclarity issue. When you say "Selected" do you mean rows/columns highlighted on the screen? If so this is probably a proclarity issue as the MDX would be unaware of this selection. If this is the case I think the proclarity community would be your best bet. -- Regards |
#5
| |||
| |||
|
|
And the set is in the background so axis wont do it. Aah... I think the light bulb just went on This probably means that it is being passed in the WHERE clause as a filter condition. In AS2k you can only put single members in the WHERE clause, the only way to filter on multiple members in AS2k is to aggregate the set into a single calculated member and use this in the where clause. AS2k5 will allow you to put a set in the WHERE clause, but it basically does an automatic aggregate on the set so you can't really do a .CurrentMember or equivalent there either. Unfortunately I am pretty confident that there is no MDX solution to this issue. Can you investigate the Proclarity angle further? I don't that much about it, but if you could intercept and pre process the MDX before it was sent to AS you might be able to build a set dynamically. Or could you change your single date drop down into 2 single select date lists that you can use to build the date range? As I said, I don't know much about Proclarity and these questions are probably better asked on their forums HTH |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.