Calculated measure's MDX formula in (regular) Non-calculated measure

Hi,

This is sort of an extension to my previous question: converting seconds to HH:MM:SS format. I got the MDX formula for that from deepak. but for that i have to create a Calculated measure!!!!

I already have the measures which display seconds, now i have to create a calculated measure which will display the seconds in HH:MM:SS. but I dont want the extra seconds measure to be displayed. I know i can make it visible=false. but this is cumbersome for me because all this is done programmatically using AMO in a program.

so is there a way to provide the MDX formula in the regaular measure itself without having to create a calculated measure.

Thus display/convert the seconds to the HH:MM:SS format in the original measure?

Regards

I forgot one more thing, can we use the "MeasureExpression" and "FormatString" properties of the measure itself? I tried puttin the MDX formula here but it gives a error.|||

Using the MeasureExpression property is the preferred technique to accomplish what you want. It works for the cases I have used it in. What error do you receive, and what is the MDX you're providing as an expression?

PGoldy

|||Only multiplication and division are allowed for measure expression.|||

Hi,

Thanks for the reply.

Here is the scenario with the MDX formula:

Consider that I have a regular measure M1. The measure M1 contains seconds as data. (eg: 400 seconds, 1000 seconds..etc.) currently what I do is whenever I have a measure which contains seconds and has to be displayed in the HH:MM:SS format:

1. I rename the M1 measure as "_M1" and make it visible=false,
2. Then I create a calulated measure with the name M1 and with the formula TimeSerial(0,0,Measures.[_M1]) and FormatString as 'hh:mm:ss'
3. Now the calculated measure M1 contains the the values of seconds formatted to the HH:MM:SS format. example: (TimeSerial(0,0, 37895) = 10:37:35 ; TimeSerial(0,0,1023) = 00:17:03)

What I require is not to create the extra calculated measure M1, but use the TimeSerial(0,0,MeasureName) formula in the regular measure M1. When I put the TimeSerial(0,0,MeasureName) formula in the MDXExpression property and the FormatString as 'hh:mm:ss', I get the following error : "The Cube xxxx cannot be saved because of the following errors: Errors in the metadata manager. The measure expression of the M1 measure is not of the form [measure1]*[measure2] or [measure1]/[measure2]"

Is there a way out for this? what other alternative is possible?

Regards

Calculated Measure (Previous Act Value) Problem

G'Day all,

I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.

The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))

This works fine until I look at the totals within EXCEL 2002/2003

1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.

2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -

3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.

WAIT - There's more

4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!

Any Ideas??

Does it make sense??

Thanks

MattWell...

ParallelPeriod

Is a udf someone wrote...

How are you connecting to sql server?

Are you connecting to sql server?

Sorry...should've looked first...

I have GOT to start playing with that stuff...never had any real need...|||No, ParallelPeriod is an Analysis Services function. I don't know anything more about it than that.

From the Holy Book:

ParallelPeriod
Returns a member from a prior period in the same relative position as a specified member.

Syntax
ParallelPeriod([Level[, Numeric Expression[, Member]]])

Remarks
This function is similar to the Cousin function, but is more closely related to time series. It takes the ancestor of Member at Level (call it ancestor); then it takes the sibling of ancestor that lags by Numeric Expression, and returns the parallel period of Member among the descendants of that sibling.|||You should it's a powerful addition to SQL Server - only sometimes you can get lost in translation (so to speak) - the MDX statements (language for writing the calcs) sometimes take a while to understand. Hopefully you take a crash course and help me with my problem.

Calculated field in dataset using Previous() function

Hi,
Iam trying to add a calculated field to my report dataset which based on
value of a field in the previous row. The calculated field is defined as
=IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value)+CDbl(Fields!Col3.Value))
. Iam basically checking the value of Col1 in previous row with value in
current row. Iam getting the following error when tried to build the report
"An internal error occurred on the report server. See the error log for more
details.". I could not find anything in error log.
Can anyone help in resolving this problem ?
Thanks,
RKOn Dec 3, 1:35 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> Iam trying to add a calculated field to my report dataset which based on
> value of a field in the previous row. The calculated field is defined as
> =IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value-)+CDbl(Fields!Col3.Value))
> . Iam basically checking the value of Col1 in previous row with value in
> current row. Iam getting the following error when tried to build the report
> "An internal error occurred on the report server. See the error log for more
> details.". I could not find anything in error log.
> Can anyone help in resolving this problem ?
> Thanks,
> RK
I found that the Previous(Fields!XYZ.Value) function only works in the
Table and Matrix expressions -- expresions that are evaluated at
render time. Although it would make sense to have them in the DataSet
as a calculated field, I've not been able to get this to work.
Oracle has LAG and LEAD functions that return the previous/next value
of a Field as part of the query results. Microsoft T-SQL does not
have an equivalent.
-- Scottsql

