Powershell AD Scripts

This script will give you a list of AD groups based on a filter.

get-adgroup -filter{name -like  "_*"} -Properties Description | Select Name

View details of a specific user

Get-ADUser -Identity James.Lawyer -Properties *

View All users in an AD Group

Get-ADGroupMember -identity "RS_BusinessFinance" -Recursive | Get-ADUser -Property DisplayName
 | Select Name

Get AD Group Details/Properties

Get-ADGroup -Identity RS_ASPRINCIPALS -Properties *

Using Like in the Where clause on SSRS Reports

In most of my SSRS reports I use IN or = for Parameters, but sometimes you may need to use Like in the where clause.  There are a number of ways you can do this, but I am only showing the method I use.

Select * from Test
Where UserName Like '%' + @ParameterName + '%'

Conditional Formatting with Expression

The following code can be used in the background property in SSRS for a field to change the color.  In this example I am changing the background color of a Totals field based off of another field (School.)

So if the School field = "District" then I want the Total Field to be Yellow, If it equals Secondary then I want it to be Red and Finally if it equals Elementary I want the field to be Blue

1. In Design View for your report, click on the Textbox you want the colors to be used.
2. Find the Background Color under Fill in the Properties.
3. Select the dropdown for background color and choose Expression
4. Use the code below as a starting point for your conditional Formatting.

=Switch(Fields!School.Value = "District", "Yellow", Fields!School.Value = "Secondary", "Red",Fields!School.Value = "Elementary", "Blue")

Export to Excel Without Page Breaks

In SSRS, by default, the Export to Excel option will render a report with Page Breaks into multiple tabs.  This is fine if you want to view the data this way, but if you would prefer to have all the data on one tab then you will need to do the following in the design view of the SSRS Report: 

1. Click the group object under Row Groups.

2. In the Properties, Click on the Group arrow to expand if the section is not already expanded.

3. Click on the Group arrow on the second line under Group to expand. 

4. Click the arrow to expand PageBreak.

5. Select End for BreakLocation

6. Select Expression for Disabled and type the following in the expression: =IIf(Globals!RenderFormat.Name="EXCELOPENXML", True, False)

7. Save and check the report to ensure the fix worked.  

Use EXCEL to create SQL Insert Code

This script can be put into EXCEL to create Insert statements for SQL.  This will save a lot of time if you have data in EXCEL that needs to be put into a SQL table. 

This looks at 1 column and adds ' around data
="Insert into tmpCourses (Courses) VALUES ("&"'" & A1 & "'"& ");"

This looks at one column adding Text string before the data

="Insert into UserIDs (userid) VALUES ("& "'SCHOOL\"& A2 & "'"& ");"

This script looks at 3 columns

="Insert into Users (userid,first,last) VALUES ("& A2 & ","& CONCATENATE("'",B2,"'") &", "& CONCATENATE("'",C2,"'") &");"