sql server reporting services: Report Date Help -- sql server reporting services

RE: Report Date Help

IST
1/6/2005 6:57:01 PM

I created these User-Defined Fuctions to generate the previous month date
range.

For example your syntax would be:
SalesDetail.GLPostingDate >= dbo.fn_ISTCurrentMonth1(GETDATE())
AND
SalesDetail.GLPostingDate <= dbo.fn_ISTCurrentMonth2(GETDATE())

----------------------------------------------
CREATE FUNCTION fn_ISTCurrentMonth1 (@DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @ISTCurrentMonth1 VarChar(20)
SET @ISTCurrentMonth1 = convert(varchar,
convert(datetime,(convert(varchar, CASE WHEN MONTH(@DATE)-1 = 0 THEN 12 ELSE
MONTH(@DATE)-1 END) + '/' + '1' + '/' + convert(varchar, CASE WHEN
MONTH(@DATE)-1 = 0 THEN YEAR(@DATE)-1 ELSE YEAR(@DATE) END)), 101), 101)
RETURN(@ISTCurrentMonth1)
END
----------------------------------------------
CREATE FUNCTION fn_ISTCurrentMonth2 (@DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @ISTCurrentMonth2 VarChar(20)
SET @ISTCurrentMonth2 = convert(varchar, DATEADD(day, -1,
convert(datetime,(convert(varchar, MONTH(@DATE)) + '/' + '1' + '/' +
convert(varchar, YEAR(@DATE))), 101)), 101)
RETURN(@ISTCurrentMonth2)
END
----------------------------------------------

I hope these help.

I also have user-defined functions to calculate Current Year, Last Year,
Last Year Month date ranges. The years are based on a 10/1 fiscal year, but
can be modified to work for any. Let me know if these would be of any
benefit to you.

Thanks.

[quoted text, click to view]

RE: Report Date Help

v-rxwang NO[at]SPAM online.microsoft.com
1/7/2005 2:28:40 AM

Hi Glen,

Are you using a SQL Server database as the Data Source?
To isolate this issue you may want to run the query via
Query Analyzer or OSQL against your database to see if
there is any data returned. If there is any problem at
this point, the SQL Server database may have something
wrong.

If SQL Server returns the data correctly, run the query
in Report Designer->Data tab to verify the result. You
may also want to create a new report and create a new
dataset to test the problem.

Sincerely,

William Wang
Microsoft Online Partner Support

Get Secure! - <www.microsoft.com/security>

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

--------------------
[quoted text, click to view]
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
[quoted text, click to view]
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
AS Price,
[quoted text, click to view]
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
[quoted text, click to view]

Re: Report Date Help

Wayne Snyder
1/7/2005 8:53:35 AM

The problem is right here in your code

Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and

The previous month will not always be < current month...when the current
month is January (1), the previous month December will be (12)..
So you need to change your code to take that into account... Try testing
something like
IF you are looking for the previous month
SalesDetail.GLPostingDate between
dateadd(mm,-1,dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate),
getdate()) ) -- first day of prev month..
and
(dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate)) - 1,
getdate()) --lastday of prev month

This is just some on the fly code, with thought and testing you can make
this better I am sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM
','PSYKOVATON','PSYKPR3000',
[quoted text, click to view]
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,L
en(SalesDetail.CustomerNumber)-2)
[quoted text, click to view]

Re: Report Date Help

Glen Tosco
1/7/2005 10:41:07 PM

If works fine for all months except December...I am sure it is in the coding
but I cannot find a way to get it to work!

[quoted text, click to view]

Re: Report Date Help

Jeff A. Stucker
1/10/2005 6:45:48 PM

Kind of a mini-Y2K every year, eh? Unless you switch around your SQL to use
date arithmetic, you're going to have to add some fancy stuff to solve the
wraparound logic. You're treating months and years like separate integers.
Put them together in a single date and let SQL do the work for you: you've
already been given the answer: DateAdd

--
Cheers,

'(' Jeff A. Stucker
\

Business Intelligence
www.criadvantage.com
--------------------------------------
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.