Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Unhappy Unanswered: Calculated Member help

    Hi, I have a calculated member that computes the Sales per week for any given level. I am using a time dimensiona and a sales dimension. The time dimension has these levels: Year, Quarter, Month, and Weeks. I am using this formula

    [Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

    The problem with this is that it works only if the user select a single time member. I am using Excel as frontend and I fixed the pivot table in such a way that Sales is on the column, and Store is on the Rows. The time dimension is on the top serving as filter (Page axis). Because you can choose multiple dimension member in the dropdown boxes of any dimension in Excel, I am having trouble making it work in these scenario. It only works if you select one time member at a time. Please help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't fully understand what you are trying to do, but since the database server is returning a simple dataset to your Excel pivot table, the problem would appear to be with the pivot table and not the query or table you are using. When you filter the pivot table you are fiiltering its stored snapshot of the query results. It does not pass the parameters back to the database server.

    blindman

  3. #3
    Join Date
    Jun 2003
    Posts
    6

    Smile Calculated Member

    Hi, thnks so much for the reply. Anyway, to make it clear to you:

    I'm using an Analysis Service Cube as my backend and Excel Pivot table as my frontend.

    I have two dimension in my cube: 1. Time(Yr, Qtr, Month, Week) and 2. Store

    I have two measures: 1. Sales and 2. Sales per week (this measure is a calculated member

    The formula I use to make the Sales per week measure is:
    [Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

    Here's my setup in Excel: The sales is broken down by store and the time is on top acting as a filter

    Time: 2003

    Store Sales Sales/Week
    Store1 100 25
    Store2 200 12

    My problem occurs evrytime the user selects more than one Time value to filter on (like all Jan AND Feb data). It works only if one time value is selected at a time. When user chooses more than 1 time value, the Sales/Week data appears as: #Num!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry smb, but I don't have experience with data cubes syntax. Hopefully now that your post is at the top of list someone else will check it out and give you an answer!

    blindman

  5. #5
    Join Date
    Jun 2003
    Posts
    6
    Thnks anyway blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •