Calculated member - Count function

Hello
I have te following problem:
I have made a cube with quotations. But a quotation consist of one or more
lines. On each line a product is selected (with price, amount, etc.).
The records in my fact table are quotationlines (not quotation-headers),
because the producthierarchy is linked to the articles (in quotationlines).
I would like to count the quotations (not the lines).
The lines linked to a certain quotation have the same document number.
How can I count the number of quotations, e.g. by using the function
calculated member or...? I have tried something but without a good result.
Can you help me?
Thanks in advance
HansIs the relatoinship between the document number and quotation is one to one?
If then, it's possible to aggregate the document number with aggregation
function of "distinct count". You can set the property of measure in Cube
Editor.
Ohjoo Kwon
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> Hello
> I have te following problem:
> I have made a cube with quotations. But a quotation consist of one or more
> lines. On each line a product is selected (with price, amount, etc.).
> The records in my fact table are quotationlines (not quotation-headers),
> because the producthierarchy is linked to the articles (in
quotationlines).
> I would like to count the quotations (not the lines).
> The lines linked to a certain quotation have the same document number.
> How can I count the number of quotations, e.g. by using the function
> calculated member or...? I have tried something but without a good result.
> Can you help me?
> Thanks in advance
> Hans|||Thank you,
But I still have a problem. At the Aggregate Function the option 'count
distinct only appears when the source field is an Numeric field type. The
documentnumber I would like to count (distinct count) is a Text-field (e.g.
VF05001234).
Do you have any ideas to solve this problem?
Or is the problem not the field type, but something else I don't know?
Hans
"Ohjoo Kwon" wrote:

> Is the relatoinship between the document number and quotation is one to on
e?
> If then, it's possible to aggregate the document number with aggregation
> function of "distinct count". You can set the property of measure in Cube
> Editor.
> Ohjoo Kwon
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> quotationlines).
>
>|||Yes, you are right. It must be numeric.
I recommend you use surrogate key mapped to document number.
Ohjoo Kwon
"hans.de.korte@.verder.nl"
<hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> Thank you,
> But I still have a problem. At the Aggregate Function the option 'count
> distinct only appears when the source field is an Numeric field type. The
> documentnumber I would like to count (distinct count) is a Text-field
(e.g.[vbcol=seagreen]
> VF05001234).
> Do you have any ideas to solve this problem?
> Or is the problem not the field type, but something else I don't know?
> Hans
> "Ohjoo Kwon" wrote:
>
one?[vbcol=seagreen]
Cube[vbcol=seagreen]
more[vbcol=seagreen]
quotation-headers),[vbcol=seagreen]
result.[vbcol=seagreen]|||Thank you!!
"Ohjoo Kwon" wrote:

> Yes, you are right. It must be numeric.
> I recommend you use surrogate key mapped to document number.
> Ohjoo Kwon
> "hans.de.korte@.verder.nl"
> <hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
> news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> (e.g.
> one?
> Cube
> more
> quotation-headers),
> result.
>
>

Calculated measure using Tail/Filter on time not always working

We created the following time based measure to get the distinct count of propertyId - but we needed to get the distinct count from the last fact table load in a time period so we used the following

Code Snippet

IIF([Time].Currentmember.level.ordinal = 4, [Measures].[Property Id],

IIF([Time].Currentmember.level.ordinal = 3,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

IIF([Time].Currentmember.level.ordinal = 2,

IIF([Time].Currentmember.properties("Is Quarter") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 1,

IIF([Time].Currentmember.properties("Is Year") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 0,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

NULL

)))))

In most cases, the calculation seems to work fine, but sometimes, when browsing the cube using Proclarity (6.1), no values are returned when we know there are values...

Can anyone see why this measure would not work in some circumstances?

I think that basically, what I'm asking for is a method of determining a distinct count of the last recordset from a fact table for any time dimension level (which is what the above is trying to do).

The above method seems only to work rather haphazardly, especially when viewing via Proclarity (6.1) When you move certain dimensions to the background, the calculation returns zero when we knbow data exists...

Is there a more efficient way of finding this sort of distinct count (using SQL 2000) ?

|||Anyone fancy a stab at this ?|||

Hi,

have not read the whole mdx,

do not know proclarity

This does not work if you do a multiselect on the time dimension beause of CurrentMember (there is no current member if you have a subquery)

Does it return null or #error and do not show up.

It really depends on how your client does write his queries. I would recomend you trace your queries with profiler and then analyse the query which is returning wrong results. Check where and how the measure is used and check if there are any subqueries based on time.

Best Regards, HANNES

|||

Thanks for having a look at this

The following query will not return any values for No of Buildings

Code Snippet

SELECT { [Measures].[Number of Units], [Measures].[Number of Buildings], [Measures].[Property Id] } ON COLUMNS ,

{ DESCENDANTS( [Building Status].[All Building Status], [Building Status].[Building Status] ) } ON ROWS

FROM [Property]

WHERE ( [Time].[Year].&[2007] )

I can't really see why not, as it should pick up the latest data for 2007 should it not, given the calculations in my first post ?

|||

Hi,

I am a little bit confused about the complexity...

A few more questions.

Do you have only one Hierarchy in Time? (All Attribute hierarchies are hidden - I guess yes because you do not specify an hierarchy name)

Is it a 2005 cube or 2000?

Are the properties populated correct? Is Quarter, Is Year

Why you need this properties - if its one hierarchy then level 2 could only be quarters? and level 1 only years and level 0 only ALL if I am right

Why do you write this complex form - why not only decendants(time.currentmember),time.levels.count, leaves) which your tail logic

maybe one of this is a hint to you.

HANNES

PS: I would alos recommend moshas blog with design time level checking instad of runtime level checking. Scope instead of member with iif (only for design purpose - have nothing to do with functionality)

|||

Hi,

1. SQL 2000 cubes - and yes, the properties are all fine and we can't change time dimension at this stage as it works fine itself.

It's this complex because we have daily loading in certain parts of DW but weekly in others - in terms of a measure using distinct count

this presents issues when querying measures from weekly loads in terms of needing to hit the last load in a period, be it month, quarter, year or All Time...

In any case we fixed the issue by creating a completely new view for building count as we read that distinct count runs in to known issues when AGGREGATE used in MDX queries, so in developing the new view we populated a 1 for propertyid as opposed to attempting to do a distinct count - also we created calculated cells to deal with the time hierarchy levels issue - we then based a new cube containing just building count but shared same dimensions with the old cube, containing other aggregateable valid measures that worked.

Megred the two cubes into a new virtual cube & now building count works fine So problem solved. I think the tail/filter issue was a red herring in any case - the data that got was fine - AS 2000 just does not like distinct counts (in the way we were trying to use them...)

Thanks for getting involved with this though, but i'll mark it as solved for now.

calculated measure problem(ssas 2005)

I have the data like following, count and Amount are the measures.

BU Tiear Designation Count Amount
BU1 1 PM 10 200
BU1 2 SE 20 300
BU2 1 PM 40 300

i need to have calculated measure like following

(10*200)+(20*30)+(40*300)/ (10+20+40)

pls tel me any way to do that
tried like following. but there is some error.pls help me to solve this.

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[SumOfECount]
AS
sum
(
[Measures].[Employee Count]
) ,
VISIBLE = 1 ;
CREATE MEMBER CURRENTCUBE.[Measures].[multi]
AS

'[Measures].[Employee Count]*[Measures].[CPC Value]'
,
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[SumOfEMulti]
AS
sum
(
[Measures].[multi]
) ,

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[Calcula]
AS '[Measures].[SumOfEMulti]/[Measures].[SumOfECount]' ,
VISIBLE = 1 ;

Calculated Measure does not appear in all the drilldowns

Hello,

I created a hierarchy [Time].[Year-Month] that has the following

-Year

--Period

English Month

