locked
Performing count query RRS feed

  • Question

  • I have dimension called Object and it has measure called IncomeRange ('below 0', '0 - 50','50 - 100', etc). I have to create MDX query that returns me how many objects each of these ranges contains. I can get count for each range separately by filtering Objects out by range value and then counting these objects. This way I get counts for ranges as columns. 

    I need output like this:

    'below 0' 14
    '0 - 50' 20
    '50 - 100' 120

    How to write query that gives me these results? I have to show these results on SSRS report and that's not enough when all there counts are on columns.

    NB! I cannot use range dimension here because in different time moments IncomeRange for Object may be different (some day Object is not used, some other day it makes some money and some day it makes different amount of money).


    With best regards,
    Gunnar Peipman
     
    Also visit my ASP.NET and SharePoint blog!
    Tuesday, December 7, 2010 11:45 AM

Answers

  • Hi Gunnar,

    It is a bit hard to understand what your measure is like - how come it contains text values like 'below 0', etc? Typically a range dim is fine with your scenario - you would just have a Time dim and then you should specify in which time range you are counting for.

    If I come from typical solutions, you would have:

    1. Dimension Object
    2. Dimension [Income Range] (containing 'below 0', '0 -50', '50 - 100', etc.)
    3. Dimension Date
    4. Measure [Object Count] with Count (of rows) aggregation type

    The dimension tables should be clear (as in structure and data). The Fact table should be like this:

    ObjectKey, IncomeRangeKey, DateKey

    Then, you can simply build a count of rows measure. When you write something like:

    SELECT
    {
        [Measures].[Object Count]
    } ON 0,
    {
        [Date].[Year].[Year]*
        [Object].[Object].[Object]*
        [Income Range].[Income Range].[Income Range]
    } ON 1
    FROM [Your Cube]

    You can expect to get the counts on columns for each year, object and income range - of course you can change this to get a different representation, including by ignoring the Date dimension altogether, but it should satisfy your requirement.

    If this is not possible, please explain in more detail what the problem is.


    Boyan Penev --- http://www.bp-msbi.com
    • Marked as answer by Raymond-Lee Wednesday, December 15, 2010 2:43 AM
    Wednesday, December 8, 2010 12:38 AM