Adding a "calculated" field to a pivot view
Posted on 2012-04-11
I have a view that displays the status of a file along 8 different review tracks. For example
USI Review1 Status1 Review2 Status2 Review3 Status3 ......................
234 FRS Pending QA Pending Senior Review Missing Docs
The view is working fine. However, the client wants me to add another field called GFP (global file position) GFP tells the user what stage the file is in based on the weight of the least weighted status. I've managed to create a field for each status (MS1, MS2) that give the weight for a particular status. So for example, Pending has a weight of 1 while missing docs has a weight of 3 so the GFP would be "Pending" for that file (USI 234)
USI GFP Review1 MS1 Status1 Review2 MS2 Status2
234 Pending FRS 3 MissingDocs QA 1 Pending
I am trying to figure out how to Populate the GFP Field. I'm at home right now but I will post the code for the pivot. It's a pretty standard pivot. So I think what I am trying to do is create a type of calculated field. I would appreciate any insights anyone might have. I'm going to sleep on it. This is on a SQL server 2005 backend.