SSRS #67 – Custom Date and Time Format Strings

When it comes to using the correct format strings for Date and Time in SSRS, many Reporting Services developers wonder where to find the valid custom Date and Time styles (or strings).

I have several SSRS books. They all give some expression examples on how to format Date and Time, but none of them mentioned where we can find all the valid Date and Time format styles (or strings).

Custom Date and Time Format Strings for VB.NET or C#.NET code

Because scripting in Reporting Services uses VB.NET or C#.NET code, so the right place to look for those format strings is VB.NET or C#.NET documentations.

Custom Date and Time Format Strings

The above link is to the Microsoft MSDN library for .NET Framework 4. It has all the mystery strings we are looking for.

Here is a part from the above documentation showing upper case M and lower case m. Upper case M is clearly reserved for month, and lower case m is reserved for minute.

Here is the part that shows lower case t is used for AM/PM designator.

Two Examples of Custom Date and Time Formatting

This example from one of my SSRS reports uses the Format() function to format the Time portion of a DateTime field. The first portion is simply formatting the time, and the second portion adds 30 minutes first then formats it.

 

Here is the result.

The second example is a little different from the first example. It comes from a SSRS report that uses MDX query against an Analysis Services data source. It takes a DateTime user input value, and format it, and then convert it to a valid MDX member value for a Date dimension.

CDate() function is used first to convert the parameter to Date data type. Format() function then formats the value to the date format that is the same as the format in the Date dimension. Then I prefix the appropriate level names and dimension name (the dimension’s name is actually Time). Now the result will be a perfectly acceptable MDX member value.

Here is the result.

With the above link to the custom Date and Time format strings, now we have no excuses but to say yes to our users when it comes to their wild Date and Time formatting requirements. Just remember, the format strings are case sensitive. As a matter of fact, the entire Reporting Services environment is case sensitive.

Have fun with formatting!

SSRS #66 – Reasons that you will need to modify the parameter queries

After I posted my blog SSRS & SSAS #63 – Stop SSRS 2008 MDX Query Designer from Overwriting Parameter Queries, a reader asked me why I wanted to modify the out-of-box parameter queries.

Here are the major reasons that I need to write customized parameter queries.

1. I want to use the Date calendar in SSRS, and allow users to select a date range.

The default date parameter that is generated from the MDX query designer uses a regular textbox, and does not allow users to select a begin date and end date. In order to use the Date calendar in SSRS, I will need to do a few customization. I’ll blog about how I did that when I get time in the future.

2. To implement cascading parameters, the auto-generated parameter queries must be customized.   

Again the default parameter query that is generated from the MDX query designer most likely does not meet my need for cascading parameters. In most cases, it is not using the right parameters I want to use. Modification of the queries are straightforward, but it does require changes in the default MDX query. 

3. Another major reason for the customization is for performance improvement

In the financial industry, the amount of data can reach an unmanageable level very quickly. Performance of the SSRS reports is a constant concern. Customizing my MDX queries for optimal performance is always on the back of mind, and it’s a never-ending task.   

When I get time in the future, I’ll try to share a few examples of the customized parameter queries.

SSIS #108 – The Makeover of the Lookup Data Flow Component in SSIS 2008

Lookup data flow component is a very popular design pattern in SSIS. The idea  is to determine whether a record in the pipeline already exists in the intended destination table or not, and then decide the next action.

SSIS has many out-of-box components that are in ready-to-use mode. They can be used in many different scenarios with default configurations and achieve good performance with little tuning on our side. The Lookup data flow component is one of them, until you run into a surprising situation where the data flow takes much longer than you have anticipated. Configuration of the Lookup component is simple enough. After you have passed the first step of configuring the component, the main focus is on how to achieve the optimal performance.

The makeover of the Lookup Component in SSIS 2008

As popular as it is, I have not used it a lot in SSIS 2005. In SSIS 2008, however, I was pleasantly surprised by a few improvements.

The new UI make it easier for configuration. The Cache mode now has three different choices. The Connection type now includes a Cache connection manager, and the output now includes a “Redirect rows to no match output”.

Cache mode

