Post reply

Displaying Months As Series - Ordering by Month Number

  • November 13, 2015 at 12:15 pm

    #322512

    Q1 - Forcing the proper sequence of months in a chart ?

    From a cube holding a set of invoices, how - for a specific year - to show the total amounts in a chart for each month ? The chart shows up ordered as April, August, December, February ...

    Q2 - How to set the year filter ?

    I created a Date dimension linked to the Invoice Measure table. The Year field of the date dimension is the "Series" section for the chart (report in the sharepoint designer). It was not possible to enter the year field in the "Background" section of the chart report.

    I ended up creating an additional date dimension (I called it "FilterYear").

    Was this really the only way to be able to do it ?

    Regards

  • November 13, 2015 at 1:21 pm

    #1839730

    For Q1:

    You need to set different values for the Value and Label properties for your month attribute in the date dimension.

    For Q2:

    You need to build a hierarchy to be able to use date parts as your filters.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 13, 2015 at 1:35 pm

    #1839738

    Thank you for your speedy reply.

    OK, I got the value/label business - that's in Visual Studio when editing the dimension.

    (value would be the month number and the label the month name ?)

    I would appreciate a bit more help - on how to setup the hierarchy within the date table.

    The columns are

    [font="Courier New"]

    DateKey (primary key, type: date), contiguous range of everyday from Jan 1 2007 up to Dec 31 2016)

    DateString (varchar(10), date formatted as yyyy-mm-dd)

    DateYear (integer YEAR(DateKey))

    DateMonth (integer MONTH(DateKey))

    DateDay (integer, DAY(DateKey))

    MonthYear (integer, YEAR(DateKey) * 100 + MONTH(DateKey))

    EnglishMonthName (varchar(20), DATENAME(mm, DateKey))[/font]

    I am a still a bit confused as to what should be "pointing" to what.

    Finally, the use of the hierarchy would mean I did not need to create an extra table to use as filter ?

    Regards

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply