How to aggregate the values from child objectives to parent objectives and so on

Hi there,
I am developing the Performance Management System for our organisation. There can be one or more than one child of an objective and the child objectives can also have one or more child objectives and this can go on and on. The values can be only entered at the final leaf.  Please see the attached diagrame for more explanation.

KPIS showing tree
To achieve this i have and Objective table with (Objid PK, ObjName, AggType(Avg,Sum etc))

to create the hierarchy created the the following table
ObjDepend(DependID, ParentID FK Obj, ChildID FK Obj)

Each Objective can have one or more than one measure
MeasureDefs(MeasID, MeasDescp)
OMPairs (ObjID - MeasID PK, Formula etc)

The values are entered in the following table
ObjValues(ObjID-MeasID FKOmPairs, Actualv, TargetV, PeriodDate)

Now i need to calculate the values of child nodes depending on the aggregation type set in the objectives table(SUm, Avg, Max, Min) and set the value of the parent. But as it can be any level so i am stuck as how to find all the objectives in the tree and update them accordingly?

Sorry if i couldn't explain it properly but really looking forward to solve this.

regards,
Ali ShahSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
If things can have only one parent then why not have the containers for the child as a member of the parent (tree like, which is your diagram above).
Then to find the value of all the child object the parent just loops through all the items in the container (think recursion, the child can calculate the value of all of its children and so on).
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure if it helps, but I recently write an article on SQL Server GROUP BY solutions that has a lot of grouping code you can work with, although you'll have to incorporate id-parent_id for it to be useful.
0
Ali ShahSQL DeveloperAuthor Commented:
Thanks for you comments.
Andy actually i am only a beginner to middle level developer in C#/ASP.Net. however with your help i am sure i can achieve this..

So do i need to create a unity container first and then use recursion in C# and loop through unity container?

Can you please also guide me creating the unity container for my specific requirements?

kindest regards,
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AndyAinscowFreelance programmer / ConsultantCommented:
A simple basic app to demonstrate, it only has children at one level - but to add children to children isn't too much extra work.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Foo f = new Foo(43);
            f.AddChild(12);
            f.AddChild(86);

            int i = f.TotalValue();
            MessageBox.Show("Total is " + i.ToString());

        }
    }

    public class Foo
    {
        public Foo(int v) { val = v; }
        private Foo() { }
        private int val;
        private List<Foo> listFoo = new List<Foo>();

        public void AddChild(int v)
        {
            Foo f = new Foo(v);
            listFoo.Add(f);
        }

        public int TotalValue()
        {
            int total = val;  //value of this object
            foreach(Foo f in listFoo)
            {
                total += f.TotalValue();
            }
            return total;
        }
    }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor Commented:
sorry i became busy with other stuff and coming back to it again. so it means that if i have the values in my datareader and i create a recursive method in C# it will work?
0
AndyAinscowFreelance programmer / ConsultantCommented:
Yes.  My short example is a simple demonstration of one way to accomplish what you ask for.
0
Ali ShahSQL DeveloperAuthor Commented:
Well well starting again on it. Too many projects at the moment
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.