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,"'") &");"

Format Date with DATEPART

This is helpful when you need to get rid of milliseconds in a date for sorting.

DATETIMEFROMPARTS ( year(s.LastRunTime), month(s.LastRunTime), day(s.LastRunTime), DATEPART(hh,s.LastRunTime), DATEPART(mi,s.LastRunTime), 0, 0)

List of Date Formats

The website below has a list of SQL Server Date Formats.

http://www.sql-server-helper.com/tips/date-formats.aspx

Subquery Join with Max Date

The query below is used when you have a table with multiple rows per person and you want to select the MAX Date.  First you will select the fields you want to display in the query, then you will create a subquery to select the Max Date, then you will join the query and subquery on the ID and the Max Date.

Add Subquery As A Column

The SQL below adds aggregated columns to a query by using sub queries. The key to using this type of query is to join the inner and outer tables on the correct field.

Use the Instant SQL Formatter to Format the SQL below.  Just copy and paste into the Formatter at: http://www.dpriver.com/pp/sqlformat.htm

SELECT DISTINCT a.eeo_cls,

                a.classification,

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-I',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-I',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'M'

                        AND C.eeo_cls = a.eeo_cls) AS 'M-O',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'F'

                        AND C.eeo_cls = a.eeo_cls) AS 'F-O'

FROM   #ee05temp a

       INNER JOIN #ee05temp b

               ON a.prem_emp = b.prem_emp

       INNER JOIN #ee05_multiple

               ON a.prem_emp = b.prem_emp

GROUP  BY a.classification,

          a.eeo_cls

ORDER  BY a.eeo_cls