Post reply

Custom Code for Bottom 10 results of an aggregate function

  • September 18, 2012 at 8:21 am

    #281903

    Hi everyone

    I have a matrix report that contains the following 3 expressions:

    =sum(Fields!PlanSales.value)

    =sum(Fields!ActualSales.Value)

    =(sum(Fields!ActualSales.Value)/sum(Fields!PlanSales.Value)-1)

    So in other words, the number of planned sales, the number of actual sales and the percentage difference between planned and actual sales.

    In the Matrix report the sales are broken down by the name of each item sold (Fields!ItemDescription.Value) and I have now been asked to only show in the table the bottom 10 performing sales items ie those performing worst in terms of actual sales against planned sales. When I try to filter using the expression =(sum(Fields!ActualSales.Value)/sum(Fields!PlanSales.Value)-1) I get an error message that filters cannot be applied to an aggregate function.

    I think that I'll need to create custom code to achieve this but am a little lost as to how to go about doing this, can anyone help?

    Thanks in advance.

    BO

  • September 18, 2012 at 8:54 am

    #1539147

    Is there a reason this is being done in the report and not the SQL statement?

    But could you not build a calculated field in the data set based on the rownumber function and then filter the matrix on that column where rownumber <= 10

  • September 18, 2012 at 11:42 am

    #1539211

    Anthony

    No real reason why I can't do it as you say. To be honest I had assumed that custom code was the only way forward with this.

    I'll give your suggestion a go.

    Thanks for your help with this.

    BO

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

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