Formula for calculated column

Hi,
I'm struggling to get a calculated column to work in sql, the fields to be calculated are:
[AdRevenue_a] money
[Admissions_a] int
[DoorPrice_a] smallmoney
[DoorSplit_a] money
And the calculation I require is:
(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
This is what I think it should be but it doesn't work...
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))

Any suggestions??

(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
should work.
In this:
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))
you have an extra bracket at the end and it will work if you remove it.|||That didn't fix the issue.
The problematic field seems to be DoorSplit_a, if this is removed the rest of the calculation works??
|||Do you have any sample data that you can provide. Also are any of the columns nullable? IF so you might need to use ISNULL() appropriately.
If I supplied all the values properly it seemed to work:

DECLARE
@.AdRevenue_amoney,
@.Admissions_aint
,@.DoorPrice_asmallmoney
,@.DoorSplit_amoney

select
@.AdRevenue_a= 100
,@.Admissions_a= 50
,@.DoorPrice_a= 2
,@.DoorSplit_a= 25

select(@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))* 100

selectconvert(decimal(6,2),((@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))*100))

|||All aggregate functions in SQL Server ignore NULL values except COUNT (*), and the ISNULL function will replace NULL with 0 which could give you wrong numbers if [DoorSplit_a] allows NULL. Try the link below for more info. Hope this helps
http://www.akadia.com/services/dealing_with_null_values.html|||Thanks for the replies.
I'm not even getting as far as the data.
The error is thrown by sql when I try to enter the calculation into the formula box.|||Please post the exact formula you are now trying to enter. As Dinakar pointed out, yourfirst example had a mistmatched number of opening and closingparentheses.
Also, for future questions, please use a more specific term than "doesnot work". An exact description of the error you are encounteringwill go a long way towards pinpointing and correcting your problem.

formula column

Hi,
I would like to create a calculated column using the formula
section for a table. I am having some trouble doing this.

The table's name is ReportParameter. The calculated column's name is
tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
I would like to use an If then statement such as the following (in
psuedo code):

If tb1 = 1 then tbcalculatedcolumn = 1
Elseif tb2 = 1 then tbcalculatedcolumn = 2
Endif

Thanks for the help,
BillHi

Maybe something like:

CREATE TABLE MyTable ( tb1 bit, tb2 bit, tbcalculatedcolumn AS CASE WHEN
tb1 = 1 then 1
WHEN tb2 = 1 then 2
END )

INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,1 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,0 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 0,1 )

SELECT * FROM MyTable
/*
tb1 tb2 tbcalculatedcolumn
-- -- ------
1 1 1
1 0 1
0 1 2
*/

John
"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0310010526.3ce4be47@.posting.google.c om...
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
> The table's name is ReportParameter. The calculated column's name is
> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
> If tb1 = 1 then tbcalculatedcolumn = 1
> Elseif tb2 = 1 then tbcalculatedcolumn = 2
> Endif
> Thanks for the help,
> Bill|||> I would like to create a calculated column using the formula
Why? Why not just put a CASE expression in a query or view rather than
create an extra redundant column.

> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
There is no Boolean data type in SQLServer. You mean a numeric column
(presumably BIT).

--
David Portas
----
Please reply only to the newsgroup
--

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0310010526.3ce4be47@.posting.google.c om...
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
> The table's name is ReportParameter. The calculated column's name is
> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
> If tb1 = 1 then tbcalculatedcolumn = 1
> Elseif tb2 = 1 then tbcalculatedcolumn = 2
> Endif
> Thanks for the help,
> Bill|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications.

>> The table's name is ReportParameter. <<

That is not a table name; a table is an entity or a relationship. It
is also too vague to be a data element. You need to read a book about
database design.

>> The calculated column's name is "tbcalculatedcolumn" ...<<

Please tell me that "tbl-" is not a silly redundant prefix; and there
is always a better name than "calculated_column" for a calculated
column -- what extactly did you you compute? Interest? discounts? To
be is to be something in particular; to be nothing in particular is to
be nothing.

>> and tb1 and tb2 are boolean columns in the table. <<

There are no BOOLEAN variables in SQL; it would destroy the 3VL. Look
up the CASE expression in any pf the books on SQL you clearly have
never read.|||> There are no BOOLEAN variables in SQL; it would destroy the 3VL.

Aren't Booleans defined in SQL99?|||Yes. Joe goes into denial when confronted with SQL99 ;-)

--
David Portas
----
Please reply only to the newsgroup
--

"Christian Maslen" <christian.maslen@.techie.com> wrote in message
news:b9c8cfba.0310021514.11d51a@.posting.google.com ...
> > There are no BOOLEAN variables in SQL; it would destroy the 3VL.
> Aren't Booleans defined in SQL99?|||>> Joe goes into denial when confronted with SQL99 <<

So does everyone else who worked on the draft documents :)

They had to re-define the foundation to get them into SQL-99 and this
is oneof many reasons nobody is gallopping to SQL-99. The US
government requires SQL-92 and refers to it as "a standard in
progress" in their bid forms.

