Data in graphs can be sorted the same way as we sort a grid (with the Data | Advanced Sorting menu). Take the previous recipe as an example: if you sort the data by Product Category, you will see the Accessories bar in the first place on the left.
We can also apply conditional formatting to the color of the series by using thresholds.
Let's see an example; say that you want the negative values of Product Margin to shout out loud in your chart:
- Run the 24 Product Margin vs. Sales Amount by Category report.
- Once the chart appears, click on menu Data | Thresholds.
- In the Thresholds window the Sales metric is selected by default and an empty condition is created. We need to remove this one before going on, so click on the small button labeled Delete on the toolbar, the one that has an X icon like the this:
- Answer Yes to the message asking you if you want to delete the threshold New Threshold and open the drop-down list at the top-left corner of the window.
- Select Product Margin and then click on the semaphore New… button next to it, a new blank condition is created.
- Click on New Threshold to rename it and type
Negative Margin. - Then using the Click here to start a new qualification link, set the condition to:
- Field: Product Margin
- Operator: Less Than
- Value: Type a value and enter
0
- Now click on the white 1234.12 textbox on the right to bring up the Format Cells dialog.
- On the Background tab open Background style and select Solid.
- In Fill color choose black and confirm with OK.
- You are back to the Thresholds windows, look at the toolbar and click on the third button from the right, the one that resembles a ruler (the tool tip on the button says Apply the thresholds on metric values only) similar to the this icon:
- When you click on it, the button on the left (tool tip: Enable threshold on Graph) changes to enabled. Now click on this one that looks like a small graph to activate the conditional formatting on the chart:
- Close this window by clicking on the OK button, now the negative Profit Margin for Bikes is black.
If you like simple and plain graphs with no 3D rendering you can switch off the rounded effect this way.
- From the Graph menu, go to Graph Options.
- Select Options | General on the left.
- Towards the end of the window see a checkbox labeled Apply rounded effects to all series, click to unselect it and press OK.
The image is now flat 2D. Now let's change the bar colors to make it "funkier".
- There is a button in the toolbar as shown in the following icon, its tool tip says Color Palettes:
- If you click on the button, nothing happens (ouch!); you should instead click on the extra-small downward arrow on the right of the button. From the list that appears, click on Metro.
Ain't it funky now?
To change the color of a series conditionally, remember to set the threshold on the correct metric. Only the background color setting is used.
You can create very good pictures with a little patience, taking advantage of the transparency settings:
- Right-click on a bar and select Format Bar Riser….
- Set the Transparency slider to something lower than 100, like
50or so. - Click on the Bevel button and select Sphere from the Bevel Type dropdown.
- Set Light Angle to
90, click on OK and then on OK again. - Similar settings exist for the background and the frame of the chart.
- The Setting up MicroStrategy Web recipe in Chapter 7, Analysis on the Web – Documents and Dashboards