Calculated Member

I'm trying to add a calculated member to my SSAS 2005 cube. I can calculate what I want using MDX, but I'm having trouble converting my MDX query to the something SSAS understands as a calulated measure.

This is my MDX query.

Working MDX

SELECT

{([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)} ON COLUMNS

,{[Employee].[Emp Full Name].allmembers}ON ROWS

FROM [Employee Scorecard]

WHERE ([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34])

Here is my calculated member

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

[EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

I would also like to add a similar member where the state name is not Texas. How would I do that as well?

I changed my scope. I forgot the "This =" . But it still dosn't work.

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

This = ([EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]);

end scope;

|||

It appears that you want to sum the activity count for a set of activity turn times, filtered by a specific segmentation and special activity. I would start off by creating a base measure (which you could set to invisible if you want)

Code Snippet

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

AS

SUM([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34],

([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

Then creating a TX specific version

Code Snippet

Create MEMBER CurrrentCube.Measures.[TX Docs Ready <=10 Days]

AS

(Measures.[Docs Ready <=10 Days],[Geography].[State Name].&[TEXAS])

Then the non TX can simply be the total, minus the TX measure. You could calculate this by summing a set of all states except TX, but this should be faster

Code Snippet

Create MEMBER CurrrentCube.Measures.[Not TX Docs Ready <=10 Days]

AS

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

- Measures.[TX Docs Ready <=10 Days]

|||

I see what your saying. However, I think that by using "Scope" the code would be more readable. I've been playing with it and this is what I have so far.

I can get this to work and it returns values. But it's not exactly what I want.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS ([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = [EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

This = [EN To Activity Turn Time].[Turn Time Name].&[2];

end scope;

What I really want, is something like this

Code Snippet

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

But when I run this code all I get is empty values.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

What am I doing wrong with this scope?

|||

The scope statement is not about readability, it is about restricting the sbucube over which a calculation is performed. I am pretty sure that the second assignment you are doing will just override the previous one that you did for Texas and by summing the measure that you are scoping over, it could be trying to recurse over itself. What I think you want is something more like the following. Assuming that you want to sum over Activity 2-32 if it's not Texas and 2-34 if it is Texas.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

scope ([Geography].[State Name].&[TEXAS]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]});

end scope;

end scope;

|||

It dosn't like the aggregate. I've tried both of these and the results are the same as if I never did the scope. I've also tried Sum(Aggregate()) and Sum().

Code Snippet

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].currentmember);

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

I feel like I'm really really close but I'm clearly missing an important concept.|||

I'm not exactly sure what the problem is. We are making it hard for the calc engine, telling it that this measure is the sum of itself. Normally I would do an approach like the following.

Code Snippet

-- this is the default calc

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS SUM({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)),

VISIBLE = 1;

-- override the calculation for TX

scope ([Geography].[State Name].&[TEXAS]);

([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]) = SUM(

{[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]}

,([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

));

end scope;

If this does not work, could you post a simple example of what results you are seeing and what you were expecting.

sql

Calculated Measures

We are presently having an issue where if we use calculated measures an MDX
query that takes about 1-2 seconds to run suddenly takes about 1.5 minutes
to run. These measures are not extremely complicated they are simple like
taking TotalDollars/TotalUnits. TotalDollars and TotalUnits are already
measures in the cube. Any ideas why this could be happening?
Thanks,
AlWe are having an issue with Calculated Members and CrossJoins. We have an
MDX query where when we get to a certain granular level we are taking a
considerable performance hit. Take the following query. It takes about 1.5
minutes to run.
WITH MEMBER [Measures].[Average Bill Rate2] AS
'Measures.[Total Revenue]/Measures.[Total Hours] '
SELECT NON EMPTY { [Measures].[Average Bill Rate2], [Measures].[Total
Cost],
[Measures].[Total Hours], [Measures].[Total Revenue] } ON COLUMNS,
NON EMPTY { (
[Profit Center Rollup].[Profit Center Rollups].[Profit Center Level 10].
ALLMEMBERS *
[Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS *
[Project Type].[Project Types].[Project Type].ALLMEMBERS *
[Transaction Type].[Transactions].[Transaction Type].ALLMEMBERS ) }
If I remove the line [Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS * from the query it takes 1-2 seconds to run.
Or if I remove "[Measures].[Average Bill Rate2]," from the query it again
takes 1-2 seconds to run. Do you know if there are any work arounds or
fixes to this issue? Also keep in mind that this query is originating in
SSRS. The issue here is that any changes made to the query directly means
that we can no longer use the query designer in SSRS.
Thanks,
Al

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 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 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...
>
>

Calculate Time Off

Hi,
I need a query that can return the total time off between 2 dates.
I have a table call tblTimeOff which has the following fields
StartTimeOff, Interval (minute), Wend
Sample data for tblTimeOff:-
10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on Wday
12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on Wday
15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on Wday
10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on Wend
12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on Wend
Saturday and Sunday are considered as Wend.
Mon - Fri are Wend
I need a query when user provide me with 2 date:-
Condition 1:
--
Start :- 2nd March 8.30am
End :- 4th March 11.00am
The result for total time off should be:- 195 mins
2nd March - 15+60+15, 3rd March - 15+60+15, 4th March (wend) - 15
Condition 2:
--
Start :- 2nd March 8.30am
End :- 2nd March 5.00pm
The result for total time off should be:- 90 mins
2nd March - 15+60+15
Anyone help ?
Thank You,
mfwooWhere are your dates stored in your tables?
Posting the full DDL may help.
http://www.aspfaq.com/etiquette.asp?id=5006
"Woo Mun Foong" <mfwoo@.yahoo.com> wrote in message
news:B108A0E8-4084-435C-9C6E-815AD808C4AB@.microsoft.com...
> Hi,
> I need a query that can return the total time off between 2 dates.
> I have a table call tblTimeOff which has the following fields
> StartTimeOff, Interval (minute), Wend
> Sample data for tblTimeOff:-
> 10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on Wday
> 12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on Wday
> 15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on Wday
> 10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on Wend
> 12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on Wend
> Saturday and Sunday are considered as Wend.
> Mon - Fri are Wend
> I need a query when user provide me with 2 date:-
> Condition 1:
> --
> Start :- 2nd March 8.30am
> End :- 4th March 11.00am
> The result for total time off should be:- 195 mins
> 2nd March - 15+60+15, 3rd March - 15+60+15, 4th March (wend) - 15
> Condition 2:
> --
> Start :- 2nd March 8.30am
> End :- 2nd March 5.00pm
> The result for total time off should be:- 90 mins
> 2nd March - 15+60+15
> Anyone help ?
> Thank You,
> mfwoo
>sql

Calculate Expression

Hello,
I have a query that I am using to calculate an order filled rate.
One of my columns is OrderNo. to get the total orders for a date I take the
order no. and just count to get a total for that column.
Now the trick is I need to subtract from that count cancelled orders.
How would I do that?
I have tried:
Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
Count(Fields!order_type.Value) - Fields!cancel_qty.Value
No luck either way.
I would appreciate any help that you could give. Thanks,
Kevindepends, perhaps:
count(Fields!order_type:Value) - sum(Fields!cancel_qty.Value)
might work.
"Kevin Eck" wrote:
> Hello,
> I have a query that I am using to calculate an order filled rate.
> One of my columns is OrderNo. to get the total orders for a date I take the
> order no. and just count to get a total for that column.
> Now the trick is I need to subtract from that count cancelled orders.
> How would I do that?
> I have tried:
> Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
> Count(Fields!order_type.Value) - Fields!cancel_qty.Value
> No luck either way.
> I would appreciate any help that you could give. Thanks,
> Kevin
>
>|||Thanks Jimbo...
worked like a charm!!!
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B167604A-34A2-4076-99A1-FF608AF774B5@.microsoft.com...
> depends, perhaps:
> count(Fields!order_type:Value) - sum(Fields!cancel_qty.Value)
>
> might work.
>
> "Kevin Eck" wrote:
>> Hello,
>> I have a query that I am using to calculate an order filled rate.
>> One of my columns is OrderNo. to get the total orders for a date I take
>> the
>> order no. and just count to get a total for that column.
>> Now the trick is I need to subtract from that count cancelled orders.
>> How would I do that?
>> I have tried:
>> Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
>> Count(Fields!order_type.Value) - Fields!cancel_qty.Value
>> No luck either way.
>> I would appreciate any help that you could give. Thanks,
>> Kevin
>>
>>

Calculate % qry

Hi,

I am trying to execute a query which calculate % on group by. here is the query

SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))

/ (

SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))

FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN
PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN
ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN
CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID
WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND
(BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))

)*100 AS [%]

FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
(B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
GROUP BY C.USR_HIGHEST_DEGREE

My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.

Any help would be appreciate.

learnasp:

My problem here it , query gives error

Please tell us the exact error.

|||

Hi,

Thanks for your reply. The error i get is

"Incorrect snytax near the keyword 'AS'.

But both queries run without any eroor separately. I run the nested select and main without nest select. Both give answers. Now I don't know what is problem.

Thanks

|||

I did not get any parsing error if I put a bracket after the multiply by 100 as shown below in red.

---

SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))

/ (

SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))

FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN
PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN
ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN
CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID
WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND
(BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))

)*100)AS [%]

FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
(B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
GROUP BY C.USR_HIGHEST_DEGREE

Calcuate the number of weeks in a month

I need a query to return the # of weeks ina month for eg

June has MAY has 4 weeks, ie 5-1-07 thru 5-7-07 is week 1,

'5-6-07' and '5-10-07' is week 2 ETc.. hence i need my results to show as follows, but it needs to be automated for every month, ie the number of weeks in a month.

EG

when date between '5-1-07' and '5-5-07' then 'Week1'
when date between '5-6-07' and '5-12-07' then week2'
when date between '5-13-07' and '5-19-07' then 'week3'

I need the results, above to be automated for each month..

You should consider the use of a Calendar table. (See this reference.)

A Calendar table is a very useful support object in most databases where it is often necessary to handle date related data.

|||I do have the calendar tbl. But that does not ahve the week of the month in it. I also looked at the reference u listed, I need the week of the month, ie between 1-5, not the week of the year.|||I do have the calendar tbl. But that does not ahve the week of the month in it. I also looked at the reference u listed, I need the week of that month, ie in May we have 5 weeks, so I need it sho show as week 1, week2 etc..ie between 1-5, not the week of the year.|||

Without knowing quite how you want to use it...here's an approach to start.

Code Snippet

declare @.date datetime

set @.date =getdate()

select 1 +(W + first.first_week_nbr)as MoWeekNbr

from dbo.Calendar

innerjoin

(

select @.date as seldate, first_week_nbr

from dbo.calendar where dt =dateadd(d,-1*(day(@.date)-1), @.date)

) first

on first.seldate = @.date

where dt = @.date

|||

It is easy to add additional columns to the Calendar table for your specific needs.

If you need a 'Week of the Month', then add a column and use an UPDATE to populate the data. It's not a complex algorithm...

|||Does the first week of each month ALWAYS start on the first?|||Yes, the first week of the month, always starts on the FIRST.|||This code gives me erros such as Invalid column name 'first_week_nbr'. etc.. we dont have the same calendar table|||

IF you add a 'computed' WeekOfMonth column to your Calendar table, the following will create the proper values.

Code Snippet

ALTER TABLE Calendar
ADD WeekOfMonth AS datepart( wk, dateadd( day, 0, datediff( day, dateadd( month, datediff( month, 0, [date] ), 0 ), [date] )))

Now you can use a JOIN with the Calendar table to find the WeekOfMonth and use that value in groupings, etc.

|||

THis code does not return the rite result, for eg if the date is 2007-02-05, it falls under week 2.-BUT The code returns week1

select datepart( wk, dateadd( day, 0, datediff( day, dateadd( month, datediff( month, 0, '20070205' ), 0 ),
'20070205' )))

|||

I asked you specifically, if

Does the first week of each month ALWAYS start on the first?

.

And you replied,

Yes, the first week of the month, always starts on the FIRST.

Following that logic, the first through the 7th is in week one, 8th through 14th in week two, etc. Therefore '2007-02-05' would correctly fall in week one.

However, it seems like now your previous response may have mislead me. (And of course, I take responsibility for not asking my question in a more exacting form. I clearly see how the confusion exists.)

It appears that you may have meant that a week is from Sunday-Saturday (Calendar). And that any number of days falling within a calendar week (even if that calendar week contains the days of a different month) constitutues the 'first week of the month'.

My assumption was that a week was seven days.

Please clarify. What determines a Week? (Numerical or Calendar)

|||

select day(getdate()) / 7 as [WeekOfMonth]

,convert(char(1),day(getdate())/ 7 )+'Week'as [MonthWeek]

--

select *

,day(<DateField>)/ 7 as [WeekOfMonth]

,convert(char(1),day(<DateField>)/ 7 )+'Week'as [MonthWeek]

from <TableName>

|||

Rusag2,

Somehow, this just doesn't seem right... (using your suggested algorithms)

Code Snippet

Select
Today = getdate(),
WeekOfMonth = day(getdate()) / 7,
MonthWeek = convert(char(1),day(getdate()) / 7 ) + 'Week'

Today WeekOfMonth MonthWeek
--
2007-06-25 19:46:26.130 3 3Week

It seems like today 'should be' in the forth (or fifth) week of the month...

|||

here's another alternative, you can also make use of a calendar udf, here's my favorite calendar udf

[edited]

CREATE FUNCTION dbo.GetCalendarDates
(
@.StartDate smalldatetime
, @.EndDate smalldatetime
)
RETURNS @.CalendarDates TABLE (
Row int IDENTITY(1,1)
, CalendarDate smalldatetime
, [Day] AS day( [CalendarDate] )
, [Month] AS month( [CalendarDate] )
, [Year] AS year( [CalendarDate] )
, YearDay AS datepart( dayofyear, [CalendarDate] )
, DayOfWeek AS datepart( weekday, [CalendarDate] )
, WeekOfYear AS datepart( week, [CalendarDate] )
, DateFirst AS @.@.DATEFIRST
, WeekOfMonth int
, NumOfWeeks int
, WeekStart smalldatetime
, WeekEnd smalldatetime
)
AS
BEGIN
DECLARE @.StartOfMonth smalldatetime
DECLARE @.EndOfMonth smalldatetime

SET @.StartOfMonth = CONVERT(varchar(2),MONTH(@.StartDate)) + '/1/' + CONVERT(varchar(4),YEAR(@.StartDate))
SET @.EndOfMonth = CONVERT(varchar(2),MONTH(@.EndDate)) + '/1/' + CONVERT(varchar(4),YEAR(@.EndDate))
SET @.EndOfMonth = DATEADD(day,-1,DATEADD(month,1,@.EndOfMonth))

WHILE @.StartOfMonth <= @.EndOfMonth
BEGIN

INSERT
INTO @.CalendarDates
SELECT CONVERT(varchar(10),@.StartOfMonth,101), 1, 1, CONVERT(varchar(10),@.StartOfMonth,101), CONVERT(varchar(10),@.StartOfMonth,101)

SET @.StartOfMonth = DATEADD(d,1,@.StartOfMonth)
END

UPDATE a
SET WeekOfMonth = WeekOfYear - MinWeek
FROM @.CalendarDates a INNER JOIN
(
SELECT [Year]
, [Month]
, MIN(WeekOfYear) - 1 AS MinWeek
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]
UPDATE a
SET WeekStart = b.WeekStart
, WeekEnd = b.WeekEnd
, NumOfWeeks = b.NumOfWeeks
FROM @.CalendarDates a INNER JOIN
(
SELECT [Year]
, [Month]
, WeekOfMonth
, MIN(CalendarDate) AS WeekStart
, MAX(CalendarDate) AS WeekEnd
, COUNT(Row) AS NumOfWeeks
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
, WeekOfMonth
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]
AND a.WeekOfMonth = b.WeekOfMonth

UPDATE a
SET NumOfWeeks = b.NumOfWeeks
FROM @.CalendarDates a INNER JOIN
( SELECT [Year]
, [Month]
, Max(WeekOfMonth) AS NumOfWeeks
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]

DELETE
FROM @.CalendarDates
WHERE CalendarDate NOT BETWEEN @.StartDate AND @.EndDate

RETURN
END

ex.

declare @.to smalldatetime
declare @.from smalldatetime
declare @.thedate smalldatetime

set @.to = '06/01/2007'
set @.from = '07/30/2007'
set @.thedate = '06/25/2007'

[edited]

select distinct @.thedate, a.WeekOfMonth, a.WeekStart, a.WeekEnd, 'Week ' + convert(varchar(1),a.WeekOfMonth)
from dbo.GetCalendarDates(@.to,@.from) a

Calacualtion of Response Time of Query

Hi
How do you calaculate the response time of a query ?
Is it possible to capture from the SQL Server trace?
Regards
ImtiazImtiaz,
The duration of a query can be captured in Profiler using the duration data
column. From within Query Analyzer, use the Execution Time in the status
bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
You can also turn on Show Sever Trace and Show Client Statistics.
HTH
Jerry
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||type this in Query Analyzer
SET STATISTICS TIME ON
run your query
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:

> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||And look in the message tab for elapsed time
"SQL" wrote:
[vbcol=seagreen]
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
>|||Sorry to stress again...
I need Reponse Time Not the Duration Time of the Query.
Response Time : is the time it takes for the first record to appear to the
client
"Jerry Spivey" wrote:

> Imtiaz,
> The duration of a query can be captured in Profiler using the duration dat
a
> column. From within Query Analyzer, use the Execution Time in the status
> bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> You can also turn on Show Sever Trace and Show Client Statistics.
> HTH
> Jerry
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
>
>|||Well how would you capture that at the SQL Server level only?
If there is dial up connection at the client then the result will take
longer than for example a T1 connection
You can give the client the illusion that the results are there faster
(particulary for a scrollable grid) by using with (fast n) in your select
statement as a hint
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
[vbcol=seagreen]
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear to the
> client
> "Jerry Spivey" wrote:
>|||Imtiaz wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear
> to the client
>
Duration is more or less the response time, just not exactly as you
describe it. Use Profiler for more complete statistics. The CPU is the
time SQL Server took to execute the query. The Duration is the total
time from query execution to rowset fetch completion. The number your
looking for is probably somewhere in between (assuming CPU value is from
a single CPU). You might find what you're looking for in the Query
Analyzer - Show Client Statistics menu option. OTOH, the response time
you are looking for could easily be calculated from the client
application. Assuming you are sending all your SQL through common
routines, you could easily add either a conditional compiler argument or
a parameter to the common routine that captures the execution time.
Assuming you're not running the queries asynchronously, it should be a
trivial matter to determine when the query returns after the first row
is ready to be fetched.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Imtiaz,
By default, the optimizer tries to minimize the response time of the
last row of the resultset. If you want to optimize the return of the
first row of the resultset, then you should add the FAST hint, in this
case OPTION(FAST 1).
As others have mentioned, the way to measure 'default' response times,
you can use SET STATISTICS TIME and/or SELECT CURRENT_TIMESTAMP.
You might be able to simulate the response time of the first row by
running the command SET ROWCOUNT 1 before the batch (and resetting it
afterwards with SET ROWCOUNT 0).
Hope this helps,
Gert-Jan
Imtiaz wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz

Calacualtion of Response Time of Query

Hi
How do you calaculate the response time of a query ?
Is it possible to capture from the SQL Server trace?
Regards
ImtiazImtiaz,
The duration of a query can be captured in Profiler using the duration data
column. From within Query Analyzer, use the Execution Time in the status
bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
You can also turn on Show Sever Trace and Show Client Statistics.
HTH
Jerry
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||type this in Query Analyzer
SET STATISTICS TIME ON
run your query
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||And look in the message tab for elapsed time
"SQL" wrote:
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >|||I want the response time. Not the througput time.
Does CPU time translate to Response Time ? Definitely Elapsed Time is the
total time of execution of query '
Regards
Imtiaz
"SQL" wrote:
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >|||Sorry to stress again...
I need Reponse Time Not the Duration Time of the Query.
Response Time : is the time it takes for the first record to appear to the
client
"Jerry Spivey" wrote:
> Imtiaz,
> The duration of a query can be captured in Profiler using the duration data
> column. From within Query Analyzer, use the Execution Time in the status
> bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> You can also turn on Show Sever Trace and Show Client Statistics.
> HTH
> Jerry
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >
>
>|||Well how would you capture that at the SQL Server level only?
If there is dial up connection at the client then the result will take
longer than for example a T1 connection
You can give the client the illusion that the results are there faster
(particulary for a scrollable grid) by using with (fast n) in your select
statement as a hint
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear to the
> client
> "Jerry Spivey" wrote:
> > Imtiaz,
> >
> > The duration of a query can be captured in Profiler using the duration data
> > column. From within Query Analyzer, use the Execution Time in the status
> > bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> > You can also turn on Show Sever Trace and Show Client Statistics.
> >
> > HTH
> >
> > Jerry
> > "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> > news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> > > Hi
> > >
> > > How do you calaculate the response time of a query ?
> > > Is it possible to capture from the SQL Server trace?
> > >
> > > Regards
> > > Imtiaz
> > >
> > >
> >
> >
> >|||Imtiaz wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear
> to the client
>
Duration is more or less the response time, just not exactly as you
describe it. Use Profiler for more complete statistics. The CPU is the
time SQL Server took to execute the query. The Duration is the total
time from query execution to rowset fetch completion. The number your
looking for is probably somewhere in between (assuming CPU value is from
a single CPU). You might find what you're looking for in the Query
Analyzer - Show Client Statistics menu option. OTOH, the response time
you are looking for could easily be calculated from the client
application. Assuming you are sending all your SQL through common
routines, you could easily add either a conditional compiler argument or
a parameter to the common routine that captures the execution time.
Assuming you're not running the queries asynchronously, it should be a
trivial matter to determine when the query returns after the first row
is ready to be fetched.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Imtiaz,
By default, the optimizer tries to minimize the response time of the
last row of the resultset. If you want to optimize the return of the
first row of the resultset, then you should add the FAST hint, in this
case OPTION(FAST 1).
As others have mentioned, the way to measure 'default' response times,
you can use SET STATISTICS TIME and/or SELECT CURRENT_TIMESTAMP.
You might be able to simulate the response time of the first row by
running the command SET ROWCOUNT 1 before the batch (and resetting it
afterwards with SET ROWCOUNT 0).
Hope this helps,
Gert-Jan
Imtiaz wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz

Calacualtion of Response Time of Query

Hi
How do you calaculate the response time of a query ?
Is it possible to capture from the SQL Server trace?
Regards
Imtiaz
Imtiaz,
The duration of a query can be captured in Profiler using the duration data
column. From within Query Analyzer, use the Execution Time in the status
bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
You can also turn on Show Sever Trace and Show Client Statistics.
HTH
Jerry
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>
|||type this in Query Analyzer
SET STATISTICS TIME ON
run your query
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:

> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>
|||And look in the message tab for elapsed time
"SQL" wrote:
[vbcol=seagreen]
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
|||Sorry to stress again...
I need Reponse Time Not the Duration Time of the Query.
Response Time : is the time it takes for the first record to appear to the
client
"Jerry Spivey" wrote:

> Imtiaz,
> The duration of a query can be captured in Profiler using the duration data
> column. From within Query Analyzer, use the Execution Time in the status
> bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> You can also turn on Show Sever Trace and Show Client Statistics.
> HTH
> Jerry
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
>
>
|||Well how would you capture that at the SQL Server level only?
If there is dial up connection at the client then the result will take
longer than for example a T1 connection
You can give the client the illusion that the results are there faster
(particulary for a scrollable grid) by using with (fast n) in your select
statement as a hint
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
[vbcol=seagreen]
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear to the
> client
> "Jerry Spivey" wrote:
|||Imtiaz wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear
> to the client
>
Duration is more or less the response time, just not exactly as you
describe it. Use Profiler for more complete statistics. The CPU is the
time SQL Server took to execute the query. The Duration is the total
time from query execution to rowset fetch completion. The number your
looking for is probably somewhere in between (assuming CPU value is from
a single CPU). You might find what you're looking for in the Query
Analyzer - Show Client Statistics menu option. OTOH, the response time
you are looking for could easily be calculated from the client
application. Assuming you are sending all your SQL through common
routines, you could easily add either a conditional compiler argument or
a parameter to the common routine that captures the execution time.
Assuming you're not running the queries asynchronously, it should be a
trivial matter to determine when the query returns after the first row
is ready to be fetched.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Imtiaz,
By default, the optimizer tries to minimize the response time of the
last row of the resultset. If you want to optimize the return of the
first row of the resultset, then you should add the FAST hint, in this
case OPTION(FAST 1).
As others have mentioned, the way to measure 'default' response times,
you can use SET STATISTICS TIME and/or SELECT CURRENT_TIMESTAMP.
You might be able to simulate the response time of the first row by
running the command SET ROWCOUNT 1 before the batch (and resetting it
afterwards with SET ROWCOUNT 0).
Hope this helps,
Gert-Jan
Imtiaz wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz

CAHRINDEX

I am trying to tune a query that uses two seperate CHARINDEX functions. The
first:
SELECT @.fieldValue = (SUBSTRING(@.delimitedList, 1, CHARINDEX(',',
@.delimitedList) - 1))
Obviously starts at the first postion and returns the data to the left of
the ','.
The second statement:
SELECT @.delimitedList = SUBSTRING(@.delimitedList, (CHARINDEX(',',
@.delimitedList) + 1), LEN(@.delimitedList))
Limits the the @.delimitedList value to get rid of the value that we have
already captured (essentially moves the second delimited piece into the firs
t
postion). THis is done in a WHILE loop so it continues until all values have
been inserted into a temp table. What I would like is a way to capture the
CHARINDEX value in the first statement to plug in as a starting variable.
Thus making the first statement look like this (or something similar):
SELECT @.fieldValue = (SUBSTRING(@.delimitedList, @.start_pos, CHARINDEX(',',
@.delimitedList) - 1))
Hope this makes sense....any ideas.
TIA, Jordan...
declare @.pos int
declare @.i int
set @.i = 1
set @.pos = charindex(',', @.delimitedList)
while @.pos > 0
begin
SELECT @.fieldValue = SUBSTRING(@.delimitedList, @.i, @.pos - 1)
..
set @.i = @.pos + 1
set @.pos = charindex(',', @.delimitedList, @.i)
end
...
AMB
"JMNUSS" wrote:

> I am trying to tune a query that uses two seperate CHARINDEX functions. Th
e
> first:
> SELECT @.fieldValue = (SUBSTRING(@.delimitedList, 1, CHARINDEX(',',
> @.delimitedList) - 1))
> Obviously starts at the first postion and returns the data to the left of
> the ','.
> The second statement:
> SELECT @.delimitedList = SUBSTRING(@.delimitedList, (CHARINDEX(',',
> @.delimitedList) + 1), LEN(@.delimitedList))
> Limits the the @.delimitedList value to get rid of the value that we have
> already captured (essentially moves the second delimited piece into the fi
rst
> postion). THis is done in a WHILE loop so it continues until all values ha
ve
> been inserted into a temp table. What I would like is a way to capture the
> CHARINDEX value in the first statement to plug in as a starting variable.
> Thus making the first statement look like this (or something similar):
> SELECT @.fieldValue = (SUBSTRING(@.delimitedList, @.start_pos, CHARINDEX(',',
> @.delimitedList) - 1))
> Hope this makes sense....any ideas.
> TIA, Jordan|||That helps, however I need the result set in table form so a delimited strin
g
of '1,2,3,4,5' would look like:
fieldvalue
--
1
2
3
4
5
Is that possible, TIA
"Alejandro Mesa" wrote:
> ...
> declare @.pos int
> declare @.i int
> set @.i = 1
> set @.pos = charindex(',', @.delimitedList)
> while @.pos > 0
> begin
> SELECT @.fieldValue = SUBSTRING(@.delimitedList, @.i, @.pos - 1)
> ...
> set @.i = @.pos + 1
> set @.pos = charindex(',', @.delimitedList, @.i)
> end
> ...
>
> AMB
> "JMNUSS" wrote:
>|||http://www.aspfaq.com/2248
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:72F0B5F6-93C2-4F0E-94D8-45EE92F2672D@.microsoft.com...
> That helps, however I need the result set in table form so a delimited
> string
> of '1,2,3,4,5' would look like:
> fieldvalue
> --
> 1
> 2
> 3
> 4
> 5
> Is that possible, TIA
>
> "Alejandro Mesa" wrote:
>|||Lot of solutions.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html#tryit
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"JMNUSS" wrote:
> That helps, however I need the result set in table form so a delimited str
ing
> of '1,2,3,4,5' would look like:
> fieldvalue
> --
> 1
> 2
> 3
> 4
> 5
> Is that possible, TIA
>
> "Alejandro Mesa" wrote:
>|||Thanks guys, I was able to find what I needed on aspfaq. I really appreciate
the help!!!!
"Alejandro Mesa" wrote:
> Lot of solutions.
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html#tryit
> Faking arrays in T-SQL stored procedures
> http://www.bizdatasolutions.com/tsql/sqlarrays.asp
> How do I simulate an array inside a stored procedure?
> http://www.aspfaq.com/show.asp?id=2248
>
> AMB
> "JMNUSS" wrote:
>

Caching reports

I have many reports that run off the same query statement. The Statement
uses parameters that are supplied when the report is executed. There are
allso additional parameters that are used in filters and displayed on the
report (such as title information). I have set up the reports to be cached
and modified the parameters in the report that are not used in the Query with
<UsedInQuery>False. If I run the report with all the same parameters the
Cache seems to work well. If I change one of the parameters used as a filter
the cache is not used. Is there any way around this?
What would be even better is if I could set up cacheing on the shared data
source instead of the report and cache the data for all reports that uses the
same query.
ThanksIt sounds like the report cache includes filters in its caching of data.
It's important to remember that the cache is not just a cache of query data,
but a cache of data the way it will be used in the report, ready for
rendering to any of several different formats.
Now, you could work on the SQL side of things, to see if you can streamline
your datasource (the database itself) to work more effeciently with multiple
reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ken McCullough" <Ken McCullough@.discussions.microsoft.com> wrote in message
news:44B0FAB8-5260-4F06-830E-47429006D5FB@.microsoft.com...
>I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be
> cached
> and modified the parameters in the report that are not used in the Query
> with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a
> filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses
> the
> same query.
> Thanks
>|||Ken,
Double check that the <UsedInQuery>False</UsedInQuery> that you added are
still in the RDL.
Though I have never determined the exact sequence to duplicate, I have had
times where I believe the Report Designer removed <UsedInQuery> settings and
I had to add them again.
We have done a fair amount of testing with
<UsedInQuery>False</UsedInQuery> and its cache effects, and at least for us
it is definately working as advertised.
Bob
"Ken McCullough" wrote:
> I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be cached
> and modified the parameters in the report that are not used in the Query with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses the
> same query.
> Thanks
>|||I stand corrected then. The documentation indicates that UsedInQuery
affects report snapshots, which is similar to caching.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:289783D1-D1DC-4E63-B967-B49DC416FCC8@.microsoft.com...
> Ken,
> Double check that the <UsedInQuery>False</UsedInQuery> that you added are
> still in the RDL.
> Though I have never determined the exact sequence to duplicate, I have
> had
> times where I believe the Report Designer removed <UsedInQuery> settings
> and
> I had to add them again.
> We have done a fair amount of testing with
> <UsedInQuery>False</UsedInQuery> and its cache effects, and at least for
> us
> it is definately working as advertised.
> Bob
> "Ken McCullough" wrote:
>> I have many reports that run off the same query statement. The Statement
>> uses parameters that are supplied when the report is executed. There are
>> allso additional parameters that are used in filters and displayed on the
>> report (such as title information). I have set up the reports to be
>> cached
>> and modified the parameters in the report that are not used in the Query
>> with
>> <UsedInQuery>False. If I run the report with all the same parameters the
>> Cache seems to work well. If I change one of the parameters used as a
>> filter
>> the cache is not used. Is there any way around this?
>> What would be even better is if I could set up cacheing on the shared
>> data
>> source instead of the report and cache the data for all reports that uses
>> the
>> same query.
>> Thanks
>>