So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...

Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown as:

copyraw
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
  1.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) 

The other formats are:
copyraw
=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)
  1.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate) 
  2.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate) 
  3.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) 
  4.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime) 
  5.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime) 

Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then ignore all the above.

Another built-in function is the text-formatter
Category: Transact-SQL :: Article: 317

Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I tried to convert a date in SQL format to a Month name.
copyraw
An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.
  1.  An error occurred during local report processing. 
  2.  An error has occurred during report processing. 
  3.  The Group expression used in grouping 'table1_month' returned a data type that is not valid. 
Category: Transact-SQL :: Article: 313

Well I find myself again the dummie of the Internet.  I basically came across this error and STFW'd for ages following complex T-SQL Timestamp conversions but to no avail.  Not saying that you shouldn't try their solutions but just check you haven't done this silly mistake.

I'm only guessing the same error would happen in Report Builder 2.0 which I've stopped using as my day job wants us to use Business Intelligence Development Studio for all our SQL Server Analysis Service (SSAS) and Reporting Service (SSRS) projects/solutions.  The features and interface are very similar when developing reports though.

The error I'd get was

An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset DataSet1.
Conversion failed when converting date and/or time from character string.

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

system   license   creator   need   where   database   google   time   script   note   website   joomla   added   following   name   first   form   windows   create   would   report   date   parameter   deluge   order   version   files   zoho   user   case   work   data   list   find   display   table   using   function   used   page   source   mysql   code   uploaded   field   error   file   client   server   value   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.