Accumulated values in analysis services

Hello,

I'm making a report where I have a matrix showing the weeks of a month in the columns and certain data. The user selects a month and I use it as a parameter to show all the calculations from a cube. I already have all the data needed for the matrix, the problem is that I need an accumulated value in the last row that sums the first week's accumulated value and the second week's value and so on (the month can have 4 or 5 weeks). The first week's accumulated value is the data itself since there is no previous value.

week1

week2

week3

....

data

x1

x2

x3

...

accumulated value

x1

x1+x2

x1+x2+x3

...

I don't know if I can do this from the cube using a mesure or something(i think it would be easier to have all the calculations in the cube),or if it has to be done from the matrix in the report.

I would appreciate all help you can give me. Thanks in advance.

Assuming that you have a hierarchy with something like Year - Month - Week, you should be able to create a calculated "MonthToDate" figure using the PeriodsToDate function.

eg.

CREATE MEMBER Measures.MonthToDate AS SUM(PeriodsToDate([Time].[Year-Month-Week].[Month],[Time].[Year-Month-Week].CurrentMember),Measures.[data])

|||

Hello Darren!,

Thanks for your reply!, that is what I wanted to do :)

Accumulate values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 5
e 4

I want to accumulate this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 12 + 11 = 23
c 8 23 + 8 = 31
d 5 31 + 5 = 36
e 4 36 + 4 = 40

Thks for your help

Rgds

Harry

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500

|||

What would I have to do if i want to decrement this values?

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Hope to recieve some news soon

Rgds & a lot of thks

Harry

|||

There are endless variations depending on table structure, data and what you want to do with the data.

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

That is a good method and works perfectly for relatively small number of records

