Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet..
I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount.
The relation between tables is GroupID, as each worker belongs to a group.
Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget.
Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group.
Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same
column as the WorkerTarget?
I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there
some kind of formula like this??
If not, any other idea will be much apreciated..
Thanks a lot.. regards..
View Complete Post