The problem is that a data type in SQL must be NULL-able; a NULL
doesnto have a data type itself, but holds a place for a value which
may or may not be determined later.

1) The fundamental rule of a NULL is that it propagates.

2) The fundamental rule of 3VL is that your have TRUE, FALSE and
UNKNOWN as the only possible values.

These fundamentals don't go together if you can have a column with a
3VL datatype. The "solution" was to make NULL = UNKNOWN but only in a
BOOLEAN column and then worry about null propagation. This screws up
3VL operators in some pretty awful ways that can drive an SQL engine
nuts:

FALSE OR UNKNOWN = UNKNOWN -- definition of OR
FALSE OR NULL = NULL = UNKNOWN -- null propagation
TRUE OR UNKNOWN = TRUE -- definition of OR
TRUE OR NULL = NULL = UNKNOWN -- null propagation

likewise,

TRUE AND UNKNOWN = UNKNOWN -- definition of AND
TRUE AND NULL = NULL = UNKNOWN -- null propagation
FALSE AND UNKNOWN = FALSE -- definition of AND
FALSE AND NULL = NULL = UNKNOWN -- null propagation

Formatting "scoped" calculated members

Hi,

How do I format calculated members that have been modified in a Scope-statement?

Example:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
END SCOPE;

My calculated member "Test1" looses its formatting in the Scope-statement and is therefore no longer formatted as "#,#.00". How can I change this?

Try the following:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
FORMAT_STRING(THIS) = '#,#.00';
END SCOPE;

Format_String Currency lost by division

Hello!

I use AS 2005 SP1.

If I create a calculated measure "C" with a currency measure ("A") and a non-currency measeasure ("B") like
C=A/B
and define the format_string for the calculated measure as currency. The result is still a non-currency value. Why?

Thanks in advance!
Ole JepsenIf you explicitly specified FORMAT_STRING='Currency' for the calculated measure C, then it will be formatted as currency.

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.

Format Currency - values include NaN

Hi, my report includes a calculated column which I wish to display as
currency.
Unfortunately, some of the calculated values are non-numeric. My report
shows these values as 'NaN'. I would like to display something a bit more
meaningful to the user such as '-'. Is there a way of doing this?Nic wrote:
> I would like to display
> something a bit more meaningful to the user such as '-'. Is there a
> way of doing this?
Write a piece of custom-code like this:
Function CheckValue(value as object) as object
If isnumeric(value) Then
Return FormatCurreny(value)
Else
Return "This is no numeric value"
End If
End Function
and put an expression in the field like:
=code.CheckValue(Fields!MyField.Value)
Maybe you have to play with field-property..i assumed "Standard"
regards
Frank
www.xax.de|||Frank,
Thanks for the information. However, I tried the function you suggested and
the isnumeric function appears to return true for those values which are NaN.
In any case, my report still contains NaN values, which have not been
replaced by the 'This is no numeric value' message. I know that the function
is being used, and I can change what is displayed by changing what is
returned when the isnumeric function is evaluated as true.
Any suggestions?
"Frank Matthiesen" wrote:
> Nic wrote:
> > I would like to display
> > something a bit more meaningful to the user such as '-'. Is there a
> > way of doing this?
> Write a piece of custom-code like this:
> Function CheckValue(value as object) as object
> If isnumeric(value) Then
> Return FormatCurreny(value)
> Else
> Return "This is no numeric value"
> End If
> End Function
> and put an expression in the field like:
> =code.CheckValue(Fields!MyField.Value)
> Maybe you have to play with field-property..i assumed "Standard"
> regards
> Frank
> www.xax.de
>
>
>|||Hi,
I have done a bit more investigation, and have come up with the following,
which seems to work. I would dearly like to know whether this can be
simplified, Franks suggestion seems neater, but I cannot get it to work - any
ideas why?
Function CheckValue(value as object) as object
If IsNum(value) Then
Return FormatCurrency(value)
Else
Return "-"
End If
End Function
Function IsNum(value as object) as boolean
Try
Decimal.Parse(value)
Return True
Catch
Return False
End Try
End Function
"Nic" wrote:
> Frank,
> Thanks for the information. However, I tried the function you suggested and
> the isnumeric function appears to return true for those values which are NaN.
> In any case, my report still contains NaN values, which have not been
> replaced by the 'This is no numeric value' message. I know that the function
> is being used, and I can change what is displayed by changing what is
> returned when the isnumeric function is evaluated as true.
> Any suggestions?
> "Frank Matthiesen" wrote:
> > Nic wrote:
> >
> > > I would like to display
> > > something a bit more meaningful to the user such as '-'. Is there a
> > > way of doing this?
> >
> > Write a piece of custom-code like this:
> >
> > Function CheckValue(value as object) as object
> > If isnumeric(value) Then
> > Return FormatCurreny(value)
> > Else
> > Return "This is no numeric value"
> > End If
> > End Function
> >
> > and put an expression in the field like:
> > =code.CheckValue(Fields!MyField.Value)
> >
> > Maybe you have to play with field-property..i assumed "Standard"
> >
> > regards
> >
> > Frank
> > www.xax.de
> >
> >
> >
> >
> >