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
Solution:
=if(isblank(b3),blank(),b3-b2)*1
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
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”;
becomes
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
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
Problem: (Excel) What version of Excel 2010 am I using
Solution: File menu, help, look on the right side of the screen
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
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
Answer –
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
Else
_dateCount = _dateCount + 1
End If
If _dateCount Mod 2 = 0 Then
ColorValue = “White”
Else
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))
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)
Problem: Turn a list of data into tabular form which can easily be queried
Solution:
select * from (values
(1,’a’),
(2,’b’),
(3,’c’)
)
AS table_name(field1,field2)
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.”
Solution:
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.
SQL Notes 