I want to return the headcount from the 1^st Period only. This works fine. I did it by making a new measurement called LastActive by using the LastNonEmpty aggregate function. I then made a calculated measure called BeginHeadCount as the following:

([Measures].[LastActive], [Time].[Period].&[1]). This works OK, sort of.

When I browse the cube, and I put the [Time].[Year-Month] hierarchy on the columns I don’t see the BeginHeadCount for each English Month, but I do see it for the totals. For example, I see the BeginHeadCount for the Year, then I hit the + and drill-down to the Period, I see it in Period, I hit the + again to drill-down to the English Month – the breakout is now English Month and Total. The Total does show the BeginHeadCount but it is blank in the English Month.

How do I get the BeginHeadCount calculated measure to also appear in the English Month?

I assume that this has something to do with referring specifically to the &[1] period. But I don’t know how to resolve this.

Thank you for the help.

Your issue here is that you have specified a tuple to calculate your value.

A tuple can best be described as an intersection in multi-dimensional space, so what you are essentially saying is to return the [BeginHeadCount] measure where Period.&[1] intersects with each of the members in the current query. What this gives you are intersections where no data exists such as period 1 and month 12.

I am guessing that what you really want to say is "regardless of the current time members, show me the amount from Period 1". You would do this by using an aggregate function that will return a numeric amount from the calculation rather than a tuple. Something like the following should do the trick, you would have to include the current member from the year attribute in the calcuation so that it does not sum up period 1 for every year (which would not make any sense).

SUM( {([Time].[Year].CurrentMember,[Time].[Period].&[1])}, [Measures].[LastActive])

|||

You didn't describe the attribute relationships among the attributes: [Year], [Period] and [English Month] of the [Time].[Year-Month] hierarchy. My guess is that this isn't a natural hierarchy - if [Period] is like [Quarter Of Year] and [English Month] like [Month Of Year] in the Adventure Works [Date] dimension, you might try:

([Measures].[LastActive], [Time].[Period].&[1], [Time].[English Month].[All])

|||

Darren/Deepak,

Thank you for the great answers. They were both a great help. I am now able to see the data the way I wanted to.

Another quick question... Deepak, when you say that it was your guess that the relationships between the attributes wasn't a natural hierarchy, what do you mean by that? I did create a new hierarchy that has the Year-Period-English month. Is this different then a natural hierarchy? What make a natural hierarchy?

Thanks for the information. Hopefully my new MDX book will come this week and I wont ask these newbie questions

|||

"What make a natural hierarchy?" - here's an entry from Mosha's blog which discusses this:

http://sqljunkies.com/WebLog/mosha/archive/2006/11/09/natural_hierarchy.aspx

>>

What are the natural hierarchies and why they are a good thing

...

A natural hierarchy is composed of attributes where each attribute is a member property of the attribute below. For example, the Geography hierarchy Country, State, City and Name is a natural hierarchy if City is a member property of Name; State is for City; and Country is for State. The hierarchy Gender-Age is not a natural hierarchy because Gender is not a member property of Age.

...

It should be clear by now, that whenever possible, unnatural hierarchies should be avoided. But this doesn't mean that unnatural hierarchies are always bad ! Any guidance should be considered within its reasoning. For example, in my article about Time Calculations in UDM, I showed how using unnatural hierarchies in the form Year -> QuarterOfYear -> MonthOfQuarter -> DayOfMonth, actually simplifies a lot writing time relation calculations.

...

>>

|||Thank you for all your help!

Calculated Measure does not appear in all the drilldowns

Hello,

I created a hierarchy [Time].[Year-Month] that has the following

-Year

--Period

English Month

I want to return the headcount from the 1^st Period only. This works fine. I did it by making a new measurement called LastActive by using the LastNonEmpty aggregate function. I then made a calculated measure called BeginHeadCount as the following:

([Measures].[LastActive], [Time].[Period].&[1]). This works OK, sort of.

When I browse the cube, and I put the [Time].[Year-Month] hierarchy on the columns I don’t see the BeginHeadCount for each English Month, but I do see it for the totals. For example, I see the BeginHeadCount for the Year, then I hit the + and drill-down to the Period, I see it in Period, I hit the + again to drill-down to the English Month – the breakout is now English Month and Total. The Total does show the BeginHeadCount but it is blank in the English Month.

How do I get the BeginHeadCount calculated measure to also appear in the English Month?

I assume that this has something to do with referring specifically to the &[1] period. But I don’t know how to resolve this.

Thank you for the help.

Your issue here is that you have specified a tuple to calculate your value.

A tuple can best be described as an intersection in multi-dimensional space, so what you are essentially saying is to return the [BeginHeadCount] measure where Period.&[1] intersects with each of the members in the current query. What this gives you are intersections where no data exists such as period 1 and month 12.

I am guessing that what you really want to say is "regardless of the current time members, show me the amount from Period 1". You would do this by using an aggregate function that will return a numeric amount from the calculation rather than a tuple. Something like the following should do the trick, you would have to include the current member from the year attribute in the calcuation so that it does not sum up period 1 for every year (which would not make any sense).

SUM( {([Time].[Year].CurrentMember,[Time].[Period].&[1])}, [Measures].[LastActive])

|||

You didn't describe the attribute relationships among the attributes: [Year], [Period] and [English Month] of the [Time].[Year-Month] hierarchy. My guess is that this isn't a natural hierarchy - if [Period] is like [Quarter Of Year] and [English Month] like [Month Of Year] in the Adventure Works [Date] dimension, you might try:

([Measures].[LastActive], [Time].[Period].&[1], [Time].[English Month].[All])

|||

Darren/Deepak,

Thank you for the great answers. They were both a great help. I am now able to see the data the way I wanted to.

Another quick question... Deepak, when you say that it was your guess that the relationships between the attributes wasn't a natural hierarchy, what do you mean by that? I did create a new hierarchy that has the Year-Period-English month. Is this different then a natural hierarchy? What make a natural hierarchy?

Thanks for the information. Hopefully my new MDX book will come this week and I wont ask these newbie questions

|||

"What make a natural hierarchy?" - here's an entry from Mosha's blog which discusses this:

http://sqljunkies.com/WebLog/mosha/archive/2006/11/09/natural_hierarchy.aspx

>>

What are the natural hierarchies and why they are a good thing

...

A natural hierarchy is composed of attributes where each attribute is a member property of the attribute below. For example, the Geography hierarchy Country, State, City and Name is a natural hierarchy if City is a member property of Name; State is for City; and Country is for State. The hierarchy Gender-Age is not a natural hierarchy because Gender is not a member property of Age.

...

It should be clear by now, that whenever possible, unnatural hierarchies should be avoided. But this doesn't mean that unnatural hierarchies are always bad ! Any guidance should be considered within its reasoning. For example, in my article about Time Calculations in UDM, I showed how using unnatural hierarchies in the form Year -> QuarterOfYear -> MonthOfQuarter -> DayOfMonth, actually simplifies a lot writing time relation calculations.

...

>>

|||Thank you for all your help!

Calculated Measure does not appear in all the drilldowns

Hello,

I created a hierarchy [Time].[Year-Month] that has the following

-Year

--Period

English Month

I want to return the headcount from the 1^st Period only. This works fine. I did it by making a new measurement called LastActive by using the LastNonEmpty aggregate function. I then made a calculated measure called BeginHeadCount as the following:

([Measures].[LastActive], [Time].[Period].&[1]). This works OK, sort of.

When I browse the cube, and I put the [Time].[Year-Month] hierarchy on the columns I don’t see the BeginHeadCount for each English Month, but I do see it for the totals. For example, I see the BeginHeadCount for the Year, then I hit the + and drill-down to the Period, I see it in Period, I hit the + again to drill-down to the English Month – the breakout is now English Month and Total. The Total does show the BeginHeadCount but it is blank in the English Month.

How do I get the BeginHeadCount calculated measure to also appear in the English Month?

I assume that this has something to do with referring specifically to the &[1] period. But I don’t know how to resolve this.

