none
Getting a RunningValue to break on Department RRS feed

  • Question

  • Hi all,

     

    I am attempting to build a table with the following information:

              2 departments = Dept A and Dept B

              2 years data = 2009 and 2010

              4 quarters per year – Q1, Q2, Q3, Q4

    I also want a running total of the quarters for each year for each dept.

     

    Here is what I’d like it to look like:

     

    Dept   Qtr   Amt    ’09 YTD    Amt    ’10 YTD

      A      Q1      1         1           5           5

      A      Q2      2         3           5         11

      A      Q3      3         6          

      A      Q4      4       10          

     

      B      Q1      5         5           9           9

      B      Q2      6       11         10          19

      B      Q3      7       18          

      B      Q4      8       26          

     

     

    My Row Groups are:

    (Group1) on Dept

        Qtr on Quarter

     

    My Column Group is:

    Yr on Year

     

    I had the expression for the running total as:

    =RunningValue(Fields! AMOUNT.Value,Sum,"YR")

    This caused the running total for the year 09 to be 1, 2, 6, 10, 15, 21, 28, 36 – it did not break on the department.

     

    I changed the Column Group to be on Yr and Dept and used the same expression and got:

     

    Dept   Qtr   Amt    ’09 YTD    Amt    ’10 YTD   Amt    ’09 YTD    Amt    ’10 YTD  

      A      Q1      1         1           5           5          

      A      Q2      2         3           5         11         

      A      Q3      3         6                                    

      A      Q4      4       10                                    

     

      B      Q1               10                      11           5         5           9           9

      B      Q2               10                      11           6       11         10          19

      B      Q3               10                                    7       18

      B      Q4               10                                    8       26

     

    The totals are correct but how do I get the table to only show 2009 and 2010 columns once instead of repeated next to each other?  If I display more departments the table will run off the right side of the report.

     

    At this point I’m guessing at what should be in the groupings and then running the report to see what happens.  Does anyone have any guesses or suggestions on what I can try?

     

    I’m still learning and it may be a case of I “don’t know enough to know it is not possible.”

     

    Thanks in advance,

     

    • Moved by Brittany Behrens Tuesday, April 27, 2010 7:00 AM (From:Visual Studio Editor)
    • Moved by Barclay Hill Wednesday, April 28, 2010 7:40 PM More appropriate forum (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Monday, April 26, 2010 5:06 PM

Answers