none
How to set a NAMED SET Dynamic for all the calculation.

    Question

  •  

    Hi All,

    I have few questions

    1. i was trying to calculate a named set based on the calculated measure but it wasshowing me error so question is can we create a named set using the CALCULATED MEASURES or Not.

    2. How to make a named set  a named set dynamic for all the measures.

    i.e  if i have for this measure i am using this kind of notation for one single measure

    TOPCOUNT(EXISTING  dim.A.members, 15, [Measures].Beer)

     

       But if i want to just define the structure so that if i will drag any measure it should for all of them not for just

    [Measures].Beer.

     

    Is any way to define so that i will just drag measure and get the top count

     

    thanks

    Monday, December 08, 2008 7:30 PM

All replies

  • Named sets are limited by the query context and not the context of an individual cell.  You can define sets sensitive to cell context but not within a named set. If you could describe your overall goal, we might be able to show a query illustrating an approach for you.

     

    Thanks,
    Bryan

    Monday, December 08, 2008 9:28 PM
    Answerer
  • Thanks Bryan,

    The purpose of the named set is to generate the top count for the PPS Dashboard where i want to drag just top count function and the measure value what i was asking to make dynamic so if i have a topcount/bottom count function based on the measure like

    i have

    select

    ([Measures].[Pct Perfect])

    on COLUMNS,

    ORDER

    ((topCount((FILTER(([Company].[Code].[Code].MEMBERS) ,

    [Measures].[P P]>0)),10,[Measures].[P P])),[Measures].[P P],

    BDESC) ON ROWS from [bbc]

    But now i don't want to write this query  every time so i am defining the named query but dynamic so i just define that in the cube calculation and drag that on PPS and we can drag any measure corrosponding to that to get the value .

    I mean i don't want to define hard-coaded measure  but i want to drag any measure so just one NAMED SET can work for all of the measures. dynamically it can work for all the measures individually when ever i drag like once we have named set i can get top 10 customer by just draging the customer , top 10 customer  sales by just draging the customer sales

    I don't want to define Multiple named set,is it possible

    Thanks again to all of you

    Monday, December 08, 2008 10:35 PM
  • Take a look at this query:

     

    select

        [Measures].CurrentMember ON COLUMNS,

        TOPCOUNT(

            [Product].[Product].[Product].Members,

            10,

            Measures.CurrentMember

            ) ON ROWS

    from (

        SELECT [Measures].[Reseller Order Quantity] ON COLUMNS

        FROM [Adventure Works]

        )

     

    In the subquery you restrict measures to the measure of interest. You can then leverage this restriction using the Measures.CurrentMember.

     

    Hope that helps,
    Bryan

    Monday, December 08, 2008 10:50 PM
    Answerer
  • Bryan,

    ACTUALLY I ASKED FOR THE" NAMED SET"

    not the calculated measure can you please suggest something about the named set

    Thanks

     

    Monday, December 08, 2008 10:54 PM
  •  

    with set [x] as

        TOPCOUNT(

        [Product].[Product].[Product].Members,

        10,

        Measures.CurrentMember

        )

    select

        [Measures].CurrentMember ON COLUMNS,

        {x} ON ROWS

    from (

        SELECT [Measures].[Reseller Order Quantity] ON COLUMNS

        FROM [Adventure Works]

        )

    Monday, December 08, 2008 10:57 PM
    Answerer
  • Bryan, Thanks once again.

    But like i said that i am using Named Set in my calculation so when ever any calculated measure i include in my

    named set this error i get

    A set has been encountered that cannot contain calculated member

     

     

    So i am  back on my first question can i use calculated measure in my NAMED SET calculation?

    Since i am getting this error. If yes what the changes i will have to look.

     

    Thanks in advance

     

     

    Tuesday, December 09, 2008 2:42 PM
  • I believe you are limited to a query-scoped named set to get the functionality you require.

     

    B.

     

    Tuesday, December 09, 2008 4:26 PM
    Answerer
  •  Bryan C. Smith wrote:

    I believe you are limited to a query-scoped named set to get the functionality you require.

     

    B.

     

     

    SSAS 2008 supports Dynamic Named Sets - thats exatcly what you asked for

    in SSAS 2005 the only "workaround" i know is a query-scoped named set as Bryan said

     

    greets,

    gerhard

    Tuesday, December 09, 2008 5:32 PM
    Answerer
  • Thanks gerhard.

    I knew that 2008 does but i wasn't  aware about the 2005.

    Thanks

    Tuesday, December 09, 2008 6:29 PM
  • Not so fast with the cube-scoped dynamic sets.  There is a point here that is being missed.

     

    Because of the way context is handled with named sets in general, this query will not return what you expect when you employ CURRENTMEMBER in the named set definition:

     

    select
        {[Measures].[My Measure]}
    on COLUMNS,
        {[My Named Set]}
    ON ROWS

    from [My Cube]

     

    The named set My Named Set, even though it is dynamic and employs CURRENTMEMBER on Measures, will not reflect My Measure unless My Measure is the cube's default measure.

     

    To make the CURRENTMEMBER actually evaluate to whichever measure you employ, you must restrict the cube space. You can do this by re-writing the query as follows:

     

    select
        {[Measures].CurrentMemember}
    on COLUMNS,
        {[My Named Set]}
    ON ROWS

    from (select  {[Measures].[My Measure]} on COLUMNS from [My Cube])

     

    This is one of several ways to restrict the space prior to the named sets evaluation. The point is, a dynamic named set is part of the solution but on its own does not deliver what you want.

     

    B.

    Tuesday, December 09, 2008 6:37 PM
    Answerer
  •  

    Brian's right! In his last post, everything up to the point when he wrote the second MDX is correct and it's good that he brought it up after mentioning of dynamic sets.

     

    Unless one doesn't keep things under control, a default measure is taken during evaluation of set. And that can mislead. Meaning, you have to restrict cube space, as he said. Using subselect. But there, in the second MDX that uses subselect, there lies a problem.

     

    You can not put calculated measure inside the subselect, for this specific scenario - not to be interpreted wrong, (for example [Measures].[Ratio to Parent Product]) because that would refer to something outside of scope (of subselect). And one would receive an error about it. Which brings us back to the original question, as ddsu001 already pointed out once.

     

    This topic is really a tricky one. However, I think I have a solution for poster's problem. Not a bulletproof one, but a working one. The kind I like .

     

    Here it is:

     

    Code Snippet

    WITH

    SET [mySet] AS

    TopCount(

    [Product].[Product].[Product].Members,

    10,

    Extract(Axis(0), [Measures]).Item(0)

       )

    SELECT

    { [Measures].[Ratio to Parent Product] } ON COLUMNS,

    { mySet } ON ROWS

    FROM

    [Adventure Works]

     

     

    Since we can not use subselect with calculated measure in this case, we could try using WHERE part. But, that would error in multiple axis for same hierarchy (Measures), so we need to do something else if we want to solve the problem. That particular problem, or, shall I refer to it as a request.

     

    By using Axis() function, we can provide mySet to be evaluated always per first measure on columns (no matter how many other hierarchies are there). So, whichever measure is placed on columns first (and that action is feasible in any client), set is evaluated per that (first, if multiple) measure. Nice?

     

    The set definition could bear a little more iif()s for testing whether there are measures on columns at all (to fallback to default measure instead), but I wanted to keep things simple.

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence Specialist

    http://www.softpro.hr

     

    Tuesday, December 09, 2008 8:23 PM
    Answerer
  • Important question on this one, Can AXIS() be employed within the script? Within a query-scoped named set, you're good but what about a cube or session-scoped named set?

     

    B.

    Tuesday, December 09, 2008 8:35 PM
    Answerer
  •  

    Thanks to both of you but question is

     when i  use this as a query then it gives the values works perfectly but Now i want to define this in the cube calculation as a named set

    (

    TopCount([Product].[Product].[Product].Members,

    10,Extract(Axis(0), [Measures]).Item(0))

    )

     as a named set in my cube and here it gets fail.

    here is the eoor i get :
    "Error 1 The Axis function was indirectly referenced from the context of a statement other than SELECT. Evaluation of expressions involving this function succeed only when indirectly triggered by a SELECT statement.  0 0 "

    I am not going to use this query but the named set mentioned above so i can just drag This and a measure in the PPS and accordingly i can get the top 10 values of products for any measure.

    Base query:

    WITH SET [mySet] AS

    TopCount([Product].[Product].[Product].Members,

    10,Extract(Axis(0), [Measures]).Item(0))

    SELECT

    {[Measures].[Gross Profit Margin] } ON COLUMNS,

    { mySet } ON ROWS FROM [Adventure Works]

     

    Help please

    D

     

     

    Tuesday, December 09, 2008 9:15 PM
  • I guess this answers my question. The AXIS function is restricted for use within a query.  You'll have to implement the named set within a query or limit its use to non-calculated (measures) members. If you don't have access to the query, which may be what is driving you to implement it within the cube, you may need to look at another presentation option.  A Proclarity Report may be your best bet as SSRS does not allow you to dynamically set the measures.

     

    Hope that helps,
    Bryan

    Tuesday, December 09, 2008 9:30 PM
    Answerer
  • Thanks again.

    I was trying to define minimum   calculations in the cube becz if i have 10-15 almost same structured value or calculation

    then i have to implement all of them,to avoid this i was trying to implement dynamic Named set.

    I can include those 15 named set and use them in performance point server just by drag and drop.

    I still any way except procalrity which we are  not using in this particular project neither reporting services,

     

    Thanks

    Tuesday, December 09, 2008 9:40 PM
  •  

    Good point Brian! Axis() is not working in script (I postponed my answer because I was testing for alternative and had problems).

     

    The only thing I came up to is this (as part of script):

     

    Code Snippet

    Create Dynamic Set CurrentCube.[mySet] AS

    TopCount(

    [Product].[Product].[Product].Members,

    10,

    [Measures].CurrentMember

    )

    ,Display_Folder = 'Sets';

     

     

    and then this as a simple query:

     

    Code Snippet

    SELECT

    {

    [mySet]

    }

    ON AXIS(0)

    FROM

    [Adventure Works]

    WHERE

    ([Measures].[Ratio to Parent Product])

     

     

    But, the former query somehow doesn't seem to work in SSMS - that's why I spent so much time on it and eventually I ran it in CubePlayer, which returned results. It returns good results, but on columns (hard to look at).

     

    I agree with your options - query-scoped named set or some compromise.

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence Specialist

    http://www.softpro.hr

     

    Tuesday, December 09, 2008 9:42 PM
    Answerer
  • could you provide the query that the ProClarity-Report builds when you use a named sets

    it may help to solve your problem

     

    greets,

    gerhard

     

     

    Tuesday, December 09, 2008 9:43 PM
    Answerer
  •  

    If using dynamic set in script and measure in slicer is feasible to you ddsu001, then maybe a small improvement of my former MDX might bring a light how to display set in rows (also not working in SSMS, at least not for me), but it works in CubePlayer, so most likely in other clients:

     

    Code Snippet

    WITH

    MEMBER

    [Sales Territory].[Sales Territory].[All Sales Territories].[MeasureInSlicer]

    AS

    [Sales Territory].[Sales Territory].DefaultMember

    SELECT

    {[Sales Territory].[Sales Territory].[All Sales Territories].[MeasureInSlicer]} ON 0,

    {

    [mySet]

    }

    ON AXIS(1)

    FROM

    [Adventure Works]

    WHERE

    ([Measures].[Ratio to Parent Product])

     

     

    That calculated member can be placed in script also. Idea is to use one not important hierarchy (might be a dummy, hidden, something, anything that will not be placed in query) and make a dummy member on it to be placed on columns. That way we can move set in rows. It works for me, I've tested it.

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence Specialist

    http://www.softpro.hr

    Tuesday, December 09, 2008 10:00 PM
    Answerer