Named Set in Excel Pivot Table Displays as Enumeration

Pardon me if this is really an Excel 2007 Pivot table question. I have a named set defined in the cube as:

CREATE SET CURRENTCUBE. [Team Building Overall]

AS {[Question].[Short Name].[Q-02],

[Question].[Short Name].[Q-03],

[Question].[Short Name].[Q-05],

[Question].[Short Name].[Q-06],

[Question].[Short Name].[Q-08],

[Question].[Short Name].[Q-13],

[Question].[Short Name].[Q-17],

[Question].[Short Name].[Q-18],

[Question].[Short Name].[Q-21],

[Question].[Short Name].[Q-30],

[Question].[Short Name].[Q-31]};

And can display measure values for this set as whole in Proclarity with:

SELECT { [Measures].[Average]} ON COLUMNS ,

{ [Corporate Hierarchy].[Hierarchy].DEFAULTMEMBER } ON ROWS

FROM [360 Training Survey]

WHERE ([Team Building Overall], [Test Month].[Test Month].&[200704] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

In Excel 2007, unfortunately I can not use this set as a filter as I can in Proclarity. Rather, the best I can accomplish is to display this as 11 different columns, one for each member of this named set.

Any suggestions ?

Anyone ?

I figure I can create a calculated measure in the cube named [Team Building Overall Average] which is defined as ([Team Building Overall],[measures].[Average]), but this seems a bit klutzy, especially given that I have eight sets and six measures, thus resulting in a total of 48 calculate measures I need to create.

|||

You could create a measure independant calculated member in the Question dimension as follows.

CREATE MEMBER CurrentCube.[Question].[Short Name].[Team Building Overall] AS AGGREGATE([Team Building Overall])

If the only reason that you have the sets is for filtering you could put the set definition inline in this memeber, otherwise you could leave them separate.

Mysterious query problem?

Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, it updates
every single record in the lta table, what's going on here? any
thoughts?

1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe

2.)
update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!

update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe|||On 10 Jan 2005 07:25:14 -0800, Roy wrote:

>Hey all,
>strange problem here... query #1 displays 357 records correctly and all
>is well. However, when placed within query #2 as a subquery, it updates
>every single record in the lta table, what's going on here? any
>thoughts?
>
>1.) select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe
>
>2.)
>update lta
>set lta.LL_RCVD = 'N'
>where exists (select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe)

Hi Roy,

The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.

You probably need

UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 10 Jan 2005 07:42:14 -0800, Roy wrote:

>Gah! What am I doing wrong? The query below is a modification of query
>#2 above, yet updates 32 records (all of which are NOT located in table
>new_list)??!
>Please help!
>update lta
>set LL_RCVD = 'j'
>from lta a, new_list b
>where a.voy = b.voy AND
>a.poe = b.poe

Hi Roy,

This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').

An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I love you forever, thanks Hugo!

My vb form displays error data when the server is busy

Hi ,
My Vb form gives error data when the server is busy. I am using Sql Server as my back end and MTS in the middle.When I run the Qurey Analyser it returns the values correctly. I am getting the wrong data only when the server is busy.
Bye,
MError data? Do you mean bad data or do you mean an error message? What exactly are you getting?

blindman