Count the Rows of a Filtered Tablix

Been having some major ball aches with SSRS (SQL Server Reporting Services) recently -specifically around trying to hide elements and filter data.

In this instance, I needed to hide a row in a group if there were no rows in the filtered tablix. This is the process I followed:

1. Added a column to the tablix and set the column visibility to hidden

2. Added a text box to the header row and set the expression for it to: =CountRows()

3. Set the row visibility expression to this:

=(Int32.Parse(ReportItems!txtUsageDataRowCount.Value) <= 0)

An extra to note here is the use of ReportItems. It's a neat way of referring to a value in a text box. It does only work for text boxes

SSRS: Black Box When Exporting to PDF with Hidden Column

Had a funny with SQL Server Reporting Services where I had a column in a table being hidden based on an expression. That was all fine, the issue was when the column was hidden and the report was exported to PDF, there was a solid black box being shown - great!

The table had a border and all inner borders were also set. I removed all the table borders and instead applied the borders to the textboxes in each cell.

Problem solved!

This has apparently been addressed in RS2005.

SSRS: Table KeepTogether Not Working

I was recently having a problem with SQL Server Reporting Services 2005 edition trying to stop a table control from breaking over a page. The obvious solution was to set the KeepTogether property to True. Wrong! Well, not exactly. It does keep the table together and not split it over a page, but only the detail! Headers and footers are not included.

I did some extensive trawling and couldn't find an easy solution - but there is one...

Drop the table into a list control and set the KeepTogether property to True on the list.

This works beautifully for me and is really easy. The only thing you need to do is make sure that the list is the same size the table (and that's optional). Thus making sure it doesn't exceed the page width and so on.

System.Web.Services.Protocols.SoapException: The permissions granted to user 'IIS APPPOOL\MyUser' are insufficient for performing this operation

I had this error (System.Web.Services.Protocols.SoapException: The permissions granted to user 'IIS APPPOOL\MyUser' are insufficient for performing this operation) and took me a while to work out what to do to fix it. All the references I found  to it were relating to SharePoint.

In my instance I had added a web reference to my application to make use the reporting services (SSRS) web service (http://localhost/ReportServer/ReportExecution2005.asmx). Although it was working locally, it was not working when I deployed it to the dev or live server.

Turns out the account that is used by the reporting services service account needed to be the same as the account that the application pool was using.

So check the service account in the reporting services configuration tool:

Then from within the IIS, change the identity that the application pool is using (right click the pool and choose Advanced Settings...):

FYI, here's the full error message:
System.Web.Services.Protocols.SoapException: The permissions granted to user 'IIS APPPOOL\MyUser' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'IIS APPPOOL\MyUser' are insufficient for performing this operation. at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo2& executionInfo) at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)



MSSQL Reporting Services: Invalid Object Name on Temporary Table

I had an Invalid Object Name error when I was using a temporary table inside a stored procedure when I was reporting services. It ran fine in management studio but errored in the report designer wizard.

Fixed it by using the SET FMTONLY OFF command. Happy days :)