Calculated field in a Cube

Hi!

I need your help!..

I need aid to obtain a value in a calculated field, I am using cube with dimension in the date.

The idea is that as of a specific month the accumulated sum of a value is obtained in a calculated field, starting off from the month of January of he himself year that the specified month previously.

For example:

If I indicate the month of April, the sum of the value had to be:

Total = Value January + Value February + Value March + Abril Value

The previous months are of he himself year that the month of April and does not have to take values from the previous year.

If it indicated the month of December it must add the 12 months of the year to which that month corresponds I hope somebody can help me.

From already, thank you very much!

Assuming that your date dimension has its levels tagged with the appropriate time types, then you could simply use YTD(), like:

Sum(YTD([Date].CurrentMember), [Measures].[MyValue])

If the date levels aren't appropriately tagged, PeriodsToDate() is a more general function, like:

Sum(PeriodsToDate([Date].[Year].[Year], [Date].CurrentMember), [Measures].[MyValue])

Calculated Dimension Member destoys date-hierarchy structure

Hi, I have a problem if I add a Calculated Dimension Member to a Date Hierarchy,

first I thought - I would set attribute relationship of my date-dimension not properly,

but then I tried to apply this to AW-DataBase.

Here's a script of creating Calculated Member in AW (Date.Calendar):

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[All Periods].[DateCalcMember_CopyOf2004]
AS [Date].[Calendar].[Calendar Year].&[2004],
VISIBLE = 1 ;

After adding it, try to expand the years-nodes in browser: you won't find any semesters behind,

except CY2001 - he got all semesters of other as childs.

It is possible to configure this behaviour somewhere or it is a bug of OWC 11 PivotTable that is used

in Cube Browser?

Thank you in advanced for your answers.

Regards, Mastroyani

Hi Mastroyani. Your problem definition is not 100% clear, so I made an assumption. The assumption is the problem definition which is, "after adding the calculated member and browsing the dimension in the cube browser the only member which shows the half-year members ([H1 CY 2002], [H2 CY 2002], etc.) is the [CY 2001] member." You can isolate where the problem lies by using another tool such as the SQL Server 2005 Manager to issue a separate MDX query outside onf the Cube Browser. Below is an MDX query using your same definition for [DateCalcMember_CopyOf2004]. You can see from the results that the member [CY 2004] shows it's own half-year children. Perhaps you are seeing an error with the cube browser?

Hope this helps - Paul Goldy

WITH MEMBER [Date].[Calendar].[All Periods].[DateCalcMember_CopyOf2004]
AS '[Date].[Calendar].[Calendar Year].&[2004]'

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,{[Date].[Calendar].[Calendar Year].&[2004]
,[Date].[Calendar].[Calendar Year].&[2004].Children
,[Date].[Calendar].[All Periods].[DateCalcMember_CopyOf2004]} ON ROWS
FROM [Adventure Works]

===== Results

Internet Sales Amount
CY 2004 $9,770,899.74
H1 CY 2004 $9,720,059.11
H2 CY 2004 $50,840.63
DateCalcMember_CopyOf2004 $9,770,899.74

|||Hi, I have exactly the same problem. Have anybody figured out the problem?

thanks
Hank

calculate weekendings for a date range

All,
I have stored procedure that accepts 2 parameters (StartDate and EndDate). I need a way to find out which weekending dates fall between the two parameters .

For example :

StartDate: 02-jan-2006
EndDate: 19-jan-2006

Therefore the weekending dates would be 8-Jan 2006 and 15-jan-2006

Can anyone help

Cheers

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2519

HTH, Jens Suessmeyer.

|||

You can compute the last "end of week" based on the day-of-week DatePart() value. From this, you can also compute the next "end of week". This will all be consistent with the @.@.DateFirst parameter setting the first day of the week -- hence moving the actual day associated with the "end of week".

To find the last "end of week", subtract days equal to 1 less than the DatePart() value. To find the next "end of week", do as above, but add 7 days modulo 7 -- which won't change the date if the end of week is originally supplied.

Declare @.date1 datetime,
@.date2 datetime

Set @.date1 = '1/2/2006'
Set @.date2 = '1/19/2006'

