Results 1 to 2 of 2

Thread: sum aggregation

  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: sum aggregation

    I've got a cube storing sales data. (Oh, I'm using SQL Server Analysis Services to create this cube, btw.) There are other dimensions in that cube, but the one of concern is TIME. This TIME dimension is structured as -

    TIME
    ------
    Year
    WeekStart
    Date

    Using this time dimension the appropriate weekly and yearly sales totals can be found, by drill-up/drill-down.

    Consider a scenario where the sales record for a certain date in the fact table is NULL, ie no sales were recorded for that date. Normally, in analysis services, when the weekly sales total is shown the NULL sales data is ignored and a sum of sales from other dates is shown. But is there any way of showing a ZERO sales total for that week.

    What I mean is, whenever sales data for a day is missing the sales total for that week should be zero. Is this sort of a thing possible in analysis services?

    i want something like

    week.sales = 0
    for (all days in week)
    if day.sales = 0 or null
    week.sales = 0
    break outta for loop
    else
    week.sales += day.sales
    next

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy


    Dont complicate things - do all the grunt work in the database ( i.e. set no sales = '0' or similar instaed of leaving it NULL ) and then use analysis services accordingly.

    Cheers

    SG

Posting Permissions

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