SSRS export to Excel converts DateTime Column to Text Column

Recently we observed a weird issue with SSRS export to Excel. One of the datetime column in the report was formatted using the FORMAT function as “MM-dd-yyyy HH:mm:ss” (24 hrs format). However when this report was exported to Excel, this column was converted as Text column instead of DateTime.

In the below screenshots, the first one depicts the report and the second one is the Excel output of the same.

Image

Image

Note the highlighted in red, the DateTime column is treated as General (Text) not as DateTime, this removes the ability to pivot or apply some formula on that column.

After couple of hours I came across an article (unfortunately forgot the URL :( ) which said that using FORMAT function removes the actual data type of the column while exporting. Instead it suggested to use FORMAT property (as in below screenshot)

Image

That trick worked. Below screenshot is from the Excel export of the report where the formatting was done using the Property and it behaved as expected.

Image

 

 

 

Posted in Uncategorized | Leave a comment

Mirrored Database as SSRS Data Source

Recently one of our report started to fail with error like Unable to Connect to Database error. Then only we came to know that the said database is in a mirror setup. In the mirrored environment, we have to always connect to Principal, Mirrored database will be inaccessible to the applications.  When the failover happens, Principal becomes Mirror and vice versa. And this flip can happen automatically also.

So our reports should be intelligent enough to identify which one is Principal. If it tries to access the Mirror, then it will error out. However SSRS spares us from this headache. The only thing we need to tell SSRS is which servers form Principal/Mirror pair. And this information has to be mentioned in the connection string itself like below

Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=DB1

Thats it. When failover happens SSRS will do a retry, if required, and connect to the appropriate Principal Server.

Posted in SSRS | Tagged | Leave a comment

SSRS: FORMAT option for milliseconds

Some of our reports have hyperlink enabled on few metrics which points to an internal website. Recently there was a change in the internal website which required us to send the a particular Date as a parameter. The page was expecting the milliseconds also.

This looked simple as we thought of using FORMAT function, but we could not find the option to include milliseconds in FORMAT function. We spent a couple of hours going thru different blogs but most of them stopped with seconds.

Finally in one of the forum, in an unrelated post, we came across the format for milliseconds.  And it is nothing but “f” :)

=format(now(),”MMM/dd/yyyy hh:mm:ss:fff”)

returns

Posted in SSRS | 4 Comments

SQL SERVER Replication – Troubleshooting Steps for Beginners

Few days back there was a replication issue in one of the QA servers. Since the DBA was out of office that day, I was called in to look into the issue. Though I am not an expert in replication, I was able to identify the issue. Just wanted to share the troubleshooting steps that I performed. This is for beginners only. If you are an experienced DBA with Replication in your arsenal, please review and correct, if anything is interpreted wrongly.

Replication has 3 components, Publisher , Subscriber and Distributor. In simple term Publisher is the source of data, Subscriber is the target (one which receives the data) and Distributor is the facilitator. You can read about the Replication terminology here.

My QA was able to give me the publisher server name. So  I had to

(a) find out the Distributor and Subscriber server.

(b) find out what are all replicated

(c) find out where the error occurred, in Publisher or Distributor or Subscriber

I logged into the Publisher to figure out the servers of Distributor and Subscriber. In the SSMS you can  find a separate folder for Replication

Right clicking on the Replication folder brought up the below property screen, which tells where the Distributor server is located. Note that Distributor may be on the Publisher itself or in another SQL SERVER.

Clicking on the “Publication Databases” displays what are all databases published from this server. Also if you note carefully, it gives you the type of Replication also. It is not necessary that all the tables or rows or columns are published in a single publication.  We can set up  multiple replication. We have to identify in which replication (publication) our objects are published. You can get this by expanding the “Local Publication” under Replication folder in SSMS.

The “Articles” lets you know the objects that are published under the specific publication.

The other property that got my attention was whether the subscription is push/pull subscription and also whether any schema changes are also published.

