No Thanks to Microsoft
Feb. 13th, 2012 05:56 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
kevin_standleeI have spent much of today trying to get Microsoft Access graphs (technically, PivotChart objects) to behave similarly to their MS Excel report cousins. Specifically, I've been challenged by formatting the Y axis so it doesn't display unnecessary decimal places (by default it wants to use 0.00 format) and relabeling chart legends so that it doesn't put "Sum of" in front of the category names, which is what it wants to do by default. In Excel, you'd right-click on the things in question. It doesn't work like that in Access, and it's fiendishly difficult to figure out where it's changed.
To change the decimal place formatting of a PivotChart, open the object in PivotChart view, select (either left or right click; it doesn't matter) the Y axis by clicking somewhere in its figures. From the Ribbon, choose the Design tab, then select Property Sheet from the Tools group. (You can't get directly to Property Sheet from right-clicking the Y axis the way I would have expected.) Select the Format tab. One of the choices will be Number. There are the usual sorts of formats in the drop-down box, like Standard, but no way to specify decimal places like when formatting a cell in Excel. But it turns out that the Number box isn't limited to the drop-down items. You can enter a formatting string here. In my case, I just typed the number 0 here and presto, the axis was relabeled with no decimal places.
To change the Legend labels in the PivotChart, you have to switch to PivotTable view. Select the column (again, left/right click doesn't matter) you want to re-label, Choose Property Sheet from the Ribbon (Design tab, Tools group). Choose the Captions tab. The field Caption is how this data gets labeled in both the PivotTable and the PivotChart.
This stuff isn't documented in any sane way, in my opinion. It took hours of searching around and trial and error before I figured it out. Normally my Google-fu is pretty good, but this was like pulling teeth. And judging from the questions I found (few of which had answers), I'm not the only person who is trying to figure this out. It's nowhere near the user interface standard used in Excel, PowerPoint, and Word, or for that matter in other parts of Access. It's only the Chart object that behaves so stupidly. Madness.
To change the decimal place formatting of a PivotChart, open the object in PivotChart view, select (either left or right click; it doesn't matter) the Y axis by clicking somewhere in its figures. From the Ribbon, choose the Design tab, then select Property Sheet from the Tools group. (You can't get directly to Property Sheet from right-clicking the Y axis the way I would have expected.) Select the Format tab. One of the choices will be Number. There are the usual sorts of formats in the drop-down box, like Standard, but no way to specify decimal places like when formatting a cell in Excel. But it turns out that the Number box isn't limited to the drop-down items. You can enter a formatting string here. In my case, I just typed the number 0 here and presto, the axis was relabeled with no decimal places.
To change the Legend labels in the PivotChart, you have to switch to PivotTable view. Select the column (again, left/right click doesn't matter) you want to re-label, Choose Property Sheet from the Ribbon (Design tab, Tools group). Choose the Captions tab. The field Caption is how this data gets labeled in both the PivotTable and the PivotChart.
This stuff isn't documented in any sane way, in my opinion. It took hours of searching around and trial and error before I figured it out. Normally my Google-fu is pretty good, but this was like pulling teeth. And judging from the questions I found (few of which had answers), I'm not the only person who is trying to figure this out. It's nowhere near the user interface standard used in Excel, PowerPoint, and Word, or for that matter in other parts of Access. It's only the Chart object that behaves so stupidly. Madness.
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2012-02-14 06:03 am (UTC)They can't even provide connectivity to their product in their own operating system. So much for that.