Select next_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date1 ) - 1 ) + 7 ) % 7, @.date1 ),
last_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date2 ) - 1 ) ) , @.date2 )

The above will return the results you are expecting. You could place these into functions for better readability.

|||

Relying on the setting of @.@.DATEFIRST can be a problem, the best thing would be that @.@.DATEFIRST could be set to any day, bit still get the same result...

It's practical with a day-table containing each date, much like a numbers-table.

create table dbo.days (d datetime not null)
go
set nocount on
declare @.d datetime
set @.d = '20060101'
while @.d < '20070101'
begin
insert dbo.days select @.d
set @.d = dateadd(day, 1, @.d)
end
set nocount off
go

To get any given weekday from a date, regardless of @.@.DATEFIRST setting, we can use the following formula:
(( @.@.datefirst + datepart(weekday, @.mydate) -2 ) %7 + 1 )

Monday is 1, Tuesday 2 etc until Sunday that is 7.

So, if we want to know which dates that are Sundays between January 2nd 2006 and January 19th 2006, we can ask our date-table. (and not bother with what @.@.DATEFIRST may be set to)

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

The last '7' is the weekday (Sunday) we're looking for.

You can play with this example and see that nothing happens when @.@.datefirst changes.

set datefirst 3
select @.@.datefirst as 'datefirstSetTo'

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d as 'mySundays'
from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

datefirstSetTo
--
3

(1 row(s) affected)

mySundays
2006-01-08 00:00:00.000
2006-01-15 00:00:00.000

(2 row(s) affected)

..hope it's of some help.

=;o)
/Kenneth

|||

Best is to use a calendar table that contains all the dates and query it to get the details. This approach gives maximum flexibility, maintainability and performance in lot of cases also. It is quite common to do this in data warehouses to simplify these type of queries. For example, you can build a Calendar table like:

create table Calendar (

dayid int not null primary key,

dt smalldatetime not null,

....

IsSaturday bit not null,

IsSunday bit not null

... other columns that can contain holidays or special days etc

)

Now your query becomes:

select count(*)

from Calendar

where dt between '20060102' and '20060119'

and 1 in ( IsSaturday, IsSunday )

You can answer more complex questions also using the calendar table which will be harder to do by writing UDFs or special procedural logic.

Calculate Weekending date.

Can someone help me with this. I need to calculate the week ending
date of the first week of the year based upon a year provided by the
user. Is there a simpler way other than writing my own UDF?"David" <david.paskiet@.t-mobile.com> wrote in message
news:9abbaed9.0411160734.1d9c021f@.posting.google.c om...
> Can someone help me with this. I need to calculate the week ending
> date of the first week of the year based upon a year provided by the
> user. Is there a simpler way other than writing my own UDF?

That depends on what week ending date and first week of the year mean.

If 1 Jan is on Wednesday do you want to come back with 4 Jan, 5 Jan, 11 Jan
or 12 Jan?

Anyway, Generally, the last day of a week is Date +7 - Day(Date) where Day()
returns the number of the day in the week (1=Sun, 2=Mon, 3=Tue,
etc...Assuming the week starts on Sunday)

Therefore DateAdd(d,-DatePart(dw,datefield),DateAdd(d,7,datefield))

However, if the datefield contains a time you may want to change the time if
you are using this in a comparison.

Regards,
Jim

Calculate week from date

Hi,

I want to convert a date to a weeknumber in my view.
How is this possible with SQL?

select datepart(wk,getdate())

hth|||Thanx!

Works fine for me!

Calculate Quarter

Hi,

I need to retrieve both Mont and Quarter from an MS SQL server field containing a date as ''01/01/2007 12:10:00'.

Retrieving the Month is working properly, but getting some sql error with the Quarter function.

I will appreciate if someone can indicate me how should I do in order to retrieve the Quarter from a date.

To retrieve the month, I am using:

& "MONTH(STOCK.VALUEDATE) AS 'Month'"

To retrieve the Quarter, I am trying (but getting error):

& "QUARTER(STOCK.VALUEDATE) AS Quarter"
Thanks,

Aldo.

I already got the answer...

