Customize ReportViewerWebPart in C# and render in SharePoint Pages

This is one of the major milestone I have achieved recently to customize the report viewer web part for SharePoint sites. The issue I was facing: the SharePoint site which I have developed was too complex and it exposed via 3 zones. http://intranetsite, http://extranetsite, https://internetsite

1. http://intranetsite – which is Windows based authentication site and for intranet people.
2. http://extranetsite – Which is Windows based authentication site and for extranet people
3. http://internetsite – Which is Forms based authentication site and for internet people.

For each sub site in our implementation it should show the SSRS dashboard report of the site we are in which will contains all information of the site through reports. But, SSRS reporting services and report viewer web part has a limitation in SharePoint integration mode:

System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.

For example, you have added a report viewer web part to a SharePoint page. And when you opened the site in any other zones other than default zone then you will see above exception. So, how to solve this problem??? No way without customizing the default ReportViewerWebPart. So, I chosen this method and the implementation I have done is working very well.

Implementation:

1. Create a simple C# Project in Visual Studio to create a web part.
2. The web part contains logic to render Report Viewer Web Part.
3. The Report Viewer Web Part will take the default zone web url to render reports.
4. Supply report parameters to the report viewer.
5. Add any properties to the report viewer web part like toolbar mode, document map mode etc.

CODE:

public class CustomReportViewerWebPart : System.Web.UI.WebControls.WebParts.WebPart
{
#region Properties

#endregion // Properties

#region Constructors

public CustomReportViewerWebPart()
{
this.ExportMode = WebPartExportMode.All;
}

#endregion // Constructors

#region Privates

//-----------------------------------------------------------------
//Simple error handler for pre-render subs
//-----------------------------------------------------------------
private void HandleErrors(Exception ex)
{
Page.Response.Write(ex.ToString());
}

#endregion  // Privates

#region Overrides

//-----------------------------------------------------------------
//Render this Web Part to the output parameter specified.
//-----------------------------------------------------------------

protected override void CreateChildControls()
{
base.CreateChildControls();

try
{
ReportViewerWebPart wp = new ReportViewerWebPart();
this.ChromeType = wp.ChromeType = PartChromeType.None;
wp.PromptAreaMode = CollapsibleDisplayMode.Hidden;
wp.ToolBarMode = ToolBarDisplayMode.None;

string defaultZoneURL = ConfigurationManager.AppSettings["SharePoint_Default_Zone_URL"];
if (string.IsNullOrEmpty(defaultZoneURL))
defaultZoneURL = "http://defaultzoneurl";

string reportPath = ConfigurationManager.AppSettings["SP_Report_Path"];
if (string.IsNullOrEmpty(reportPath))
reportPath = "reportpath"; //If it is the same report everywhere then use it. Otherwise create a web part property. So that user can input report path and use it here.

string parameter1 = "parameter1 value"; 

if (!string.IsNullOrEmpty(defaultZoneURL))
{
if (defaultZoneURL.EndsWith("/"))
defaultZoneURL = defaultZoneURL.Trim('/');

wp.ReportPath = string.Format("{0}{1}", defaultZoneURL, reportPath);

ReportParameterDefaultCollection parame = wp.OverrideParameters;
parame.Add(new ReportParameter("Parameter1", parameter1)); //Add all report parameters here.
Height = Unit.Pixel(1000);
wp.Height = Height.ToString(); //If you are using single report everywhere then you can hard-coded the height property. Otherwise leave it.

this.Controls.Add(wp);
}
}
catch (Exception ex)
{
Literal litMsg = new Literal();
litMsg.Text = "There is some problem in rendering the dashboard report. Please try again later." + ex.Message;
this.Controls.Add(litMsg);
}
}        
#endregion //Overrides
}

I believe the above code is simple to understand and you got it. Please let me know if there are any issues in understanding or run this code. I am always here to help.

Conclusion:
With the above code, you can solve the problem of viewing the report in any zone not only other than "SharePoint default Zone".

Limitation:
As we are customizing the report viewer web part through code, we cannot make the webpart works for all reports. If there are one or two reports in your site and they are in use everywhere then this will be a perfect solution. So, I will work on doing this applying for all reports in couple of days and post in this blog.

Note:
The ReportViewerWebPart class will be reside in the namespace "Microsoft.ReportingServices.SharePoint.UI.WebParts" in the DLL "Microsoft.ReportingServices.SharePoint.UI.WebParts.DLL". The DLL will not be available directly through the file system. You have to get it from GAC. To get it, please follow my another post "How to get the files from GAC in Windows".

