Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Sunday, March 25, 2012

Calculated measure based on field in fact table that is not a measure in cube.

I have several fields in the fact table that are not measures in a cube. In this case I have a OrderEntryDateId, WantDateId, and InvoicedDateId. If the order has not invoiced the InvoiceDateId is 0 (zero).

My cube joins the fact table to the time dimension table on the wantDateId. The cube has measures for feet, dollars, pounds, material cost...

I want to create a calculated measure called "Invoiced Dollars" that is based on iff(invoiceDateId>0;TotalDollars,0) however invoiceDateId is not a measure nor is it linked to time in this cube.

Any ideas?

Hi. You could make the InvoiceDateID a member property of time dimension at the leaf level. You can then check the member property value in your calulated member. It might look something like this:

IIF(CINT(Time.Time.CurrentMember.Properties("InvoiceDateID")) > 0, TotalDollars, 0)

Drawbacks to this solution: (1) it only works at the leaf level of the time dimension, (2) Probably slow because of the conversion of the member property value.

Hope this helps - Paul Goldy

|||

I already played with this a bit this afternoon and it is not what I need.

I guess the best way to do this is to add a column to the fact table so it can work at all levels and be pre-aggregated.

Thanks!

Calculated fields with Days/Dates

Preface: This is a bit of a long read, but if I cut too many corners it
would all sound like nonsense, so apologies in advance.
I'm working on application where customers may have a nominated carrier to
deliver goods to them on one of several given days. The carrier will collect
goods from us on say 2 days per w though this may change over time.
Currently we only have one carrier, though there may be more in time, and
they collect on Tuesdays & Thursdays.
Say we have a Customer with 2 depots; Depot A wants deliveries on a Tuesdays
& Thursday, and Depot B wants deliveries Mondays & Fridays.
When despatching a product, the user will first select a collection day (Tue
or Thu), which will then present them with a list of possible delivery
dates; so in this example, if Thursday was selected, if it was Depot A only
the following Tuesday would be offered, if it was Depot B both Friday &
Monday need to be offered. It is this code that is proving the problem for
me at the moment.
Architecture (Snipped DDL at end):
We have a Customers table that records the nominated Carrier for that
customer.
We have a Locations (i.e. Depots) table with a int field to store the
preferred DeliveryDays for that depot - so for Depot A, DeliveryDays=10
(where Tues = 2, Thurs=8, total = 10) which will be queried using bitwise
operators.
We have a CarrierCollections table which holds a record for each carrier,
for each day they collect on, which indicates what the possible delivery
dates for that collection date are:
e.g..
CarrierID - DeliveryDay - CollectionDays
6 - 2 - 12
6 - 8 - 23
12 = Weds/Thurs
23 = Mon/Tue/Fri
I can query for a given depot what delivery dates are appropriate:
Select CC.CollectionDay,(CC.DeliveryDays & L.DeliveryDays) as DeliveryDays
from Locations L
inner join Customers C on C.CustomerID = L.CustomerID
inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
Where L.LocationID = @.LocationID
and (CC.DeliveryDays & L.DeliveryDays) > 0
and CC.CollectionDay = @.CollectionDay
which, for Depot A/Thursday collection returns:
CollectionDay DeliveryDays
-- --
8.00 2.00
For Depot B/Thursday Collection:
CollectionDay DeliveryDays
-- --
8.00 17.00
What I want to do now is to modify the query to return a row for each
delivery day including what the date of that delivery date would be, so for
Depot B example above, I want to return (if run today, 19th Aug):
CollectionDay DeliveryDay NextDate
-- -- --
8.00 Mon 22/08/05
8.00 Fri 26/08/05
And this is where I am stuck, I'm still working on it, but so far I haven't
found a (good) solution. I could handle this in my ASP application, but I'm
assuming that an SQL-only solution would be better(?).
I'm not sure if what I have done so far is a stroke of genius or a sign of
madness. I deliberated about storing the DeliveryDays for both the Depot and
the Carrier Collection tables in separate tables, but I was drawn to the
bitwise comparison route. I'm not sure if this is foolhardy or not!
So can this be done given my current architecture? If not, could it be done
if I took a different approach in SQL? Or should I stick with what I have,
and do the final steps in ASP?
Thanks in advance - you deserve a medal for reading this far...
Chris
DDL:
CREATE TABLE [dbo].[CarrierCollections] (
[CarrierID] [int] NOT NULL ,
[CollectionDay] [int] NOT NULL ,
[DeliveryDays] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerType] [tinyint] NULL ,
[ManagedCarrierID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Locations] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DeliveryDays] [int] NOT NULL
) ON [PRIMARY]
GO
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]It sounds like your database has problem with Normalization. This statemen
t
tells me that it is not in 1st NormalForm: DeliveryDays=10 (where Tues = 2,
Thurs=8, total = 10). It is not atomic field. I would suggest to redesign
your database first, then you will be easy to find out the solution.
Perayu
"CJM" wrote:

