Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

You need to use CREATE MEMBER syntax instead of WITH MEMBER. Otherwise, it should work.sql

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated Average w/o a dimension specified

OK, Here's another one...

I want to create a calculated member that compares the current member to the average of the other members at the same level the member is in.

The catch is that I want this to be a calculated member in the cube that works regardless of what dimensions/hierarchies the data is broken down with.

The MDX below shows what I want to do except that it requires the SERIAL_NUM hierarchy to be hardcoded:

Code Snippet

WITH MEMBER [Avg] AS AVG([SERIAL_NUM].Siblings, Measures.[Count Of Failures])

SELECT NON EMPTY { [Measures].[Count of Failures] , [Avg]} ON COLUMNS, NON EMPTY { ([Vehicle Info].[SERIAL_NUM].[SERIAL_NUM].ALLMEMBERS ) } ...

The end result should be something like this:

SERIAL_NUM Count Of Failures Avg 423343 10 30 432434 20 30 123123 60 30

But I again, I don't know what dimensions hierarchies will be used later on, I just want all the values the user is currently looking at be included in the calculated measure. If they replaced SERIAL_NUM with MODEL as the dimension, I want it to perform the same calculated on the data provided from that query.

I tried something like MEASURES.SIBLINGS, MEASURES.MEMBERS, etc, but they returned only the current value, as it appears to be the lowest hierarchy so it has no siblings.

