如何在MicroStrategy Developer中实现行转列报表并添加多指标

Method 1: Dynamic Pivot (Scalable for Any Periods) #

This approach avoids creating individual metrics for each period and works for any number of period values:

1. Set Up the Report Structure:

   • Drag Contract_ID to the Rows axis.
   • Drag Period to the Columns axis.
   • Drag both Forecast and Shortfall metrics directly onto the Period attribute in the Columns panel—this nests the metrics under each period, so every period will have two sub-columns (Forecast and Shortfall).

2. Customize Column Headers:

   • Double-click each column header to edit it. Combine the period name and metric name (e.g., change "Forecast" under May23 to "May23Forecast"). Use the batch edit option (right-click header area > Batch Edit Headers) to speed this up.

3. Add Total Metrics:

   • Create two new metrics:
     • Total Forecast: Formula: Sum(Forecast), set the level to Contract_ID (right-click metric > Edit > Level tab > Select Contract_ID).
     • Total Shortfall: Formula: Sum(Shortfall), level set to Contract_ID.
   • Drag these two total metrics to the end of the Columns axis.

4. Hide Zero Values:

   • For all metrics, go to Format > Metric Format > Number tab, set "Display zero as" to blank. This ensures empty cells show nothing instead of 0.

Method 2: Static Period-Specific Metrics (For Fixed Periods) #

If you only need to handle the three periods in your example, create dedicated metrics for each combination:

1. Create Period-Filtered Metrics:

   • For each period-metric pair, make a new metric:
     • May23Forecast: Forecast filtered by Period = 'May23' (add filter in metric editor).
     • May23Shortfall: Shortfall filtered by Period = 'May23'.
     • Repeat for June24 and Sept22 to get all six metrics.

2. Assemble the Report:

   • Add Contract_ID to Rows.
   • Add all six period-specific metrics to Columns in your desired order.
   • Add the Total Forecast and Total Shortfall metrics (from Method 1 step 3) to the end of Columns.

3. Format Empty Cells:

   • Set each period-specific metric to display blank when no data exists (same as Method 1 step 4).

Troubleshooting Tip: #

If you can't add Shortfall when Period is in Rows, it's because you're trying to place metrics in Rows instead of Columns. Ensure Period is in Columns, and metrics are nested under it or placed alongside to get the pivoted structure you need.

内容的提问来源于stack exchange，提问作者simplysimply