I am using Salesforce to track milestone completion for PhD students. I would like to make a crosstab report that shows the student names down the left, milestone names across the columns, and then show Not Started / In Progress / Complete for the intersection. Each department has different milestones so the columns will be different depending on the user.
MS 1 | MS 2 | MS 3
Student A | Complete | Not Started| Not Started
Student B | In Prog | In Prog | Not Started
Student C | Complete | Complete | Complete
In say SAP Business objects, I'd set the column to be the requirement name, the row to be the student name, and the aggregation to be the minimum of the Progress field.
Since you cannot apply numerical aggregations to text fields in Salesforce, only counts, I wasn't sure how I could accomplish my objective. I also considered joining a bunch of reports together, but didn't know how I could do that without hard coding the requirement names. Since the departments have different requirements hardcoding seems like a terrible idea from a maintenance perspective.
Can someone point me in the right direction?