It took me a while to figure out the Subscriber, however in the process I noticed one more tool, “Replication Monitor”. You can launch the Replication Monitor from SSMS by right clicking on “Replication” folder.

In the Replication Monitor you could see your publisher listed. Just click on the Publisher and in the right side window, you will see all the Subscriber listed

In the above screenshot, the  Subscriber is in error status. Just double-click on it to the subscription history.

Note that in the above screen, you can find 3 tabs, Publisher to Distributor, Distributor to Subscriber and Undistributed Commands. In a single screen you can find, not just the status but the reason of failure, of all the publication from the current publisher.

Posted in Replication, SQL SERVER | 1 Comment

Converting SQL SERVER DateTime? Test it thoroughly….

Today one of my user complained that a report was missing one particular ticket from the output. The report in question is very simple, it accepts start date & end date and filters on the ticket creation date time column to get the list of tickets.

The report query was something like

Select * from table1 where convert(datetime,CONVERT (int, CreatedDate)) between ‘2012-01-01′ AND ‘2012-01-31′

Cursory look at the query looked fine. However I was proved wrong when I ran the query in SSMS. It was missing a ticket whose CreatedDate  was ‘2012-01-31 18:57′

After some troubleshooting , I figured out that the issue was due to the conversion of CreatedDate to INT. In the query, the date was first converted to INT to remove the time part and then again converted to date time for comparison.

When the time part is in PM, the conversion to INT, converts it to next day. The below screenshot will explain it more precisely

Handling DateTime in SQL SERVER is very tricky especially when you are some processing like conversion, finding datediff etc. The only option we have is to test it out thoroughly.

Posted in T-SQL | 10 Comments

Passing BIGINT data to SSRS Sub Report

Few days back one of my team mate complained to me that one particular sub report is failing randomly with

Error: Subreport could not be shown

The particular sub report was called from another report’s tablix control column. It accepts  a parameter whose base data type in SQL SERVER database was BIGINT. Since SSRS does not have BIGINT, the parameter type was set to FLOAT.

As mentioned by my colleague, the sub-report was failing for some particular rows. I thought this might be an issue due to mis-handled scenario in the sub-report. I went thru the code and everything worked fine. I just ran the sub-report as stand-alone and that too worked fine. Spent more that 4 hrs and tried many things but neither could find the issue nor get the report working.

At last I was able to identify the issue. When we pass a BIGINT value as a parameter to SSRS sub-report, even though the parameter type is float seems it does not accept the value.

Here is what I did. I created a main report (Report2), with one parameter. I am passing this parameter to a dataset which in-turn inserts it into a temp table. This dataset is binded to a tablix, which calls a sub-report (Report3) and passes this value.

The sub-report just prints the value.

When I supplied a value , say 2127762734, it worked fine.

However when I supplied a value , say 2327762734, it failed even though the sub report parameter was set to FLOAT.

This seems strange and I could not find the root cause. Another strange thing I observed is that when the value is hard-coded (instead of inserting into temp table) it worked fine like in the below screenshot.

 

Posted in SQL SERVER, SSRS | 1 Comment

SSRS 2008 Error – The report definition is not valid

Recently I downloaded a file from one of our production server, did some changes and reviewed it in BIDS and everything looked good. However when I tried to upload the file in the Report Manager got a strange error

The report definition is not valid. Details: ‘.’, hexadecimal value 0x00, is an invalid character. Line 1570, position 10. (rsInvalidReportDefinition)

Since the error message was talking about Report Definition is not valid, checked whether I have corrected the report in appropriate version (we have SSRS 2005, 2008 & R2).

I opened the code behind of the RDL by clicking the “View Code” option in BIDS and looked at the line mentioned in the error message. I was surprised to see few blank spaces after the end of the Report tag and BIDS had highlighted it as an error.

I just simply deleted it, saved and built. Then it worked fine. However I still could not figure out how it was added :(

Posted in SSMS | Leave a comment