Archive for the ‘Uncategorized’ Category

My latest project is to create some custom reports for a vendor application, for the I.T Director. I thought it would be nice to have a common layout for all the reports to have consistency in look and feel. Guess what, there is a way to do that in SSRS.

To create a custom template, you have to design a report as you will normally do in BIDS/VS. Design the header and the footer, adding the title and images that you would be using in all the reports.

Once this is done, you need to save it in an appropriate location, so that when you create a new report you get this template in the installed templates list.

Locations to save the templates according to SQL Server versions

For SQL Server 2005
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2008
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2012

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Once you save the custom template in the appropriate location, your custom template appears in the templates list and you can use that to build your report.

SPReports

For one of my projects I had to get the results of the sql query in an XML format.  I knew SQL Server supported the feature to get results in XML but I didn’t know how to do that. Googled it and found the easy solution.  Append  “FOR XML PATH(‘Path Name’), ROOT (‘root name’)”  

My actual query is a rather convoluted one but suppose there is a table called tbl_documents and I have to get all the data in that table in an xml format. I would use the query

select * from tbl_document FOR XML PATH(‘Document’), ROOT (‘Documents’)

The result would look like this

 

<Documents>
<Document>
<document_id>1</document_id>
<document_order>82</document_order>
<document_parent_id>0</document_parent_id>
<document_display>0</document_display>
<document_name>Doc1</document_name>
</Document>
<Document>
<document_id>2</document_id>
<document_order>81</document_order>
<document_parent_id>0</document_parent_id>
<document_display>0</document_display>
<document_name>Doc2</document_name>
</Document>

</Documents>

 

 

 

Found out an amazing article about how to progress in the software development field

https://medium.com/management-the-art-and-the-science/e1f14edb864d

While testing/debugging a c0nsole application developed in dotnet, I was genuinely baffled.. How do I pass arguments while debugging the application? Everytime I had to test the application I had to compile it, run the CMD and call the application with the parameters from the console, which was time consuming. I needed to find out something that would do the trick withing the Visual Studio application itself.

As always I turned to Mr.Google and the ever-dependable fellow pointed me to the right direction.

Looks like Microsoft has a solution after all..

Goto Project->Properties and click the Debug Tab. There is a section for command line arguments. Enter the arguments and voila!

You can read more about it here.

 

 

Upgrading Reporting Services

Posted: February 6, 2014 in Uncategorized

My most recent project involves upgrading reports that are developed in RS 2005 to RS 2012. So I was curious about what really happens when you deploy an RDL file to a Reporting Server. So here are some of the stuff that happens in the background when you deply an RDL file.

  • The RDL is stored as a BLOB in the Report Server Database. It is not stored as a file.
  • The Report Server alse stores a compiled version of the RDL file

Curious about the table that stores the RDL file info?
It is the Catalog table in ReportServer database.

The XML schema is stored in a field called content and you can retrieve the contents from the ‘content’ filed and convert it back to XML schema using the following SQL code.
select *,convert(varchar(max), convert(varbinary(max), content)) as xmlvalue
from catalog
where content is not null

Check out the following link to get more info on catalog table and retreiving information from the catalog table,
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/

So back to the upgradation part..
Turns out you can upgrade the RDL files in two different ways.. You can open the solution in the designer and the designer will upgrade all the reports in the solution for you.. This is mostly a painless process although there are some areas where you will find some issues..I should also mention that this is the only way to modify the actual RDL file.

The second option is to actually run the report from the report manager or the application which is accessing the reports. The first time the reports are run, they are compiled into the version of Report Server. For example if you have a RS 2005 report and you ran it on a server that has RS2012, the report is recombiled to match the 2012 version. If the upgrade was successful, the report will run as a 2012 report, if there were any issues the report will run as a 2005 report in backward combatibility mode.

One important thing to note about the section option is that the actual RDL file does not change. When you retrieve it from the report server, you’ll get the original RS 2005 RDL you published to the server. This is handy if you need to get back the original report in future.

So the question is, how does Report Server display the upgraded report if the original RDL file is unchanged? Looks like what happens in the background is that the first time the report is accessed after the upgrade the report is recompiled to match out new schema. This is done on demand. So if you have a report that is never run, its compiled format is never updated.

Need more info? Read http://blogs.msdn.com/b/lukaszp/archive/2006/02/20/upgrading-reporting-services-what-about-my-report-definitions-rdls.aspx