SSRS Default date parameters

Question

SSRS Default date parameters

var05

Hall of Fame

Points: 3011
More actions
November 22, 2012 at 7:45 am

#271141

Hi All,
I have a report with 2 date parameters : Start and end date..
By default the date paramaters should display current date - 1
SO, I can use the below expression in default value of ssrs date paramater
=dateadd("d",-1,today) --- so that end date will be displayed as '22/11/2012 23:59:59'. This is ok
But say when I run the report on Monday, it takes current date-1 ie Sunday's value... But I don ve to display SUnday's data as there is no data for it. I would like to display Saturdays data when the report is run on Monday...
How this can be achieved in SSRS default value??? Should I have a seperate dataset for this scenario?Any help on this?
Thanks!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

That depends on whether you just want to skip Sunday every time or whether you are saying you want to skip any day that has no data...

Case 1 : Skip Sunday every time...

Method : Use an IF in the expression, if today is monday, use dateadd(dd,-2,getdate()), else use dateadd(dd,-1,getdate())

Case 2 : Skip days with no data

Method 1 : Change the dataset in such a way that the query returns the first day before or including the selected date that contains data.

Method 2 : Have a second dataset that returns the most recent date with some data, then use that to feed the date parameter.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 2

You could also use subscriptions and skip monday in your schedule.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

var05 Hall of Fame Points: 3011 More actions · Answer 3

Thanks...

I wanted the Case 1 of yours...

Default Paramater of date to be set to -2 if today is Monday(so that saturday data is displayed) and -1 if other weekdays...

I had defined the paramaters as date/time... I.e custom calendar date....

For eg: I had set default date for both paramaters i.e start and end date as

= dateadd("d",-1,today)

SO , When I open the report today, then Nov 21st data would be displayed....

But when I open the report on 26th NOv, the default value will be set to 25th (sunday).....But I wanted it as 24th while opening the report...

IS that possible? Thanks!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Yes, use an IF in the expression as I said...

=IIF( Weekday( Today(), FirstDayOfWeek.Monday ) = 1,

Dateadd( "d", -2, Today() ), Dateadd( "d", -1, Today() ) )

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

var05 Hall of Fame Points: 3011 More actions · Answer 5

var05

Hall of Fame

Points: 3011

November 23, 2012 at 8:11 am

#1561876

Thanks mate for your help!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

You are welcome.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);