Very Computer

I need some help  regarding MDX and Analysis Services.

        My requirement is something like this ... I need to develop a
        report similar to the following example

               |    Perc   |   Score   |     Rating
        ________________________________________

          A   |         90 %  |     23      |      Above Average
        ________________________________________

          B   |     64 % |      7       |       Below Average
        ________________________________________

        and so on.

        The field Perc is a Calculated Member and can be assumed
something like this ( [A] , [Time].[2003].[Jan] )

        I now have a table in my Database called as DIM_SCORE

Eg.

            Key    |     Start    |      End     |    Score   |  Rating
        ___________________________________________________

              1      |      91      |     100        |    25
              |   Excellent
            ___________________________________________________

          2      |      81      |     90          |   23        |
          Above Average
        ___________________________________________________

            . .. ...
            . .. ...
            . .. ...
        ___________________________________________________

         3      |      61       |    70          |    7         |
         Below Average
            ___________________________________________________

The puzzling part is that I need to figure out which range ( Start : End
) , the Perc value falls in and need to display Score and Desc
correspondingly.

i.e as the Perc value 90 falls in the range 81:90 , it must display a
  score of 23 and Rating as ABove Average. The values must be fetched
  dynamically from the table.

Of COurse, this can be hardcoded in the Calc Member, but is there anyway
to do it dynamically.

--
Posted via http://dbforums.com

1) Build a separate cube to contain the threshholds.
2) If the threshholds are global, and constant over time, this cube will
have one Grade dimension, with the members {A, B, C, etc.}
3) The Grade dim will have a Rating member property that contains the Rating
descriptors
4) One measure will define the min score neccesary to achieve the grade
5) Combine the cubes together in a virtual cube (or else use LookupCube)

   www.tomchester.net

Quote:

> I need some help  regarding MDX and Analysis Services.

>         My requirement is something like this ... I need to develop a
>         report similar to the following example

>                |    Perc   |   Score   |     Rating
>         ________________________________________

>           A   |         90 %  |     23      |      Above Average
>         ________________________________________

>           B   |     64 % |      7       |       Below Average
>         ________________________________________

>         and so on.

>         The field Perc is a Calculated Member and can be assumed
> something like this ( [A] , [Time].[2003].[Jan] )

>         I now have a table in my Database called as DIM_SCORE

> Eg.

>             Key    |     Start    |      End     |    Score   |  Rating
>         ___________________________________________________

>               1      |      91      |     100        |    25
>               |   Excellent
>             ___________________________________________________

>           2      |      81      |     90          |   23        |
>           Above Average
>         ___________________________________________________

>             . .. ...
>             . .. ...
>             . .. ...
>         ___________________________________________________

>          3      |      61       |    70          |    7         |
>          Below Average
>             ___________________________________________________

> The puzzling part is that I need to figure out which range ( Start : End
> ) , the Perc value falls in and need to display Score and Desc
> correspondingly.

> i.e as the Perc value 90 falls in the range 81:90 , it must display a
>   score of 23 and Rating as ABove Average. The values must be fetched
>   dynamically from the table.

> Of COurse, this can be hardcoded in the Calc Member, but is there anyway
> to do it dynamically.

> --
> Posted via http://dbforums.com

Regards,
Richard Tkachuk

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.