How to add calculated Facts in Measure Group

hi,

I am new to SSAS. i am working up on cube. I want to add calculated fact in the cube.

like... Suppose we have fact table in which we have fields like (ID, Status)

and status can have 3 values (1,2 ,3) . My requirement is Count of rows having Status 1 + Count of rows having status 2.

How to accomplish this task.

Additionally if dimension is required suppose i have Status(statusID) dimension also.

Please provide me urgent solution!

Thanks in advance.

Mandip

Hi Mandip,

before I start I beg for pardon, because I am working on a German version of SSAS.

You can put two new named calculations in Your datasource view.

First.

CASE WHEN Status = 1 THEN 1 ELSE NULL END

(suggested columnname Stat1)

Second:

CASE WHEN Status = 2 THEN 1 ELSE NULL END

(suggested columnsname Stat2)

Now You can use these two new columns as new measures (count or sum).

cheers

B.

|||

Thanks very much!

I know if the status entries are going to be static this would work.

but in my case at anytime in future we can have new status suppose (4)...

then can there any other method by which i need not to add new columns in fact table... and still i can be able to take calculated fact? Means i don't want to change design structure of cube.

Mandip

|||In the situation you describe, status should be implemented as a dimension. As a rule of thumb any time you want to report amounts "by" something, it is a good indicator that you should implement it as a dimension.

How to add a calculated field in query out-put?

Dear All,

I wanted include a calculated field (column) in my query output. how do i go about that?

Please help me.

SELECT amtcol1, amtcol2, amtcol1 + amtcol2 as 'Total'

or

SELECT amtcol1, amtcol2, 'Total' = amtcol1 + amtcol2

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

http://msdn2.microsoft.com/en-us/library/ms145486.aspx

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

http://msdn2.microsoft.com/en-us/library/ms145486.aspx

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.

How to accomplish creating a simple calculated column in a table

Is there a way to create a calculated column in a table like this, or is
there a better way to accomplish this task? I've tried, but SQL keeps
adjusting my precedence and it goofs up the formula:
Let's say the column name is "NetPrice". I want to deduct the discounted
amount from the total to arrive at the net price. Discount is a decimal
value, ie: 25% = .25.
[TotalPrice] - ([TotPrice] * [Discount])Oops, typo below. Should be: [TotalPrice] - ([TotalPrice] * [Discount])
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Bill
See if this helps
CREATE TABLE #Test (
TotalPrice DECIMAL, Discount DECIMAL,
NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
SELECT * FROM #Test
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Thanks Uri. I think I was entering something incorrectly when I originally
entered the formula into the table designer.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePsmpBmnGHA.764@.TK2MSFTNGP03.phx.gbl...
> Bill
> See if this helps
> CREATE TABLE #Test (
> TotalPrice DECIMAL, Discount DECIMAL,
> NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
>
> INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
> SELECT * FROM #Test
>
> "Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
> news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
>