This will not be a problem in the new version of Sql Server. Sql Server 2008 R2 AAM has solved this problem. So, this solution will be helpful to the people who are still on earlier versions of 2008 R2. 

Read More...

Fix to Report viewer problems in IIS 7 or later

When we migrate web applications from IIS 6 to IIS 7 or IIS 7.5, we will face some problems in http handlers, mappings etc. I faced some problems with reportviewer control. So, below are all problems I have faced and solutions to them. And one more thing is, the server to which we migrated the application may or may not have all the report viewer dlls available in the system. If they didn't install then we need to install them. Otherwise you will get compilation error as we are using that in our application.

First of all, before proceed what are the dlls needed for the report viewer to run?
Microsoft.reportviewer.common.dll
Microsoft.reportviewer.processingobject.dll
Microsoft.reportviewer.webforms.dll
Microsoft.reportviewer.winforms.dll - For windows applications.

So, to get the dlls you need to install the redistributable packages for 2005/2008. Below are details to get that. Find the matched version[2005/2008] and isntall the correct patch. Once installed you will find all above dlls in the GAC.

Microsoft Report Viewer Redistributable 2008
File name : ReportViewer.exe
Version : 9.00.21022.08
Download Size : 2.8 MB

Microsoft Report Viewer Redistributable 2005
File name : ReportViewer.exe
Version : 2007
Download Size : 1.8 MB

Microsoft Report Viewer Redistributable 2005 SP1 (Upgrade)
File name : VS80-KB933137-X86.exe
Version : 1
Download Size : 1.7 MB

Microsoft Report Viewer Redistributable 2005 SP1 (Full Installation)
File name : ReportViewer.exe
Version : 1.0
Download Size : 1.8 MB

ReportViewer Samples for Microsoft Visual Studio 2008
File name : ReportViewerSamples2008.exe
Version : 1.0
Download Size : 172 KB

ReportViewer Samples for Visual Studio 2005
File name : ReportViewerSamples.exe
Version : 1.0
Download Size : 173 KB

With the installed patch, you can solve the compilation error. But, the report viewer control won't render correctly on the browser. What are the possible problems come?

1. 'RSClientController' is undefined
2. Report resource images are not coming or loading.
3. RSParamaters  not defined.
4. ReportViewerHoverButton not defined etc...
5. Other javascript errors which caused the report viewer failed to load correct.

The only fix to the problems are below.
Solution:
We need to configure the report viewer auto generated axd files[Reserved.ReportViewerWebControl.axd] in the IIS.
See below pictures to understand it well.
1. Open the IIS by typing the "inetmgr" in run command.
2. Goto your site in the list of web sites and select it as shown below.

3. Now, see the Features view area. Here, you can see all options available for the web site. From all the options select "Handler Mappings".

4.  Now, click on the "Handler Mappings" section then you will see all the mappings applied for that web site. Now, on the right side panel, you are able to see all the options available for the handler mappings. Select the option "Add Managed Handler" as shown below.

5. When you click that link, you will see a popup window with the text boxes as shown below.
Fill, Path = Reserved.ReportViewerWebControl.axd,
Type = Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Name= Reserved.ReportViewerWebControl.axd

Note: Please change the version number[9.0.0.0] in the Type above given depends on your requirement.
6. Click OK and do IISRESET.
Now, browse the page where you have report viewer control. And you see no more issues and everything renders fine. Hope this will help you to understand well and please let me know, if you have any questions or issues. Read More...

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. Read More...

Report viewer control authentication – Part 2 – Forms Authentication

If the reporting services configured to use forms authentication and you need to show the reports in the custom developed applications then the need of processing authentication the report viewer control through the code.
Report viewer control authentication using windows authentication is described in the part 1 here. Now, we will discuss the authenticating forms authentication through C# code.
If we are are developing windows application and want to use report viewer control, then we need to implement the below logic to authenticate.

reportViewer1.ServerReport.ReportServerCredentials.SetFormsCredentials(null, "userName", "password", "");
this.reportViewer1.RefreshReport();

Where as in forms authentication simply assigning the credentials not enough. The reasons behind are, security, code efficiency, performance, response time etc everything come into the picture. So, we need to write code which supports everything.
What are the major tasks?