> Preface: This is a bit of a long read, but if I cut too many corners it
> would all sound like nonsense, so apologies in advance.
>
> I'm working on application where customers may have a nominated carrier to
> deliver goods to them on one of several given days. The carrier will colle
ct
> goods from us on say 2 days per w though this may change over time.
> Currently we only have one carrier, though there may be more in time, and
> they collect on Tuesdays & Thursdays.
> Say we have a Customer with 2 depots; Depot A wants deliveries on a Tuesda
ys
> & Thursday, and Depot B wants deliveries Mondays & Fridays.
> When despatching a product, the user will first select a collection day (T
ue
> or Thu), which will then present them with a list of possible delivery
> dates; so in this example, if Thursday was selected, if it was Depot A onl
y
> the following Tuesday would be offered, if it was Depot B both Friday &
> Monday need to be offered. It is this code that is proving the problem for
> me at the moment.
>
> Architecture (Snipped DDL at end):
> We have a Customers table that records the nominated Carrier for that
> customer.
> We have a Locations (i.e. Depots) table with a int field to store the
> preferred DeliveryDays for that depot - so for Depot A, DeliveryDays=10
> (where Tues = 2, Thurs=8, total = 10) which will be queried using bitwise
> operators.
> We have a CarrierCollections table which holds a record for each carrier,
> for each day they collect on, which indicates what the possible delivery
> dates for that collection date are:
> e.g..
> CarrierID - DeliveryDay - CollectionDays
> 6 - 2 - 12
> 6 - 8 - 23
> 12 = Weds/Thurs
> 23 = Mon/Tue/Fri
> I can query for a given depot what delivery dates are appropriate:
> Select CC.CollectionDay,(CC.DeliveryDays & L.DeliveryDays) as DeliveryDay
s
> from Locations L
> inner join Customers C on C.CustomerID = L.CustomerID
> inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
> Where L.LocationID = @.LocationID
> and (CC.DeliveryDays & L.DeliveryDays) > 0
> and CC.CollectionDay = @.CollectionDay
> which, for Depot A/Thursday collection returns:
> CollectionDay DeliveryDays
> -- --
> 8.00 2.00
> For Depot B/Thursday Collection:
> CollectionDay DeliveryDays
> -- --
> 8.00 17.00
> What I want to do now is to modify the query to return a row for each
> delivery day including what the date of that delivery date would be, so fo
r
> Depot B example above, I want to return (if run today, 19th Aug):
> CollectionDay DeliveryDay NextDate
> -- -- --
> 8.00 Mon 22/08/05
> 8.00 Fri 26/08/05
> And this is where I am stuck, I'm still working on it, but so far I haven'
t
> found a (good) solution. I could handle this in my ASP application, but I'
m
> assuming that an SQL-only solution would be better(?).
> I'm not sure if what I have done so far is a stroke of genius or a sign of
> madness. I deliberated about storing the DeliveryDays for both the Depot a
nd
> the Carrier Collection tables in separate tables, but I was drawn to the
> bitwise comparison route. I'm not sure if this is foolhardy or not!
> So can this be done given my current architecture? If not, could it be don
e
> if I took a different approach in SQL? Or should I stick with what I have,
> and do the final steps in ASP?
> Thanks in advance - you deserve a medal for reading this far...
> Chris
> DDL:
> CREATE TABLE [dbo].[CarrierCollections] (
> [CarrierID] [int] NOT NULL ,
> [CollectionDay] [int] NOT NULL ,
> [DeliveryDays] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Customers] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> [CustomerType] [tinyint] NULL ,
> [ManagedCarrierID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Locations] (
> [LocationID] [int] IDENTITY (1, 1) NOT NULL ,
> [LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [DeliveryDays] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:3398A112-FC1D-4E5C-931E-4BE3237E3FAF@.microsoft.com...
> It sounds like your database has problem with Normalization. This
> statement
> tells me that it is not in 1st NormalForm: DeliveryDays=10 (where Tues =
> 2,
> Thurs=8, total = 10). It is not atomic field. I would suggest to redesign
> your database first, then you will be easy to find out the solution.
>
You may well be right, but suggesting that there is a problem isnt the same
as identifying the problem, nor solving it. Also, in some situations, you
don't want to fully normalize the data for performance reasons; is this one
of those cases?
More importantly, what would you suggest the data structure should be?
By normalizing the data such that there are two extra tables to store the
DeliveryDays for the Depot's and the Carriers, you will change the SQl used
in my query, but you will still be left with the same problem in how to
determine the appropriate delivery & dates and sorted in the right order.|||I added a table:
CREATE TABLE [dbo].[LocationDeliveryDay] (
[LocationID] [tinyint] NOT NULL ,
[DeliveryDays] [tinyint] NOT NULL
) ON [PRIMARY]
GO
removed DeliveryDays from Locations table.
The DeliveryDays will be entered as Sunday - Saturday values as 1 - 7
Then your query will be modified as :
Select CC.CollectionDay,
LD.DeliveryDays,
DeliveryDay =
case when LD.DeliveryDays < (select Datepart(dw, (Getdate()))) then
(select dateadd(dd, (select Datepart(dw, (Getdate())) + LD.DeliveryDays -
4 ), getdate()))
else
(select dateadd(dd, (LD.DeliveryDays - (select Datepart(dw,
(Getdate())))), getdate()))
end
from Locations L
inner join LocationDeliveryDay LD on LD.LocationID = L.LocationID
inner join Customers C on C.CustomerID = L.CustomerID
inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
Where L.LocationID = @.LocationId
and CC.CollectionDay = @.CollectionDay
When I tried to run this query with @.LocationId = 1, @.CollectionDay = 8 ,
the result will look like this:
CollectionDay DeliveryDays DeliveryDay
-- --
---
8 2 2005-08-23 11:47:28.933
8 4 2005-08-25 11:47:28.933
You can reformat the date result as whatever you want.
I just gave an example of how you could modify your tables so that you can
get what you want. If you are going to modify, you may want to modify table
CarrierCollections also.
Just an one cent idea.
Perayu
"CJM" wrote:

> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:3398A112-FC1D-4E5C-931E-4BE3237E3FAF@.microsoft.com...
> You may well be right, but suggesting that there is a problem isnt the sam
e
> as identifying the problem, nor solving it. Also, in some situations, you
> don't want to fully normalize the data for performance reasons; is this on
e
> of those cases?
> More importantly, what would you suggest the data structure should be?
> By normalizing the data such that there are two extra tables to store the
> DeliveryDays for the Depot's and the Carriers, you will change the SQl use
d
> in my query, but you will still be left with the same problem in how to
> determine the appropriate delivery & dates and sorted in the right order.
>
>|||Thanks Perayu,
I'd already changed the structure a little; the CarrierCollections table now
has one record per Carrier, per collection day, per delivery day - which I'm
thinking is the kind of change you were hinting at. And I am currently
working on a function to calculate the date of the next delivery day(s).
However, I'll digest your suggestions and see if and how I can incorporate
them, before I go any further down the line. I'll post back with any useful
conclusions.
Thanks for your efforts
Chris|||why not build a calendar table somethgn like this?
CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
mn_del DATETIME NOT NULL,
tu_del DATETIME NOT NULL,
.
fr_del DATETIME NOT NULL);
Now you can adjust for holidays and use temporal functions on the data.
I also hope you are nto acctually using IDENTITY for locations and
customers.|||Here's my version of this code:
Select L.LocationID, L.LocationName, DD.DeliveryDay,
Case when DD.DeliveryDay <= (Select DatePart(dw,GetDate())) then
(Select DateAdd(dd, 7 - (Select DatePart(dw,GetDate()) - DD.DeliveryDay),
GetDate()))
Else
(Select DateAdd(dd, DD.DeliveryDay - (Select DatePart(dw,GetDate())) ,
GetDate()))
End as NextDate
from Locations L
inner join DeliveryDays DD on DD.LocationID = L.LocationID
inner join Customers C on C.CustomerID = L.CustomerID
inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
and CC.DeliveryDay = DD.DeliveryDay
where L.LocationID = @.LocationID
and CC.CollectionDay = @.CollectionDay
Order By CollectionDay
I notice it varies from yours within the case statement, but it seems to
work fine for me.
I'm not sure I quite understand your approach. It may be incorrect , but I
haven't tested it fully.
Anyway, thanks for your help...
Chris

calculated fields w/user defined functions

Hi,
In the new application we are extensively using calculated fields that
consist of user defined functions that query multiple tables in the
database. Does anybody know of any limitations or drowbacks of using
user-defined functions in calculated fields (performance, locking, etc) Here
is an example of the most complex user defined function we have:
CREATE FUNCTION dbo.PayrollDetail_Subtotal
(
@.PayrollDetailID int,
@.CoverageType varchar(25)
)
RETURNS decimal(19,4) AS
BEGIN
DECLARE
@.AmountCap AS decimal(19,2),
@.AmountEffective as decimal (19,2),
@.AssignmentID as int,
@.PolicyID1 as int,
@.PolicyID2 as int,
@.PolicyTo1 as datetime,
@.PolicyFrom2 as datetime,
@.WorkCode AS int,
@.Result AS decimal(19,2),
@.PayrollFrom AS datetime,
@.PayrollTo AS datetime,
@.PayrollType AS varchar(50),
@.PayrollHeaderID AS int,
@.Composite1 AS decimal(19,2),
@.Composite2 AS decimal(19,2),
@.Territory1 AS int,
@.TerritoryMod1 as decimal(10,4),
@.WCRateID1 AS int,
@.WCRate1 AS decimal(19,4),
@.WCFactor1 AS varchar(50)
SET @.Result = 0
/* Payroll Header ID and dates*/
/* NULL for PayrollHeader is not allowed in tblPayrollDetail */
SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
@.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
/* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType = PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
PayrollHeaderID = @.PayrollHeaderID
SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
@.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
@.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
/* Composite Rating in tblCoverageEntry has default of 0 */
if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
(@.Composite2=0)
return 0
ELSE
BEGIN
IF (@.PolicyID1 = @.PolicyID2)
/* ++++++CASE 1 : Payroll dates fall within the same policy year
+++++++*/
BEGIN
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
return 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result = @.WCRate1*0.001
ELSE
SELECT @.Result = @.WCRate1
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result = @.Result*@.AmountEffective
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result = @.Result * @.AmountEffective
ELSE
SELECT @.Result = @.Result * @.AmountCap
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result = @.Result * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result = @.Result * @.Composite1
ELSE
SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
END
END
END
ELSE
/* IF Payroll falls under 2 policy years */
BEGIN
DECLARE
@.Territory2 AS int,
@.TerritoryMod2 as decimal(10,4),
@.WCRateID2 AS int,
@.WCRate2 AS decimal(19,4),
@.WCFactor2 AS varchar(50),
@.Result1 AS decimal(19,4),
@.Result2 AS decimal(19,4),
@.NoDays1 AS int,
@.NoDays2 AS int,
@.PayrollLength AS int
/* +++POLICY YEAR 1+++ */
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
SELECT @.Result1 = 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result1 = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result1 = @.WCRate1*0.001
ELSE
SELECT @.Result1 = @.WCRate1
END
/* base */
SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
SELECT @.PayrollLength = @.NoDays1+@.NoDays2
/* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
Year 1 */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
ELSE
SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
END
END
/* +++POLICY YEAR 2+++ */
/* work code rate */
SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID2 is null)
SELECT @.Result2 = 0
ELSE
BEGIN
IF @.WCFactor2 = 'Rate/100'
SELECT @.Result2 = @.WCRate2*0.01
ELSE
BEGIN
IF @.WCFactor2='Rate/1000'
SELECT @.Result2 = @.WCRate2*0.001
ELSE
SELECT @.Result2 = @.WCRate2
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
ELSE
SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
END
/* Territory */
SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory2 is null)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
BEGIN
SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
END
END
SELECT @.Result = @.Result1 + @.Result2
END
END
return @.Result
ENDUser defined functions in computed columns perform pretty badly compared to
plain SQL. The reason is that behind the season the function works as a
cursor, all the work is done on a row-by-row basis. You probably won't
notice that much if you only have a few rows, but once you get to a largish
amount (say 1000+) performance will go out of the window.
I did some testing the other day and SELECT * on a table with around 8000
rows had a subsecond return time without a computed column with a UDF, but
20+ seconds with a computed column with a UDF. On that basis I decided to
redesign the solution so it would work without a computed column, which
involved creating a few new tables, and performance is now acceptable.
So, you can use it, but only if your table has a very small number of rows,
or if you have a larger table and you are sure you will never ever have a
table scan on that table.
"ilona" <ieshulman@.sseinc.com> wrote in message
news:etjJPzjRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In the new application we are extensively using calculated fields that
> consist of user defined functions that query multiple tables in the
> database. Does anybody know of any limitations or drowbacks of using
> user-defined functions in calculated fields (performance, locking, etc)
Here
> is an example of the most complex user defined function we have:
>
> CREATE FUNCTION dbo.PayrollDetail_Subtotal
> (
> @.PayrollDetailID int,
> @.CoverageType varchar(25)
> )
> RETURNS decimal(19,4) AS
> BEGIN
> DECLARE
> @.AmountCap AS decimal(19,2),
> @.AmountEffective as decimal (19,2),
> @.AssignmentID as int,
> @.PolicyID1 as int,
> @.PolicyID2 as int,
> @.PolicyTo1 as datetime,
> @.PolicyFrom2 as datetime,
> @.WorkCode AS int,
> @.Result AS decimal(19,2),
> @.PayrollFrom AS datetime,
> @.PayrollTo AS datetime,
> @.PayrollType AS varchar(50),
> @.PayrollHeaderID AS int,
> @.Composite1 AS decimal(19,2),
> @.Composite2 AS decimal(19,2),
> @.Territory1 AS int,
> @.TerritoryMod1 as decimal(10,4),
> @.WCRateID1 AS int,
> @.WCRate1 AS decimal(19,4),
> @.WCFactor1 AS varchar(50)
>
> SET @.Result = 0
> /* Payroll Header ID and dates*/
> /* NULL for PayrollHeader is not allowed in tblPayrollDetail */
> SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
> @.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
> tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
> /* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
> SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType => PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
> PayrollHeaderID = @.PayrollHeaderID
> SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
> @.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
> SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
> @.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
> /* Composite Rating in tblCoverageEntry has default of 0 */
> if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
> (@.Composite2=0)
> return 0
> ELSE
> BEGIN
> IF (@.PolicyID1 = @.PolicyID2)
> /* ++++++CASE 1 : Payroll dates fall within the same policy year
> +++++++*/
> BEGIN
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> return 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result = @.WCRate1*0.001
> ELSE
> SELECT @.Result = @.WCRate1
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result = @.Result*@.AmountEffective
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result = @.Result * @.AmountEffective
> ELSE
> SELECT @.Result = @.Result * @.AmountCap
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
> END
> END
> END
> ELSE
> /* IF Payroll falls under 2 policy years */
> BEGIN
> DECLARE
> @.Territory2 AS int,
> @.TerritoryMod2 as decimal(10,4),
> @.WCRateID2 AS int,
> @.WCRate2 AS decimal(19,4),
> @.WCFactor2 AS varchar(50),
> @.Result1 AS decimal(19,4),
> @.Result2 AS decimal(19,4),
> @.NoDays1 AS int,
> @.NoDays2 AS int,
> @.PayrollLength AS int
>
> /* +++POLICY YEAR 1+++ */
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> SELECT @.Result1 = 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result1 = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result1 = @.WCRate1*0.001
> ELSE
> SELECT @.Result1 = @.WCRate1
> END
> /* base */
> SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
> SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
> SELECT @.PayrollLength = @.NoDays1+@.NoDays2
> /* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
> Year 1 */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> ELSE
> SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
> END
>
> END
> /* +++POLICY YEAR 2+++ */
> /* work code rate */
> SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID2 is null)
> SELECT @.Result2 = 0
> ELSE
> BEGIN
> IF @.WCFactor2 = 'Rate/100'
> SELECT @.Result2 = @.WCRate2*0.01
> ELSE
> BEGIN
> IF @.WCFactor2='Rate/1000'
> SELECT @.Result2 = @.WCRate2*0.001
> ELSE
> SELECT @.Result2 = @.WCRate2
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> ELSE
> SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
> END
> /* Territory */
> SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory2 is null)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> BEGIN
> SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
> IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
> END
> END
> SELECT @.Result = @.Result1 + @.Result2
> END
> END
> return @.Result
>
> END
>sql