Thank you for the help.

Your issue here is that you have specified a tuple to calculate your value.

A tuple can best be described as an intersection in multi-dimensional space, so what you are essentially saying is to return the [BeginHeadCount] measure where Period.&[1] intersects with each of the members in the current query. What this gives you are intersections where no data exists such as period 1 and month 12.

I am guessing that what you really want to say is "regardless of the current time members, show me the amount from Period 1". You would do this by using an aggregate function that will return a numeric amount from the calculation rather than a tuple. Something like the following should do the trick, you would have to include the current member from the year attribute in the calcuation so that it does not sum up period 1 for every year (which would not make any sense).

SUM( {([Time].[Year].CurrentMember,[Time].[Period].&[1])}, [Measures].[LastActive])

|||

You didn't describe the attribute relationships among the attributes: [Year], [Period] and [English Month] of the [Time].[Year-Month] hierarchy. My guess is that this isn't a natural hierarchy - if [Period] is like [Quarter Of Year] and [English Month] like [Month Of Year] in the Adventure Works [Date] dimension, you might try:

([Measures].[LastActive], [Time].[Period].&[1], [Time].[English Month].[All])

|||

Darren/Deepak,

Thank you for the great answers. They were both a great help. I am now able to see the data the way I wanted to.

Another quick question... Deepak, when you say that it was your guess that the relationships between the attributes wasn't a natural hierarchy, what do you mean by that? I did create a new hierarchy that has the Year-Period-English month. Is this different then a natural hierarchy? What make a natural hierarchy?

Thanks for the information. Hopefully my new MDX book will come this week and I wont ask these newbie questions

|||

"What make a natural hierarchy?" - here's an entry from Mosha's blog which discusses this:

http://sqljunkies.com/WebLog/mosha/archive/2006/11/09/natural_hierarchy.aspx

>>

What are the natural hierarchies and why they are a good thing

...

A natural hierarchy is composed of attributes where each attribute is a member property of the attribute below. For example, the Geography hierarchy Country, State, City and Name is a natural hierarchy if City is a member property of Name; State is for City; and Country is for State. The hierarchy Gender-Age is not a natural hierarchy because Gender is not a member property of Age.

...

It should be clear by now, that whenever possible, unnatural hierarchies should be avoided. But this doesn't mean that unnatural hierarchies are always bad ! Any guidance should be considered within its reasoning. For example, in my article about Time Calculations in UDM, I showed how using unnatural hierarchies in the form Year -> QuarterOfYear -> MonthOfQuarter -> DayOfMonth, actually simplifies a lot writing time relation calculations.

...

>>

|||Thank you for all your help!sql

Calculated Measure Aggregation Seems to differ from MSAS 2K5 and 2000

I'm in the process of upgrading a cube in MSAS 2000 to MSAS 2005 (SP 1). In the existing MSAS 2000 cube, I have the following calculated measure defined:

SUM(Descendants([Term].[Term].CurrentMember, [Term Name]),
SUM
(
PeriodsToDate
([ActivityDate].[Std].[(All)]
,strToMember("[ActivityDate].[Std].&[" + Cstr([Term].[Term].CurrentMember.Properties("CensusDateValue")) +"]"
)), [Measures].[Activity Hours]))

This measure is supposed to sum up all activity up until the "CensusDateValue". it seems to be working fine and Aggregates up correctly even when the Term is a level other then [term name]

I came up with the following AS 2005 MDX that attempts to perform the same calculation(Some of the dimension names and properties have be modified):

SUM(Descendants([Term Dimension].CurrentMember, [Term Dimension].[TermName] ),
SUM(
PeriodsToDate(
[Activity Date Dimension].[Date].[(All)]
,STRTOMEMBER("[Activity Date Dimension].[Date].&[" + CSTR([Term Dimension].[Term Name].CurrentMember.Properties("Census Date SK")) + "]")), [Measures].[User Activity Hours]))

The calculation seems to work fine as long as I have the [Term Dimension].[Term.name] in the rows axis, otherwise it returns null. I suspect this is a difference between how member properties are calculated and aggregated between MSAS2K5 and MSAS200

Does anyone have any ideas or suggestions for troubleshooting?
It's hard to tell what's going on without looking at your cube design. Why don't you debug the problem by picking a row which caused the problem. Break your calculation into individual components and check to see if the component evaluates as expected at the given coordinate.

Calculated Measure - Unique Requirement

Hi Guys,

I have unique Requirement my database has following things:-

Dimensions

Activity (Each Item belongs to Activity)

Item

Measures

Quantity - 2005

Quantity - 2006

Total Invoice Value - 2005

Total Invoice Value - 2006

Calculated Measure

Delta Price (Difference Between 2005 & 2006 Price)

Price - 2005 (Value/Quantity)

Price - 2006

Basically how it happened is Price was calcuated at PartNumber and then at Activity level. My finance Department now wants calculation of Delta Price for Activity from PartNumber i.e.

Quantity - 2005 * Price - 2005 * Delta Price at (Part Number Level) Sum this and Divided by Value - 2005

I am giving following definition in calculated member -

Sum({[Part Number]},[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED])

But what this does is again takes value at Activity level and does the calculation, can somebody help me to achieve this

Regards,

Kaushal

Hi Kaushal. I'd like to help, but Ithink I need more information first. If you can answer a few questions I think we can help.

