Is there any format that will not show a negative sign symbol?
I'm trying to get results in this format:
| Category | Dollars |
| A | 1,234 |
| B | 2,345 |
| Difference | 1,111 |
The output will always have two rows (categories) of data and a difference row. As you can see, Difference = B - A.
One thought was to get the data in 3 separate queries and union them together. This way I can calculate the dollars column for each case then combine the data. Then the Difference row gets some conditional formatting to look like a summary row. This turned out to be cumbersome and difficult to troubleshoot for a complicated base query.
What I'm trying now is to union the first two and take the difference as a sum. (Seems silly now, but keep reading.) This can be done with separate queries unioned together or with one query. In the first case requires the dollars column to be negated for the first row (-dollars), the second requires the same thing in the form of a case statement.
case [category] when 'A' then -[dollars] else [dollars] endThe problem with this method?
| Category | Dollars |
| A | -1,234 |
| B | 2,345 |
| Difference | 1,111 |
All of the dollars that will show on this report will be positive. I don't want to show dollars with a minus sign.
In the
Data format dialog I see a selection for
Negative sign symbol, but there's no option for blank. Then I noticed there is a
Negative pattern. I figured setting this (and
Pattern to (space)#,##0(space) for the list column body cells would do the trick. It doesn't. I still get a minus sign, but now there's a space between it and the number.
I don't care about the actual "data" displayed (negative vs. positive) just the appearance. I'm not concerned about users trying to run this report to Excel. It would be pretty useless in that format.
Is there any format that will not show a negative sign symbol?
Quote from: dougp on 27 Apr 2017 11:55:44 AM
Is there any format that will not show a negative sign symbol?
I'm trying to get results in this format:
| Category | Dollars |
| A | 1,234 |
| B | 2,345 |
| Difference | 1,111 |
The output will always have two rows (categories) of data and a difference row. As you can see, Difference = B - A.
One thought was to get the data in 3 separate queries and union them together. This way I can calculate the dollars column for each case then combine the data. Then the Difference row gets some conditional formatting to look like a summary row. This turned out to be cumbersome and difficult to troubleshoot for a complicated base query.
What I'm trying now is to union the first two and take the difference as a sum. (Seems silly now, but keep reading.) This can be done with separate queries unioned together or with one query. In the first case requires the dollars column to be negated for the first row (-dollars), the second requires the same thing in the form of a case statement. case [category] when 'A' then -[dollars] else [dollars] end
The problem with this method?
| Category | Dollars |
| A | -1,234 |
| B | 2,345 |
| Difference | 1,111 |
All of the dollars that will show on this report will be positive. I don't want to show dollars with a minus sign.
In the Data format dialog I see a selection for Negative sign symbol, but there's no option for blank. Then I noticed there is a Negative pattern. I figured setting this (and Pattern to (space)#,##0(space) for the list column body cells would do the trick. It doesn't. I still get a minus sign, but now there's a space between it and the number.
I don't care about the actual "data" displayed (negative vs. positive) just the appearance. I'm not concerned about users trying to run this report to Excel. It would be pretty useless in that format.
Is there any format that will not show a negative sign symbol?
Is the goal to avoid ever having a negative difference? If so, does using an absolute value function solve the problem?
abs ( [Difference] )
Sorry if I am not understanding....'tis Friday
No, the goal is to calculate the difference in the simplest way possible. I can think of a few ways to do this. This post is about a format-based solution. It seemed the simplest of my ideas until I hit this snag.
I wanted to hide the minus sign for the data rows. The summary (difference) row should show the correct calculated value (B Dollars - A Dollars). So, if the numbers were reversed:
| Category | Dollars |
| A | 2,345 |
| B | 1,234 |
| Difference | -1,111 |
why not absolute the values? Change the source type of the text item to report expression, and wrap the reference in abs:
abs([Query1].[Dollars])
Just make sure the Dollars is being referenced in the data container in some way (properties, hidden text item, etc).
and I just noticed Lynn wrote the same thing. Whoops. Using a report expression to make the value absolute ensures that it's not affecting the actual calculation for the difference.
Thanks, Paul. I tried that and it works. There's only one problem. I was hoping to hang multiple queries off of a main query so I could control all of the query logic in one place. According to the source pane in the expression editor, I can't reference any of the downstream queries from a report expression. I guess every solution has trade-offs.
A: case when [Category]='A' then [Dollars] else 0 end
B: case when [Category]='B' then [Dollars] else 0 end
diff = abs(abs([A]) - abs())
Regardless of which is greater, that will always return the difference as a positive value.
Since I can't stop fiddling with stuff, I learned something new this morning...
In the
Data format dialog:
| These settings | | ...produce these results | |
| Pattern | (blank) | Positive Number | 1,234.56 |
| Negative pattern | (blank) | Negative number | -1,234.56 |
| | | | |
| Pattern | (space)#,##0(space) | Positive number | (space)1,234.56(space) |
| Negative pattern | (space)#,##0(space) | Negative number | -(space)1,234.56(space) |
| | | | |
| Pattern | (blank) | Positive number | 1,234 |
| Negative pattern | (space)#,##0 | Negative number | (space)1,234 |
It looks like Pattern and Negative pattern work together somehow. That second pair is what was causing me trouble.
The third pair is exactly what I needed. It looks like the space takes the place of the minus sign.
That is very handy to know. Thanks very much for sharing!