Unfortunatly for a large number it is too slow ,,, :(:(:( do you have another faster method ?

I'm a little desparate

Thanks

|||Yes. do it in your front end application or reporting tool.

Accumulate values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 5
e 4

I want to accumulate this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 12 + 11 = 23
c 8 23 + 8 = 31
d 5 31 + 5 = 36
e 4 36 + 4 = 40

Thks for your help

Rgds

Harry

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500

|||

What would I have to do if i want to decrement this values?

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Hope to recieve some news soon

Rgds & a lot of thks

Harry

|||

There are endless variations depending on table structure, data and what you want to do with the data.

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

That is a good method and works perfectly for relatively small number of records

Unfortunatly for a large number it is too slow ,,, :(:(:( do you have another faster method ?

I'm a little desparate

Thanks

|||Yes. do it in your front end application or reporting tool.

Accessing Values Parameter Values from another report

Hi, How can I display a value of a report parameter from one report into a textbox on another report?

If you are calling the 2nd report from the first, setup a parameter in the 2nd report, and pass that value to it from the first report. You can set this up in the navigation window.

BobP

Accessing sub totals in a matrix

Hi,

How can I access the subtotal cells/values from each of the columns in Matrix and use them for calculations on other places in the report?

Thanks.

Any body?

How can I access the sub-totals of a Matrix as I need to use them in calculations in another place on the report? How can I programatically access the matrix report item's cell or text box values, particularly the sub totals?

Thanks,

|||Does any one know how the sub total field can be accessed in the matrix?|||

Right click on your Column Group Header to acces the Column Subtotal. Same with the Row Group header for the Row Subtotal. I don't think you can access the Subtotal value in other places on the report though.

-Aayush

|||Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column.

Accessing sub totals in a matrix

Hi,

How can I access the subtotal cells/values from each of the columns in Matrix and use them for calculations on other places in the report?

Thanks.

Any body?

How can I access the sub-totals of a Matrix as I need to use them in calculations in another place on the report? How can I programatically access the matrix report item's cell or text box values, particularly the sub totals?

Thanks,

|||Does any one know how the sub total field can be accessed in the matrix?|||

Right click on your Column Group Header to acces the Column Subtotal. Same with the Row Group header for the Row Subtotal. I don't think you can access the Subtotal value in other places on the report though.

-Aayush

|||Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column.

Accessing stored proc multiple return values

Hi,
I have a problem. I have two stored procs. One I am building currently
(sp_load) and another that is already in the data warehouse and which I
have no control over (sp_log_event).
sp_log_event is for control logging. It accepts a process name
parameter. It outputs 3 return parameters by issuing the following
command:
SELECT
load_id,
last_succ_load_id,
datEventDate
FROM
ctl_event_log_header
WHERE
load_Id = @.intLoadId
I am no expert on this but as I understand it these are technically not
output parameters. If I create an Execute SQL Task in DTS I have the
option of setting these 3 return values to my global variables in my
package - which is easy enough and I am already doing this.
However my problem is that I now need to call this (sp_log_event) from
within the stored proc I am creating (sp_load). Something like EXEC
MY_SP @.processname
If the return was an output parameter i could simply do EXEC MY_SP
@.processname, @.loadid output
Also if it was just one return value I could do
EXEC @.loadid = (MY_SP @.processname)
But it is neither of these scenarios and I can't work out how I can get
access to these 3 returned values from the confines of my procedure.
load id is a primary key so the select will definitely only return one
record. how do i get access to the 3 return variables and assign them
to variables within my stored proc (sp_load)evs
BOL has very good examples how to use storerd procedure that has a few
OUTPUT parameters
BTW , it is really bad practice to use sp_ prefix to name stored
procedures, because in that way SQL Server is going to check for system
stored procedures first
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a problem. I have two stored procs. One I am building currently
> (sp_load) and another that is already in the data warehouse and which I
> have no control over (sp_log_event).
> sp_log_event is for control logging. It accepts a process name
> parameter. It outputs 3 return parameters by issuing the following
> command:
> SELECT
> load_id,
> last_succ_load_id,
> datEventDate
> FROM
> ctl_event_log_header
> WHERE
> load_Id = @.intLoadId
>
> I am no expert on this but as I understand it these are technically not
> output parameters. If I create an Execute SQL Task in DTS I have the
> option of setting these 3 return values to my global variables in my
> package - which is easy enough and I am already doing this.
> However my problem is that I now need to call this (sp_log_event) from
> within the stored proc I am creating (sp_load). Something like EXEC
> MY_SP @.processname
> If the return was an output parameter i could simply do EXEC MY_SP
> @.processname, @.loadid output
> Also if it was just one return value I could do
> EXEC @.loadid = (MY_SP @.processname)
> But it is neither of these scenarios and I can't work out how I can get
> access to these 3 returned values from the confines of my procedure.
> load id is a primary key so the select will definitely only return one
> record. how do i get access to the 3 return variables and assign them
> to variables within my stored proc (sp_load)
>|||Hi Uri,
Thanks for the reply. First of all, what is BOL? :)
Second of all - I am not actually naming my stored procs like that. I
just used that for simplicity. They are actually USP_CTL_xxx and
USP_ETL_xxx
Cheers though.
Uri Dimant wrote:
> evs
> BOL has very good examples how to use storerd procedure that has a few
> OUTPUT parameters
> BTW , it is really bad practice to use sp_ prefix to name stored
> procedures, because in that way SQL Server is going to check for system
> stored procedures first
>
>
> "evs" <evan.winstanley@.gmail.com> wrote in message
> news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...|||evs
BOL -Books On Line (tool suppliedb by MS with SQL Server)

> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
I was referencing to <(sp_log_event). from your previous post
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149052185.818089.227430@.h76g2000cwa.googlegroups.com...
> Hi Uri,
> Thanks for the reply. First of all, what is BOL? :)
> Second of all - I am not actually naming my stored procs like that. I
> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
> Cheers though.
>
> Uri Dimant wrote:
>
>|||On 30 May 2006 21:38:45 -0700, evs wrote:

>Hi,
>I have a problem. I have two stored procs. One I am building currently
>(sp_load) and another that is already in the data warehouse and which I
>have no control over (sp_log_event).
>sp_log_event is for control logging. It accepts a process name
>parameter. It outputs 3 return parameters by issuing the following
>command:
>SELECT
> load_id,
> last_succ_load_id,
> datEventDate
>FROM
> ctl_event_log_header
>WHERE
> load_Id = @.intLoadId
>
>I am no expert on this but as I understand it these are technically not
>output parameters. If I create an Execute SQL Task in DTS I have the
>option of setting these 3 return values to my global variables in my
>package - which is easy enough and I am already doing this.
>However my problem is that I now need to call this (sp_log_event) from
>within the stored proc I am creating (sp_load). Something like EXEC
>MY_SP @.processname
>If the return was an output parameter i could simply do EXEC MY_SP
>@.processname, @.loadid output
>Also if it was just one return value I could do
>EXEC @.loadid = (MY_SP @.processname)
>But it is neither of these scenarios and I can't work out how I can get
>access to these 3 returned values from the confines of my procedure.
>load id is a primary key so the select will definitely only return one
>record. how do i get access to the 3 return variables and assign them
>to variables within my stored proc (sp_load)
Hi evs,
CREATE TABLE #tmp ( load_id -- datatype
, last_succ_load_id -- datatype
, datEventDate -- datatype
);
INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
EXEC MY_SP @.processname;
SELECT load_id, last_succ_load_id, datEventDate
FROM #tmp;
DROP TABLE #tmp;
Hugo Kornelis, SQL Server MVP|||Hugo,
Thank you so much mate! Works perfectly. I was aware of temporary
tables I have just never used them before and didn't think of it as an
option. Thanks again.
Hugo Kornelis wrote:
> On 30 May 2006 21:38:45 -0700, evs wrote:
>
> Hi evs,
> CREATE TABLE #tmp ( load_id -- datatype
> , last_succ_load_id -- datatype
> , datEventDate -- datatype
> );
> INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
> EXEC MY_SP @.processname;
> SELECT load_id, last_succ_load_id, datEventDate
> FROM #tmp;
> DROP TABLE #tmp;
> --
> Hugo Kornelis, SQL Server MVP

Accessing rows of a table using script task

Hi all,

Can anyone tell me how to access all the rows in a table using script task?

I want to access each row in a table get their values and put it in a global variable. Can anyone hwlp mw with this please.

Thanks in advance,

Why do you want to use the Script Task for this? You could just use an OleDBSource and push it to a DataReaderDestination (DataSet)

What datatype will you be using for your global variable? Will it be a single variable, or multiple variables?

|||

Hi,

Thank you Sean

I am actually logging all the status information of my package at each stage into the table which has about 20 columns and on the completion of the package i wanted send an email which gets its information from the columns values of the table.

Thanks,

|||

if you want to send 1 email for each row of the table, then you could make a dataflow that uses your logging table as a source and uses a recordset destination. then use a foreach loop (containing the sendmail task) over the recordset to send 1 mail per row. this is if you want to send the emails at the end of all other processing, when the status table is filled up.

Accessing return from sp_monitor

When I run the stored procedure sp_monitor through VB I onlyseem to be able to access the first 3 values returned, see code below. When I run the stored procedure through the enterprise manager I can see about 13 returned values. The reader loop only seems to have one iteration, with 3 arguments, how do I access the others?

cmd.CommandText = "sp_monitor"
cmd.CommandType = CommandType.StoredProcedure
reader = cmd.ExecuteReader()

While reader.Read()
results.AppendLine(String.Format("{0}, {1}, {2}", reader.GetName(0),reader.GetName(1), reader.GetName(2)))
results.AppendLine(String.Format("{0} {1} {2} {3}<br/>",reader(0), reader(1), reader(2), ctr))
ctr = ctr + 1
End While

If you want to retrieve multiple result sets using SqlDataReader, you can use SqlDataReader.NextResult to go to next result set. For example:

cmd.CommandText = "sp_monitor"
cmd.CommandType = CommandType.StoredProcedure
reader = cmd.ExecuteReader()
While reader.HasRows
While reader.Read()
results.AppendLine(String.Format("{0}, {1}, {2}", reader.GetName(0), reader.GetName(1), reader.GetName(2)))
results.AppendLine(String.Format("{0} {1} {2} {3}<br/>", reader(0), reader(1), reader(2), ctr))
ctr = ctr + 1
End While
reader.NextResult()
End While

Or you can useSqlDataAdapter to fill the result sets into a DataSet.