The concept of caching is not difficult to understand. When using SSIS data flow components, Integration Services load the necessary data into the memory. When not all the data can be loaded into the memory at once, Integration Services load and process data one buffer at a time. See Understanding SSIS Data Flow Buffers on MSDN. The Cache mode tells Integration Services your choice of whether to load and process the lookup data all at once into memory, not at all, or somewhere between. Full cache, in most cases, will give you the optimal performance since all the lookup data will be loaded into the memory.   

Cache connection manager

Cache mode is not really a new concept in SSIS 2008. Cache connection, however, is a major improvement in SSIS 2008. Now, we are able to use a cache for our lookup data prior to the the execution of the dataflow. Cache Connection  is a new connection manager type in SSIS 2008.

With this new type of connection type in SSIS 2008, now we are able to populate our cache many different types of sources, such as text files, raw files, XML files, Excel files, a recordset, or anything that can be accessed using an ADO.Net provider. This is compared to SSIS 2005 where we were limited to use an OLE DB source. This means that the cache can be used in multiple lookups. Imagine when using the Foreach iteration in a loop in SSIS 2005, we would repopulate the cache for every iteration.In SSIS 2008, the cache needs only to be populated once. If you had performance issue with your Lookup components in SSIS 2005, now it’s time to re-examine it. The new cache connection manager might be a life saver.

Creating a new Cache connection is simple. Select New Connection, and then CACHE.

Redirect rows to no match output

Another improvement is the addition of Redirect rows to no match output. In SSIS 2005, unmatched rows must be redirected as error output. This is a cosmetic improvement, but it does make me smile when I do not need to use the red error output.

In one of my usages of the Lookup component, I would only take the unmatched records,and insert them into the destination table.

In the Connection tab, I’d use a query (instead of data from the entire table) to get only the lookup column.

The DayID is also the only column I need for output.

The new improvements in the Lookup components in SSIS 2008, especially the Cache connection type, makes it using the Lookup component much more fun.

Book Review – Interview Questions and Answers for Database Developers and Administrators

Recently I had privilege of reviewing a book “SQL Server Interview Questions and Answers”. Two authors Pinal Dave and Vinod Kumar are well-respected names in SQL Server community. I decided to write a book review with hope that database professionals can take full advantage of this book and ace that SQL Server interview.

I have worked with database applications for most of my career, and have been a SQL Server database developer for the last fifteen years, with specialization in Microsoft Business Intelligence development since 2006. I always thought SQL database development is my choice of career. However, by 2005, due to numerous family relocation that crossed two countries and 5 states and provinces in 4 years, I started to struggle with my career.  I bumped from one contract to another, and endured (and failed) many grueling job interviews.

In one of such interviews, I was ushered into a conference room with 6 young IT professionals already sitting in the room with papers in front of them on the long conference table. One of them, the leader I assumed, started introduction. Before the lengthy introduction ended, I realized that these 6 young but serious-looking IT processionals weren’t the only interviewers I had to face. There were 4 other interviewers whom I would not be able see face to face, but I could hear them on the speaker phone. Three of them are developers specialized in certain areas and one is the hiring manager.

Needleless to say that I failed that interview miserably. It wasn’t until several failed job interviews that I began to see a pattern in the interview questions. That is they don’t ask tricky questions. They ask database concepts with SQL server, and expect accurate and complete answers with real-life examples.

I immediately started to collect every SQL interview question I could find online, and immersed myself in questions that I could not articulate very well, and expanded on the questions that I was personally interested in.

Looking back several years later, I wish I had a book like “SQL Server Interview Questions and Answers”. It has exactly what most interviewers are looking for in your answers, SQL server database concepts, accurate and complete answers with real-life examples.

Study the Interview Questions and Answers for Job Interviews

While talking to a co-worker who had to start a job interview process again, she mentioned to me that interviewers are really looking for textbook-style answers. She decided to spend a few months to study for a Microsoft SQL Server certificate exam, hoping that by the time she passes the certificate exam, she would be able to ace that job interview. I wish I knew about the book at the time so I could recommend the book to her.

Where can you get a copy?

You can go to this page SQL Server Interview Questions and Answers on beyondrelational.com to get a copy of this book.

Enjoy and good luck!