DateTime Parameter

Reporting Services 2005 seems to have a problem with DateTime parameters.
One report has default datetime parameters. It uses the navigation feature
to call a second report, with the same parameters passed to the second
report. In the designer this works fine but when deployed the second report
crashes with an rsReportParameterTypeMismatch error.
This only happens when the dates being passed are non/ambiguous US/English.
In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly 6-Jan-06
works, 16-Jan-06 doesn't.
So it's not really a type mismatch error but a dataconversion issue.
Both reports are set to language en-GB
Any help welcome!
Bob
--
--
BobBut if the parms are being passed as DateTimes instances, then there should
be no string format issues. Am I missing something?
--
William Stacey [MVP]
"Bob" <bmorris@.swiftkenya.com(nospamm)> wrote in message
news:9A881BF6-AFD2-4213-889A-F26EC8209F74@.microsoft.com...
| Reporting Services 2005 seems to have a problem with DateTime parameters.
|
| One report has default datetime parameters. It uses the navigation feature
| to call a second report, with the same parameters passed to the second
| report. In the designer this works fine but when deployed the second
report
| crashes with an rsReportParameterTypeMismatch error.
|
| This only happens when the dates being passed are non/ambiguous
US/English.
| In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly
6-Jan-06
| works, 16-Jan-06 doesn't.
|
| So it's not really a type mismatch error but a dataconversion issue.
|
| Both reports are set to language en-GB
|
| Any help welcome!
|
| Bob
| --
| --
| Bob

datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
Clint
SQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>
|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a column in a view which is of the DATETIME datatype. This is
> fine,
> > but when I output this to MS Reporting Services it also shows the time
> > (which is always 12:00 as we are not using time as a field).
> >
> > How do I use the cast statement or another statement to have only the
> date?
> > I have read BOL without success.
> >
> > Thanks for any help provided.
> >
> > Clint
> >
> >
>

Dates in Reporting Services

hey all

set up

Visual Studio 2005

SQL Server Express / Reporting Services

four fields

State date Start time Finish Date Finish Time

I need to take one away from the other - can someone please help me?

Is it better to keep these in separate fields or to combine and subtract?

Is there anything special I need to know with subtracting time?

I am reasonably newbie still so would appreciate any help thanks

I am using the visual side in Reporting services - Data - Layout - Preview.

thanks

Jewel

Jewel,

What you are describing could be done a couple different ways, but the best method would likely depend on your situation. Can you provide a bit more detail or maybe an example of what you would expect to happen?

|||

thanks heaps for replying

so I would have a line like this - other info eg Tracker / Description etc would be on the 2nd line

and Criteria would be = Closed

Call ID Received Date Received Time Closed Date Closed Time TIME TAKEN

so Time Taken would be the result from Received to Closed.

thanks

Jewel

|||

To come up with Time Taken in this case, I would probably combine your start date and time into one variable, then combine your end date and time into another variable and perform your subtraction from there. So if you had the following date initially:

Received Date: 01/12/2006
Received Time: 18:23:47
Closed Date: 01/13/2006
Closed Time: 06:35:27

Then you would have two new fields as follows:

Received DateTime: 01/12/2006 18:23:47
Closed DateTime: 01/13/2006 06:35:27

You would then subtract one from the other to get the time difference.

|||

thanks

so I have combined my fields as you said.

When I do the subtraction -

New fields - Text=ClosedDate Text=RecvdDate

=(ClosedDate) - (RecvdDate)

I get error ClosedDate not declared

or should I be using?

thanks

|||

it depends where you combine the fields. You can either do this in you source query or using the .NET object model in an expression. Either way you need to make sure the field has the correct datatype

SQL
===

SELECT ReceivedDateTime = CAST(ReceivedDate + ' ' + ReceivedTime AS DATETIME)
, ClosedDateTime = CAST(ClosedDate + ' ' + ClosedTime AS DATETIME)
FROM your_table

Expression (assuming your fields are string data type)

=CDate(Fields!ClosedDate.Value + " " + Fields!ClosedTime.Value) - CDate(Fields!ReceivedDate.Value + " " + Fields!ReceivedTime.Value)

|||

thanks Adam

that works a treat - appreciate it

Dates function for Reporting Services

Excel has a function called workday which takes out weekends and holidays and give you the date of when the project will be done
I'm can't find any function that does what workday function does in Reporting services.

DateAdd includes the weekends and holiday which i don't need.
I need something that can do something close to workday in excel

There is no such function that I am aware of. You can obviously approximate it by

@.WANTED = DATEADD(Day, 1.4 * @.WorkDays. @.StartDate)

To go beyond that requires a table of Holidays and a more involved function. There may be such a function i SQLCENTRAL.COM.

|||

Use

CREATE FUNCTION dbo.fnAddWorkdays(@.FROM DATETIME, @.DAYS INT)
-- Purpose:
-- Calculate Date @.DAYS in future from @.FROM
-- Copyright (C) 2007 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
RETURNS DATETIME AS
BEGIN
DECLARE @.YYYYMMDD CHAR(8)
WHILE @.DAYS > 0
BEGIN
SET @.FROM = DATEADD(day, 1, @.FROM)
SET @.YYYYMMDD = SUBSTRING(REPLACE(CONVERT(CHAR(20), @.FROM, 126), '-', ''), 1, 8)
IF DATEPART(weekday,@.FROM) NOT IN (1, 7) BEGIN
IF NOT EXISTS(SELECT * FROM Holiday WHERE YYYYMMDD = @.YYYYMMDD) SET @.DAYS = @.DAYS - 1
END
END
RETURN @.FROM
END
GO
SELECT dbo.fnAddWorkdays(GETDATE(), 1)
SELECT dbo.fnAddWorkdays(GETDATE(), 2)
SELECT dbo.fnAddWorkdays(GETDATE(), 3)

|||

Table create is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Holiday]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[YYYYMMDD] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[Holiday] WITH NOCHECK ADD
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Holiday] ON [dbo].[Holiday]([YYYYMMDD]) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'Date in yyyyMMdd notation', N'user', N'dbo', N'table', N'Holiday', N'column', N'YYYYMMDD'

GO

DateDiff Function, VERY IMPORTANT!!

I have 2 dates one a parameter and one from a field, I need to calculate the
number of days inside a reporting services expression window. The Datediff
function does not seem to work, I cannot do it on the SQL Query because one
of the dates is from a parameter (ie. @.date)You can use the datediff in your query.
SELECT DATEDIFF(day, pubdate, @.date) AS no_of_days
FROM titles where pubdate > @.date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)|||You can also do it in the expression using VB datediff ie
=datediff(DateInterval.Day,Parameters!myparm.Value,Today())
I prefer doing this stuff in sql like Mike does tho...
--
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
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)