7 Replies Latest reply on Jan 21, 2014 9:50 AM by mmrobinson

    Using global fields with self-relationships for reporting

    mmrobinson

      I am currently working on an offertory report for yearend data for multiple locations. I have two global fields where users can enter the two years they want to compare the data for all locations.

       

      What I am using is two self-relationships that currently only works for current and previous years.

       

      When I use the current year and previous year information, the report shows the information for the 2 years and, using a calculation field, calculates the difference between them correctly. The first problem I am running into is I have to use a date range to pull the data. If I input 2 consecutive years, 2012 & 2013 or 2011 & 2012, it displays the offertories for those years, however, it does not calculate the difference for those years. For 2012 and 2013, only 2013 total shows up in the offertory difference field. For 2011 and 2012 nothing shows in the offertory difference field.

       

      The second problem I am running into, because I have to use a date range, if I use 2011 & 2013, it displays all 3 years. How do I only show the years 2011 and 2013, and not 2011, 2012, and 2013? And I still have the problem that it calculates only the difference for the current and previous years if they are included in the date range.

       

      I know solving one of the problems above will help to solve the other, but in a nut shell, what I would like to do is compare the two years of data that are entered into the global fields (consecutive or not) and come out with a difference between the 2. But, no matter how I try to relate the two self-relationships to the original table, the offertory difference does not work. It will pull the right data for consecutive years I want but will not calculate the difference correctly, and if I want non-consecutive years, I get all the years displayed.

       

      I hope this makes sense.

       

      The last piece of info I need to get is the total offertory for each year entered into the global fields, inclusive of all locations, and then of course the difference between these totals as well.

      I know it is possible and I am sure it is just one simple step I am forgetting but any help would be greatly appreciated.

       

      Thanks,

      Marcie