Parameter Question

Question

Post reply

Parameter Question

Kerrie Sorrell

SSC Eights!

Points: 921

September 1, 2005 at 11:17 am

#168585

Hi all,

I am still kind of new at this...

I am trying to have a date parameter that the user selects a month and it

brings back all the data for that month. Say the users picks August, the

report comes back for all the data for the month of August.

I hope I explained it well enough.

Any help would be great.

Thanks in advance,

Kerrie

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

CREATE PROCEDURE dbo.SPName @DateStart as datetime, @DateEnd as datetime

AS

SET NOCOUNT ON

Select WhatEver from dbo.YourTable where DateCol >= @DateStart and DateCol < @DateEnd

SET NOCOUNT OFF

This kind of sp allows you to have any daterange (month, day, year...). Just calculate the correct range on the app side and the proc will do the rest.

DJR-254141 SSC Enthusiast Points: 170 More actions · Answer 2

Presuming you simply want to pass in one parameter you could do:

CREATE PROCEDURE dbo.SPName @v_Dt as datetime

AS

Select WhatEver from dbo.YourTable

where DateCol >= @v_Dt and DateCol < DATEADD(m, 1, @v_Dt)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This is assuming that the reporting will only ever be monthly and that would be a huge design flaw if you want my opinion.

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 4

Thanks everyone for the replies!!!

For some reason I am not to create a proceedure per the boss. I have to figure out another way to do this.

I will figure it out evenually.

Thanks everyone!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

You're using sql server and he doesn't want you to use stored procs????

Is there a real reason behind is madness?

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 6

Create a parameter on the Report named Parm1, listing the month's names, and for value use the month number.

Then, in the query add the following to the WHERE clause:

month(DateField) = @Param1

That should do the trick.

Hope this helps.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

month(DateField) = @Param1 = TABLE SCAN convert to between at least.

Aiwa SSC Veteran Points: 201 More actions · Answer 8

rqR'us

You could create a new parameter (@month) in your report defined as integer.

Then you select the "Non-Queried" option and in the Label/Value list you insert the parameter data.

Value           Label
1                 January
2                 February
...

Then in you query you'll something like this:

select <fields> from <Table> where datepart(mm,<datecolumn&gt = @month

btw, you will need to manage year as well

HTH,

Eric

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 9

I have no iseal why I am not allowed to stored prceedures. But I figured out how to do it a different way. I made a new dataset and did it that way.

Syntax....

SELECT DISTINCT

MIN(DISTINCT RTRIM(CAST(DATEPART(Month, TimeLineDate) AS char(2))) + '/' + CAST(DATEPART(Year, TimeLineDate) AS char)) AS Month_Year,

RTRIM(CAST(DATEPART(Year, TimeLineDate) AS char(4)) + '/' + CAST(DATEPART(Month, TimeLineDate) AS char)) AS Year_Month

FROM dbo.TimeLines

GROUP BY RTRIM(CAST(DATEPART(Year, TimeLineDate) AS char(4)) + '/' + CAST(DATEPART(Month, TimeLineDate) AS char))

ORDER BY RTRIM(CAST(DATEPART(Year, TimeLineDate) AS char(4)) + '/' + CAST(DATEPART(Month, TimeLineDate) AS char)) DESC

It comes out the wat i want it but now my sort order is not sorted correctly.

2004/2, 2004/12, 2004/11 , 2004/10, 2004/1

Anyone have an suggestion for this?

Thanks everyone!!!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

I never worked with RS, but I work with MS SQL server.

I can guarantee that this syntaxe will force the server to scan the whole table to extract the information while the BETWEEN operator will allow for an index ot be used. Now on 100 rows it probabely won't matter, on a few millions with 1000 users, the server will die from that query running more than once/minute.

(select fields from Table where datepart(mm,datecolumn)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Check this out : Select RIGHT('0' + CAST(DatePart(MM, GetDate()) AS Varchar(2)), 2)

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 12

Thank you for your suggestion (and for showing me something new) but it is bringing in only one month, I think the most current month.

I am still very new at writting code. but I think it is because of the GetDate(), am I correct?

You Guys are great!!!!!!

Thanks again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I'm showing you the syntax, you have to understand it and then use it for your original query.

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 14

Thanks rqR'us, I will get. It just takes me a minute. I do appreciate all that you have shown me and for all your time.

Your the greatest and everyone else too!!

Thanks, Kerrie