While working on my last article I faced a weird issue while designing the sample report. It was not the first time I faced such issues in report designer so I decided to blog about these glitches.
So to prepare my sample report I copied a table from one of the existing report and removed some unnecessary columns from it and changed some header text; Then as I had two more similar tables I thought to copy columns from previous table to other tables and changed some column positios. I was selecting few columns, cutting them and then pasting them to other table something like this -
and suddenly I recived this error - "Index was outside the bounds of the array."
ooops what happened, no idea. I clicked OK; Now what I can't undo the change, even delete shows the same error message; I am stuck with a table having an empty space for three columns and nothing can be done.
Only option left is to close the report and save the changes(don't do this, click No), thank god my changes are saved and columns are deleted.
Wait a second, my aim was to cut the columns and paste them into other table and here I am left with a table without those columns, Great.
Intresting thing about this is that, this behavior is not consistent cut/paste will work fine for some time and then suddenly you will get this error and then you don't have any option other then doing all unsaved work again :(
Yesterday I wrote my first article showing how a tab control like structure can be implemented in SSRS report. I had implemented this in one of my projects and this post [How to create excel like tab lay out?] inspired me to share it. Although it's not very elegant implementation but it was good enough for me to fulfill client requirement.
As we don't have any click events in reporting services I had to use Visibility can be toggled by another report item property to show hide tab contents. I used text boxes and tables to get a tab control like structure but any other control can be used which supports this property or a rectangle to use combination of report items.
Only major issue with this implementation is in exported reports. Behavior is different in exported PDF and Excel. It was not a problem for me as we were not supporting export in excel and exported pdf is same as you see report in report viewer.
I tried using page breaks (as suggested in the post) but I was not able to get different tabs in excel (was using office 2007 if that makes any difference), do let me know if that works for you with this tab control implementation.
Detailed implementation can be found here - http://www.codeproject.com/KB/reporting-services/AnUglyTabControlForSSRS.aspx
Sample report is attached with the post.
And yes, please provide your valuable feedback on how this can be improved.
A great read, it makes you believe that if you do something you believe in sincerely and keep doing it consistently, then it will definitely pay some day, even reading first 3-4 paragraphs will convince you on why Muhammad Yunus(proud initiator of Grameen Bank) deserved the Nobel Peace Prize.
"Each of you has the power to change the world"
Below is the prepared text of the Commencement address by Muhammad Yunus, winner of the 2006 Nobel Peace Prize, for MIT's 142nd Commencement held June 6, 2008.
For the last one year I was working on reporting services and as project is closed, now its time to do some documentation and knowledge sharing :). So I started to prepare a doument which can be used as a starting point for future projects. In this post I will be posting its first part - Installing and Configuring SQL Server 2005 Reporting Services.
Its my first technical post and your valuable feedback will surely be very helpful and appreciated.
- Windows Server 2003 / Windows 2000 Server/ Windows vista / Windows XP/ Windows Server 2008
- IIS 5.0 or later is required for Microsoft SQL Server 2005 Reporting Services (SSRS) installations.
- ASP.NET 2.0 is required for Reporting Services. When installing Reporting Services, SQL Server Setup will enable ASP.NET if it is not already enabled.
- SQL server 2005 with SP1(In case you need to reinstall reporting services its advised to reinstall full SQL server rather then just reporting services).
- SQL Server Setup requires Microsoft Windows Installer 3.1 or later and Microsoft Data Access Components (MDAC) 2.8 SP1 or later. You can download MDAC 2.8 SP1 from this Microsoft Web site.
For detailed requirements see:
2 Install the SQL server reporting services
3 Post Installation Configuration
On the report server computer, follow these steps after setup is finished to complete the deployment:
- Open port 80 in Microsoft Windows Firewall to enable incoming requests over port 80. If you installed Reporting Services on Microsoft Windows XP with Service Pack 2 or Windows Server 2003 with Service Pack, HTTP requests over port 80 are blocked by default. To open port 80, do the following:
- Click Start, point to Control Panel, click Windows Firewall, click Exceptions, and then click Add Port.
- In Name, type Web Server (TCP 80).
- In Port number, type 80.
- For the protocol, select TCP.
- Click OK.
- Modify Windows Firewall settings to enable remote administration.
- Add InetInfo.exe to the Windows Firewall Exception List
- Click Start, point to Control Panel, and click Windows Firewall.
- Click Exceptions.
- Click Add Program.
- Click Browse.
- Select %windir%\system32\inetsrv\inetinfo.exe
- Click Change Scope to set the scope.
- Click OK.
- Reboot the computer.
- Enable remote connections to the SQL Server instance that hosts the report server database
- Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and click SQL Server Surface Area Configuration Tool.
- Click Surface Area Configuration for Services and Connections.
- Open the folder for the SQL Server instance that hosts the report server databases.
- Click Remote Connections.
- Click Local and Remote Connections.
- Click Using both TCP/IP and named pipes.
- Click OK, and then restart the service.
- Run the Reporting Services Configuration tool, connect to the report server instance you just installed, and review the status indicator for each setting to verify that it is configured. Make sure that it’s all green ticks.
- Assign write permissions to the temporary ASP.NET folder to reporting service account (<domain or computer name>\reportingsvc).
4 Verify Reporting Services Installation
4.1 Verify that the report server is installed and running
- Run the Reporting Services Configuration tool, connect to the report server instance you just installed, and review the status indicator for each setting to verify that it is configured.
- Open the Services window and verify that the Report Server Windows service is running. To view the status of the Report Server Windows service, click Start, point to Control Panel, double-click Administrative Tools, and then double-click Services. When the list of services appears, scroll to Report Server (MSSQLSERVER). The status should be Started.
- On the instance of SQL Server that is hosting the report server database, verify that the SQL Server Agent service is running. SQL Server Agent must be running before users define subscriptions and other scheduled operations. Use the SQL Server Surface Area Configuration tool to enable SQL Server Agent.
- In a browser window, type http://localhost/reportserver (http://<computer name>/ReportServer<instance name>).The URL will be different if you installed the report server as a named instance.
- Run reports to test report server operations. For this step, you can install and publish the sample reports if you do not have any reports available. For more information, see AdventureWorks Report Samples in SQL Server Books Online.
4.2 Verify that Report Manager is installed and running
- In a browser window, type http://localhost/reports (http://<computer name>/Reports<instance name>) this is the virtual directory for Report Manager.
You can also go and try browsing report manager directly through IIS.
- Use Report Manager to create a new folder or upload a file to test whether definitions are passed back to the report server database. If these operations are successful, the connection is functional.
- Installing SQL Server Reporting Services – http://msdn.microsoft.com/en-us/library/ms143736.aspx
- Server Deployment Checklist –
- How to: Install and Configure Reporting Services on Windows Server 2008 -
Hurrey, I am blogging, this is my first ever blog post and it really took a while to come, although I always wanted to start my blog ever since I started working in this Industry but thanks to my laziness and my wish of a good blogging platform, like MSDN blogs J. Thanks to Joe for setting up a blog for me, I will try my best to post some useful stuff here.