& "STOCK.VALUEDATE AS 'Original Date', " _
& "DATEPART(month, STOCK.VALUEDATE) AS 'Month', " _
& "DATEPART(quarter, STOCK.VALUEDATE) AS 'Quarter', " _
& "DATEPART(year, STOCK.VALUEDATE) AS 'Year', " _
Thanks anyway,

Aldo.

|||Aldo,
Please use the Transact-SQL forum for these kinds of questions. This has nothing to do with SSIS.

Thanks,
Phil

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 date difference exclusding weekend

I want to calculate the day difference between 2 dates excluding wend.
I notice that DateDiff function, but it does not support option to excluding
wend.
Any information is great appreciated,
Souris,http://www.google.co.uk/groups?selm...br />
oups.com
David Portas
SQL Server MVP
--|||Look up the use of a calendar table for this kind of problem. This
way, you will get all the holidays.|||http://www.aspfaq.com/2519
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"souris" <soukkris@.viddotron.com> wrote in message
news:e2w1dwKLFHA.2136@.TK2MSFTNGP14.phx.gbl...
> I want to calculate the day difference between 2 dates excluding wend.
> I notice that DateDiff function, but it does not support option to
excluding
> wend.
> Any information is great appreciated,
> Souris,
>
>|||Thanks for the information,
I got it works.
It can calculate the date difference between 2 days in the table.
Is it possible to calculate the date difference in other table?
For example:
I have a table
Account_Number Opened_Date
1 03/10/2005
2 03/15/2005
Today is 03/30/2005.
I wanted calculate the days difference between today and Opened_date
excluding wend.
I tried using the table, but not lucky.
Any information is great appreciated,
Souris,
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111255234.753472.313190@.l41g2000cwc.googlegroups.com...
> http://www.google.co.uk/groups?selm... />
groups.com
> --
> David Portas
> SQL Server MVP
> --
>|||On Wed, 30 Mar 2005 18:46:12 -0500, souris wrote:

>Thanks for the information,
>I got it works.
>It can calculate the date difference between 2 days in the table.
>Is it possible to calculate the date difference in other table?
>For example:
>I have a table
>Account_Number Opened_Date
> 1 03/10/2005
> 2 03/15/2005
>Today is 03/30/2005.
>I wanted calculate the days difference between today and Opened_date
>excluding wend.
>I tried using the table, but not lucky.
>Any information is great appreciated,
Hi Souris,
Try if this works:
SELECT a.Account_Number, a.Opened_Date.
(SELECT COUNT(*)
FROM Calendar AS c
WHERE c.caldate >= a.Opened_Date
AND c.caldate < CURRENT_TIMESTAMP + 1)
FROM YourTable AS a
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Calculate Concurrent Users Between a Date/Time Range

We have log records in a table that track user's session durations with a
Start_Date_Time of when their session started and an End_Date_Time of when
they logged off. Is there a way in SQL Server/Transact SQL to determine how
many users were on the system at any given time? Concurrent users would be
all those users whose start and end times span the same slice of time --
hopefully we can compare these time interval snapshots to see concurrent
usage over the course of the day. We don't need that much granualarity but
maybe in 1 - 5 minute durations. This log table generates some 4,200
records an hour so it's pretty big.
Thanks in Advance,
Randall
On Fri, 25 Mar 2005 14:56:20 -0800, Microsoft Newsreader wrote:

>We have log records in a table that track user's session durations with a
>Start_Date_Time of when their session started and an End_Date_Time of when
>they logged off. Is there a way in SQL Server/Transact SQL to determine how
>many users were on the system at any given time? Concurrent users would be
>all those users whose start and end times span the same slice of time --
Hi Randall,
DECLARE @.GivenTime datetime
SET @.GivenTime = '2005-03-25T23:45:00'
SELECT COUNT(*)
FROM MyTable
WHERE StartTime <= @.GivenTime
AND EndTime >= @.GivenTime
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thanks for the prompt response. One item I forgot to mention is that the
report needs to track maximum concurrent usage over a date range
Regards,
Randall
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:1o69411dqn2i9jbm2b1srgug0mi0tlkojk@.4ax.com...
> On Fri, 25 Mar 2005 14:56:20 -0800, Microsoft Newsreader wrote:
>
> Hi Randall,
> DECLARE @.GivenTime datetime
> SET @.GivenTime = '2005-03-25T23:45:00'
> SELECT COUNT(*)
> FROM MyTable
> WHERE StartTime <= @.GivenTime
> AND EndTime >= @.GivenTime
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 25 Mar 2005 15:23:09 -0800, Microsoft Newsreader wrote:

