Download reports from Sql Server Reporting Services

I know that this is what the reporting services didn’t give us an available option. There are many scenarios we require to get the reports to the local server and edit the changes. For example, I have 10 reports and they aren’t available right now in my server or they are under source control and it is corrupted. Then only place they are available is Reporting services. From web service we can view the reports but cannot get them to the local machine. 
By using Report Manager site, we can download one report at a time but not all reports. So, I found a way which is really helpful and saves so much of time. Thanks to Gaurav, my colleague for telling me about this.
There is a free tool available for this. Which is called “Reporting Services Scripter”. This is very good tool. Which needs the report web service url and it will bring up all the reports in the window. Once you select what reports you needed and give save location, hit on script button, that’s it. It will download all selected reports to the given location.

Download it here. http://www.sqldbatips.com/showarticle.asp?ID=62
OR
http://web.archive.org/web/20120830141644/http://www.sqldbatips.com/showarticle.asp?ID=62

Remember that, before you do anything please make sure you have given the correct report web service url as shown below.

From options, you will see above screen. And from Servers tab, you have to give the correct servername.
Note: The RS Scriptor is using default windows logged in credentials. So, make sure the user logged  in has access to the reports and the user role should be “Content Manager”.

SSRS Reports get Month name from Month Index or from date

This is one of the small tips which I want to tell my readers. When you work with SSRS reports, you may come across with the requirement like, you know the index of the month, but you want to print month name from it. So, if that is the situation for you, this is the right post for you. :)

MonthName(monthindex) 

You may get it from t-SQL as I mentioned in my previous post here.
But in SSRS reports the way you  need to approach is like this.

1. Take a textbox to report body.
2. Write the below syntax into it [As you know, it is the expression].

=MonthName(7)

In any case, you need to get it by date then you still use this below function.

=MonthName(Month(Fields!datefield.Value))

Hope this helps and please let me know, if you face any problems.

Sql Server reporting services and Forms authentication – Part 3 – Linking aspnetdb to report server

I think, you have read my previous two posts related to implementing forms authentication in report server.

Sql server reporting server and forms authentication – Part 1 – Understanding the concept.

Sql Server reporting server an forms authentication – Part 2 – How to implement.

I just followed Russell’s article to link sql server report server to aspnetdb. So, I prefer to redirect my users to the same post. I don't want to repeat the whole story here again. So, please go here and continue your reading.

http://blogs.msdn.com/bimusings/archive/2005/12/05/using-sql-reporting-services-2005-and-forms-authentication-with-the-whidbey-2-0-sqlmembershipprovider.aspx

Hope with this post, you are completely done how to use, implement and linking the default aspnetdb to the report server in forms authentication.

Sql Server Reporting services and forms authentication – Part2 - How to implement?

Before continue this post, please read the post understanding the concept forms authentication in sql reports – Part1. The implementation of the process is so simple.

• Microsoft team already given a sample application which has the forms authentication enabled.
• They have written some code which has a security extension with login pages.
• To understand it well or to implement just do this. Go here and download the sql server samples from Codeplex. http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000
• The file SqlServerSamples.msi contains all the example types for sql server.
• Download the file and start installing them.
• After installed the file successfully, go to the location where it installed [Usually if it is sql server 2005 then the location would be C:\Program Files\Microsoft SQL Server\90.] and the folder named "samples".
• And in the samples folder we need to consider the sample "Samples\Reporting Services\Extension Samples\FormsAuthentication Sample".
• This sample has all the code we needed.
• Now, before proceed open the Readme file found in the location mentioned above.
• This is the time we actually start the implementation. Please do as is mentioned in the readme file without anything missed.

The above process will take around half an hour to configure everything correct. And if we do everything correct, then you are able to see the report server running using forms authentication.

What to remember while implementing the process?

• The connection string in the sample code. The sample code contains has the connection string as shown below.

  using (SqlConnection conn = new SqlConnection("Server=localhost;" +
                "Integrated Security=SSPI;" + "database=UserAccounts"))

• So be sure you installed the useraccounts database in the default instance of the sql server and the windows account you have logged in has access to it.

Now, you have everything installed and setup the forms authentication in sql server reports.

Hope you enjoyed the post. Please let me know your feedback and any questions if you have.

Sql Server Reporting services 2008 Tablix control

When working with Sql server 2008 reporting services I found a new control named Tablix. [Of course there are plenty of new features] This is a mix of both Table and a Matrix. The main and great advantage of it is when we drag a table or matrix to the report body you can't find table or matrix any more. The report control automatically convert it to Tablix. Now, it's very easy to show the complex and grouping of data with this control.

The interest thing in 2008 reporting services is, When I open report builder 2008, BIDS 2008 I didn't find the Tablix control at all and started researching for the control. Stupidly searched in google like download tablix control ssrs 2008 etc… But, once I drap and drop the table or matrix and right clicked for properties found "tablix properties" in list of properties there. Then understood the whole concept and implemented the reports very fast.

Hope this post will help you understand it. Let me know what you think.

Expand Collapse columns in a table SSRS

Problem:
How to make columns collapsible, expandable in a table Sql server reporting services.

Solution:
I asked to some people, how to do this, lot of people proposed me to use matrix. But my data is simple data and has 14 columns like first name, last name, state, city, zipcode, q1, q2, q3, h1,h2, h3 etc… I need to display the columns q1, q2, q3; h1,h2,h3 expansible and collapsible. By default when user see the report, i need to show only first name, last name, state, city, zipcode, q1, h1 columns. But the q1, h1 columns with expand, collapse symbols. When any one clicked to expand i need to show the other columns like q2,q3, and same for h2, h3.
Below is the solution what I have followed to make it work. I didn't use any matrix here, only tables.

1. Select all the columns that you want to make expandable, collapsible.
2. Hit F4 [Properties window] –> Select Visibility, and in that, set these values, Hidden = true and ToggleItem = ID of the textbox [Where you want to show the Expand[+], Collpse[-] symbols that text box id. in other words parent text box id.]
3. That's it. A simple solution to the issue.