• Report viewer control excepts the credential of type IReportServerCredentials. So, we need to create an object which inherits from this interface and pass this to the report viewer control to authenticate.
• Handling cookie. Based on the login and request is successful we will write the cookie to browser and keep that in browser for further requests processing. The advantage is if cookie is available then won’t make any request to report server for authenticating.
• To create the cookie related information we actually need of hijack the request and response and get the cookie information and save it to browser. So, how to catch the request and response which made to report server? We will discuss this later in this article.
• To actually communicate to the report server, we need to make the communication with it. The best way to do that is using web services. Everyone knows that reports in SSRS gets with two sites. One is report manager means report web application [/reports] and the report server means a report web service[/reportserver]. So, we will use the web service, write a proxy and implement the existing functions in it.

I think, it is little bit complex to understand the above tasks. But actually they are very simple to implement.


Code needed: We need two classes to get what we need. These are custom classes and add them in single file named ReportServerCredentials.cs somewhere in the project.

• Classes needed - ReportServerCredentials and ReportingService.

public class ReportServerCredentials : IReportServerCredentials
{
private Cookie m_authCookie;
public ReportServerCredentials(Cookie authCookie)
{
m_authCookie = authCookie;
}
public WindowsIdentity ImpersonationUser
{
get
{
return null;  // Use default identity.
}
}
public ICredentials NetworkCredentials
{
get
{
return null;  // Not using NetworkCredentials to authenticate.
}
}
public bool GetFormsCredentials(out Cookie authCookie,
out string user, out string password, out string authority)
{
authCookie = null;
user = ConfigurationManager.AppSettings["ReportServerUserName"];
password = ConfigurationManager.AppSettings["ReportServerPassword"];
authority = "";
return true;  // Use forms credentials to authenticate.
}
}
public class MyReportingService : rsExecutionReference.ReportExecutionService
{
private Cookie m_authCookie;
public Cookie AuthCookie
{
get
{
return m_authCookie;
}
}
protected override WebRequest GetWebRequest(Uri uri)
{
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(uri);
request.CookieContainer = new CookieContainer();
if (m_authCookie != null)
request.CookieContainer.Add(m_authCookie);
return request;
}
protected override WebResponse GetWebResponse(WebRequest request)
{
WebResponse response = base.GetWebResponse(request);
string cookieName = response.Headers["RSAuthenticationHeader"];
if (cookieName != null)
{
HttpWebResponse webResponse = (HttpWebResponse)response;
m_authCookie = webResponse.Cookies[cookieName];
}
return response;
}
}

A small explanation:
ReportServerCredentials class is the class which is inheriting from the IReportServerCredentials interface.

• If we are using the impersonation then the first property will be used.
• If we are passing the custom network credentials then the second property will be used.
• If we are using forms authentication then the method GetFormsCredentials() will be used.

ReportingService is the class which is inheriting from the ReportExecutionService web service. The question in your mind now is what is the "rsExecutionReference". This is the web reference to the report server web service. To get this, right click on the web application/web site and select the option "Add web reference". Now, in the input box, enter the report server url ending with "ReportExecution2005.asmx". For example, if your report server is at http://localhost/reportserver then the web service location will be http://localhost/reportserver/ReportExecution2005.asmx. Add the web reference to project or site. Now, we have the web service proxy created in the solution. So, in my code rsExecutionReference is nothing but the web service proxy for the report server web service.
The web service has two methods already implemented GetWebRequest() and GetWebResponse(). So, we are overriding them in our code to catch the cookie ticket information which is returned by the report server. If you observe the GetWebResponse() code, we are actually checking for the header information from the response and in the response we are catching the cookie we need. If cookie is null means the request authentication failed and means invalid credentials passed.
I think, till now what I have mentioned is clear for you and we are done with contacting or communicating with the web service. Now, where we are calling the web service or in other words where the authentication request started? It's not yet. Check the below code and notes for it.
I have a Utility class in my application where I place all the util related code in it. For example purpose, I am assuming this method you have placed in Utility class  [If no Utility class in your application then create one and place this below method in it].

public static HttpCookie AuthenticateReportServerAccess()
{
MyReportingService svc = new MyReportingService();
svc.Url = ConfigurationManager.AppSettings["ReportServerWebSeriveUrl"];
HttpCookie hcookie = null;
try
{
svc.LogonUser(ConfigurationManager.AppSettings["ReportServerUserName"],
ConfigurationManager.AppSettings["ReportServerPassword"], null);
Cookie myAuthCookie = svc.AuthCookie;
if (myAuthCookie == null)
{
//Message.Text = "Logon failed";
}
else
{
hcookie = new HttpCookie(myAuthCookie.Name, myAuthCookie.Value);
HttpContext.Current.Response.Cookies.Add(hcookie);
}
}
catch (Exception ex)
{
//Message.Text = "Logon failed: " + ex.Message;
}
return hcookie;
}