>Hugo,
>Thanks for the prompt response. One item I forgot to mention is that the
>report needs to track maximum concurrent usage over a date range
Hi Randall,
Try if this one works, then:
SELECT MAX(ConcurrentUsers)
FROM (SELECT a.StartTime, COUNT(*) AS ConcurrentUsers
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.StartTime <= a.StartTime
AND b.EndTime >= a.EndTime
WHERE a.StartTime >= @.StartOfPeriod
AND a.StartTime <= @.EndOfPeriod
GROUP BY a.StartTime) AS x
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That worked beautifully, thank you so very much!!!
Regards,
Randall
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9i99415p2ghodt0d2o4njb4phfu6rt32qj@.4ax.com...
> On Fri, 25 Mar 2005 15:23:09 -0800, Microsoft Newsreader wrote:
>
> Hi Randall,
> Try if this one works, then:
> SELECT MAX(ConcurrentUsers)
> FROM (SELECT a.StartTime, COUNT(*) AS ConcurrentUsers
> FROM MyTable AS a
> INNER JOIN MyTable AS b
> ON b.StartTime <= a.StartTime
> AND b.EndTime >= a.EndTime
> WHERE a.StartTime >= @.StartOfPeriod
> AND a.StartTime <= @.EndOfPeriod
> GROUP BY a.StartTime) AS x
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Calculate Age

Hi,
I have a 'date of birth' of a person and a date of an event and need to
calculate the persons age at that event (both datetime format). Is there a
function available I can use for this ?
Any advice appreciated
Nichttp://www.aspfaq.com/2233
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eZ2TS%23tkGHA.1936@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a 'date of birth' of a person and a date of an event and need to
> calculate the persons age at that event (both datetime format). Is there a
> function available I can use for this ?
> Any advice appreciated
> Nic
>|||datediff(yy,dob,eventdate)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This approach is not always good one
select datediff(yy,'20061231','20070101')
--
Should be
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN MONTH('20061231') > MONTH('20070101')
OR MONTH('20061231') = MONTH('20070101')
AND DAY('20061231') > DAY('20070101') THEN 1
ELSE 0
END
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9F8F5152-FBA9-4BDF-B358-01C9AEDB70C4@.microsoft.com...
> datediff(yy,dob,eventdate)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thanks for poitning that out Uri.. looks like you are out to take me down
today :)
Juz joking.. u made me understand a few things.. usually I look at the holes
in the solution.. Wonder how I missed the crater :)
Anyways.. guess this should do..
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
ELSE 0
END
Juz Rephrasing ur solution :)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||That doesn't quite work because leap years have one more day than normal
years. So
SELECT DATEDIFF(year, '20071231', '20081231')
- CASE
WHEN datepart(dy,'20071231') > datepart(dy, '20081231') THEN 1
ELSE 0
END
correctly gives age as 1, but
SELECT DATEDIFF(year, '20081231', '20091231')
- CASE
WHEN datepart(dy,'20081231') > datepart(dy, '20091231') THEN 1
ELSE 0
END
gives age as 0.
Tom
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BA42D051-F0AF-4971-8B42-6B83FE2757CC@.microsoft.com...
> Thanks for poitning that out Uri.. looks like you are out to take me down
> today :)
> Juz joking.. u made me understand a few things.. usually I look at the
> holes
> in the solution.. Wonder how I missed the crater :)
> Anyways.. guess this should do..
>
> SELECT DATEDIFF(year, '20061231', '20070101')
> - CASE
> WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
> ELSE 0
> END
> Juz Rephrasing ur solution :)
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Absolute murder.. better to keep my mouth shut for a while :)
So what happens if that guy was born on feb 29th?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Haven't you ever seen any Gilbert and Sullivan?
People born on Feb 29th are only about one fourth the age that we might
think they are.
In Pirates of Penzance, which my son just performed in as the Modern Major
General, a young man was indentured to the pirates until his 21st birthday.
But it turned out he was born Feb 29, so the 21st year after he had been
born, he had only had 5 birthdays, so his servitude had to continue.
HTH
Kalen Delaney, SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:53EC76A6-9297-4057-ACD4-6FD499DEC5EE@.microsoft.com...
> Absolute murder.. better to keep my mouth shut for a while :)
> So what happens if that guy was born on feb 29th?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Tracy,
As you probably know, a non-leap year has 365 days, and a leap year has 366
days:
Following returns 365:
SELECT DATEDIFF(day, '20030101', '20040101')
Following returns 366:
SELECT DATEDIFF(day, '20040101', '20050101')
Your expression would incorrectly return 1 for the following:
SELECT DATEDIFF(day, '20040102', '20050101') / 365
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23kwfIJxkGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Niclas wrote:
> I *think* this should work, I can't seem to think of a case where it
> wouldn't:
> SELECT DATEDIFF(day, birthdate, eventdate) / 365
>|||Itzik Ben-Gan wrote:
> Tracy,
> As you probably know, a non-leap year has 365 days, and a leap year has 36
6
> days:
> Following returns 365:
> SELECT DATEDIFF(day, '20030101', '20040101')
> Following returns 366:
> SELECT DATEDIFF(day, '20040101', '20050101')
> Your expression would incorrectly return 1 for the following:
> SELECT DATEDIFF(day, '20040102', '20050101') / 365
>
Ahh, there's the case I didn't think of...

