date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

sql

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)