(The end result, BTW, is not the average, but the number of std dev's a certain value is from the average of the other values, essentially a dynamic outlyer flag. So it'll be (Count of Failures - Avg (siblings)) / StdDev (siblings), I'm just trying to keep things simple)

Geof

Well, I thought I had it for a moment...

The function I tried was Axis(0)...

Code Snippet

IIF(MEASURES.[Count of Failures] = Null, Null,

(MEASURES.[Count of Failures] - AVG(AXIS(0), MEASURES.[Count of Failures])) / StDev(Axis(0), MEASURES.[Count of Failures])

)

Seems like it should work, but it's not giving the correct data. The data varies according to where I've drilled down to, and isn't consistent. Perhaps I could filter the Axis(0) set somehow? It looks like it's including the subtotal members from each dimension (the more dimensions I add, the more rows are reported in the Axis(0) set)

|||

You might be able to use a combination of generate and/or filter to filter out the dimension in MDX, but given that you don't really know how many dimensions could be stacked on the axis, I can't envisage how it would really work.

The other problem with Axis(0) is that it refers to the column axis, if a user flips the query around to put the dimension on the rows you would need to use Axis(1), but there is not easy way to tell what the user is doing.

How many dimensions would you want to use with this calculation? Would it be feasable to create a calculated measure for each applicable dimension? This might also lead to some interesting analysis opportunities.

|||

The biggest problem I've got with Axis(0) is that it include the subtotals and grand totals as simply another row, and since I don't have a fixed dimension to work with, I've been unable to filter them out of the set.

The Rows/Columns issue you mention is a problem, but not the end of the world. The users doing the queries will be reasonably experienced, just not enough to craft their own MDX. I can warn them about that scenario (if the other problem can be fixed)

How many dimensions? So far 4 is the largest number for the basic queries currently planned, though when people start wanting more estoteric comparisons, it will increase. So that could be feasible.

|||This sort of filtering might be something that is best suited to a .Net stored proc. I think you might need to iterate over the set a couple of times to figure out what you are looking at and which members are at the lowest level on the axis. The best set of stored proc samples I know if is at a project which I contribute to at www.codeplex.com/asstoredprocedures

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

|||Hi,

I am also facing same problem...do u got any solution....
plz help mesql

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Cache: rsInvalidDataSourceCredentialSetting

Error-Message:
"The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in the
report server database. (rsInvalidDataSource-CredentialSetting) (Report
Services SOAP Proxy Source)".
Our previous action:
In SQL-Server Management Studio, Connect to Reporting Services, Home,
myReports, someReportName.
Right-Click on this report, Properties.
Here on the left side: Execution
Right Side: Cache the Report, OK
Now happens the Error cited above.
Logged on as Local Adminsitrator, as usual, no Domain configured, standalone
Server in Workgroup.
The whole ReportServer was installed and configured from scratch with all
possible patience:
All the following programs in english:
Win Enterprise 2003 R2, SQL Server 2005 completely, SQL-Server SP1, Post SP1
hotfixes and ALL recommended Security Updates/Fixes by Windows Update.Hi Henry,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You can not
configure the Report Cache and you get the following error message:
"The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in the
report server database. (rsInvalidDataSource-CredentialSetting) (Report
Services SOAP Proxy Source)".
If I misunderstood your concern, please feel free to let me know.
Since the report cache need a credential to connect to the datasource to
render the report, it will need the credential stored in the report server
database.
To store the credential, please do the following:
1. Open the Management Studio, connect to Reporting Services, go to the
report you want to configure.
2. Expand the left panel, and click the Data Sources, right-click your data
source name and click Properties.
3. Check the Credentials stored securely on the report server and specify a
Login Name and password.
4. Click Ok and try to configure the report cache.
Hope this will be helpful, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
thanks to your advice we finally achieved to configure caching.
It follows here:
- our initial security-config
- the way we solved the problem by temporarily changing this config
- ONE Question
All Reports use a shared datasource.
This shared datasource is configured to connect using Windows integrated
security.
Our security-config is as following:
- SQL-Server is configured for "SQL Server and Windows Authentication mode"
- The reportserver machine is in a workgroup, not in a Domain
- Anonymous access to the ReportServer website is allowed via the
IUSR_machine User
- we created a local Server windows-group "Report-Reader" in Computer
Management
- we added the IUSR_machine Account to this group
- we added this windows-group as a "System User" (not System Administrator)
to Report Services
- we gave this windows-group the Browser-Role
- we gave this windows-group the datareader role with explicit rights to
select and execute on the target databases
We achieved enabling caching only after configuring this shared datasource
temporarily with:
- Connection: Credentials stored securely on the report server
- sa, password
standard config before and after: Windows integrated security (= the
IUSR_machine account) did not work
Question:
Me, the SQL Server Admin, with all possible rights, I want to configure the
caching behaviour.
Why does there need to be configured differently any shared datasource
rights to do this?
What has the config of the datasource to do with ME wanting to change a
behaviour?
Muchas Gracias, yours Henry|||Hello Henry,
Thank you for your update and glad to hear the information is helpful.
I would liket to explain that why we need to store the Credential.
Since the Report Cache is an automatical process to render the report, it
will need the credential to connect to the datasource to get the data. If
you use the Windows integrated security, it will be fine if an user try to
access the report. But the Cache can not connect to the datasource because
it does not know which credential it should use to connect to the
datasource. Thus, you need to store the credential in the database.
Don't worry about the security because the database use a symetic key to
encrypt the credential.
Here is an article for your reference:
Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330(d=ide).aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Henry,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
OK, resolved.
But one big question:
If I give e.g. the sa-credentials to be stored securely in the data-source,
and if any one user achieves to send some "= 1; drop someDB; --" Code to our
Stored Procedures that sometimes use dynamic SQL, then we're fried.
Seems to be necessary to configure a SQL-Server account with less privileges.
Or let caching out.
Saludos, Henry|||Hi Henry,
Thank you for the update.
The SQL injection is really a big problem. I recommend you to configure a
SQL account which only have select permission on the database since the
reporting service only need to get the data from datasource.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Henry,
How are you doing on this thread? As Wei has been OOF due to some urgent
issues, I'm helping him contact you to see whether you still have any
problems on this. For the security threaten you mentioned earlier, Wei and
I have discussed this with our product team's engineers and their
suggestion is that we recommend the reporting service datasource always use
a readonly permission identity to access database server since reporting
service report only need readonly access. As always, if you have any
further issues, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Steven,
thank you very much for your invested time.
We are happy, that SSRS-caching could finally be switched on in our
dev-/test-environment.
We'll investigate it further, when our product is live and running and we
need to optimize it.
Have a nice day, greetings from Peru
Yours Henry

C# with ADO 2.8: Receiving "Current Recordset does not support updating"

Hi All,

I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".

I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.

I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.

The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.

My code is:

// Connection string template. Filled in properly in real code.
strConnect = "Server={0};Database={1};"

// Set the connection properties.
this.SQLConnection.ConnectionString = strConnect;
this.SQLConnection.Provider = "SQLOLEDB";
this.SQLConnection.Mode = adModeReadWrite;

// Open the connection.
this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);

=================

// Create the ADO objects needed.
dbRSAdd = new Recordset();

// Open the recordset.
dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);

// Cycle through each record to add.
dbRS.MoveFirst();
for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++)
{
// Add a new record.
dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

...
}

// NOTE: The code crashes with the call to 'AddNew'.

Any advice?Hi All,

(Head between my legs.) I can't tell you how long I looked at the code only to look at the code on this post and immediately see the problem. I had two record sets. The first record set (dbRS) is read only. I made the stupid mistake of not putting dbRSAdd with the AddNew. VB6 uses a 'With' statement, whereas C# does not support a 'With' statement. My problem was that I wasn't careful in copying and pasting, when filling in the prefix before the '.'.

Oops!