Using a date table in a parameter

Question

Using a date table in a parameter

jbalbo

SSCertifiable

Points: 7583
More actions
February 15, 2013 at 10:37 am

#274338

Hi,
I have a parameter startdate, I am using a shared dateset which gets the value do a dimdate table to only display friday dates.
Is there away to set the parameter to display as a date so they get a calender view of Friday dates?
Thanks In advance
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

jbalbo (2/15/2013)
Hi,
I have a parameter startdate, I am using a shared dateset which gets the value do a dimdate table to only display friday dates.
Is there away to set the parameter to display as a date so they get a calender view of Friday dates?
Thanks In advance
Joe

Will that StartDate parameter always be a Friday or not? If not, which Friday do you want to use as the real start? The Friday before or the Friday after?

Also, how many Friday dates do you want to be returned?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Thanks Jeff for getting back

I've kind of changed direction a bit

I'm new at SSRS, and I wanted to use the date table to display only Friday dates,

but I wanted it to display in a calendar, I may be asking for it to do too much

Put what I have are 4 titles for 4 week prior to the parameter date and I wanted to write them out as mm/yy

I did come up with this, it's cheezzzzy.... in the SP

,right('00' + Cast(DATEPART(M, @enddate-21) as varchar(2)),2) + '/' + right('00'+cast(DATEPART(DAY, @enddate-21) as varchar(2)),2) as title1

,right('00' + Cast(DATEPART(M, @enddate-14) as varchar(2)),2) + '/' + right('00'+cast(DATEPART(DAY, @enddate-14) as varchar(2)),2) as title2

,right('00' + Cast(DATEPART(M, @enddate-7) as varchar(2)),2) + '/' + right('00'+cast(DATEPART(DAY, @enddate-7) as varchar(2)),2) as title3

,right('00' + Cast(DATEPART(M, @enddate) as varchar(2)),2) + '/' + Right('00'+ cast(DATEPART(DAY, @enddate) as varchar(2)),2) as title4

Any Ideas would be great

Thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

jbalbo (2/15/2013)
Put what I have are 4 titles for 4 week prior to the parameter date and I wanted to write them out as mm/yy

Do you really mean "mm/dd" IAW the code you posted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

This will work (for mm/dd) in T-SQL for just about any version. Not sure if it'll work in SSRS and it's not much more sophisticated than what you had.

DECLARE @EndDate DATETIME;

SELECT @EndDate = '20130215';

SELECT Title1 = LEFT(CONVERT(CHAR(8),DATEADD(wk,-3,@EndDate),1),5),

Title2 = LEFT(CONVERT(CHAR(8),DATEADD(wk,-2,@EndDate),1),5),

Title3 = LEFT(CONVERT(CHAR(8),DATEADD(wk,-1,@EndDate),1),5),

Title4 = LEFT(CONVERT(CHAR(8),DATEADD(wk,-0,@EndDate),1),5)

I guess my question would be, why not do the actual date formatting in SSRS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)