Calculated fields on olap pivottable in excel 2007

Is there a way to add calculated fields in excel 2007 to a pivot table, connected to a SSAS cube?

Meaning that is calculated on the fly automatically while drilling up/down...

I do not think so. The formulas button is disabled. Formulas have to be server side MDX calculated members. Same in Excel 2003

You will also note some other diffences like Grouping may require security access to cube metadata in order to work.

Philippe

|||This is quite a disadvantage! Hopefully the kpi wizard and the set selector of ProClarity will be integrated into excel!

Calculated fields not displaying in the layout

I am continually seeing an issue where I write a query with some calculations
in it and when I put that same query in reporting services those calculated
fields do not display in the report. I get a warning saying those particular
fields is not part of the result set.
I can usually get around this by creating a calulated field in Reporting
Services but I'd much rather do the math in my query. Here's an example of
my latest issue:
SELECT *
FROM
( SELECT dense_rank() over (partition by openbusinessdate order by
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) desc) dr,
GUEST_CHECK_HIST.openbusinessdate,
GUEST_CHECK_HIST.checkNum as checkNum,
GUEST_CHECK_HIST.tableRef as Tablenum,
GUEST_CHECK_HIST.openDatetime as opentime,
GUEST_CHECK_HIST.closeDatetime as closetime,
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
GUEST_CHECK_HIST.numGuests as numguests,
substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
REVENUE_CENTER.nameMaster AS rvcName,
initcap(EMPLOYEE.firstName) as fname,
initcap(EMPLOYEE.lastName) as lname
FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
GUEST_CHECK_HIST
LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID = LOCATION_HIERARCHY_ITEM.locationID
ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
(GUEST_CHECK_HIST.openBusinessDate = :begindate))
where dr <=100
as you can guess what I'm calling duration does do display in the report.
Any help would be greatly appreciated.Never mind. I resolved the issue.
Thanks
"Zach" wrote:
> I am continually seeing an issue where I write a query with some calculations
> in it and when I put that same query in reporting services those calculated
> fields do not display in the report. I get a warning saying those particular
> fields is not part of the result set.
> I can usually get around this by creating a calulated field in Reporting
> Services but I'd much rather do the math in my query. Here's an example of
> my latest issue:
> SELECT *
> FROM
> ( SELECT dense_rank() over (partition by openbusinessdate order by
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) desc) dr,
> GUEST_CHECK_HIST.openbusinessdate,
> GUEST_CHECK_HIST.checkNum as checkNum,
> GUEST_CHECK_HIST.tableRef as Tablenum,
> GUEST_CHECK_HIST.openDatetime as opentime,
> GUEST_CHECK_HIST.closeDatetime as closetime,
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
> GUEST_CHECK_HIST.numGuests as numguests,
> substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
> REVENUE_CENTER.nameMaster AS rvcName,
> initcap(EMPLOYEE.firstName) as fname,
> initcap(EMPLOYEE.lastName) as lname
> FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
> GUEST_CHECK_HIST
> LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID => LOCATION_HIERARCHY_ITEM.locationID
> ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
> ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
> WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
> (GUEST_CHECK_HIST.openBusinessDate = :begindate))
> where dr <=100
> as you can guess what I'm calling duration does do display in the report.
> Any help would be greatly appreciated.

