Weekend Date Transformation

I needed a calculated field in one of my reports that had logic to tell if a "CreatedDate" field fell into the Weekend window for a particular department in my company. If the field was between friday 5pm and sunday 11pm it was considered the weekend and the New date given would be start of business sunday at 11pm.

This query takes the date... transforms it to a number value representing the day of the week. it then strips off the date to a known date of january 1, 0001 and leaves the time... next a datediff in seconds calculates the amount of seconds needed to alter later add to the date to make it a finished product.

I was not given much time for polishing... but my testing shows it works. It probably could be improved... but hey here is a general direction if you face the same problem I did.

Here is some code that I wrote up simular to what you mentioned...I wanted to take a field called "CreatedDate" and if it fell into our window of what we call a weekend to show it as the next business day which would be Friday 5:00PM thru sunday 11:00PM.It will have to be altered to fit your needs...

=iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <=
25199),IIF(Weekday(Fields!CreatedDate.Value) = 6 , DateAdd("S"
,iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0") + (iif(iif((Weekday(Fields!CreatedDate.Value) = 6 and
Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM")
<= 25199) or (Weekday(Fields!CreatedDate.Value) = 7) or
(Weekday(Fields!CreatedDate.Value) = 1 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM") >=1),
Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM"),
"0") >= 1,172800,0)), Fields!CreatedDate.Value), ""),"")
&
IIF(Weekday(Fields!CreatedDate.Value) = 7,
IIF(Weekday(Fields!CreatedDate.Value) = 7, DateAdd("S"
,(iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0") + 86400), Fields!CreatedDate.Value) , ""),"")
&
IIF((Weekday(Fields!CreatedDate.Value) = 1) and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM") >=1,
IIF(Weekday(Fields!CreatedDate.Value) = 1, DateAdd("S"
,iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0"), Fields!CreatedDate.Value), ""),
"")&
IIF((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") >
25199),Fields!CreatedDate.Value,"")&
IIF((Weekday(Fields!CreatedDate.Value)
= 1) and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") < 1, Fields!CreatedDate.Value,
"")&
IIF(Weekday(Fields!CreatedDate.Value) = 2 or
Weekday(Fields!CreatedDate.Value) = 3 or Weekday(Fields!CreatedDate.Value) = 4
or Weekday(Fields!CreatedDate.Value) = 5, Fields!CreatedDate.Value,"")

SSRS Parameters Parameters Parameters

Parameters are nifty little dew dads in SSRS. Here are a list of things I find useful with parameters:

Using a parameter in a where clause in a SQL statement to make the report dynamically select records based on parameter selection.

ex: WHERE (Table.Field = @acme)

Adding a parameter in the connection string to dynamically change servers or databases based on a parameter selection.

ex: = "data source=" & Parameters!server.Value & ";initial catalog=" & Parameters!database.Value

Using a parameter to execute a given stored procedure in the query field

ex: EXEC (@StoredProc)

A hidden parameter is nice way to pass data between two different data sets in the same report

If using a parameter for date selection change the parameter type from string to datetime and the final report will provide a nice calendar button to select the date/time

Parameters provide a great way to link two reports and provide relevant data between the two. Tip: it helps if parameters between two different reports are identical in terms of name, data type, and case sensitive likeness as well.

Using a parameter to selectively hide portions of the report based on parameter choice. Here is an example from the visibility field of a table.

ex: =IIF(Parameters!reporttype.Value LIKE "*CLERICAL*", False, True)

The tittle of your report can be dynamic as well based on parameter selection.

ex: =Parameters!protocol.Value + " " + (iif(Parameters!reporttype.Value = "ABP ECG","Both ECG and ABP",Parameters!reporttype.Value)) + " Procedures Country/Site"

There are countless ways to take advantage of parameters in SSRS. I would be very interested to hear of your ideas and ways you incorporate parameters in your reports! I will add more as I remember them.

SSRS and Excel workarounds

Any ssrs report designer that has tried to make their report Excel friendly has seen that most often... unless you make your report Excel friendly from the very start of report development... will not integrate very nicely. Hidden columns and merged cells in unexpected places are the most common obstacles you will find. Followed by frustration by both the developer, and the end user having to fix your Excel outputs.

There is a MSDN out there to "Help" with this.. but I found the article to be lacking. But it did set me on the right path but left me with about hours of working out the kinks of EXACTLY what do I have to do to fix my Excel outputs.

If you find this article you will no doubt find very shortly that a great many of ssrs report designers have issues with Excel. You will also notice that after market software developers have also noted this problem and have solutions that make your reports Excel friendly for a modest fee... Cheapest I found was 250 bones. Most expensive was 1500 for a software developing solution.

The problems that occur with Excel outputs are related to converting the ssrs layout data from Inches to Pts. I believe there is some kind of rounding issue with the conversion engine for ssrs that the conversion is not exact. Causing hidden columns and unexpected merges of the cells.

Preferably... building your report to work with Excel from the very beginning is preferable and much easier to do. Right off the bat when you create that blank report page change all the report options to the pt system. This information will help you convert a finished report to be more Excel friendly.

1 inch = 72pt

• In the report\options change the grid options to pts... margins... spacing
• Every item that you add. Textbox, img, table, etc must have its SIZE and LOCATION changed to pts.
• Some items I found CANNOT be converted to points. In that case I try and make the size of that object an inch value that does not have that many decimal places... like 1inch, 1inch or 1inch, .5inch etc.

I don't have a clue why the engine is not better in conversion... but after doing these steps you will hopefully learn to make a predictable excel output every time.

How to troubleshoot an Excel output to find the field causing the problem. This section will aid you in working out kinks in your ouput AFTER you perfomed the above steps to convert the report to pts.

• Carefuly examine the column labeling in excel and find a letter that skips... for example you would have comuns A, B, D. What happened to C?
• move the mouse gently between B and D and expand out the column and wala! There is C!
• After you expanded C out highlight that column so you select all record's C value.
• Next you have to hunt by clicking in different rows to find the box that has a clickable "C" region that is NOT merged.
• This is the rotten apple. examine that field closely in your report layout and make sure that the conversion was correct and that it lines up with another field.
• Continue the process until no hidden columns are found.