September 14, 2016 at 5:26 am
Hi All,
So, Our report is made up of data and for some of it on a Monday it would be collated up to and including the Friday before.
We want to put a date on the report to reflect this, I can add a date in for the other bits (as I can base it off the last run of some data) but how would I go about getting in the event of a Monday the last weekday date.
Effectively what would happen is the report would say (example based on this Monday):
Data @ 11/09/2016 for Topline, Boards data @ 09/09/2016.
If it was today, the data would say
Data @ 13/09/2016 for Topline, Boards data @ 13/09/2016.
Thanks for any help!
September 14, 2016 at 6:47 am
So effectively your looking at:
Day run: Sunday/ Top line day: Saturday/ Board day: Friday
Day run: Monday/ Top line day: Sunday/ Board day: Friday
Day run: Tuesday/ Top line day: Monday/ Board day: Monday
Day run: Wednesday/ Top line day: Tuesday/ Board day: Tuesday
Day run: Thursday/ Top line day: Wednesday/ Board day: Wednesday
Day run: Friday/ Top line day: Thursday/ Board day: Thursday
Day run: Saturday/ Top line day: Friday/ Board day: Friday
Assuming that the topline day is a parameter, This would work:
=iif(Weekday(Parameters!TopLineDate.Value,1) = 1, dateadd("d", -2, Parameters!TopLineDate.Value), iif(Weekday(Parameters!TopLineDate.Value,1) = 7, dateadd("d", -1, Parameters!TopLineDate.Value), Parameters!TopLineDate.Value))
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 14, 2016 at 9:40 am
Thank you, I adapted it so that it used a field I had rather than a parameter and it worked so far.
Will test it on Monday to see the date changes.
Really appreciated.
September 16, 2016 at 12:28 pm
So for the Monday, you want Friday listed,
Same for Sunday.
Would the code not actually read as
(1 indicates a Monday)
iif(Weekday(Parameters!TopLineDate.Value,1) = 1,
dateadd("d", -3, Parameters!TopLineDate.Value),
iif(Weekday(Parameters!TopLineDate.Value,1) = 7,
dateadd("d", -2, Parameters!TopLineDate.Value),
Parameters!TopLineDate.Value)
)
?
Is this how you 'adapted' it?
----------------------------------------------------
September 26, 2016 at 3:09 am
Sorry foe the delay, I was out last Monday on holiday so couldn't test it properly.
So I went in to test this morning all hopeful....and sadly I am getting an error because the date format I have chosen for the field is dd/mm/yyyy where I have used convert (varchar(10), date, 103). So using dateadd or weekday with that doesn't work (numbnuts here didn't think of that!).
So my next question is how would I convert the format in SSRS, as if I change the date format in SQL it works (as I expected but tested anyway). However I need it in a dd/mm/yyyy format.
You are going to tell me this is an easy one aren't you? 😛
September 26, 2016 at 6:44 am
You can format a text box by right clicking it and selecting Textbox properties. Then in the format/text pane (I can't remember what it called, and i don't have SSRS on this machine), you can select your format you desire, or enter a custom one (such as dd/MM/yyyy, although this should be a format in the dates selection).
Alternatively, you can select the textbox and hit F4. Then in the properties pane there is a field called "format", where you can enter your customer format. When doing it this way, you can highlight multiple textboxes, and apply the same format across the all of them. You can't do this in the dialog box.
To do this, you will need to ensure that the T-SQL statement returns the date in a date, or datetime field type, or the format won't work (SSRS won't understand how to change the look of a string formatted in a date like format).
Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 26, 2016 at 11:34 am
Kazmerelda (9/26/2016)
Sorry foe the delay, I was out last Monday on holiday so couldn't test it properly.So I went in to test this morning all hopeful....and sadly I am getting an error because the date format I have chosen for the field is dd/mm/yyyy where I have used convert (varchar(10), date, 103). So using dateadd or weekday with that doesn't work (numbnuts here didn't think of that!).
So my next question is how would I convert the format in SSRS, as if I change the date format in SQL it works (as I expected but tested anyway). However I need it in a dd/mm/yyyy format.
You are going to tell me this is an easy one aren't you? 😛
Right click the cell with the date > Text Properties > Number > Date > [Select your desired format]
If you need to do date calculations in an expression, you may need to use the "cDate" (convert to date) function on the field if it is not coming out of the query as a date. Be careful here as you will need to handle nulls and empty strings among possibly a few other things.
----------------------------------------------------
September 27, 2016 at 8:15 am
Thanks both, in the end what worked was changing the language on the actual report and that fixed my formatting.
I will test again next Monday but I have no doubt it will be fine. Thank you so much for your help, it's appreciated
September 27, 2016 at 11:27 am
Just out of curiosity, what was your language setting?
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply