How do I make a cross tab report where the intersection is a text field value?

Question

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?

Answer 1

I have since worked with Salesforce support and the answer is you can do a bunch of extra steps in the data pipeline step in the CRM Analytics platform to allow you to do this, or just use Tableau and the Tableau connector to display the report inside of Salesforce. Salesforce folks said that CRMA does 90% of what Tableau can, but this is just one of the things in the gap.