Currency Format

The $ must appears only on certain total levels and has to be in the same position in the column regardless of the value for financial reporting. In other words, I don't want the $ to float next the most significant digit in the value. See the example below from Excel. Using an IIF statement I can concatenate the $ to the string when it is a certain group level. However, I have tried many various formatting strings but can't find one that results in a fixed number of characters so that the $ will always appears in the same position in the column.

Anyone have a way to do this?

General Government

222

3.45%

$ 405,590,654

1.98%

Public Buildings

194

3.01%

375,453,314

1.83%

Other Facilities

22

0.34%

21,324,140

0.1%

Property Acquisition

6

0.09%

8,813,200

0.04%

Grand Total

6,439

100.0%

$ 20,507,977,298

100.0%

I did get this to work by adding a column to the grouping. That somehow causes the heading size to increase (?) and takes much more time than coding a format code in an expression (I have many reports to do).

Can you try something like this in your field's expression:

="$" & Space(30 - len(Fields!Total.Value)) & Fields!Total.Value

The 30 in my example would have to be higher than the maximum number of digits in your Total field. This will put spaces between the $ and the actual number you are showing, but only enough to make the $ symbols align.

Hope this helps.

Jarret

|||

This is close, but the proportional font keeps the $ from lining up.

I am not a VB programmer so I have a little trouble finding the appropriate string and format functions.

Thanks for your reply.

|||

Could you change to a fixed-width font so that it would line up?

Or, you could add a column to the left of the field that has the $ symbol in it, but only for the group header and footer. This way, it would always line up.

Jarret

|||

I did the add a column and it causes the group headers to grow and be ugly. I am hoping for a solution where I can code something in the expression. If not, I may have someone code me a little VB routine to use.

Thanks.

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.