(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?

(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?

(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.

Thanks.

PGoldy

|||

(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?

Yes PartNumber is part of Activity Dimension

(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?

Yes

(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.

Activity

BT001

Part Number

Quantity

Value

Net Price

Delta Price

A

100

9000

90

0.1

B

120

480

4

0.4

C

140

700

5

0.13

D

50

100

2

0.16

E

70

350

5

0.28

480

10630

22.14583

0.17

Wrong Value

1807.1

10630 * 0.17

Right Value

1297

SUMPRODUCT(C4:C8,E4:E8)

i.e. Value * Delta Price at PartNumber level.

Regards,

Kaushal

|||

Your issue appears to be that you are getting a product of sums, not a sum of the products.

To force the calculation to do the product first and then sum up you would do something like the following:

Sum(
Descendants([Activity].CurrentMember,[Activity].[Part Number])
,[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED]
)

This will effectively get all the relevant part numbers, perform the calculation and then sum up the results of the calculation.

|||

Thanks Darren - I was a little behind following up.

PGoldy

|||

Thanks Darren,

It was great help, can you tell me if Part Number is not a level of Activity can i apply the same formula in someother way.

Regards,

Kaushal

|||

Paul: No worries, I think it is one of the strengths of these forums in that we can all chip in and contribute :)

Kaushal: You could probably apply a similar technique, but maybe not that exact formula if Part Number was on a different dimension. It really depends on how you want the logic to work and how your cube is structured. It could be as simple as replacing the references to the Activity dimension or it could get more complicated. I really don't have enough information to be able to answer that question fully.

If you are using SSAS 2005, you have a couple of other options open to you in addition to doing a straight calculated measure. One approach which would probably be ideal in this situation is to use a Measure Expression, these expressions get evaluated at the fact table grain and then get aggregated up and are very fast.

If your logic gets a bit more complicated another approach in SSAS 2005 could be to use a scoped assignment, this is very similar to a straight calculated measure, but potentially more efficient as you can restrict the subcube over which the calculation is perform.

Calculated field in a dataset

Hi All,

I have a dataset with the following fields

Name Value

ABC 100

DEF 150

GHI 180

Now i need to have a calculated field DIFF which will calculate the difference in the value of the current row and the previous row. The report should have the following fields:

Name Value DIFF

ABC 100 NULL

DEF 150 50

GHI 180 30

Any pointers on how to achieve this requirement will help.

Thanks in advance,

Arun

You can do this with a custom function. Place the following, in the Code section of the Report. Using Report Designer this is on the Code tab of the Report Properties Dialog.

Private m_previousValue As Integer = 0

Public Function ComputeDifference(currentValue as Integer) as Integer

Dim diffValue as Integer = currentValue - m_previousValue
m_previousValue = currentValue

Return diffValue

End Function

The value for the calculated field:

=Code.ComputeDifference(Fields!Sales_Amount.Value)

|||

you can use previous function of the reproting services. these function will give the previous value.

substract the current value from the previous value.

Calculated columns...

Hello,
can anyone help me with this?
SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
The point here is, if I use the following query
SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
the thing works, but for programming-technical reasons a have to use X
SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
What's wrong here?
Regards,
Kurt RogiersWHERE is evaluated before the field list, meaning that when you run
this, there is no column named "X" when the WHERE clause is evaluated.
Something like this will achieve the desired effect:
SELECT X
FROM (SELECT (A+B) AS X FROM TABLE) AS calcX
WHERE X = 'sqdf'
Kurt Rogiers wrote:
> Hello,
> can anyone help me with this?
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> The point here is, if I use the following query
> SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
> the thing works, but for programming-technical reasons a have to use X
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> What's wrong here?
> Regards,
> Kurt Rogiers|||You cannot use column aliases like this. You can only reference a column
alias in an ORDER BY clause, or outside of an inline query.
You can change the way you are coding this, to eliminate the "
programming-technical reasons " that require you to use "X".
You can also try one of these approaches:
select X from
(
SELECT (A+B) AS X FROM TABLE
)
where X = 'sqdf'
Create view MyView as
SELECT (A+B) AS X FROM TABLE
select X from MyView where X='sqdf'
"Kurt Rogiers" <k.rogiers@.skynet.be> wrote in message
news:%23jLUu%23viGHA.4056@.TK2MSFTNGP02.phx.gbl...
> Hello,
> can anyone help me with this?
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> The point here is, if I use the following query
> SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
> the thing works, but for programming-technical reasons a have to use X
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> What's wrong here?
> Regards,
> Kurt Rogiers
>|||Hello,
life can be SOOO easy!!!
Thanks a lot, the solution works wonderful
Regard,
Kurt
"Jim Underwood" <james.underwoodATfallonclinic.com> schreef in bericht
news:O%23wf3IwiGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You cannot use column aliases like this. You can only reference a column
> alias in an ORDER BY clause, or outside of an inline query.
> You can change the way you are coding this, to eliminate the "
> programming-technical reasons " that require you to use "X".
> You can also try one of these approaches:
> select X from
> (
> SELECT (A+B) AS X FROM TABLE
> )
> where X = 'sqdf'
>
> Create view MyView as
> SELECT (A+B) AS X FROM TABLE
> select X from MyView where X='sqdf'
>
> "Kurt Rogiers" <k.rogiers@.skynet.be> wrote in message
> news:%23jLUu%23viGHA.4056@.TK2MSFTNGP02.phx.gbl...
>sql

Calculate Total Column

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

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

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

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

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

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

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

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

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

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

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

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

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

Calculate 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 time lapse

Can anyone help with the following Transact SQL question? Thanks. I
need a store procedure to return the the result recordset which will be
execute from a web page. The database has tables, A and B. For each A
record, there are many related B records. In the B table there is a
timestamp field which tracks the change of A record. For example, A1
has B like the followings:

ID TimeStamp Chg Code Descption
== ========= ======= ========
A1 1138375875 E null //end of the event
A1 1138025002 S resume
A1 1137092615 S don't care
A1 1137092570 S stop
A1 1137092256 I null //start of the
event

I need to generate all records in table A and total elapse time for
each record, but B with Chg Code 'S' that has "don't cacre" to be
deducted from the total time, so that the result will be like this:

ID Name TotalTime
(seconds)
== ==== =======
A1 xyz 351187try this, I haven't tested it:

select A.ID, A.Name, Sum(B.TimeStamp)
from A inner join B on A.ID = B.ID
group by A.ID, A.Name
having (B.ChgCode <> 'S' and B.Description <> 'don''t care')

adi|||js (androidsun@.yahoo.com) writes:
> Can anyone help with the following Transact SQL question? Thanks. I
> need a store procedure to return the the result recordset which will be
> execute from a web page. The database has tables, A and B. For each A
> record, there are many related B records. In the B table there is a
> timestamp field which tracks the change of A record. For example, A1
> has B like the followings:
> ID TimeStamp Chg Code Descption
>== ========= ======= ========
> A1 1138375875 E null //end of the event
> A1 1138025002 S resume
> A1 1137092615 S don't care
> A1 1137092570 S stop
> A1 1137092256 I null //start of the
> event
> I need to generate all records in table A and total elapse time for
> each record, but B with Chg Code 'S' that has "don't cacre" to be
> deducted from the total time, so that the result will be like this:
> ID Name TotalTime
> (seconds)
>== ==== =======
> A1 xyz 351187

It is not clear to how that "don't care" row is to be deducted, since
that is just a point in time. Had you posted CREATE TABLE statements
for the tables, and INSERT statements with the data, it would have been
easy to play around. The below is just a guess, and is untested:

SELECT B.ID, SUM(elapsed)
FROM (SELECT B1.ID, elapsed = B1.TimeStamp - B2.Timestamp,
B2.ChgCode, B2.Description
FROM B B1
JOIN B B2 ON B1.ID = B2.ID
AND B2.TimeStamp = (SELECT MAX(B3.Timestamp)
FROM B B3
WHERE B3.ID = B1.ID
AND B3.TimeStamp <
B1.TimeStamp)
) AS B
WHERE NOT (B.ChgCode = 'S' AND B2.ChgCode = 'don''t care')
GROUP BY B.ID

Here, I'm making the assumption that it is the time from the don't-
care event until the next event that is to be ignored.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply. The TimeStamp column is the actual time the
event occurs not the length of each event, so summing the column will
not give me the actual length of the time from the start to end. The
calculation should be
(time event starts - time envent ends) - (time resume starts - time
resume stops)
thus the result is
(1138375875 - 1137092256) - (1138025002 - 1137092570) = 351187

Any idea? I think I have to use cursor to loop through each result
block and determine if the Description field contains 'stop' or
'resume'. Thanks.|||Thank you. It works with minor modification. Now I would like to use
a trigger so that upon insert the elapsed time will be posted in Table
A column (int) "TimeLapse". However, it would not accept the value.
Can you help?

ALTER TRIGGER [updateA]
ON [dbo].[B]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

Update A
set A.TimeLapse = (SELECT SUM(elapsed)
FROM (SELECT B1.ID, B1.[TimeStamp] -
B2.[TimeStamp] AS elapsed, B2.ChgCode, B2.description
FROM B B1 INNER JOIN
B B2 ON B1.ID = B2.ID AND
B2.[TimeStamp] =
(SELECT
MAX(B3.Timestamp)
FROM B B3
WHERE B3.ID = B1.ID
AND B3.TimeStamp < B1.TimeStamp)
WHERE not (B1.ChgCode = 'S' and
(b1.description
like '%resume%' or b1.description like '%don''t care%')) OR
(b1.description IS
NULL)) B GROUP BY ID)
END|||js (androidsun@.yahoo.com) writes:
> Thank you. It works with minor modification. Now I would like to use
> a trigger so that upon insert the elapsed time will be posted in Table
> A column (int) "TimeLapse". However, it would not accept the value.
> Can you help?

You must correlate the computation of elapsed with a row in A. The
easiest way is to use the proprietary FROM/JOIN syntax supported by
MS SQL Server:

Update A
set TimeLapse = Btot.elapsed
FROM A
JOIN (SELECT B.ID, elapsed = SUM(elapsed)
FROM (SELECT B1.ID, B1.[TimeStamp] - B2.[TimeStamp] AS elapsed,
B2.ChgCode, B2.description
FROM B B1
JOIN B B2 ON B1.ID = B2.ID
AND B2.[TimeStamp] =
(SELECT MAX(B3.Timestamp)
FROM B B3
WHERE B3.ID = B1.ID
AND B3.TimeStamp < B1.TimeStamp)
WHERE not (B1.ChgCode = 'S' and
(b1.description like '%resume%' or
b1.description like '%don''t care%'))
OR (b1.description IS NULL)) B
GROUP BY B.ID) AS Btot = A.ID = B.ID

However, neither this is entierly satisfactory, as you are reading the
entire B table a couple of times on each insert, and this could be
expensive. SQL Server offers the the virtual tables "inserted" and
"deleted" which holds after-image and before-images of the rows
affected by the statement. (For an INSERT, there are only rows in
"inserted" obviously.)

Rewriting the trigger to look at inserted is not trivial, least of all
if rows can be inserted out of order. (What if a "don't care" row is
inserted in the middle of it all?)

Not knowing the exact scenario where this appears I prefer to not suggest
a solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Calculate New 'Unique' Records Each Day

Hi All,
If I have the following table and want to get the below results out,
what would be the best way of approaching this:
StartDate | TelNumber
01/11/05 | 1111
01/11/05 | 2222
01/11/05 | 3333
02/11/05 | 1111
02/11/05 | 4444
03/11/05 | 1111
03/11/05 | 5555
03/11/05 | 1111
03/11/05 | 6666
Each day I want to know the number of Unique TelNumbers across the
whole table each day.
So the answer I am looking for based on the above table, from the query
is:
01/11/05 -> 3 Unique TelNumbers
02/11/05 -> 1 Unique TelNumbers
03/11/05 -> 2 Unique TelNumbers
I've been messing around with Distinct but I can't seem to get it to
look at previous days.
Can anyone help?
Thanking you all in advance.
HPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
SELECT foobar_date, COUNT(DISTINCT tel_nbr)
FROM Foobar
GROUP BY foobar_date;
You might also want to learn about ISO-8601 dates.|||harrys@.gmail.com wrote:

> Each day I want to know the number of Unique TelNumbers across the
> whole table each day.
> So the answer I am looking for based on the above table, from the
> query is:
> 01/11/05 -> 3 Unique TelNumbers
> 02/11/05 -> 1 Unique TelNumbers
> 03/11/05 -> 2 Unique TelNumbers
> I've been messing around with Distinct but I can't seem to get it to
> look at previous days.
> Can anyone help?
DDL would indeed be nice. This is untested but I think it'll work:
select T1.StartDate, count(select distinct TelNumber from Table T2
where T2.StartDate = T1.StartDate and TelNumber not in (select distinct
TelNumber from Table T3 where T3.StartDate < T2.StartDate))
from Table T1
group by T1.StartDate
HTH,
Stijn Verrept.|||Thanks for the replies. As requested, just to be more specific.
I receive a file every day that is set up as below. I have been
requested to find out how many new callers there are each day that have
never called before:
I use BULK INSERT to put the table into SQL.
-- Insert Data Into Table --
BULK INSERT [MasterData]
FROM 'c:\a2.csv'
WITH (FIELDTERMINATOR = ',')
Table:
ID | StartDate | TelNumber |
1 | 01/11/05 09:32:02 | 012321111
2 | 01/11/05 09:35:18 | 012322222
3 | 02/11/05 15:50:32 | 012321111
4 | 02/11/05 19:10:46 | 012323333
5 | 03/11/05 11:10:11 | 012324444
6 | 03/11/05 17:46:25 | 012322222
7 | 03/11/05 06:34:42 | 012328888
So i have to work out how many new callers there are each day that have
never called in before. So the results from the above would be:
01/11/05 - 2 New Callers
02/11/05 - 1 New Callers
03/11/05 - 3 New Callers
- - - - - - - - - - - -
Celko- Your query gives me distinct callers each day, but it doesnt
take into account if they have already called on the previous day.
Stijn - I tried working/amending your query but just couldnt get it to
work. (Apologies if it's me being stupid!) Could you expand on how you
think that may solve my dilema?
This query will need to be run everyday!
Thanks for your assistance.
H|||Hi There,
See if this can help you.
Select T1.StartDate,(
Select Count(Distinct T2.TElNumber) From tmpData1 T2 Where
T2.StartDate =T1.StartDate
And T2.TelNumber Not In ( Select T3.TElNumber From tmpData1 T3 Where
T3.StartDate<T2.StartDate)
)
>From tmpData1 T1 Group By T1.StartDate
Here tmpData1 is the table Name
With Warm regards
Jatinder Singh|||Hi,
No joy!
I tried the following:
SELECT DATEPART(dd,T1.StartDate),(
SELECT Count(Distinct T2.CLI) From MasterData T2 Where
T2.StartDate =T1.StartDate
And T2.CLI Not In ( Select T3.CLI From MasterData T3 Where
T3.StartDate<T2.StartDate)
)
FROM MasterData T1 GROUP BY DATEPART(dd,T1.StartDate)
But I just get a list of zeros!
Exscuse my ignorance, but where can I found out more about this T1 T2
business? Had a look on google, but obviously I dont know what to
search on at the moment!
Thanks
H|||Just to add to this:
If I run the following:
SELECT convert(varchar(10), StartDate, 101) AS DayCol, COUNT(DISTINCT
CLI) AS CountCLI
FROM MasterData
GROUP BY convert(varchar(10), StartDate, 101)
ORDER BY convert(varchar(10), StartDate, 101)
It gives me the nuimber of unique telephone numbers EACH DAY.
Is there no way to creat a sub-query to calculate the number of unique
telephone numbers each day since the beggining?|||harrys@.gmail.com wrote:

> It gives me the nuimber of unique telephone numbers EACH DAY.
> Is there no way to creat a sub-query to calculate the number of unique
> telephone numbers each day since the beggining?
Well that is what my query should do LOL. The T1, T2, ... are just
aliases. If you can provide some DLL (create table, insert into table,
...) of the table and sample data you use, we can easily try out the
code and correct it if wrong. See this post for an example of good
DDL:
http://groups.google.com/group/micr...programming/br
owse_thread/thread/5d4fc158de977f3f
Kind regards,
Stijn Verrept.|||Stign,
Thanks for the reference link. I see what you mean now. And ackowledged
for future reference!
So on that basis, below are the details:
- - - - - TABLE
CREATE TABLE [dbo].[MasterData](
[CDRRef] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CLI] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[AccessNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[TransNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[DurSec] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
- - - - - INSERT DATA
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(AFE5468ASD,0122233344,0777000000,077700
0000,10/01/05
09:00:02,10/01/05 09:05:05,5)
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(ADF8912GGE,0122233355,0777000000,077700
0000,10/01/05
10:18:05,10/01/05 10:18:32,27)
So on and so forth. . . .
Stign - I did try your query; Amended as follows:
SELECT T1.StartDate,
COUNT(SELECT DISTINCT CLI FROM MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from MasterData T3 where T3.StartDate < T2.StartDate))
from MasterData T1
group by T1.StartDate
But even after playing around with it, I receive the error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Hope the additional info helps someone help me solve this one!!
Thanks
H|||harrys@.gmail.com wrote:

> Hope the additional info helps someone help me solve this one!!
Indeed much better, only the data isn't correct. You can use this
great freeware program to generate some data that can be inserted:
http://www.rac4sql.net/objectscriptr_main.asp
Try this query:
SELECT T1.StartDate,
(SELECT COUNT(DISTINCT CLI) FROM #MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from #MasterData T3 where T3.StartDate < T2.StartDate)) as UniqueCLI
from #MasterData T1
group by T1.StartDate
It doesn't give any error.
Kind regards,
Stijn Verrept.

Calculate median of difference in days between records

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB1. you can start by reading about datediff.

2. median. that is the value in the physical middle, right?. I would create a temp tale using the IDENTITY function and a order by on the column in question and then do the math on the new identity value.|||2. Create a query. Cross join table to itself. Deduct your field from itself (T1.Field1 - T2.Field2). Apply the Sign function to the result and sum it. The median is the one that comes up 0. If there are an even numbers of values then there will be two medians (1 and -1) so you need to decide if you want the higher or lower one.

DATEDIFF will help you with the above too.

HTH

EDIT - the above allows you to use Groupings so you can get medians by group too.|||Is there any chance I could get an example in sql code of what you are describing... IM a little confused...|||Well, first, do you mean the difference in days between all samples, or just the difference between a sample and the immediately preceding sample? One, requires a cross-join, and the other does not, so the devil is in the details.|||EDIT - the above allows you to use Groupings so you can get medians by group too.Oh yeah - but that would require a join by the grouping column(s). Forgot that little detail :o|||here is some sample data:

Sample_date sample_no
4/29/1962 0:00 1962706086
5/1/1962 0:00 1962704968
5/3/1962 0:00 1962704891
5/4/1962 0:00 1962722248
5/15/1962 0:00 1962706641
5/18/1962 0:00 1962722249
5/18/1962 0:00 1962704969
5/22/1962 0:00 1962706344
6/5/1962 0:00 1962722690
6/7/1962 0:00 1962722250
6/8/1962 0:00 1962708068
6/9/1962 0:00 1962722842
6/12/1962 0:00 1962708069
6/12/1962 0:00 1962722928
6/13/1962 0:00 1962722691
6/13/1962 0:00 1962706345
6/13/1962 0:00 1962706427
6/16/1962 0:00 1962706561
6/16/1962 0:00 1962704970
6/16/1962 0:00 1962704971
6/16/1962 0:00 1962705794
6/17/1962 0:00 1962708070
6/17/1962 0:00 1962708071
6/17/1962 0:00 1962722929
6/17/1962 0:00 1962722930
6/18/1962 0:00 1962722692
6/18/1962 0:00 1962722693
6/18/1962 0:00 1962722694
6/18/1962 0:00 1962722695
6/18/1962 0:00 1962722696
6/18/1962 0:00 1962722697
6/18/1962 0:00 1962722698
6/18/1962 0:00 1962722843
6/23/1962 0:00 1962706562
6/23/1962 0:00 1962722251
6/23/1962 0:00 1962722252
6/29/1962 0:00 1962704892
6/29/1962 0:00 1962704893
7/1/1962 0:00 1962722931
7/2/1962 0:00 1962708072
7/2/1962 0:00 1962722844
7/4/1962 0:00 1962708073
7/6/1962 0:00 1962722932
7/7/1962 0:00 1962705881
7/7/1962 0:00 1962722253
7/11/1962 0:00 1962722933
7/15/1962 0:00 1962722254
7/18/1962 0:00 1962708074
7/18/1962 0:00 1962708075
7/18/1962 0:00 1962722934
7/22/1962 0:00 1962722255
7/22/1962 0:00 1962704894
7/25/1962 0:00 1962722935
7/27/1962 0:00 1962708076
7/27/1962 0:00 1962708077
7/28/1962 0:00 1962704895
7/28/1962 0:00 1962722256
8/1/1962 0:00 1962708078
8/1/1962 0:00 1962708079
8/1/1962 0:00 1962722699
8/3/1962 0:00 1962722936
8/3/1962 0:00 1962722937

When you look at the first 2 rows:
4/29/1962 0:00 1962706086
5/1/1962 0:00 1962704968

you see there is a difference of 3 Days.

When you look at row 2 and 3
5/1/1962 0:00 1962704968
5/3/1962 0:00 1962704891

you see there is a difference of 2 Days.

This difference can be from 1 - infinate days.

calculate all the differences between all the consecutive days in this pattern then get the median value (the number in the MIDDLE of the set of given numbers)
ie if you have 9 values
1. 12
2. 25
3. 44
4. 55
5. 89
6. 122
7. 155
8. 325
9. 8000

the median is 89. I dont want to have to calculate median manually like this, its just way to much processing although im not sure if its built into sql server.

so for each station there should be a single value comming out and:
if num between 1 - 7 then "Monthly"
if num between 7 - 14 then "Bi-Weekly"
if num between 14 - 30 then "Monthly"
if num between 30 - 360 then "Yearly"

... or something similar

To make matters worse this is only a small fraction of a larger "Station Level" query that will be including this query somehow. Groups of samples belong to different "Stations" which I have to seperate by. For example, all the data you see here is for 1 station. There are about 300 stations being returned in the higher level view that this query will belong to. For kicks I will include it:

you can see that Sampling_Frequency is there as "UNKNOWN" cause i dont know how to do it. also if anyone wants to suggest how i optimize this query feel free to add suggestions:

SELECT DISTINCT stat.station_number AS Site_ID, stat.station_number AS Site_Number, stat.station_name AS Site_Name, stat.station_description AS Site_Description,
stat.creation_date AS Site_Activation_Date, dbo.station_coordinates.lat_dec_deg AS Latitude,
dbo.station_coordinates.lat_dec_deg AS North_bounding_Latitude, dbo.station_coordinates.lat_dec_deg AS South_bounding_Latitude,
dbo.station_coordinates.lon_dec_deg AS Longitude, dbo.station_coordinates.lon_dec_deg AS West_bounding_Longitude,
dbo.station_coordinates.lon_dec_deg AS East_bounding_Longitude, 'N/A' AS Site_Photograph_link, 'N/A' AS Local_site_influences_link,
'http://map.ns.ec.gc.ca/NA' AS Collection_level_metadata_link, 'http://map.ns.ec.gc.ca/envirodat/' AS Other_info_link,
'Environment Canada, Atlantic Region, Environmental Conservation Branch' AS Organization_name, '5068512398' AS Contact_Phone_number,
'5068516608' AS Contact_Fax_number, 'PO BOX 23005' AS Contact_street_address, 'Moncton' AS Contact_city, 'NB' AS Contact_province,
'E1A 6S8' AS Contact_postal_code, 'cathy.cormier@.ec.gc.ca' AS Contact_e_mail, 'http://map.ec.gc.ca/envirodat' AS Organization_web_site,
'0800-1600' AS Contact_hours_of_service, 'N/A' AS Contact_other_instructions, dbo.projects.project_no, dbo.projects.project_name,

(SELECT DISTINCT wshed
FROM envguest.stations_watersheds
WHERE station = stat.station_number) AS watershed,


case active_indicator WHEN 'Y' Then NULL else (select max(sample_date) from samples where station_no = stat.Station_number) END as Site_Final_Sampling_Date,

CASE active_indicator WHEN 'Y' THEN 'Active' ELSE 'Inactive' END AS Current_Status,

'UNKNOWN' as Jurisdiction, 'UNKNOWN' as Sampling_Frequency


FROM dbo.station_coordinates INNER JOIN
dbo.station stat ON dbo.station_coordinates.station_number = stat.station_number INNER JOIN
dbo.samples ON stat.station_number = dbo.samples.station_no INNER JOIN
dbo.projects ON dbo.samples.project_no = dbo.projects.project_no|||Hi

A few quickies:

Seen this posted a few times and now it is my turn (ahem):

Order has no meaning in a relational database.

This means that the apparent order of any rows in a table is arbitary - they are in fact descrete elements.

I presume that you want to order the dates in ascending order and get the difference in days between successive dates?

Also (not being picky - just to understand):
calculate all the differences between all the consecutive days in this pattern then get the median value (the number in the MIDDLE of the set of given numbers)
ie if you have 9 values
1. 12
2. 25
3. 44
4. 55
5. 89
6. 122
7. 155
8. 325
9. 8000
If there were nine dates there should only be eight numbers returned right?
Finally - I notice the above return is ordered ascending. Is this a requirement? You aren't looking for the difference in days between the two median dates but the median of the differences?

BTW - are you a surfing French man?|||Lost the server for a bit so had a play. Below is based on assumption that my assumptions were correct :D

Decided not to use Thrasys Identity column - however you would be forgiven for thinking I have coded this with obsucation in mind. Not true - I just wanted to create a single query solution

IF EXISTS(SELECT * FROM sysobjects O WHERE O.name = 'MedianT' AND O.xtype = 'U') BEGIN
DROP TABLE MedianT
End

CREATE TABLE MedianT (TheDate DATETIME)

INSERT INTO MedianT (TheDate)
SELECT '4/29/1962' UNION
SELECT '5/1/1962' UNION
SELECT '5/3/1962' UNION
SELECT '5/4/1962' UNION
SELECT '5/8/1962' UNION
SELECT '5/10/1962' UNION
SELECT '5/13/1962' UNION
SELECT '5/14/1962' UNION
SELECT '5/29/1962' UNION
SELECT '5/29/1963' UNION
SELECT '5/29/1967' UNION
SELECT '5/29/1969' UNION
SELECT '5/29/1974' UNION
SELECT '5/29/1980' UNION
SELECT '5/29/2000'

SELECT Da.TheDifference AS TheMedian
FROM (SELECT DISTINCT TheDifference FROM (SELECT DATEDIFF(D, A.TheDate, MIN(B.TheDate)) AS TheDifference
FROM MedianT A, MedianT B
WHERE A.TheDate < B.TheDate
GROUP BY A.TheDate) Daa) Da,
(SELECT DATEDIFF(D, A.TheDate, MIN(B.TheDate)) AS TheDifference
FROM MedianT A, MedianT B
WHERE A.TheDate < B.TheDate
GROUP BY A.TheDate) Db
GROUP BY Da.TheDifference
HAVING sum(SIGN(Da.TheDifference - Db.TheDifference)) IN (0, -1)|||BTW - the IN(0, -1) bit means:
If there are an odd number of vlaues then there is a natural median -> the result being 0. If there are an even number of values then there are two candidates for median. The -1 selects the lower of the two. Change to 1 if you want the higher.|||That looks really good. Hopefully it will work on a dataset of 10,000's of records. Havent had a chance to implement it yet but will today.
BTW. Im not french, but a decendant of French west coast Newfoundlanders who jumped ship from france in the 1800's and I dont surf. hah

Calculate Difference?

I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:

Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

RobTry something like:

select a.truck, a.reading - b.reading
from (select truck, reading from table where period = '2/28/03') b
inner join table a
on a.truck = b.truck
where a.period = '3/31/2003'|||Thank you for the quick response rnealejr, but this won't work for a table with 4 years of data. I am trying to do the following, but it is not quite right

select [Month] = t.period,
[Value] = t.reading - ISNULL(t2.reading, 0)
from mytable t
left join (select DATEADD(m,-1,period) X, reading from mytable) AS t2 ON t2.X = t.period

I think the answer is to create a udf that subtracts 1 from the day until the month changes, so I always get the last day of the month. Does this sound like the correct solution?

Rob|||select a.truck, a.period as enddate, a.milage - b.milage as milesrun
from test1 a join test1 b on a.truck = b.truck
where a.period = dateadd (mm, 1, b.period)
or b.period = dateadd (mm, -1, a.period)

I think you just missed the join on truckID. Also, since february 28 + 1 month is March 28, I decided to try it both ways (up and down). Hope this helps.|||OK - I finally got it. I ended up doing the following;

I first created a udf that looked like this

ALTER function PMonth(@.dt DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.ret DATETIME
SET @.ret = @.dt
WHILE DATEPART(m, @.dt) = DATEPART(m, @.ret)
SET @.ret = DATEADD(d,-1,@.ret)
RETURN @.ret
END

Then I wrote my sp like this:

SELECT [Truck] = m.truck,
[Period] = m.period,
[Value] = m.reading - ISNULL(m2.reading, 0)
FROM mytable m
LEFT JOIN (SELECT period, truck, reading FROM mytable) AS m2 ON m2.period = dbo.PMonth(m.period) AND m2.truck = m.truck

I am betting its not the most efficient solution (it takes 4 seconds), so if anyone has suggestions, please let me know.

Thanks,

Rob|||select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman|||Originally posted by blindman
select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman

Wow - this works much faster. Thank you very much blindman.

Rob|||Make sure you only have one entry per truck per month!

calc totals for months question

I have the following records:
Unit Create_date Type
A 01/02/2004 Clock
A 01/15/2004 Car
A 01/20/2004 Truck
A 01/23/2004 Clock
A 01/24/2004 Clock
A 01/25/2004 Car
A 01/27/2004 Truck
A 02/01/2004 Clock
A 02/02/2004 Car
A 02/15/2004 Truck
A 02/20/2004 Car
A 02/27/2004 Car
A 06/03/2004 Truck
A 06/04/2004 Truck
A 06/15/2004 Clock
A 06/29/2004 Car
Report Output example
Unit Created Type
A Jan 04 Clock
Type Count = 3
A Jan 04 Car
Type Count = 2
A Jan 04 Truck
Type Count = 2
Total for January = 7
A Feb 04 Clock
Type Count = 1
A Feb 04 Car
Type Count = 3
A Feb 04 Truck
Type Count = 1
Total for February = 5
A June 04 Clock
Type Count = 1
A June 04 Car
Type Count = 1
A June 04 Truck
Type Count = 2
Total for June = 4
I am trying to determine how to count the totals for the months.
Any ideas?
Thanks.Just use a table, and add a table grouping "MonthGrouping" like e.g.:
=Year(Fields!TimeStamp.Value)*100 + Month(Fields!TimeStamp.Value)
Then add a second (inner) table grouping "TypeGrouping" which groups on the
type, e.g.: =Fields!Type.Value
These two groupings should give you the basic structure of the desired
output. Finally, to determine the counts for the months, just add an
expression like this to the "MonthGrouping" groop footer:
=CountRows("MonthGrouping")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:83250B36-B1BC-4601-9002-87EDE106838E@.microsoft.com...
>I have the following records:
> Unit Create_date Type
> A 01/02/2004 Clock
> A 01/15/2004 Car
> A 01/20/2004 Truck
> A 01/23/2004 Clock
> A 01/24/2004 Clock
> A 01/25/2004 Car
> A 01/27/2004 Truck
> A 02/01/2004 Clock
> A 02/02/2004 Car
> A 02/15/2004 Truck
> A 02/20/2004 Car
> A 02/27/2004 Car
> A 06/03/2004 Truck
> A 06/04/2004 Truck
> A 06/15/2004 Clock
> A 06/29/2004 Car
> Report Output example
> Unit Created Type
> A Jan 04 Clock
> Type Count = 3
> A Jan 04 Car
> Type Count = 2
> A Jan 04 Truck
> Type Count = 2
> Total for January = 7
> A Feb 04 Clock
> Type Count = 1
> A Feb 04 Car
> Type Count = 3
> A Feb 04 Truck
> Type Count = 1
> Total for February = 5
> A June 04 Clock
> Type Count = 1
> A June 04 Car
> Type Count = 1
> A June 04 Truck
> Type Count = 2
> Total for June = 4
> I am trying to determine how to count the totals for the months.
> Any ideas?
>
> Thanks.
>

caching questions

I am new to Reporting Services and would like to know if it can do the
following:
1. If I cache a report with timed expiration such as 48 hours, can I
programatically expire a specific report before it would have expired
normally?
2. When a report is generated from cache, is it possible for it to get any
"superficial" customizations at render time, such as colors or a gif?
Thanks for any help!Hi,
> 1. If I cache a report with timed expiration such as 48 hours, can I
> programatically expire a specific report before it would have expired
> normally?
We do not have straightforward method available. To do so, you could use
SetCacheOptions Method set the CacheReport false and then reset the
expiration time.
> 2. When a report is generated from cache, is it possible for it to get
any
> "superficial" customizations at render time, such as colors or a gif?
No, it's not possible
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Caching of reports

Hi!
I am using the report service to generate all of my reports. The reports are
generated as pdf and then printed.
The following situaton:
- The user fills out a windows form and previews the report. On the preview
he recognised that there is a mistake in the text he filled out and closes
the preview.
- The user corrects the mistake and previews the report again (same report,
same user, same parameters)
- In the preview the report has not changed !!!!
I have set all the reports not to cache (store no temporary copies of the
report).
When I use SQL Profiler to analyze whats going on on the sql server the
following messages appear:
"exec GetChunkPointerAndLength @.SnapshotDataID = 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
= N'CompiledDefinition', @.ChunkType = 0"
and after that a few calls of:
"exec ReadChunkPortion"
Definitly the stored procedure which provides the values for the report is
not called.
In the scenario shown above the stored procedure must be called everytime
the report is rendered.
How can I configure report services to do so?
Thanks.I am calling the "FlushCache" method before rendering the pdf-file which does
not change the behavior listed below.
"Thomas Vierlinger" schrieb:
> Hi!
> I am using the report service to generate all of my reports. The reports are
> generated as pdf and then printed.
> The following situaton:
> - The user fills out a windows form and previews the report. On the preview
> he recognised that there is a mistake in the text he filled out and closes
> the preview.
> - The user corrects the mistake and previews the report again (same report,
> same user, same parameters)
> - In the preview the report has not changed !!!!
>
> I have set all the reports not to cache (store no temporary copies of the
> report).
> When I use SQL Profiler to analyze whats going on on the sql server the
> following messages appear:
> "exec GetChunkPointerAndLength @.SnapshotDataID => 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> = N'CompiledDefinition', @.ChunkType = 0"
> and after that a few calls of:
> "exec ReadChunkPortion"
> Definitly the stored procedure which provides the values for the report is
> not called.
> In the scenario shown above the stored procedure must be called everytime
> the report is rendered.
> How can I configure report services to do so?
> Thanks.
>
>|||Try passing the following parameter...
to the report...
rs:ClearSession=true
"Thomas Vierlinger" wrote:
> I am calling the "FlushCache" method before rendering the pdf-file which does
> not change the behavior listed below.
> "Thomas Vierlinger" schrieb:
> > Hi!
> >
> > I am using the report service to generate all of my reports. The reports are
> > generated as pdf and then printed.
> >
> > The following situaton:
> > - The user fills out a windows form and previews the report. On the preview
> > he recognised that there is a mistake in the text he filled out and closes
> > the preview.
> > - The user corrects the mistake and previews the report again (same report,
> > same user, same parameters)
> > - In the preview the report has not changed !!!!
> >
> >
> > I have set all the reports not to cache (store no temporary copies of the
> > report).
> > When I use SQL Profiler to analyze whats going on on the sql server the
> > following messages appear:
> >
> > "exec GetChunkPointerAndLength @.SnapshotDataID => > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > = N'CompiledDefinition', @.ChunkType = 0"
> >
> > and after that a few calls of:
> >
> > "exec ReadChunkPortion"
> >
> > Definitly the stored procedure which provides the values for the report is
> > not called.
> > In the scenario shown above the stored procedure must be called everytime
> > the report is rendered.
> >
> > How can I configure report services to do so?
> >
> > Thanks.
> >
> >
> >|||As described below I am not rendering with url access.
I am rendering pdf files direkt from C# code using the webservice render
method (ReportingService.Render).
"CCP" schrieb:
> Try passing the following parameter...
> to the report...
> rs:ClearSession=true
>
> "Thomas Vierlinger" wrote:
> > I am calling the "FlushCache" method before rendering the pdf-file which does
> > not change the behavior listed below.
> >
> > "Thomas Vierlinger" schrieb:
> >
> > > Hi!
> > >
> > > I am using the report service to generate all of my reports. The reports are
> > > generated as pdf and then printed.
> > >
> > > The following situaton:
> > > - The user fills out a windows form and previews the report. On the preview
> > > he recognised that there is a mistake in the text he filled out and closes
> > > the preview.
> > > - The user corrects the mistake and previews the report again (same report,
> > > same user, same parameters)
> > > - In the preview the report has not changed !!!!
> > >
> > >
> > > I have set all the reports not to cache (store no temporary copies of the
> > > report).
> > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > following messages appear:
> > >
> > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > = N'CompiledDefinition', @.ChunkType = 0"
> > >
> > > and after that a few calls of:
> > >
> > > "exec ReadChunkPortion"
> > >
> > > Definitly the stored procedure which provides the values for the report is
> > > not called.
> > > In the scenario shown above the stored procedure must be called everytime
> > > the report is rendered.
> > >
> > > How can I configure report services to do so?
> > >
> > > Thanks.
> > >
> > >
> > >|||i think u can set that value as a parameter in your report...
"Thomas Vierlinger" wrote:
> As described below I am not rendering with url access.
> I am rendering pdf files direkt from C# code using the webservice render
> method (ReportingService.Render).
>
> "CCP" schrieb:
> > Try passing the following parameter...
> > to the report...
> > rs:ClearSession=true
> >
> >
> > "Thomas Vierlinger" wrote:
> >
> > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > not change the behavior listed below.
> > >
> > > "Thomas Vierlinger" schrieb:
> > >
> > > > Hi!
> > > >
> > > > I am using the report service to generate all of my reports. The reports are
> > > > generated as pdf and then printed.
> > > >
> > > > The following situaton:
> > > > - The user fills out a windows form and previews the report. On the preview
> > > > he recognised that there is a mistake in the text he filled out and closes
> > > > the preview.
> > > > - The user corrects the mistake and previews the report again (same report,
> > > > same user, same parameters)
> > > > - In the preview the report has not changed !!!!
> > > >
> > > >
> > > > I have set all the reports not to cache (store no temporary copies of the
> > > > report).
> > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > following messages appear:
> > > >
> > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > >
> > > > and after that a few calls of:
> > > >
> > > > "exec ReadChunkPortion"
> > > >
> > > > Definitly the stored procedure which provides the values for the report is
> > > > not called.
> > > > In the scenario shown above the stored procedure must be called everytime
> > > > the report is rendered.
> > > >
> > > > How can I configure report services to do so?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >|||and how should that work?
bol says it is an url parameter
"CCP" schrieb:
> i think u can set that value as a parameter in your report...
> "Thomas Vierlinger" wrote:
> > As described below I am not rendering with url access.
> >
> > I am rendering pdf files direkt from C# code using the webservice render
> > method (ReportingService.Render).
> >
> >
> > "CCP" schrieb:
> >
> > > Try passing the following parameter...
> > > to the report...
> > > rs:ClearSession=true
> > >
> > >
> > > "Thomas Vierlinger" wrote:
> > >
> > > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > > not change the behavior listed below.
> > > >
> > > > "Thomas Vierlinger" schrieb:
> > > >
> > > > > Hi!
> > > > >
> > > > > I am using the report service to generate all of my reports. The reports are
> > > > > generated as pdf and then printed.
> > > > >
> > > > > The following situaton:
> > > > > - The user fills out a windows form and previews the report. On the preview
> > > > > he recognised that there is a mistake in the text he filled out and closes
> > > > > the preview.
> > > > > - The user corrects the mistake and previews the report again (same report,
> > > > > same user, same parameters)
> > > > > - In the preview the report has not changed !!!!
> > > > >
> > > > >
> > > > > I have set all the reports not to cache (store no temporary copies of the
> > > > > report).
> > > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > > following messages appear:
> > > > >
> > > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > > >
> > > > > and after that a few calls of:
> > > > >
> > > > > "exec ReadChunkPortion"
> > > > >
> > > > > Definitly the stored procedure which provides the values for the report is
> > > > > not called.
> > > > > In the scenario shown above the stored procedure must be called everytime
> > > > > the report is rendered.
> > > > >
> > > > > How can I configure report services to do so?
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >