creating a boolean measure

Hello,

I've created a new measure where my source column is of type Boolean and have selected aggregate type "None" (simply because I just need to show true or false while browsing my cube).

However, when i deploy my cube and browse it, it shows empty cells against my dimension.

It doesnt allow me to select aggregate "First Non Empty Value" and i get a messag saying "Semi-additive measure requires a time dimension"

any suggestions would be appreciated...This is my desired output

Accepted

Submission 1 True

Submission 2 False

Submission 3 True

.

.

.

and so on. where Submission is my dimension and Accepted is my Measure.

TIA

russzee

Any reason why you couldn't create, rather than a measure, a dimension like "AcceptStatus", with members "True" and "False"?|||

Deepak,

Thanks for your reply.. Thats a very valid question. I forgot to eloborate that I already have a dimension called "Accepted" and it works just fine while browsing the cube against "Submission".

However , my client application is Performance Point Dashboard Designer so I need to convert this to a Measure to be able to use it as a KPI in the client application.

Thanks

russzee

|||In that case, you could create a calculated measure, which returns a value based on the member of "Accepted" with data, rather than creating a cube measure. If you provide more details of your data and of the required KPI, that would help.

Creating a Boolean EvalExpression comparing dates

To the experts in the field:

There is probably a very simple solution that is avoiding my grasp.

I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:

@.BeforeRunDt==@.CurrenDate

I get an error stating "Cannot convert expression value to propeerty type"

I understand that the result of the expression should be a boolean value but am just struggling on how to create it.

Thanks!

I believe that the following will work:

@.BeforeRunDt == @.CurrentDate ? True : False

Edit: You would set the variable to type boolean, and evaluate as expression to true with the above as the expression...

|||

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

|||

desibull wrote:

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

I would still have expected the expression in your first post to work.

Try replacing it with

(DT_BOOL)(@.BeforeRunDt==@.CurrenDate)

and see if it works.

cheers

Jamie

|||I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...|||

EWisdahl wrote:

I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...

Yeah. it could be similar to the issue talked about here:

NULLs in expressions gotcha

(http://blogs.conchango.com/jamiethomson/archive/2006/10/12/SSIS_3A00_-NULLs-in-expressions-gotcha.aspx)

-Jamie

Creating A Boolean Computed Column

Consider the following columns:

EmpID, Status, TermDate

I want a computed column called OkToDisable as a bit value: True if the TermDate is any day before today.

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

I cant figure out how to do this... SQL keeps complaining that it can't validate the formula.

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to: 'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

Can't you create boolean computed fields?

Thanks.

J

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com
|||

Bit is a boolean type, no? )

I had tried the CASE but I couldn't get it to work properly... It helps when you use the proper syntax. (I had left off the END statement)

Then I had to use a cast, otherwise it was an integer.

Thanks for the reply.

J