Thursday, November 13, 2014

Find Active Directory Group - Report Security

Report Builder has a built-in function for User ID, but if you want to limit access or include expressions based on Active Directory Groups you will have to use custom code.

In the Report Properties, go to the Custom Code window and type in the following code:


Public Function IsMemberOfGroup() As Boolean

If System.Threading.Thread.CurrentPrincipal.IsInRole("Your AD Group Name") Then
    Return True
Else
   Return False
End If
End Function

 You can call the code in any expression window by using the following code: Code.FunctionName() 

Change image depending on Ad Security Group

=IIF(Code.IsMemberOfGroup()="True", "questionmarkrpt","questionmarkrptblank")

 Go to a specific link depending on AD Security Group. 

=IIF(Code.IsMemberOfGroup() = "True", "javascript:void(window.open('http://Google.com', '_blank'))", "javascript:void(window.open('http://yahoo.com', '_blank'))")

Wednesday, November 12, 2014

SSRS Report Builder - Table Column Width

I have found that some of the properties in the Report Builder for table columns do not work properly.  This is very frustrating, because what is rendered in the Report Builder is not the same as what is rendered on the server when the report is executed.  What I have found to work is to create an expression in the value of the cell to minimize the column width.

Say you have a field with a lot of data and it is separated by a special character.  This will cause the column to render one line because there is no separation in the data.  

Place this code in the value expression of the Text Box Property.  

=Replace(Fields!MyField.Value, ";", Environment.NewLine)

Change the ";" character to modify the separator that is present in your data. This will replace each special character with a new line, therefore, narrowing the table column width and giving your rendered report a better look.


SSRS Report Builder - Remove Element from Report Export



To render an element on a report but not print the element in the export file, place the code below in the element's "Visibility" Hidden property.

=NOT(Globals!RenderFormat.IsInteractive)



Use the code below to exclude from specific export files. 

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)
 This table represents the Render Format Name to use in the expression. 

RendererRenderFormat.NameRenderFormat.IsInteractive
Preview in BIDS or rendered through Report ManagerRPLTrue
XML file with report dataXMLFalse
CSV (comma delimited)CSVFalse
TIFF file or Print buttonIMAGEFalse
PDFPDFFalse
MHTML (web archive)MHTMLTrue
ExcelEXCELFalse
WordWORDFalse