Archive for July, 2012

Calculate the number of days between 2 dates in power pivot

July 31, 2012

Problem: Calculate the number of days between 2 dates in power pivot

Solution: There is no datediff function in DAX, so take 1 date from the other then multiply the result by 1

eg (Date1 – Date2) * 1

Problem: If Date2 is blank then we dont want to display anthing



User cannot see datasources (SSRS)

July 31, 2012

Problem: User cannot see Data source in order to attached it to a report
Solution: Publisher is required on a folder to have visibility of the datasources contained within in

SSIS – text values importing as NULLS

July 31, 2012

Problem: (SSIS) Importing an Excel spreadsheet, a column which contains both numbers and letters. The non numeric value aren being imported as NULL

Solution: 3 Parts, all mandatory

Spreadsheet: Add an apostrohie in the first row of data eg 1 becomes ‘1
SSIS: add IMEX=1 to you excel connection string eg

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\filepath\ssis.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES”;


Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\filepath\ssis.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES;IMEX=1″;

SQL Server: change your destination column type to unicode eg varchar(100) becomes nvarchar(100) alternatively use a data conversion component to change the unicode data to non unicode in your SSIS package

Make your Excel 2010 Pivot tables look and behave like 2003 pivot tables

July 31, 2012

Problem: (Excel) Make your 2008 pivot tables look and behave like 2003 pivot tables
Solution: Right click pivot table, pivot table options, Display Tab, select Classic Pivot Table layout

What is my Excel 2010 version number

July 31, 2012

Problem: (Excel) What version of Excel 2010 am I using
Solution: File menu, help, look on the right side of the screen

red down arrow next to user/login (SSMS)

July 31, 2012

Problem – What does the red down arrow next to a user in Management Studio signify?
Answer – User does not have connect permissions to the database

Problem – What does the red down arrow next to a login in Management Studio signify?
Answer – Login is disabled

SSRS – Change the background Row color when a value in the row differs from the corresponding value in the previous row

July 31, 2012

Problem – I want the background color of a row to change each time a value changes from row to row – this will help visually group the data

Create some custom code

Private _dateCount As Integer = 0
Private CurRowNumber as Integer = 0
Private ColorValue as String = “”

Function GetDateRowColor(ByVal previousVal As String, ByVal currentVal As String, MyRowNumber as Integer) As String

‘Check if this is a new row number…
If MyRowNumber <> CurRowNumber then
CurRowNumber = CurRowNumber + 1 ‘New row, so increment counter

If previousVal = currentVal Then
‘ Do nothing
_dateCount = _dateCount + 1
End If

If _dateCount Mod 2 = 0 Then
ColorValue = “White”
ColorValue = “Lavender”
End If
End If

Return ColorValue ‘Always return a value (for columns other than the first one)

End Function
Call the code in the row background setting property

=Code.GetDateRowColor(Previous(Fields!field_name.Value), Fields!field_name.Value, RowNumber(Nothing))

Identify Job Name from sp_who2 Program name entry

July 31, 2012

Problem: Identify job name from sp_who2 Program Name eg SQLAgent – TSQL JobStep (Job 0x0DCB3F67B0EBAD4DA83789C63F997E47 : Step 2)

Solution: select * from msdb.dbo.sysjobs where job_id = cast(0x0DCB3F67B0EBAD4DA83789C63F997E47 as uniqueidentifier)

Query a list of values in SQL Server

July 31, 2012

Problem: Turn a list of data into tabular form which can easily be queried


select * from (values

AS table_name(field1,field2)

Unable to connect to Report Server in SSMS – Unable to connect to the server at . The specified URL might not be valid or there might be a problem with the report server version or configuration

July 31, 2012

Problem: Unable to connect to report server using management studio.

“Unable to connect to the server at . The specified URL might not be valid or there might be a problem with the report server version or configuration.”


I was able to connect using a url rather than the server name in the server name box.
The url should be same url used to connect to the reportserver page (not reports page)
eg http://cat-tin-aardvark01/ReportServer_AARDVARK01_SSRS
where cat-tin-aardvar01 was the name of the server and AARDVARK01_SSRS was the name of the reporting server instance.