Calculated Fields

When you use the FORMULA attribute for a column in EM does SQL Server
actually store the "calculated" data or just materialize it when it's
fetched ?
TIA
Liz
Liz wrote:
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
The value is not permanently stored unlesss you create an index on the
computed column. For that reason computed columns in tables are of
little value unless you intend to index them.
If you try to update a computed column directly you will get an error.
That's why for most purposes I think it is safer and makes the meaning
clearer if you put computations into views rather than tables. Views
can be indexed too, so I would only consider a computed column where an
indexed view or some other method couldn't achieve the same purpose.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1166266554.326117.237240@.16g2000cwy.googlegro ups.com...
> Liz wrote:
> The value is not permanently stored unlesss you create an index on the
> computed column. For that reason computed columns in tables are of
> little value unless you intend to index them.
I tried it out because I'm working with a table that has incredibly bizarre
column names so it just makes it easier to reference a+b+c as MYFIELD ... I
did index the calculated column and then had a brief glitch; a row which
should have matched did not ... then the problem "went away" ... which makes
me uneasy. If the "underlying" columns are indexed does the "calculated
column" effectively "use" the index ? I guess I can look at an execution
plan and find out ...
Thanks,
Liz

> If you try to update a computed column directly you will get an error.
> That's why for most purposes I think it is safer and makes the meaning
> clearer if you put computations into views rather than tables. Views
> can be indexed too, so I would only consider a computed column where an
> indexed view or some other method couldn't achieve the same purpose.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Calculated fields are 'calculated' upon request. The data is not
materialized. (There is a way, however, involving indexing... But that is
not 'normal'.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:OeszSGNIHHA.2632@.TK2MSFTNGP06.phx.gbl...
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
>

Calculated Fields

When you use the FORMULA attribute for a column in EM does SQL Server
actually store the "calculated" data or just materialize it when it's
fetched ?
TIA
LizLiz wrote:
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
The value is not permanently stored unlesss you create an index on the
computed column. For that reason computed columns in tables are of
little value unless you intend to index them.
If you try to update a computed column directly you will get an error.
That's why for most purposes I think it is safer and makes the meaning
clearer if you put computations into views rather than tables. Views
can be indexed too, so I would only consider a computed column where an
indexed view or some other method couldn't achieve the same purpose.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1166266554.326117.237240@.16g2000cwy.googlegroups.com...
> Liz wrote:
>> When you use the FORMULA attribute for a column in EM does SQL Server
>> actually store the "calculated" data or just materialize it when it's
>> fetched ?
>> TIA
>> Liz
> The value is not permanently stored unlesss you create an index on the
> computed column. For that reason computed columns in tables are of
> little value unless you intend to index them.
I tried it out because I'm working with a table that has incredibly bizarre
column names so it just makes it easier to reference a+b+c as MYFIELD ... I
did index the calculated column and then had a brief glitch; a row which
should have matched did not ... then the problem "went away" ... which makes
me uneasy. If the "underlying" columns are indexed does the "calculated
column" effectively "use" the index ? I guess I can look at an execution
plan and find out ...
Thanks,
Liz
> If you try to update a computed column directly you will get an error.
> That's why for most purposes I think it is safer and makes the meaning
> clearer if you put computations into views rather than tables. Views
> can be indexed too, so I would only consider a computed column where an
> indexed view or some other method couldn't achieve the same purpose.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Calculated fields are 'calculated' upon request. The data is not
materialized. (There is a way, however, involving indexing... But that is
not 'normal'.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:OeszSGNIHHA.2632@.TK2MSFTNGP06.phx.gbl...
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
>

Calculated Fields

When you use the FORMULA attribute for a column in EM does SQL Server
actually store the "calculated" data or just materialize it when it's
fetched ?
TIA
LizLiz wrote:
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
The value is not permanently stored unlesss you create an index on the
computed column. For that reason computed columns in tables are of
little value unless you intend to index them.
If you try to update a computed column directly you will get an error.
That's why for most purposes I think it is safer and makes the meaning
clearer if you put computations into views rather than tables. Views
can be indexed too, so I would only consider a computed column where an
indexed view or some other method couldn't achieve the same purpose.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1166266554.326117.237240@.16g2000cwy.googlegroups.com...
> Liz wrote:
> The value is not permanently stored unlesss you create an index on the
> computed column. For that reason computed columns in tables are of
> little value unless you intend to index them.
I tried it out because I'm working with a table that has incredibly bizarre
column names so it just makes it easier to reference a+b+c as MYFIELD ... I
did index the calculated column and then had a brief glitch; a row which
should have matched did not ... then the problem "went away" ... which makes
me uneasy. If the "underlying" columns are indexed does the "calculated
column" effectively "use" the index ? I guess I can look at an execution
plan and find out ...
Thanks,
Liz

> If you try to update a computed column directly you will get an error.
> That's why for most purposes I think it is safer and makes the meaning
> clearer if you put computations into views rather than tables. Views
> can be indexed too, so I would only consider a computed column where an
> indexed view or some other method couldn't achieve the same purpose.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Calculated fields are 'calculated' upon request. The data is not
materialized. (There is a way, however, involving indexing... But that is
not 'normal'.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:OeszSGNIHHA.2632@.TK2MSFTNGP06.phx.gbl...
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
>

Calculated Columns

Hi,
In have a select query with one calculated column in the select column
collection. When I change the select FROM clause from table name to a table
defined with select statement, I get error. The query is:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
(Please copy paste the query somewhere else, it’s much easier to read and
understand the problem)
The error I get is:
Server: Msg 207, Level 16, State 3, Line 24
Invalid column name 'DISTRIBUTION CENTER'.Can you replace the * with the column names and then try executing it and
repaste the query if it doesn't work (with the error message).
Thanks
Omnibuzz|||Hi Omnibuzz, thanks for the quick response, but its not working. Here is the
query again:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:

> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Sorry, this is the updated query:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:

> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Can you post the create script for my_table.
"Aviad" wrote:
> Hi Omnibuzz, thanks for the quick response, but its not working. Here is t
he
> query again:
> DECLARE @.YearsSet TABLE (
> [YEARCOLTIME] VARCHAR(8000))
> INSERT @.YearsSet
> SELECT [YEARCOLTIME]
> FROM (SELECT *,
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> WHERE [YEARCOLTIME] = N'2000'
> OR [YEARCOLTIME] = N'2001'
> GROUP BY [YEARCOLTIME]
> DECLARE @.ProductsSet TABLE (
> [PRODUCTS] VARCHAR(8000))
> INSERT @.ProductsSet
> SELECT [PRODUCTS]
> FROM [MY_TABLE]
> WHERE [PRODUCTS] = N'IES XXI JK'
> OR [PRODUCTS] = N'Troy Sys 4'
> OR [PRODUCTS] = N'Core Series 12'
> GROUP BY [PRODUCTS]
> SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
> [@.YEARSSET].[YEARCOLTIME],
> (SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
> [AGGREGATEDSALES AMT],
> (SELECT AVG([SALES AMT])
> FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
> FLOAT))) AS [SALES AMT]
> FROM (SELECT [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS
> [YEARCOLTIME]
> FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
> INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
> ON [@.PRODUCTSSET].[PRODUCTS] =
> [FORMULATIMELEVELTABLE].[PRODUCTS]
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
> AND [@.YEARSSET].[YEARCOLTIME] =
> [FORMULATIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
> [AGGREGATEDFORMULA0]
> FROM (SELECT [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> INNER JOIN @.YearsSet AS [@.YEARSSET]
> ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
> [@.YEARSSET].[YEARCOLTIME]
>
> "Omnibuzz" wrote:
>|||Here it is:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[Products] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Distribution Center] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Sales Amt] [float] NULL ,
[Time] [datetime] NULL ,
[Col100] [int] NOT NULL
) ON [PRIMARY]
GO
"Omnibuzz" wrote:
> Can you post the create script for my_table.
>
> "Aviad" wrote:
>|||Hi Aviad,
The create table script was having a syntax error. Fixed it.
But here it seems to work fine in my machine :)
try removing the distribution center column from the select and the group by
of the final query and try..|||Hi,
Its not working here, somehow it doesn’t "recognize" the column:
[TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
Which in the calculated column.
"Omnibuzz" wrote:

> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||and the fixed script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[Products] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Distribution Center] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Sales Amt] [float] NULL ,
[Time] [datetime] NULL ,
) ON [PRIMARY]
GO
sorry :->
"Omnibuzz" wrote:

> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||I remember someone posting something like this...
the query analyzer was taking the next line of the comment as commented or
something similar.
I am not able to see what the problem might be :(
few last resorts..
If MY_TABLE is really your table then rename the column with an "_" in
between and try to get rid of this square brackets.
try it in a new QA window (Never knew I could get down to this level :)
use it as "DISTRIBUTION CENTER" instead of sq bracks..
"Aviad" wrote:
> Hi,
> Its not working here, somehow it doesn’t "recognize" the column:
> [TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
> Which in the calculated column.
> "Omnibuzz" wrote:
>

Calculated Column performance

Hi,
Is calulation more efficent in the SQL CODE or in a Calculated Column? If
I'm correct the Calculated Column is done on the client SELECT query every
time where as my INSERT TSQL code will only do it once on the INSERT?
Thanks
DECLARE
@.ACCDCC_Table TABLE
(cnt INT NULL,
Time INT NULL,
Location FLOAT NULL,
FPM1 FLOAT NULL,
FPM2 FLOAT NULL,
FPM_Diff AS (
CASE
WHEN
FPM1 IS NULL OR
FPM2 IS NULL THEN NULL
ELSE
FPM2 - FPM1
END),
AccDcc VARCHAR(10) NULL
)
as per doing this:
UPDATE
@.ACCDCC_Table
SET
FPM_Diff = FPM2 - FPM1
WHERE
FPM_Diff IS NULL
--
don> Is calulation more efficent in the SQL CODE or in a Calculated Column? If
> I'm correct the Calculated Column is done on the client SELECT query every
> time where as my INSERT TSQL code will only do it once on the INSERT?
That's correct.
The flip side is that you will have to constantly maintain the value in the
"calculated" column if you're going to rely on doing it manually.
A|||Thanks
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Think you meant you have to maintain it when you are doing it once, on
update. If it's calculated automatically, every time you "select" the
column, the value is not being stored in database, It's being re-calculated
every time you do a select, so there's no maintenance required.
Which is better depends on whether you need
A) Insert/Update Performance, and/Or storage Size Constraints -- Use
Calculated Column, or
B) Select Performance is the main concern -- Then Use persisted Column and
maintain it upon every Insert/Update
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Thanks
"CBretana" wrote:
> Think you meant you have to maintain it when you are doing it once, on
> update. If it's calculated automatically, every time you "select" the
> column, the value is not being stored in database, It's being re-calculate
d
> every time you do a select, so there's no maintenance required.
> Which is better depends on whether you need
> A) Insert/Update Performance, and/Or storage Size Constraints -- Use
> Calculated Column, or
> B) Select Performance is the main concern -- Then Use persisted Column and
> maintain it upon every Insert/Update
> "AB - MVP" wrote:
>|||I guess someone missed the meaning of "quotes" around "calculated"... <sigh>
"donron" <donron@.discussions.microsoft.com> wrote in message
news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
> Thanks
> "CBretana" wrote:
>|||that would be me... but rereading, (I may be just dense this am), but I'm
still not sure what you mean by it... I thought it was just a typo...
"AB - MVP" wrote:

