Multiple Parameters from one dropdown select

[chromarog]

Ok, I'm going attempt to describe what I need.

I want to choose a parameter from a dropdown list of months. When I select a month (January for this example) I want the start date to be passed to the @startdate parameter (this is successful), but I ALSO want to send the end date for the month to the @enddate parameter for the query to return all items in the where between clause. I thought multivalue might be the way to go but I can't make it work right or I've got it wrong in my select as my where clause is a BETWEEN and not an IN clause.

Is it possible to do this?

 

[bburnell]

Are you using a stored procedure to do this or just standard T-SQL? Can you show us the code? You should be able to pass multiple parameters to a report. It's actually pretty easy. I'm not sure if that's what your asking though.
~Brett

--------------------------
My new website coming soon!

 

[SgtJarrow]

Brett....

I think chrom wants only one parameter entered on the report, but pass two reports back to the data source.

I don't think you can do this. As Brett suggested, you will probably want to modify a stored procedure to do this for you.

If your not using a stored procedure and instead have the SQL in the datasource, you will need to modify it to make use of DATEADD a couple of times to find the last day of the month.

As Brett suggested, please show use your query/stored procedure to help you.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[chromarog]

The report query from our DB on the MP2 server. I can't remember now but there was a reason why I couldn't use a stored proc against my better judgment. Two different parameters maybe or it could be that I'm using two different servers for the end result. (that just sounded bad) (suggestions there would be helpful as well)

SELECT WO.WONUM, WO.WOTYPE, EQUIP.PRIORITY, WO.COMPLETIONDATE, WOE.REGHRS, WOE.OTHRS
FROM WOE AS WOE INNER JOIN
WO AS WO ON WOE.CLOSEDATE = WO.CLOSEDATE AND WOE.SITEID = WO.SITEID AND WOE.WONUM = WO.WONUM INNER JOIN
EQUIP AS EQUIP ON WOE.EQNUM = EQUIP.EQNUM AND WO.SITEID = EQUIP.SITEID AND WOE.LOCATION = EQUIP.LOCATION AND
WOE.SITEID = EQUIP.SITEID AND WOE.SUBLOCATION1 = EQUIP.SUBLOCATION1 AND WOE.SUBLOCATION2 = EQUIP.SUBLOCATION2 AND
WOE.SUBLOCATION3 = EQUIP.SUBLOCATION3
WHERE (WO.COMPLETIONDATE BETWEEN @StartDate AND @ENDOFDATE) AND (WO.WOTYPE IN ('BR', 'SU'))
ORDER BY WO.COMPLETIONDATE


This is the code from a small table I created on the reporting service server for referencing our company accounting period.

SELECT [ACC_MONTH]as MonthSelect
,min(ACC_STARTDATE)AS STARTDATE
,max(ACC_ENDDATE)AS ENDOFDATE
FROM [VSTrack].[dbo].[T_ACC_CALENDAR]
GROUP BY ACC_MONTH
Order by STARTDATE

 

[SgtJarrow]

something like:

SELECT     WO.WONUM, WO.WOTYPE, EQUIP.PRIORITY, WO.COMPLETIONDATE, WOE.REGHRS, WOE.OTHRS
FROM         WOE AS WOE INNER JOIN
                      WO AS WO ON WOE.CLOSEDATE = WO.CLOSEDATE AND WOE.SITEID = WO.SITEID AND WOE.WONUM = WO.WONUM INNER JOIN
                      EQUIP AS EQUIP ON WOE.EQNUM = EQUIP.EQNUM AND WO.SITEID = EQUIP.SITEID AND WOE.LOCATION = EQUIP.LOCATION AND
                      WOE.SITEID = EQUIP.SITEID AND WOE.SUBLOCATION1 = EQUIP.SUBLOCATION1 AND WOE.SUBLOCATION2 = EQUIP.SUBLOCATION2 AND
                      WOE.SUBLOCATION3 = EQUIP.SUBLOCATION3
WHERE     (WO.COMPLETIONDATE BETWEEN @StartDate AND DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartDate))) AND (WO.WOTYPE IN ('BR', 'SU'))
ORDER BY WO.COMPLETIONDATE

No EndofMonth date needed. This takes your start date, adds one month and then subracts on day.....which should the be last day of the month.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[chromarog]

Our accounting calendar doesn't allow for that. 7 of the months are 28 days, the other 5 are 35. So could logic be employed to take in to account for those months?

 

[SgtJarrow]

I guess you could.....you would have to create a UDF or something in SQL to allow for this. But UDFs tend to decrease performance. I think you best bet in the case is just to have the users select the start and end date directly.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[ousoonerjoe]

We had many jobs/reports that had to be executed according to Accounting's schedule rather than the end of the month. We created a table to hold the StartDate and EndDate. Since we had multiple jobs and needed to follow different client's schedules, we created a table to keep them all in and all processes would check that table to execute. The clients and our acct dept would send us their dates as they defined them.

Here's the table we used. Pass the Month and Year to report on and it would return the StartDate and EndDates.

NOTE: ExecMonth - 0 = year end (Jan 1 to Dec 31)
21 = 1st Quarter, 22 = 2nd Quarter, etc.

CREATE TABLE AcctDates
	(
	DateID int NOT NULL IDENTITY (1, 1),
	ExecMonth tinyint NOT NULL,
	ExecYear smallint NOT NULL,
	StartDate datetime NOT NULL,
	EndDate datetime NOT NULL,
	JobComplete bit NOT NULL,
	ClientID int NOT NULL
	)  ON [PRIMARY]

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[chromarog]

While playing around with the default values section of the reporting parameters, I was trying to find a way to use this:

SELECT [ACC_MONTH]as MONTHSELECT
,max(ACC_ENDDATE)AS ENDOFDATE
FROM [VSTrack].[dbo].[T_ACC_CALENDAR]
where ACC_MONTH = (@MONTHSELECT)
GROUP BY ACC_MONTH

The parameter passed was the month from the second dropdown and the default value was the date I wanted but I'm still forced to use a second dropdown list to populate a third box. My goal in that scenario was to have one dropdown to pick the month, then the second was defaulted based on the the month picked. I'm just having a hell of a time getting this condensed down to two dropdowns, one where the user picks a month, the second would be autopopulated.

 

[chromarog]

That's what I did here as well SoonerJoe. It works great as a cross-refrence to our accounting period and it's been a big success, but I'm trying to capitalize on the accounting month column that I added to the table for this very report that's kickin my tail.

 

[ousoonerjoe]

Just pass the Accounting Month and Year to the procedure and let the rest be internal.

CREATE PROCEDURE usp_DateStuff (@Mon TINYINT , @Year SMALLINT)

AS

DECLARE @StartDate	DATETIME
DECLARE @EndDate	DATETIME

SET @StartDate = (SELECT StartDate FROM AcctDates WHERE ExecMonth = @Mon AND ExecYear = @Year AND ClientID = 5)
SET @EndDate = (SELECT EndDate FROM AcctDates WHERE ExecMonth = @Mon AND ExecYear = @Year AND ClientID = 5)

SELECT Field1, Field2
FROM Table1
WHERE SomeDate BETWEEN @StartDate AND @EndDate

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.