Showing posts with label calculated. Show all posts
Showing posts with label calculated. Show all posts

Tuesday, March 27, 2012

Help with Calculated member

I'm trying to create a calculate member in SQL Server 2005 that will return a distinct count of clients year to date for all dates within my time dimension. I have figured out that if I create a measure that returns a distinct count then I can use the aggregate function and YTD function to get the distinct count YTD for any date. However, the aggregate function requires a slicer dimension (i think) and every time I put the mdx into the expression box, it does not like the syntax.

I have tried this:

With member [Authorization Service Date].[Calendar].[BlaBla] as

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

and I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

returns error: Error 1 MdxScript(DSS) (67, 4) The function can be called only on a named set. 0 0

I don't know wht this means

Any help would be deeply appreciated

I think you are looking to use the CURRENTMEMBER function instead of CURRENT.

I think this will simply take you to your next challenge. If you run this query, you'll likely get NULL for your result.

I think something like this will better serve your needs:

Code Snippet

With member [Measures].[x] as

COUNT(

EXISTS(

[Customer].[Customer].[Customer].Members,

YTD([Delivery Date].[Calendar].CurrentMember),

'Internet Sales'

)

)

select

[Measures].[x] on columns

from [Adventure Works]

where [Delivery Date].[Date].[June 1, 2002]

;

|||

Bryan,

I appreciate your taking your time to assist me with this. Unfortunately, it did not work. As translated, your mdx on my system looks like

with member [Measures].[x] as

COUNT(

EXISTS(

[CCE Members].[MemberId].Members,

YTD([Authorization Service Date].[Calendar].CurrentMember),

'Authorization Service Days Count'

)
)

SELECT [Measures].[x] ON 0

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

I get x[:#]Error

Just for your info however, I know that all of the underlying attributes and metrics join up properly becuase when I execute:

SELECT [Measures].[Authorization Service Days Count] ON 0,

[CCE Members].[MemberId].Members ON 1

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

it works.

Again, your assistance is trully appreciated.

Thanks,

Michael

|||

Two things I noticed in the EXISTS function. Try changing the set to include just the leaf level members:

[CCE Members].[MemberId].Members --> [CCE Members].[MemberId].[MemberId].Members

Also, is 'Authorization Service Days Count' the name of the measure or the measure group? You need to identify the measure group.

B.

sql

Friday, March 23, 2012

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Monday, March 12, 2012

Help With a Calculated Member Calculation

Hello,

I have a fact file that includes a measure called Credit Hours. I also have a dimension called DimTerm what includes time data for two terms: 05Fall and 06Fall. My goal is to get a number change between 05 and 06 terms. So I created a calculated member with the logic below but my percentage change is always %0.00. Maybe someone knows a way of doing this through MDX which would allow me to connct to Term values?

([Measures].[Credit Hours] - [Measures].[Credit Hours]) / [Measures].[Credit Hours]

Thanks!

Take a look at the ParallelPeriod function. Your calculated member will look something like

([Credit Hours] - ([Credit Hours], ParallelPeriod(...))) / [Credit Hours]

Wednesday, March 7, 2012

HELP to write stored procedure whose values are calculated automatically in database

Hi frdz,

I m creating my web-application in asp.net with C# 2005 and using sql server 2005.

I have created the stored procedure for the insert,update.

I want to know how to write the mathematical calculations in the stored procedure..

Pls tell me from the below stored procedure were i m making the mistake ??

As the discount and the total amount are not calculated by itself...and stored in the database

How to convert the

@.discpercentnumeric(5,2) to
@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...


ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend



Thanxs in adv...

Hello my friend,

It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.

Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.

Kind regards

Scotty

|||

thanxs for the reply...

can u pls help to calculate this for my above stored procedure its not working..

select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster

|||

Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?

select @.discamt=(@.opbal - @.clbal )* @.discpercent/100

should do.


|||


select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only

I tried with this also but don't work pls help me...

if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end

thanxs for ur replies....



|||

Could it be because either @.opbal or @.clbal is null?

I would use the following: -

IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END

SELECT @.Total

Kind regards

Scotty

|||

thanxs very much again...

Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??

What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution

|||

Hello again Sheenaa,

You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.

Kind regards

Scotty