Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

Date issues

I have 2 tables the first table has master records. The secong has child
records. I need to get the latest record in the 2nd table using the
'ProgramLOIAcknowledged' date. The only problem is the record does not show
up if it is NULL. I tried to put a date in there using getdate(), but I
really just want an emply field.
SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSID,
dbo.tblACRDIMProgramTemp.ProgramName,
MySubQuery2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp INNER JOIN
(SELECT tblACRDIMProgramPending.*
FROM tblACRDIMProgramPending JOIN
(SELECT
tblACRDIMProgramPending.ProgramSID, Max(case
tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() Else
tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
FROM
tblACRDIMProgramPending
GROUP BY
ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
MySubQuery1.ProgramSID AND
tblACRDIMProgramPending.ProgramLOIAcknowledged
= MySubQuery1.MyMaxDate) MySubQuery2 ON
dbo.tblACRDIMProgramTemp.ProgramSID =
MySubQuery2.ProgramSID
WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
HTH, Jens Smeyer.
"Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>I have 2 tables the first table has master records. The secong has child
>records. I need to get the latest record in the 2nd table using the
>'ProgramLOIAcknowledged' date. The only problem is the record does not show
>up if it is NULL. I tried to put a date in there using getdate(), but I
>really just want an emply field.
>
> SELECT MySubQuery2.ProgramPendingID,
> dbo.tblACRDIMProgramTemp.SysOrgSID, dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
> tblACRDIMProgramPending.ProgramSID, Max(case
> tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate()
> Else tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
> MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate)
> MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
> MySubQuery2.ProgramSID
> WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>|||d not work
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238jww5bQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
> HTH, Jens Smeyer.
> "Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
> news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||> The only problem is the record does not show up if it is NULL.
The row does not show up when column [?] is null. Try:
SELECT
MySubQuery2.ProgramPendingID,
dbo.tblACRDIMProgramTemp.SysOrgSID,
dbo.tblACRDIMProgramTemp.ProgramName,
MySubQuery2.ProgramLOIAcknowledged
FROM
dbo.tblACRDIMProgramTemp
INNER JOIN
(
SELECT
tblACRDIMProgramPending.*
FROM
tblACRDIMProgramPending
JOIN
(
SELECT
tblACRDIMProgramPending.ProgramSID,
Max(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) as
MyMaxDate
FROM
tblACRDIMProgramPending
GROUP BY
ProgramSID
) AS MySubQuery1
ON tblACRDIMProgramPending.ProgramSID = MySubQuery1.ProgramSID
AND tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate
) MySubQuery2
ON dbo.tblACRDIMProgramTemp.ProgramSID = MySubQuery2.ProgramSID
WHERE
(dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
AMB
"Fetty" wrote:

> I have 2 tables the first table has master records. The secong has child
> records. I need to get the latest record in the 2nd table using the
> 'ProgramLOIAcknowledged' date. The only problem is the record does not sho
w
> up if it is NULL. I tried to put a date in there using getdate(), but I
> really just want an emply field.
>
> SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSI
D,
> dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
> tblACRDIMProgramPending.ProgramSID, Max(case
> tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() El
se
> tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
> MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending
.ProgramLOIAcknowledged
> = MySubQuery1.MyMaxDate) MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
> MySubQuery2.ProgramSID
> WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
>|||I tried that too
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AD944C94-4200-400E-AA6B-BB1AAE5F91C4@.microsoft.com...
> The row does not show up when column [?] is null. Try:
> SELECT
> MySubQuery2.ProgramPendingID,
> dbo.tblACRDIMProgramTemp.SysOrgSID,
> dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM
> dbo.tblACRDIMProgramTemp
> INNER JOIN
> (
> SELECT
> tblACRDIMProgramPending.*
> FROM
> tblACRDIMProgramPending
> JOIN
> (
> SELECT
> tblACRDIMProgramPending.ProgramSID,
> Max(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) as
> MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID
> ) AS MySubQuery1
> ON tblACRDIMProgramPending.ProgramSID = MySubQuery1.ProgramSID
> AND tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate
> ) MySubQuery2
> ON dbo.tblACRDIMProgramTemp.ProgramSID = MySubQuery2.ProgramSID
> WHERE
> (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
> AMB
>
> "Fetty" wrote:
>|||Then give us real specs, and tell us what "d not work" means. Error
message? If so, what is it? Wrong results? If so, how are they wrong?
What results were you expecting?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Fetty" <dfetrow410@.hotmail.com> wrote in message
news:OoDeTlcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
>I tried that too|||On Fri, 15 Apr 2005 09:18:21 -0400, Fetty wrote:

>I have 2 tables the first table has master records. The secong has child
>records. I need to get the latest record in the 2nd table using the
>'ProgramLOIAcknowledged' date. The only problem is the record does not show
>up if it is NULL. I tried to put a date in there using getdate(), but I
>really just want an emply field.
>
>SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSID
,
>dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
>FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
>tblACRDIMProgramPending.ProgramSID, Max(case
>tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() Els
e
>tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
>tblACRDIMProgramPending
> GROUP BY
>ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
>MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending.
ProgramLOIAcknowledged
>= MySubQuery1.MyMaxDate) MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
>MySubQuery2.ProgramSID
>WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
Hi Fetty,
As others already indicated, it's nigh on impoossible to help you
without knowing your tables, sample data and expected output. And it
would also help if you could reformat the SQL to be more readable, and
replace those long unintelligible table names with short and snappy
aliases.
But allow me to take a wild shot:
SELECT sub2.ProgramPendingID,
temp.SysOrgSID,
temp.ProgramName,
sub2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp AS temp
INNER JOIN (SELECT pend.*
FROM tblACRDIMProgramPending AS pend
INNER JOIN (SELECT ProgramSID,
MAX(COALESCE(ProgramLOIAcknowledged,
getdate()) AS MyMaxDate
FROM tblACRDIMProgramPending
GROUP BY ProgramSID) AS sub1
ON pend.ProgramSID = sub1.ProgramSID
AND COALESCE(pend.ProgramLOIAcknowledged,
getdate() = sub1.MyMaxDate) AS sub2
ON temp.ProgramSID = sub2.ProgramSID
WHERE temp.SysOrgSID = 2
(untested, of course)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Date Formats

Hi,
While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
the following error :
"The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value"
But the same statement executes without any error if I connnect the
application to a different SQL Server.
I'm using SQL Server 7.
Why this error, do I need to set anything the server or the client ?
Thanks
Rajeev RIs DATEOFLEAVING of datetime datatype?
If so try the following:
set dateformat dmy
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = '08/31/2003'
or this
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = convert(datetime, '08/31/2003', 103)
--
Dean Savovic
www.teched.hr
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client ?
> Thanks
> Rajeev R
>|||Try changing the statement to:
SELECT STAFFNO FROM MASTER WHERE DATEOFLEAVING = CAST('2003-08-31 00:00:00'
AS DATETIME)
Pete
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message
news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm
getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect
the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client
?
> Thanks
> Rajeev R
>