Calculate Age

I need to caluclate the age using the birth date. how do i go about calculating the age using reporting service?

your regrads
Angela Goh

Hello:
There are two(2) ways that I use and they are as follows - but I prefer the second method.....
1st method - in Report Properties "paste" the following code:
Public Function Age(ByVal Date_Parm)
Dim date1 as string
date1 = Date_Parm.insert(4,"/")
Dim date2 as string
date2 = date1.insert(7,"/")
Dim DateOfBirth as DateTime
DateofBirth = Date2
Dim AgeCalc as integer

Dim intDOB As Integer = _
DateOfBirth.Day + DateOfBirth.Month * 100 + DateOfBirth.Year * 10000
Dim intToday As Integer = _
DateTime.Now.Day + DateTime.Now.Month * 100 + DateTime.Now.Year * 10000
AgeCalc = (intToday - intDOB) / 10000

Return AgeCalc
End Function

Then within the field in Layout where you want to display AGE use =Code.Age(whatever the birthdate field name is)
But I prefer to (not trying to create any cotroversy here) do everything within Stored Procedures and limit any custom code within Reporting Services - So I
Create a User Defined Function within SQL - the code is as follows:
CREATE function dbo.fn_GetAge
(@.in_DOB AS datetime,@.now as datetime)
returns int
as
begin
DECLARE @.age int
IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
else
IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)
IF datepart(d,@.now) >= datepart(d,@.in_DOB)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1
RETURN @.age
end

Then within the stored procedure I use as the data set within Reporting Services my SQL example is:
Select PM_Birth_Date, PM_Age from Patient_Master
Where PM_Age =
Case when isdate(Pm_Birth_Date) = 1 then dbo.fn_getage(PM_Birth_Date, getdate()) else '999' end
The isdate determines if PM_Birth_Date is a valid date and if so I execute the UDF GetAge to compute the age, if Birth date is not a valid date I assign 999 as an invalid age.
Just my personal preference, but I prefer to do everything within Stored Procedures within SQL and present all data, calculations, etc. to reporting services. The only conditional formatting I conduct in RS is color of fields.
Hope this helps!
Best Regards - Joe

|||

hi Joe,

for the 1st method, where do you paste the code into the report properties, at the custom code?

your regards

Angela

|||Yes,|||

hi joe,

i try using ur SP method. but encounter some error. so like to check with you did u create a field in the table for the age? because for my db i don't store age i jux need to generate the age.

thanks

Angie

|||Hello:
Yes, we keep all kind of things in the DB (kind of inherited) which we re-compute on the "fly".
The following will work in your situation and Age ends up being a calculated "work field"
Select PM_Birth_Date,
Age = Case When isdate(PM_Birth_Date) = 1 then dbo.fn_getage (PM_Birth_Date, getdate()) else '999' END
From Patient_Master
Best Regards,
Joe|||