Calculated fields in Views?

Hi
I am running an SQL server with an MS Access front-end.
One of my main forms is a list, which have a number of
calculated fields within the query that sits behind it (in
Access, not in SQL server).
I would like to bring these calculations into a View in
SQL server, to speed things up as I think this is what is
causing this list to hang for a while when it first opens.
The two calcuations are as follows:
DaysOnHold: IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
I've discovered that the functions used above are
incompatible with SQL server (I tried creating them
in 'Views' within Enterprise Manager but gave me all sorts
of errors.)
If anyone could assist with the correct phrasing of the
above for the VIews, I'd be extremely grateful!!
Thanks
Russell
hi russell,
since the im not sure what is your exact expression in the query , by
doing some assumption you can convert existing IIF conditions using CASE and
ISNULL functions.
IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
above expression can be converted to SQL server using isnull condition.
Ex:
isnull( [DateUnsuspended]-[DateSuspended], 0)
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
above expression can be converted to SQL server using CASE expression.
Ex:
case when [DateClosed] is null then
( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
else
( datepart(dd,[DateClosed]) - [JobVacant]-[DaysOnHold])/7 end
Look in books online on the topics "date functions", CASE, ISNULL
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi Vishal
Thanks for your help. However, I have tried to enter this
expression in a View Column, but get the following error
message:
The Query Designer does not support the CASE SQL construct.
What does this mean? Is there another way to create views
which will allow me to use this CASE function?
Thanks
Russell

>--Original Message--
> hi russell,
> since the im not sure what is your exact expression in
the query , by
>doing some assumption you can convert existing IIF
conditions using CASE and
>ISNULL functions.
> IIf([DateUnsuspended]-[DateSuspended] Is
>Null,0,[DateUnsuspended]-[DateSuspended])
> above expression can be converted to SQL server using
isnull condition.
> Ex:
> isnull( [DateUnsuspended]-[DateSuspended], 0)
> WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
> [JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-
[JobVacant]-
> [DaysOnHold])/7))
> above expression can be converted to SQL server using
CASE expression.
> Ex:
> case when [DateClosed] is null then
> ( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
> else
> ( datepart(dd,[DateClosed]) - [JobVacant]-
[DaysOnHold])/7 end
> Look in books online on the topics "date functions",
CASE, ISNULL
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>
|||hi russell,
Make use of Query analyzer rather than these tools. With Query analyzer
you can execute all t-sql commands and tools like "query designer" have
limited functionality.
Vishal Parkar
vgparkar@.yahoo.co.in
|||Vishal
Thanks for your help! Figured it out and it works!
Thanks again
Russell

>--Original Message--
> hi russell,
> Make use of Query analyzer rather than these tools.
With Query analyzer
>you can execute all t-sql commands and tools like "query
designer" have
>limited functionality.
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>

Calculated fields in Matrix

Hi!

I have a calculated field in a dataset, Productivity, defined by this expression:

Fields!BCM.Value/Fields!MINUTES.Value

When I use it on a table, everything works fine. But when I try to use it on a matrix, I get an error
(#ERROR) on the field.
Why does it happen? Can't I use calculated fields on matrix?

Thank you!

Dear,

Sure , u will get the error beause u are using the field in matrix but when u define the function u are not passing the matrix refrence.

eg:

=iif(inscope(Sum(Fields!OrderQty.Value, "MatrixSource")),sum(Fields!OrderQty.Value),sum(Fields!OrderQty.Value)/Sum(Fields!OrderQty.Value, "MatrixSource"))

HTH

from

sufian

Calculated Fields in an OLAP cube severly impact data retrieval time

I have a OLAP cube that I'm using for a data source that has several
calculated measures in it. When I add the calculated measures to my report
the retrieval time goes from ~1 second to ~4 minutes. I haven't been able
to locate any information about this issue. Is there something I'm doing
wrong, or is this a Reporting services issue?Nevermind. I hadn't realized that splitting a calculated field over an
unrelated dimension would have the same effect.
"Jeremy Farnham" <jeremy.farnham@.omya.com> wrote in message
news:esFYjweBIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I have a OLAP cube that I'm using for a data source that has several
>calculated measures in it. When I add the calculated measures to my report
>the retrieval time goes from ~1 second to ~4 minutes. I haven't been able
>to locate any information about this issue. Is there something I'm doing
>wrong, or is this a Reporting services issue?
>sql

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

I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?

Could you not put the calculated fields into the stored proc?

|||

Have you check these field after executing your SP ?

May be Sp is not returing the calculated field.

Regards

Manoj Verma

|||Thank you for your replies. I had added the calculated fields within the report itsself and not within the stored procedure. I thought that the report would hold them, but when I refreshed the dataset, it lost the calculated fields that I had added in the report. I guess that I will have to add them into the SP.|||How did you go about refreshing the dataset? I have calculated fields and when I add a new field to the stored procedure I use the refresh fields button. This brings back the newly added field and I didn't lose my report-side calculated fields.|||Thank you Simone. I think that I was rerunning the query. I will not do that next time and will just refresh the fields.

Calculated Fields

I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?

Could you not put the calculated fields into the stored proc?

|||

Have you check these field after executing your SP ?

May be Sp is not returing the calculated field.

Regards

Manoj Verma

|||Thank you for your replies. I had added the calculated fields within the report itsself and not within the stored procedure. I thought that the report would hold them, but when I refreshed the dataset, it lost the calculated fields that I had added in the report. I guess that I will have to add them into the SP.|||How did you go about refreshing the dataset? I have calculated fields and when I add a new field to the stored procedure I use the refresh fields button. This brings back the newly added field and I didn't lose my report-side calculated fields.|||Thank you Simone. I think that I was rerunning the query. I will not do that next time and will just refresh the fields.

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

I have two questions regarding calculated fields.
I have a report with two datasets.
Issue 1:
Dataset 1 queries a table that contains configuration information. This
table has one row.
I would like to create a calculated field that divides the value of a
column in this one row but another value in the one row. The problem
appears to be that SRS is expecting the query to return 1 or more rows,
and thus only likes to let me do aggregations on the output from the
dataset. Is there a way to either tell it that there will only be one
row, or a different way to extract the config info from the database
that makes more sense?
Issue 2:
I would then like to use the output of the calculated field from issue
one as part of a calculated field in the second dataset. This seems to
be a problem, as SRS complains with the following statement:
=Sum(Fields!INVOICEAMOUNT.Value, "DailySalesDS") /
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
Will not compile. I get errors regarding aggregates in report
parameters and about using something from another dataset.
My guess is that I am going about this the wrong way. Any help is
appreciated.In case you missed it, I have answered your questions already in the
"Computed Fields/Multiple Datasources" thread yesterday.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message
news:chtc25$sd9@.odbk17.prod.google.com...
> I have two questions regarding calculated fields.
> I have a report with two datasets.
> Issue 1:
> Dataset 1 queries a table that contains configuration information. This
> table has one row.
> I would like to create a calculated field that divides the value of a
> column in this one row but another value in the one row. The problem
> appears to be that SRS is expecting the query to return 1 or more rows,
> and thus only likes to let me do aggregations on the output from the
> dataset. Is there a way to either tell it that there will only be one
> row, or a different way to extract the config info from the database
> that makes more sense?
> Issue 2:
> I would then like to use the output of the calculated field from issue
> one as part of a calculated field in the second dataset. This seems to
> be a problem, as SRS complains with the following statement:
> =Sum(Fields!INVOICEAMOUNT.Value, "DailySalesDS") /
> Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
> Will not compile. I get errors regarding aggregates in report
> parameters and about using something from another dataset.
> My guess is that I am going about this the wrong way. Any help is
> appreciated.
>sql

Calculated fields

I'm an SRS 2005 newbie. I'm trying to create a Cash Requirements
Forecast that plots values in six weekly buckets (columns) based on
the Required Date from line items in the PO. I'm stuck on one last
task in the report - getting the values bucketed - and am trying to
use calculated fields to do so.
REQDATE is the field from the PO line item that I'm applying this
expression to. startdate is a parameter that I have setup. I'm
trying to limit the output in a field on the report to a one-week
period beginning on the startdate that the user selects. Here is my
expression for that calculated field -
=Fields!REQDATE.Value>=Parameters!startdate.Value And Fields!
REQDATE.Value < (Parameters!startdate.Value+8)
First, is this the correct way to do this? If not, how should I
calculate this value and add it to my Matrix?
Second, should I be using a Matrix in my report or a Table? It seems
less than intuitive as to how to add column fields in a Matrix so
perhaps I should be using a Table instead.
Third, please keep any responses in plain English as I'm not a
programmer and I did not stay at Holiday Inn Express last night.
Thanks and best regards,
Frank Hamelly
MCP-GP, MCT
East Coast Dynamics
www.eastcoast-dynamics.comQuestions:
1. What's a PO?
2. Is this what you want your report to look like:
Week of 12/3-12/10 | Week of
12/11-12/18 |
Bikes
50 58
Cars
1 3
Boats
0 99
I'm guessing you want to combine all items that fit in that week.
Some answers:
Your expression will not work unfortunately. Second, you should use
the SQL statement in the data table and your parameter to limit the
records for the dates you select. Sorry I have to talk geek now so
book a room in the Holiday Inn (I still have no idea what that joke
referenced). For example:
select *
from table1
where datetime >= @.startdate
When the user puts a start date, the query will only bring back data
after that so you are limiting your data from the get go. If you
understand me so far and you can answer my questions, I can try to
help you out a little more.|||On Dec 5, 9:53 am, SQL Guy <ayma...@.gmail.com> wrote:
> (I still have no idea what that joke referenced).
There is a long-running series of television commercials here in the
USA about a chain of hotels called "Holiday Inn Express". In the
commercial, a person is doing a very technical job (doctor,
electrician, etc) using all sorts of jargon, and at the end of the
commercial the person says "Oh, I'm not a <profession>, I just stayed
at a Holiday Inn Express last night", implying that simply sleeping at
the hotel will make you more rested and more intelligent the next
day.
As far as the query, SQL Guy is correct, it will be more efficient to
limit the data to 7 days in the Data tab (the query itself) rather
than filtering the data in the report. Imagine that 5 years from now
your query returned 20,000 rows, so every time you view the query, it
has to pull all the data from the database, then filter it down at
Report Render time to the 10 that occurred this week. Inefficient.
It is much better to put a WHERE clause in the query to let the server
limit the rows.
Next the "weekly buckets": go to your DataSet, right-click and Add a
new Field, then give it a calculated expression like:
= DateAdd( "d", 1 - Weekday(Fields!datetime.Value), Fields!
datetime.Value )
for every row in your DataSet, this returns a DateTime equal to the
Sunday in that week based off of the field datetime. You can then
group on this calculated field just like you would any othe from your
original dataset.
The Matrix object is good when you have an undetermined number of
elements that you want to group by. For example, you put LineItem in
the Rows group, the calculted Week field in the Column group, and
=Format( Sum( Fields!amount.Value ), "$#,##0.00") in the Details
group. You dont care how many line items you have to report on, or
how many weeks you are reporting, the object draws it.
But, a Table is good for... well... tabular reports. You have one
report for a week period, each Detail row is a line item, and you have
a column that is explicitly one value of your billing amount per line
item. Sum()s then go in headers and footers, with some grouping
capability as well. When you Group here (by right-clicking and Adding
a Group), it just repeats the Grouped sections, and every section has
the exact same explicit layout.
-- Scott|||Thank yoiu Scott and SQL Guy. Scott, what does the "d" in the
expression represent?
Thx, Frank|||Also, assuming I need 6 distinct weekly buckets in my report, I need
to define 6 calculated fields with explicit filtering for each field,
correct? If so, how/where do I insert the calculated fields into the
matrix so that the result is a 6-week header with one column for each
week?
Thanks so much. I really appreciate the help. I've been trying to
figure this out on my own for too long now!
Frank|||On Dec 5, 11:54 am, "Frank Hamelly, MCP-GP" <fhame...@.cfl.rr.com>
wrote:
> Also, assuming I need 6 distinct weekly buckets in my report, I need
> to define 6 calculated fields with explicit filtering for each field,
> correct? If so, how/where do I insert the calculated fields into the
> matrix so that the result is a 6-week header with one column for each
> week?
> Thanks so much. I really appreciate the help. I've been trying to
> figure this out on my own for too long now!
> Frank
The "d" represents Day. The DateAdd function is very funky. Unlike
normal VB, the DateAdd and Format commands use case sensitive
identifiers: MM for month, dd for day, yyyy for year, HH for 24 hour
format, hh for 12 hour format, mm for minutes, ss for second.
The way a Matrix works is that it takes the Field that you supplied,
breaks it into Groups containing distinct values of the contents of
the Field, then Aggregates on the Group in the Details section. The
two display groups are Rows and Columns.
Think of it this way, you have a list of items with similar
characteristics:
Shape, Color, Price
Circle, Red, $10
Circle, Green, $25
Square, Blue, $15
Square, Red, $5
If you drag the Shape field into a Row of a Matrix, and the Color into
a Column, and the =Sum( Fields!Price.Value ) in the Details section,
when you go to Preview mode you will see two rows (Circle and
Sequare), three columns (Red, Green, Blue), and Summed values ($10 |
$25 | Nothing || $5 | Nothing | $15) in the Details table. You didn't
have to define anything, it just evaluated the row contents and
created the groups.
For your dataset:
-- If using a Matrix, all you need is a Field that returns 6 distinct
values for your 6 weeks, and it will automatically build 6 columns.
Any function in the Details section is going to display the aggregate
for only the
-- If using a Table, you will need 6 distinct calculations, and it
gets a little trickier.
1. Add a "WeekDate" Field that rounds the ReqDate back to the first
day of the week using the method previously outlined.
2. Drag the "WeekDate" into the Column section of the Matrix
3. Drag the "LineItemName" into the Row section of the Matrix
4. Drag the "AmountNeedingAggregation" into the Details section of
the Matrix
Right-click on the Row/Column to and Edit Group to change Sorting,
Filtering, etc.
-- Scott|||Thanks for the Holiday Inn info. I've stayed in so many but I don't
watch TV at all so that's why I was missing that valuable nugget of
information. I now feel like I've spent the night there!
"d" stands for day. That function is quite nice for many things. Lets
say you want to default a parameter to last week you can simply type
for the default parameter =dateadd("d",-7,today()) - today is a
function that gets today's date in RS. So what I understand from
Orne's suggestion is that he will take the date in the field and
subtract it from... actually I'm not really following that so much
maybe because it's almost closing time. I think it can be done in
tabular form but I will wait for Orne's explanation so I can better
understand what he's trying to get at.|||Thank you Scott and SQL Guy for your help. I'm going to give your
further suggestions a try tomorrow.
Thanks again,
Frank

calculated fields

Hey All,
I wondering how you do a calculated field in Reporting
Services. I need to be able to iterate through two
seperate tables and figure out the activation rate. So I
thought I could use a calcuated field but when I do it, it
tries to apply the sum function when I drag that field out
of the fields list. When I remove the sum function I get
an error.
= Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
don't get it how do I do this?I'm afraid that's not possible.
You'll need to calculate it in a query
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c472c5$eb5b27c0$a401280a@.phx.gbl...
> Hey All,
> I wondering how you do a calculated field in Reporting
> Services. I need to be able to iterate through two
> seperate tables and figure out the activation rate. So I
> thought I could use a calcuated field but when I do it, it
> tries to apply the sum function when I drag that field out
> of the fields list. When I remove the sum function I get
> an error.
> = Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
> don't get it how do I do this?

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 field values?

Dear All,

I have a table called CustomerWorkPlan and some of the fields are
1. PlannedID - Primary Key
2. Planned_Start_Date -Datetime field
3. Actual_Start_Date -Datetime field

I wanted to write a query in the from of StoreProcedures which will select some of the

fields and a given field name "Start Date" with the values coming from

Actual_Start_Date if not null else from Planned_Start_Date.

How can I achieve this query? Need help.

create table #tt (plannedid int ,Planned_Start_Date datetime,Actual_Start_Date datetime)
insert into #tt select 1,null,getdate()
insert into #tt select 1,getdate()-1,null

select *,isnull(Actual_Start_Date,Planned_Start_Date)as startdate
from #tt

check this

Madhu

|||

Hi,

is this answer, dear?

Code Snippet

Select PlannedId,

case

when Actual_Start_Date is null then Planned_Start_Date

else Planned_Start_Date

end

from

your table name

|||

masum,

Your suggestion will only provide the Planned_Start_Date -it will never provide the Actual_Start_Date.

Can you see why it is not a correct answer to the question?

Thursday, March 22, 2012

Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible?
Thanks for your help in advance.
This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:

> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
>
|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.

Calculated field

This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.

What is the simplest way to accomplish this?

One way would be to modify the SQL statement

[LastName] + ', ' + [FirstName] as FullName

Another would be to use a calculated field.

Fields!LastName.Value + ", " + Fields!FirstName.Value

cheers,

Andrew

Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible'
Thanks for your help in advance.This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.
>|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.sql