What this method is doing? A little explanation is here. We need to make or send a request to report server for authenticating the current request to load a report. So, I am using this method for that. I have a proxy class as described above and based on that I have created my own custom class named ReportingService. So, I am using that to make the call by using the built in function exists in the web service named LogonUser(). Which is actually takes three parameters named username, password and authority. Authority is optional, the specific authority to use when authenticating a user. For example, a Windows domain, some name to make distinct the call for that user. Pass a value of null to omit this argument. This will make call to the report server. Because of we override the request and response methods in proxy, it will come to those method and executes all the code in them. So, in the method AuthenticateReportServerAccess() we are making request and getting the cookie. If wrong credentials passed then it means cookie is null. So, you can write your own logic there like throw exception or show some message, If you have any login page implemented for reporting then redirecting the user there etc… If cookie exist then Add that cookie to the response object.
Now, we are done with making a call and processing it and loading cookie. Now what? What we left with… See below.
In the page where we have placed the report viewer control, there add the below code. The below code you can write may be in onclick event of something or when page loads depends on your requirement.

HttpCookie cookie = Request.Cookies["sqlAuthCookie"];
if (cookie == null)
{
cookie = Util.AuthenticateReportServerAccess();
}
if(cookie != null)            {
//Add logic to pass parameters if needed.

reportViewer1.ServerReport.ReportServerUrl = new Uri("reportserverurl");
reportViewer1.ProcessingMode = ProcessingMode.Remote;
reportViewer1.ServerReport.ReportPath = "reportPath";
Cookie authCookie = new Cookie(cookie.Name, cookie.Value);
authCookie.Domain = Request.Url.Host;
reportViewer1.ServerReport.ReportServerCredentials =
new ReportServerCredentials(authCookie);           
//If any parameters then reportViewer1.ServerReport.SetParameters(reportParams);
}

What is happening in the code? We are checking whether the cookie already exist means user is already authenticated and if it null then we are sending request to report server and loading the cookie to browser. If cookie exists then it will go to second if condition and loads the report in the report viewer control by passing the cookie information to report server.
OHHH… What else? we are done with complete coding. We are left with testing the code.
Note: If you observe the code, there are some places we are getting the appsettings keys from the web.config.

• ReportServerWebSeriveUrl: Report server web service url like http://localhost/reportserver/ReportExecution2005.asmx
• ReportServerUserName: Report server username
• ReportServerPassword: Report server password.

Hope you are well understood and not with many questions in mind at this stage. Please feel free to ask me if you have any questions. Love to hear comments. Read More...

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.

Read More...

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.

Read More...

Sql Server reporting services and forms authentication - Part1

Till now, I know Sql Server reporting services supports only Windows authentication. And of course till now What I did for all clients is the same. The roles or access everything will define using the default report manager site and deploy that on the client environment. But, the current project I am working on is completely different and the main requirement is enabling forms authentication in the sql server reporting services. The story behind is, the client is running different applications based on the aspnetdb [Forms authentication]. So, they want us to run the report server using forms authentication based on the same database. So, how to achieve that? This was the question in my mind till last week. After done research about two days found some nice articles and the way to achieve that. So, I thought of explaining the research, analysis and the way we need to proceed in this article to my readers. So, if you have any requirement like this in future just go ahead and start the work in the same process as explained below. This Part1 describes all about understanding the concept.

To understand the concept:

• Using forms authentication in reporting services: http://msdn.microsoft.com/en-us/library/aa902691%28SQL.80%29.aspx

Here you need to understand one thing that Reporting services in SQL server which comes by default only supports Windows Authentication. There is no default way to make it or turn on forms authentication. To do this, we need to make some custom configurations, changes to web.config and custom coding. But, nothing to worry. It's easy. :)

Reporting services are developed to be extensible. You can write your own extensions and deploy or integrate them to reporting services by making some changes to their config files. So, now we need to concentrate on what is a security extension? Before proceed we need to understand some key points:

