INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Add Parameter into the report and pass it to a function

Add Parameter into the report and pass it to a function

(OP)
Hi All,

I need add a parameter which it is the year into the report, then pass it to a stored procedure, then sp passes it to a function. In the function, base on the Year user selects, for example, Year 2014, value is 14, the set up the start date and end date for each quarter (the company's Year is not calendar year), so if the @Year is 2014, then the first quarter start date should be set to '10/1/@Year-1', enddate = '12/31/@Year-1', second quarter startDate='1/1/@Year', endDate = '3/31/@Year' and so on...
When I create sp, I pass the parameter, also in function, but when I pass it to function, how can I set it up to those quarter startdate and enddate?

CREATE PROCEDURE [dbo].[sp_Test] @Year int = 14
select *
from Fn_Test(@Year int)
...

CREATE function[dbo].[Fn_Test](@Year)
returns table
as
return
...
Thank you so much for any helps and suggestions!!!

RE: Add Parameter into the report and pass it to a function

Hi,

I am not sure if you even need a table function for this.

Can you explain what the report does? So it takes in a year - but then it has to work out the quarters for what reason?

I am not trying to be awkward but it sounds like you are thinking about this in the wrong way. For a starting point you can find the quarter that a day falls into by using the datepart function on the date.

Perhaps the best way to do this is if you show an example of how your data looks like, and then what you want on the report.

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Add Parameter into the report and pass it to a function

(OP)
Dan,
Thank you so much for your response!!
You are right, the way I tried to get the quarter day is not the efficient way to do it.The report was ran for a year which was set in the report before, and I was asked to modify it to let users select the year to run.
Now I use DATEADD() to do it, when user select year 2014, the report will set quarter 1 (company's year is not the same as calendar year)start date 10/1/2013 as: DATEADD(dd,9,DATEADD(yy,@Year-1901,0)), end date as 12/31/2013: DATEADD(mm,11,DATEADD(yy,@Year-1901,30)), so on. The report provides the result for each quarter in year 2014. I believe there are other ways to work it around.
Again, thank you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close