Calculate values between previous rows

First, thanks to all of those that post to this newsgroup. You've
saved me tons of development time. I am trying to calculate values
between previous rows in my table. The table looks something like
this:
ID Value
1 100
2 200
3 300
4 400
5 ?
I was wondering if there was a way to create an expression that would
calculate the difference between row 4 and row 1. I am expecting row 5
to be 300. I have looked at the previous function, but this does not
seem to be what I am looking for. Any ideas?
Thank you,
Scott Leandro
scottleandro_@.gmail.com (remove the _)save row1 value into a variable and then subtract from it
i.e.
set @.myValue = select value from table where ID = 1
then
select id , value -@.myvalue
from xxx
you might have to work around your design so that value for ID =1 won't
return 0
ken
scottleandro@.gmail.com wrote:
> First, thanks to all of those that post to this newsgroup. You've
> saved me tons of development time. I am trying to calculate values
> between previous rows in my table. The table looks something like
> this:
> ID Value
> 1 100
> 2 200
> 3 300
> 4 400
> 5 ?
> I was wondering if there was a way to create an expression that would
> calculate the difference between row 4 and row 1. I am expecting row 5
> to be 300. I have looked at the previous function, but this does not
> seem to be what I am looking for. Any ideas?
> Thank you,
> Scott Leandro
> scottleandro_@.gmail.com (remove the _)|||SQLKen,
Thanks for the reply. I wish I could do this in a SP, but
unfortunately I have to to do this calculation in a RS table cell
expression.
Thanks,
Scott
SQLKen wrote:
> save row1 value into a variable and then subtract from it
> i.e.
> set @.myValue = select value from table where ID = 1
> then
> select id , value -@.myvalue
> from xxx
> you might have to work around your design so that value for ID =1 won't
> return 0
> ken
>
> scottleandro@.gmail.com wrote:
> > First, thanks to all of those that post to this newsgroup. You've
> > saved me tons of development time. I am trying to calculate values
> > between previous rows in my table. The table looks something like
> > this:
> >
> > ID Value
> > 1 100
> > 2 200
> > 3 300
> > 4 400
> > 5 ?
> >
> > I was wondering if there was a way to create an expression that would
> > calculate the difference between row 4 and row 1. I am expecting row 5
> > to be 300. I have looked at the previous function, but this does not
> > seem to be what I am looking for. Any ideas?
> >
> > Thank you,
> > Scott Leandro
> > scottleandro_@.gmail.com (remove the _)sql

Calculate difference between previous row and insert to new column -problem

Hi!

I have an algorithm that uses cursors to calculate difference between row and row-1 in a certain (int-type) column. How could I insert the difference value to this same table (or generate new dynamic table) as a new column?

I need this information to do some reporting with Reporting Services and show the difference there...

Thanks!

-Jukka

hi,

can you post your proc?|||

My knee-jerk reaction would be to suggest self-joining the table. This depends on whether you have a ready-made criteria that you can use either for keying or ordering your table. A simple-minded example might be something like:

declare @.xample table
( rowId integer primary key,
theValue numeric (5,2)
)
insert into @.xample
select number,
33.34*dbo.rand() + 33.33*dbo.rand() + 33.33*dbo.rand()
from master.dbo.spt_values
where name is null
and number <= 5

select a.rowId,
a.theValue as [A Value],
b.theValue as [B Value],
a.theValue - b.theValue as Difference
from @.xample a
join @.xample b
on a.rowId = b.rowId + 1

/*
rowId A Value B Value Difference
-- - - -
1 6.82 37.87 -31.05
2 20.28 6.82 13.46
3 35.15 20.28 14.87
4 56.60 35.15 21.45
5 35.21 56.60 -21.39
*/