none
How to calculate the root of filtered members based on another filter RRS feed

  • Question

  • Hello All,

      Please find the below query.

    with member agg as 
    sum(

    exists([Product].[OTC1].[OTC1],existing([Product].[Pack].[Pack])])
    ,[Measures].[Values HNA])

    select 
    agg on 0 from cube where [Product].[Product].&[101]

    For a particular product am trying to know the existing packs associated with it and then finding the OTC1's for which we are having sales for a product. 

    I want to find the total sales(all product sales) for the OTC1's which are having sales for the filtered product.

    Note- In filtered it can be any other product attributes not only product so am going to the lowest level pack to identify the OTC1's.

    Regards,

    Bharath


    Tuesday, November 12, 2019 9:40 AM

All replies

  • Hi Bharath,

    Thanks for your post.

    When I read your description, I gradually got confused. Could you please use some sample data to support your description? It's easy to go to wrong direction.

    By the way, please develop your habits of closing your threads whose state are open. Find that there are many unopen threads here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/user/threads?user=Bharath_RAM&filter=alltypes&sort=firstpostdesc&searchTerm=undefined

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 7:19 AM
  • Hello Will,

       Sorry for the confusion . Let me try to explain with some examples.

    For product 101 we are having values only for OTC1 '02' and '98'. Below query will provide the sales of only product '101' as it is filtering the cube

    with member agg as 
    sum(

    exists([Product].[OTC1].[OTC1],existing([Product].[Pack].[Pack])])
    ,[Measures].[Values HNA])

    select 
    agg on 0 from cube where [Product].[Product].&[101]

    However my intention is to first  get the OTC1's which are having sales for product '101' which is '02' and '98' in this case and later finding the values of all the product for only OTC1's '02' and '98'.

    Thank you will make sure do close my threads.

    Regards,

    Bharath

    Wednesday, November 13, 2019 8:32 AM
  • Hi Bharath,

    Thanks for your clarification.

    You description seems to be more clear. In fact, you'd better not use EXISTS to filter specific set, it would lead to low performance. Based on your current description, maybe you need this.

    >>However my intention is to first  get the OTC1's which are having sales for product '101' which is '02' >>and '98' in this case and later finding the values of all the product for only OTC1's '02' and '98'.

    WITH member [Measures].[agg] as
    IIF([Product].[OTC1].CURRENTMEMBER IS [Product].[OTC1].[ALL], SUM(EXISTING([Product].[OTC1].[OTC1]),[Measures].[Values HNA]),[Measures].[Values HNA])
    SELECT 
    [Measures].[agg]  ON 0,
    NON EMPTY{[Product].[OTC1].MEMBERS} ON 1
    FROM cube 
    where [Product].[Product].&[101]

    Best Regards,

    Will



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 7:50 AM