none
Expression to Divide by Variable Figures? RRS feed

  • Question

  • Okay.  So I am unable to alter the stored procedure, and must use it for the report.  That said, here's the scenario

        Gom  Jabbar Outlook
    Blue Area 8 12 10
    City 1 3 2 5
    City 5 5 10 5
         
    Red Area 10 20 18
    City 7 3 5 6
    City 3 2 7 6
    City 4 5 8 6

    Info grouped by City and by Area.  The information for columns 1&2 is delivered from the stored proc at the City level, and summed at the Area level.  However, the Outlook value in the last (highlighted) column is supplied at the Area level, and is divided by the number of cities in each area in order to arrive at the City outlook figure.   Is there a way I can create an expression in the appropriate fields to automatically calculate that figure, even when the # of citites per area will vary?

    TIA for your help!

    Sunday, April 18, 2010 9:59 PM

Answers

  • Hi Breequa,

    it seems like are doing the 'overal' formula on the detailrow. Judging your example, you should count the 'overall' data (like the '2' of Blue Area) on the header of the detailgroup. This should result in the correct numbers, could you try this?


    M. Streutker, Info Support | Blog
    • Marked as answer by Breequa Monday, April 19, 2010 1:01 PM
    Monday, April 19, 2010 11:56 AM
  •  

    Try below one by adding the group name as shown in example

    Outlook.value/Count(Fields!City.value,"GroupbyCity")


    Praxy
    • Proposed as answer by Parry2k Monday, April 19, 2010 12:42 PM
    • Marked as answer by Breequa Monday, April 19, 2010 1:01 PM
    Monday, April 19, 2010 12:08 PM

All replies

  • Perhaps I'm misunderstanding your question, but would SUM(measure) / COUNT(city) do the trick?
    M. Streutker, Info Support | Blog
    Monday, April 19, 2010 6:18 AM
  • Hi, thanks for answering.

    I tired both Count and CountDistinct (used (Outlook.value/count(City.value) ) but that just returned the number "1" for the City values, as opposed to "2" (in the Blue Are) or "3" in the Red Area.  It's like I need to sum the distinct values, but you can't nest aggregate functions, apparently.

     

    Monday, April 19, 2010 11:06 AM
  • Hi, thanks for answering.

    I tired both Count and CountDistinct (used (Outlook.value/count(City.value) ) but that just returned the number "1" for the City values, as opposed to "2" (in the Blue Are) or "3" in the Red Area.  It's like I need to sum the distinct values, but you can't nest aggregate functions, apparently.

     

    Monday, April 19, 2010 11:06 AM
  • Hi Breequa,

    it seems like are doing the 'overal' formula on the detailrow. Judging your example, you should count the 'overall' data (like the '2' of Blue Area) on the header of the detailgroup. This should result in the correct numbers, could you try this?


    M. Streutker, Info Support | Blog
    • Marked as answer by Breequa Monday, April 19, 2010 1:01 PM
    Monday, April 19, 2010 11:56 AM
  •  

    Try below one by adding the group name as shown in example

    Outlook.value/Count(Fields!City.value,"GroupbyCity")


    Praxy
    • Proposed as answer by Parry2k Monday, April 19, 2010 12:42 PM
    • Marked as answer by Breequa Monday, April 19, 2010 1:01 PM
    Monday, April 19, 2010 12:08 PM
  • Thanks Praxy and M. Steutker....I modified the expression to GroupbyArea and it did the trick! 

    I really appreciate your input!

    Monday, April 19, 2010 1:01 PM