• Reporting services uses role-based security system to authorize users.
• Security extension supported is based on an open and existing API.
• Use custom defined authentication or forms authentication if any only if, you need to define different users and roles which is not based on windows accounts.
• Read the security extension API in the documentation given in the above link. This has all the classes and interfaces defined to start coding.

Now, what is the security extension. "A Reporting Services security extension enables the authentication and authorization of users or groups; that is, it enables different users to log into a report server and, based on their identities, perform different tasks or operations."

So from this post I hope you understand what we need to do to enable forms authentication in reporting services.

Read More...

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.

Read More...

Report project template in Visual Studio

This is the question most of the people asking. So, just thought of posting it for my readers.

Report project template is associated with the reporting services in the Sql server. This template is  not an addin to visual studio or a specific download won't be available. You will see the template if and only if you install client tools while installing the Sql Server. So, to get that template, open the sql server setup file and select the client tools option from the list of features available. That's it!!! Let me know, if you have any issues to questions.

Read More...

Data Formatting in sql server reporting services

In Sql server reporting services the formatting the data is pretty much simple as we have plenty of built in options available for percentage, numbers, decimal, currency etc. So, just them to data format and display the data as you want. Below are most commonly use format for the usual reports.

• c – Currency
• d – Decimal
• n – Number
• p – Percentage.

If you want two decimal places in decimal number then in the format option [Text box properties -> format] then the  format should be d2. In percentage if you want two decimal places in percentage then the format is p2 etc.. But, I have tried it some reports, and found not working. Then used the default C# string formats for the data formatting in reports. That is, for two decimal places the format is like 0.00. Then the result data will be two decimal places. Example, 5.67. So, you can try both and use which one is best fit for you.

There are other plenty of formats available and they are completely described here.

http://msdn.microsoft.com/en-us/library/ms252080%28VS.80%29.aspx

This help you to understand the data formatting in SSRS. What do you think?

Read More...

Fix to Divided by zero problem in Sql server reports

When we work with reports, most of the times we need to do complex calculations and logic. When we do calculations, there are chances of using the division operation. So, need of checking the divided by zero problem every time. So the solution I propose is, write a simple custom function and call it wherever needed. For every report there is a part called Code and there we can write the custom VB code.

Public Function CheckDividedByZero(ByVal param1 As Double, ByVal param2 As Double) As Double
        If param2 = 0 Then
            Return 0
        Else
            Return param1 / param2
        End If
    End Function

So, call this function wherever you are performing the division operation. Hope this will solve your problem and you never face the divided by zero problem any more.

Read More...

Date formatting in Sql server reporting services

When we work with reporting services, we come across with different requirements to display the date in different formats. So, we need to know the formats we can give to get required value. Sql server reporting services supports different formats as T-sql and C# does. So, I just want to give the valid formats I use to display the date in required format.

=Format(Fields!DateCreated.Value, "M/d/yy") - 3/14/1986
=Format(Fields!DateCreated.Value, "d-MMMM-yy") - 14-March-86
=Format(Fields!DateCreated.Value, "MM/dd/yyyy") - 03/14/1986
=Format(Fields!DateCreated.Value, "M/d/yyyy H:mm") - 3/14/1986 22:10
=Format(Fields!DateCreated.Value, "dd-MMM-yyyy") - 14-March-1986
=Format(Fields!DateCreated.Value, "MMM-dd-yyyy") - Mar-14-1986
=Format(Fields!DateCreated.Value,"dd-MMM-yy") - 14-Mar-86

You can try different combinations of above to get desired output. Hope this helps and love to hear the comments.

Read More...

How to give anonymous access to the reports.

Give anonymous access to the Application.

1. Go to IIS.

2. Select reports application –> properties –> Directory Security –> Authentication and Access control –> Edit –> Enable Anonymous access

Give anonymous access to a report.

1. Open the reports on the report server located at http://<servername>/reports

2. Browse to the report that needs to be accessed anonymously.

3. Select properties tab for the report.

4. Select security from the left navigation.

5. Select New Role Assignment from this page.

a. Give a group name - everyone (in the “group or user name” text box).

b. Select the option Browser role and then click OK.

[Browser role is for READ - ONLY access]. Read More...

Expand Collapse columns in a table SSRS

Problem:

How to make columns collapsible, expandable in 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 firstname, 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 firstname, 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 q2,q3, and same for h2, h3.

Here are the steps i followed to make it work.

No Matrix, no other,. i am done with tables only.
Just a small trick

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. Done, Here we go.

Read More...