hi joe,
thanks for the sp. i try another way by calling the function and not using the case to check the date. bcause i encounter some error if i try using case to check for the date. therefor i skip that step n use the function directly.
thanks
Angela

Calandar Control

How do I use the new calandar control available in 2005 when select date
parameters?I had to turn on the DateTimePicker component and everything is working.
"retkow" wrote:
> How do I use the new calandar control available in 2005 when select date
> parameters?|||Make sure the parameter type is date/time and it will appear automatically.
Report Menu ->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"retkow" <retkow@.discussions.microsoft.com> wrote in message
news:08C9C625-ED37-431C-9AEF-2E70C5852643@.microsoft.com...
> How do I use the new calandar control available in 2005 when select date
> parameters?

Caching in Reporting Services

Consider the following:
I have a report that takes in 2 parameters: Start Date and End Date.
If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
the parameters
and then I run it again with date range 1/1/2000-1/1/2001
Will RS go back to the Database to retrieve that data? Or is it cached
and it will just generate the results immidietly.
regards,
Stas K.No caching occurs if a parameter changes in any way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136313076.592719.46480@.g44g2000cwa.googlegroups.com...
> Consider the following:
> I have a report that takes in 2 parameters: Start Date and End Date.
> If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
> the parameters
> and then I run it again with date range 1/1/2000-1/1/2001
> Will RS go back to the Database to retrieve that data? Or is it cached
> and it will just generate the results immidietly.
> regards,
> Stas K.
>|||Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
=B7Query parameters filter data at the source as it is retrieved.
=B7Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.|||If you want to use snapshots combined with a filter then you could do this.
Note though that all the data is returned to reporting services and stored
for filtering later. If your data is large at all this could be problematic.
My opinion is that snapshots should be a last resort and only after a lot of
thought.
So, are you using filters or query paraemeters. You are using query
parameters if you query definition looks something like this:
select * from mytable where mydatefield > @.StartDate and mydatefield <
@.EndDate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136315942.418004.219830@.g44g2000cwa.googlegroups.com...
Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
·Query parameters filter data at the source as it is retrieved.
·Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.

C2 auditing ?

Hello there
I just inherited a data farm and I noticed that the person before me had c2
auditing turned on.
I noticed that the trc files date only back to 04/05 and that the last time
a fiel has been modified is yesterday.
the rate of growing out of 200mb should make these files date of once a day
or even 2. does anybody know how I can check if c2 is still turned on ? or
any idea why that is ? I suppoe i can wait another couple of days and see
but ...
thanks
>> does anybody know how I can check if c2 is still turned on ?
You can use sp_configure look for the running value for c2 audit mode.
Anith

C2 auditing ?

Hello there
I just inherited a data farm and I noticed that the person before me had c2
auditing turned on.
I noticed that the trc files date only back to 04/05 and that the last time
a fiel has been modified is yesterday.
the rate of growing out of 200mb should make these files date of once a day
or even 2. does anybody know how I can check if c2 is still turned on ? or
any idea why that is ? I suppoe i can wait another couple of days and see
but ...
thanks>> does anybody know how I can check if c2 is still turned on ?
You can use sp_configure look for the running value for c2 audit mode.
--
Anith

C2 auditing ?

Hello there
I just inherited a data farm and I noticed that the person before me had c2
auditing turned on.
I noticed that the trc files date only back to 04/05 and that the last time
a fiel has been modified is yesterday.
the rate of growing out of 200mb should make these files date of once a day
or even 2. does anybody know how I can check if c2 is still turned on ? or
any idea why that is ? I suppoe i can wait another couple of days and see
but ...
thanks>> does anybody know how I can check if c2 is still turned on ?
You can use sp_configure look for the running value for c2 audit mode.
Anith

ByYear or By Date Range

I would like to give the User to option of choosing the report by year or by
a To-From Date Range. How would I accomplish this option?
Thanks in advance,
SeanTake a look at the [Employee Sales Summary.rdl] sample report that ships
with the product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:D05FC8E5-24B4-4142-A1B3-016147DA94DF@.microsoft.com...
> I would like to give the User to option of choosing the report by year or
by
> a To-From Date Range. How would I accomplish this option?
> Thanks in advance,
> Sean