> I guess someone missed the meaning of "quotes" around "calculated"... <sig
h>
>
>
> "donron" <donron@.discussions.microsoft.com> wrote in message
> news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
>
>

Calculated column not working

Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much.

Dave Roda

Change the stored proc so that it only performs the calulation if both columns contain positive integers. There's no point calculating an average if either column has no value.

If ((TotalRating > 0 And TotalRating Is Not Null) AND (TotalVotes > 0 AND TotalVotes Is Not Null))

Update Tablename Set AverageRating = TotalRating/TotalVotes...

|||

Hi Dave,

Based on your description, I understand that you're getting null values in AverageRating if one of the other columns is null.

Actually, this is by design. In this case, you have to check for nulls using ISNULL in your calculate stored procedure.

HTH. If anything is unclear, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||This is a very helpful post thank you. I was wondering if there was a way to use conditionals in a stored procedure. Could you please give me a quick example? I understand the concept i will check if the cells values are null and set the value to one instead of incrementing null+1. That would work but it would also be just as easy to set the default value of the columns to 0 but i cannot figure out how to do this. Answers to either of those questions would be greatly appreciated. Thanks so much!|||

I'm not sure what's causing your problem with the default value. You shouldn't have any problem setting the default for a number column to 0; Can't you change this in table design mode (if sql doesn't work)? If you're using SQL express, you candownload a SQL Enterprise Manager-like interface to access and change database info, if you are more familiar with SQL Server than Server Explorer (again, I don't know what you're using right now).

As for conditionals:

DECLARE @.TotalintSELECT @.Total =CASEWHEN TotalVotesisnullTHEN 0WHEN TotalVotes < 0THEN 0ELSE TotalVotesFROM VoteTableWhere ItemID = 10Return @.Total

This statement dynamically assigns a value to a variable. If doing a view, etc., you could leave out the @.Total =, and the results would come through like a regular query but with your altered values.

Calculated column in a table

Hi,
I have a table with a calculated column.
There is also a footer which calculates the sum in a column.
How I can calculate the sum from a caculated column?
Ex: 2 10
5 25 <- calculated column
--
7 35 <- calculated sum of column
(SRS 2000 SP2)
Thanks
EricThe best way to do this is to created a calculated field. On the field list,
do a right mouse click, add, click on calculated field. After that it is
treated like any other field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <jug@.nospam.nospam> wrote in message
news:uHCxZsEaFHA.1448@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a calculated column.
> There is also a footer which calculates the sum in a column.
> How I can calculate the sum from a caculated column?
>
> Ex: 2 10
> 5 25 <- calculated column
> --
> 7 35 <- calculated sum of column
> (SRS 2000 SP2)
>
> Thanks
> Eric
>|||Thanks for answering.
But now there's another problem.
I can't use an aggreate function in the column. The column calculates the
percentage of the first column to the Total.
Any idea jow I can do that in the report?
Thanks
Eric
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:eyqGY%23EaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> The best way to do this is to created a calculated field. On the field
> list, do a right mouse click, add, click on calculated field. After that
> it is treated like any other field.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <jug@.nospam.nospam> wrote in message
> news:uHCxZsEaFHA.1448@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a table with a calculated column.
>> There is also a footer which calculates the sum in a column.
>> How I can calculate the sum from a caculated column?
>>
>> Ex: 2 10
>> 5 25 <- calculated column
>> --
>> 7 35 <- calculated sum of column
>> (SRS 2000 SP2)
>>
>> Thanks
>> Eric
>|||Hi Eric,
What aggreate function? You may refer sample report Company Sales.rdl for
more information about how to sum the columns.
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.|||Thanks for answering.
this is an too easy sample.
I have a column with values (DataBound). A second column should show the
percentage of first column to the sum.
Example:
20 20%
80 80%
-- --
100 100%
For calcualating the Second column I need to know the sum of the first
column.
How I do that?
If the second column is a 'calculated field', I can't use aggreate function
like 'Sum(column1)'.
thanks
Eric
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> schrieb im
Newsbeitrag news:MDOIi7LbFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hi Eric,
> What aggreate function? You may refer sample report Company Sales.rdl for
> more information about how to sum the columns.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner 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 Eric,
Thanks for your patience.
For now, aggregates of aggregates are not currently supported in Reporting
Services so you will find other way to accomplish this. For example, you
could generate this data in the DataSet.\
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.

Calculated Column based on the last record

Hi everyone,

I am thinking of this is possible:

ID Type TypeID

1 car 1

2 car 2

3 house 1

4 car 3

5 house 2

6 house 3

7 car 4

8 car 5

9 car 6

10 house 4

I have identity column (ID), Varchar (Type), Int (TypeID)

I need the TypeID to be an identity for each Type column, incremented the same way as the ID column but values depends on the Type column value.

How can I calculate this?

Thanks!

Use 2 tables.

Table 1:

Set concatenated primary key as Type + TypeID identity

Table 2:

Primary Key Identity ID int

Type varchar()

TypeID int

Adamus

|||

Try:

Code Snippet

create table dbo.t1 (

ID int not null identity,

[Type] varchar(25) not null

)

go

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

go

select

ID,

[Type],

(

select count(*)

from dbo.t1 as b

where b.[Type] = a.[Type] and b.ID <= a.ID

) as TypeID

from

dbo.t1 as a

order by

[Type],

[TypeID]

go

-- SS 2005

select

ID,

[Type],

row_number() over(partition by [Type] order by ID) as TypeID

from

dbo.t1

order by

[Type],

[TypeID]

go

drop table dbo.t1

go

AMB

|||

Since this is a derivable value, rather than using a column in the base table consider using an expression to extract the ranking value. In general cases folks use a view or a computed column for such requirements. In t-SQL, here are a few options:

-- #1

SELECT "id", "type",
RANK() OVER ( PARTITION BY "type" ORDER BY "id" )
FROM tbl
ORDER BY "id" ;

--#2

SELECT "id", "type",
( SELECT COUNT(*) FROM tbl t2
WHERE t2.type = t1.type
AND t2."id" <= t1."id" )
FROM tbl t1 ;

--#2

SELECT t1."id", t1."type", COUNT(*)
FROM tbl t1
JOIN tbl t2
ON t2.type = t1.type
AND t2."id" <= t1."id"
GROUP BY t1."id", t1."type"
ORDER BY t1."id";

|||

I'd like to comment that although both posted approaches will work, it may be the beginning of a maintenance nightmare. If, in fact, the values will be stored and not derived, 2 tables would support good design and optimize the cognition when alterations on the query are required.

Also, if they are calculated, why not calculate on insert?

Adamus

|||

thanks!

i created a stored procedure to insert calculated column using the row_number() over(partition by [Type] order by ID) as TypeID

Calculate Total Column

I need to calculate the Average by Location and then sum the Average for a Total that is displayed on a report. Following is my stored procedure so far:

SELECT tblLocn.LocnCode, tblLocn.LocnDesc,
#work.StartDate, #work.ZValue,
J1.ZAvg ............. Average by locn -- want the sum of all locns here
FROM tblLocn
LEFT JOIN #work ON tblLocn.LocnCode = #work.LocnCode
LEFT JOIN (SELECT #work.LocnCode,
ZAvg = Avg(#work.ZValue)
FROM #work
GROUP BY #work.LocnCode) J1 ON tblLocn.LocnCode = J1.LocnCode

How would I change this procedure to display either a) total average on each row, or b) location average on 1 row only so when I Sum this field it is the correct value.
I am aware that I could create a workfile with the Total value and then Join to that. Is there another alternative? ThanksJ1.ZAvg is your average by location, so when you sum it on a report you will get the sum of all averages.

Bigger question: are you sure this the operation you want? Your statistics will be skewed if your location don't have equal number of records. Consider:
Sum(Avg(1,2,3,4,5), Avg(6,7,8)) = 10

But the same values distributed across different locations yields this:
Sum(Avg(1,2,3), Avg(4,5,6,7,8)) = 8

If you just want the average per location, this value is constant across all locations since it applies to the entire enterprise, and can be calculated separately:
Sum(#work.ZValue)/Count(Locations)

blindman|||I do want the average by location as I have it coded. The problem with just summing the J1.ZAvg field is that the SELECT statement is not grouped by location but rather by DATE. This means that the J1.ZAvg field appears on each row for the location. If I sum that up, I would get an exploded value. Make sense?|||If I change the J1.ZAvg to Sum(J1.ZAvg), that would give me the total I want, except that it seems I cannot use the Sum unless I use a GROUP BY clause. In Access I used the DSUM aggregate function which is not available for Access Project. I may be missing something as I'm fairly new to SQL Server.|||So calculate the value once, store it in a variable, and include it in your output select statement:

set @.SumLocationAvgs = sum((select avg(zwork) from #Workd group by location))

Note: the above is just pseudo-code. You'll have to write for your specification.

blindman|||Makes total sense! Thanks a bunch.|||Oops .. I get the following message:

ERROR 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Set @.SumLocationAvgs = Sum((SELECT Avg(#work.ZValue) FROM #work GROUP BY LocnCode))|||Try this instead:

Set @.SumLocationAvgs =
(select Sum(SubQuery.LocnCodeAvg)
from (SELECT Avg(#work.ZValue) LocnCodeAvg FROM #work GROUP BY LocnCode) SubQuery)|||Exactly what I needed. Thank you.

calculate percentage

I have a report in which there is a column callled "Login Status".The values in the Login Status column can be 'Available','Successful' and 'Error'.
I am able to get the count for each options.
For example i have a table that has 10 rows.
I am able to get number of rows that have Login Status ="Available" and so on.I am getting this using Running Total Fields.
Now i want to calculate the percentage of each options.
For example there are 10 rows.
Available count=5
Successful count=3
Error=2
So Available Percent=Available Count*100/Total.
How can i achieve this calculation?
i am placing all Running Total Field in report footer
Thank you very much in advance
Regards
JigneshAdd a formula for each porcentage calculation and place them in report footer section

To get the total of records You can use the function RecordCount|||Also, read up on the PercentOf functions, which may be useful if you are grouping on status.sql

Calculate percent based on SUM

I'd like to calculate the percent of the total for column DataValue. So if
the sum of column DataValue is 200 and the values for record A is 25 I'd
like to return 12.5%
Based on my sample data I'd like to return:
A,12.5
B,25
C,12.5
D,50
CREATE TABLE TEST
(ID varchar(1),DataValue int)
INSERT INTO TEST (ID,DataValue)VALUES('A',25)
INSERT INTO TEST (ID,DataValue)VALUES('B',50)
INSERT INTO TEST (ID,DataValue)VALUES('C',25)
INSERT INTO TEST (ID,DataValue)VALUES('D',100)
Thanks...SELECT id, datavalue/
(SELECT CAST(SUM(datavalue) AS REAL)
FROM Test)*100
FROM Test
David Portas
SQL Server MVP
--|||Try,
use northwind
go
CREATE TABLE TEST
(ID varchar(1),DataValue int)
INSERT INTO TEST (ID,DataValue)VALUES('A',25)
INSERT INTO TEST (ID,DataValue)VALUES('B',50)
INSERT INTO TEST (ID,DataValue)VALUES('C',25)
INSERT INTO TEST (ID,DataValue)VALUES('D',100)
go
select
a.[id],
(a.datavalue * 100.00) / nullif(b.sum_datavalue, 0) as col_percent
from
test as a
inner join
(
select sum(datavalue) from test
) as b(sum_datavalue)
on 1 = 1
go
drop table test
go
AMB
"Terri" wrote:

> I'd like to calculate the percent of the total for column DataValue. So if
> the sum of column DataValue is 200 and the values for record A is 25 I'd
> like to return 12.5%
> Based on my sample data I'd like to return:
> A,12.5
> B,25
> C,12.5
> D,50
> CREATE TABLE TEST
> (ID varchar(1),DataValue int)
> INSERT INTO TEST (ID,DataValue)VALUES('A',25)
> INSERT INTO TEST (ID,DataValue)VALUES('B',50)
> INSERT INTO TEST (ID,DataValue)VALUES('C',25)
> INSERT INTO TEST (ID,DataValue)VALUES('D',100)
> Thanks...
>
>

Calculate integral of time series

Hello,
I want to compute an integral of values in a table that has 2 columns:
Column A: Time-stamp (T)
Column B: value (KWH)
I need to compute the sum of (KWH*(T(n)-T(n-1)))
Could you suggest a way to do it in T-SQL?
thanks
AmiAmi Einav wrote:
>I need to compute the sum of (KWH*(T(n)-T(n-1)))
Ad an autoincrementing id to your Table named idTbl
Assuming Tbl as Your Tablename
SELECT Tbl.T, Tbl2.T, Tbl.KWH , Tbl.KWH*(Tbl.T-Tbl2.T) as KWHt
FROM Tbl
INNER JOIN Tbl as Tbl2 on Tbl2.idTbl+1 = Tbl.idTbl
Maybe you a datetime - conversion to get the calculation work.
in the step Goup/Sum it together as you like
greetings,
J.C.|||Thanks Joe - that's exactly what I was looking for.
Ami
"Joe Care" <u18810@.uwe> wrote in message news:5c0163d5b2f02@.uwe...
> Ami Einav wrote:
> Ad an autoincrementing id to your Table named idTbl
> Assuming Tbl as Your Tablename
> SELECT Tbl.T, Tbl2.T, Tbl.KWH , Tbl.KWH*(Tbl.T-Tbl2.T) as KWHt
> FROM Tbl
> INNER JOIN Tbl as Tbl2 on Tbl2.idTbl+1 = Tbl.idTbl
> Maybe you a datetime - conversion to get the calculation work.
> in the step Goup/Sum it together as you like
> greetings,
> J.C.sql

Calculate expression - error

The field name in my column 'Num' (1, 2, 3 and 4),
the data i am using is a 'price' field, so under column name 1 my data is
'10', under 2 is '20', under 3 is '60' and under 4 is '90'.
On my next table i need to create data expression. Some thing like this;
'Price' where 'Num' = 1 devide by 'Price' where 'Num' > 1. So in this case
my data should look like this;
under column 1 nothing
under column 2 would be '2'
under column 3 would be '6'
under column 4 would be '9'
=IIF(Fields!Num.Value = 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Value),
Fields!Num.Value > 1)Although I am not truly sure I understand your question, I'll give a stab..
IIF(Fields!Num.Value = 1
,Fields!Price.Value
, Sum(Fields!Num.Value)/Sum(Fields!Price.Value)
)
If Num = 1 Return the Price, Otherwise return the Num/Price...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''''s
community of SQL Professionals.
"JT" wrote:
> The field name in my column 'Num' (1, 2, 3 and 4),
> the data i am using is a 'price' field, so under column name 1 my data is
> '10', under 2 is '20', under 3 is '60' and under 4 is '90'.
> On my next table i need to create data expression. Some thing like this;
> 'Price' where 'Num' = 1 devide by 'Price' where 'Num' > 1. So in this case
> my data should look like this;
> under column 1 nothing
> under column 2 would be '2'
> under column 3 would be '6'
> under column 4 would be '9'
> =IIF(Fields!Num.Value = 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Value),
> Fields!Num.Value > 1)|||On Feb 12, 5:40=A0pm, JT <J...@.discussions.microsoft.com> wrote:
> The field name in my column 'Num' (1, 2, 3 and 4),
> the data i am using is a 'price' field, so under column name 1 my data is
> '10', under 2 is =A0'20', under 3 is '60' and under 4 is '90'.
> On my next table i need to create data expression. Some thing like this;
> 'Price' where 'Num' =3D 1 devide by 'Price' where 'Num' > 1. So in this ca=se
> my data should look like this;
> under column 1 nothing
> under column 2 would be '2'
> under column 3 would be '6'
> under column 4 would be '9'
> =3DIIF(Fields!Num.Value =3D 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Va=lue),
> Fields!Num.Value > 1)
The problem is that when you group by the Num (by using it in a
Column), then the values that are in scope in the Details area of the
Matrix are only the rows that are available in that group. To get
around this, you have to provide a value for the Scope Parameter in
the Sum function to to a Sum over all rows in the Dataset.
Assuming your dataset is named "DataSet1", your column is grouping on
Fields!Num.Value, then you can get the desired output with the
following expression in the Details area:
=3DIIF( Fields!Num.Value =3D 1, Nothing,
Sum( Fields!Price.Value ) / Sum( IIF(Fields!Num.Value =3D 1, Fields!
Price.Value, Nothing ), "DataSet1" ) )
-- Scott

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"

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
*/

Caculating the Balance

hello friends

i have these field as database fields

EntryNo
EntryDate
Remarks
Dr
Cr

and i want to add one column as a balance by using formula

just like this

EntryNo EntryDte Remarks Dr Cr balance

1 02/02/2006 fg 20 0 20
2 03/03/2006 ff 50 0 70
3 05/03/2006 dd 80 0 150
4 06/03/2006 kk 150 0 300
5 05/04/2006 tt 30 270


so how can i get the balance like this by using formula
please help

thanksuse a running total field based on formula
if dr=0 then
-cr
else
dr|||-cr should be -1*cr|||Hi if you still need a soluton you can try this way
Create formula field (BP) Bp = dr-cr
place it next to coulmn cr>right click >format editor and choose to suppres it.
Create another formula filed(balance).. balance = Bp+previous(Bp)
place it next to column Bp
hope this works as you expect
do reply if it works out
regards

Cache Database structure (How to detect if database-design has changed..)

Hello everyone,

I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.

I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.

I want to know if a column or table has changed any values, like datatype, name, properties, etc.

Any suggestions out there ?!Sure, use a DDL trigger. Anytime something changes that you need to know about insert a record into another table (tblDDLChanged?). Then use a SqlDependancy object on the tblDDLChanged table? Although you might be able to just use a set of SqlDependancy objects to monitor the base tables themselves, but I'm not sure if it detects DDL changes or not.|||I am not able to create any triggers only able to read the tables.|||After looking at the system tables I found the answer.

You retreive when last change was done to a column in a database by the following query:

SELECT TOP 1 sysobjects.refdate FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id ORDER BY sysobjects.refdate DESC