SQL Server Reporting Services – Textbox Location Changing

I recently ran into a problem in a report I was developing.  The report was a Sales Invoice, and if there were enough line items to move the Totals section to a second page, the tablix which contained the totals moved from the right side of the report, where I had set the location, to the middle.  Eventually I learned that SSRS attempts to maintain relative spacing between report items, so that if an item to the left of another item changes in size, or is hidden, the item to its right is moved.

I had a number of items in my report that had an expression for the Hidden property: ( =iif(Fields!InvoiceType.Value = 2, false, true).  I fixed my report by changing the Hidden property for all items to False.  Then, to achieve the same effect as hiding, I worked with the Color property: (=iif(Fields!InvoiceType.Value = 2, “Black”,”White”).  Changing the font color to white makes it invisible and my Totals tablix stayed where I wanted it, on the right side of the report.



User does not have required permissions – Reporting Services

This is just a link to a blog posting from Soheib Blog that perfectly addresses this problem I have run up against with new SQL Server Reporting Services installations (SQL Server 2012 or 2008 R2). If you are getting an error message about a user not having the required permissions, this blog post should do the trick.


Reporting Services – Report Manager won’t work after renaming web server

It took me a while to find out where I had to make a change when one of our clients changed the name of their web server. The error message I got when I tried to browse the Report Manager from IIS said “The remote name could not be resolved:” and displayed the old name of the web server.

There is a config file called RSWebApplication.config. You will find this file in your Reports Virtual Directory. The default location for this file on my development machine is C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportManager.

In this file, you need to change the value of the ReportServerURL to reflect the new name of your web server.

Reporting Services Subscriptions Have Unintelligible Job Names

If you’ve set up a subscription in Microsoft SQL Server Reporting Services, you might notice when you go to find the job that the name is all letters and numbers. If you have more than one job, it’s very difficult to figure out which one is which. This link at SQLServerCentral.com provides an excellent view which will help you identify the various jobs. Here’s the code for the view, thanks to SQLServerCentral user stevefromOz. You should run this code in the ReportServer database.

SELECT Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath
FROM ReportSchedule INNER JOIN
Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

As Steve says, “because you’re going directly against the app tables, these fields (types, names, relationships etc etc) could change in the future with no requirement on m$ft to inform anyone. ie if you do a sp upgrade and this stops working, chances are they changed something .”