SSRS - Repeat Header Section for A Tablix

How to repeat header section for a tablix:

1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
   
   
    
2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
    
3. select the corresponding (static) item in the row group hierarchy
    
4. in the properties grid:
   - set KeepWithGroup to After
   - set RepeatOnNewPage to true for repeating headers
   - set FixedData to true for keeping headers visible

http://blogs.msdn.com/b/robertbruckner/archive/2008/10/13/repeat-header-and-visible-fixed-header-table.aspx

SSRS - Adding Page Break

To add page break to a large result set: 

In your scenario, you want to display 10 records in each page, we can achieve it follow the steps below.

1. Click the Details group in the Row Groups pane.
2. From the Tablix member Properties pane, expand “Group”-> “PageBreak”.
3. Set the “BreakLocation” to “End” and set the “Disable” property to the expression like below:  =IIF(Globals!RenderFormat.Name = nothing, IIF((rownumber(nothing) mod 30) = 0,false,true), true)  NOTE: this expression disable pagebreak when exporting.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/276acd62-49e7-41ad-a50e-7e4b97e44dcc/tablix-paging-in-ssrs

SSRS - To Drill Through A Report Using A Popup Window

Add the following code in the cell's expression:

="javascript:void(window.open('" + "http://SharePointServer/_vti_bin/reportserver?http%3A//SharePointServer/ReportFolder/ReportName.rdl" + "','Title','height=400,width=150,scrollbars=1'))"

SSRS - To See Report Execution History

USE ReportServer;

SELECT

      COUNT(Name) AS ExecutionCount

    , Name AS ReportName

    , MAX(TimeStart) AS ReportLastRun   

    , UserName AS ReportRunByUser

    , AVG(TimeDataRetrieval) AS TimeDataRetrieval

    , AVG(TimeProcessing) AS TimeProcessing

    , AVG(TimeRendering) AS TimeRendering

    , AVG(ByteCount) AS ByteCount

    , AVG([RowCount]) AS [RowCount]

    , MAX([Format]) AS [Format]

    , MAX([Parameters]) AS [Parameters]

FROM (SELECT

           e.TimeStart

          ,c.Type

          ,c.Name

          ,e.UserName

          ,e.TimeDataRetrieval

          ,e.TimeProcessing

          ,e.TimeRendering

          ,e.ByteCount

          ,e.[RowCount]

          ,e.[Format]

          ,SUBSTRING(e.[Parameters], 1, 200) [Parameters]

      FROM Catalog c

      JOIN ExecutionLog e

        ON c.ItemID = e.ReportID

      WHERE c.Type = 2) AS RE

WHERE

     UserName NOT LIKE 'DOMAIN\user1%'

AND UserName NOT LIKE 'DOMAIN\user2%'

GROUP BY

     Name

    ,UserName

ORDER BY

     ReportLastRun DESC

    ,ReportName

    ,UserName

RegEx - Useful Patterns

Description	RegEx Pattern	Sample Data & Result
Search for all numbers except ones prefixed with #	(?<!\#)\b\d{5,}\b	1234567-JUL13 Ck #1234567 145A1 12345 05/14.

Here is a good online tester: http://regexr.com

Quick Cheat Sheet: http://www.rexegg.com/regex-quickstart.html#quantifiers

BizTalk - Great RosettaNet Resources

POSTSEEK: a good aggregated site:
http://www.postseek.com/meta/bf130ea51b6bb37eae0b85fb737b6e0d

Microsoft forum:
https://social.msdn.microsoft.com/Forums/en-US/home?sort=relevancedesc&brandIgnore=True&searchTerm=6A1

MSSQL - Permission Issue on OPENROWSET

Got error when running OPENROWSET to read data from external Excel file:

SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=C:\TEMP\test.xlsx;HDR=YES;IMEX=1',
                 'SELECT * FROM [Excel$]') FC

Msg 7303, Level 16, State 1, Server MyServer, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

SOLUTION 1:

As it turns out when running the sql with any domain account the sql will need to have access to the folder “C:\Users\someaccount\AppData\Local\Temp” where the someaccount is the logon account used by SQL service.  Giving full control for users who need to run the sql to this folder on the SQL server solved the issue.  An easy way to debug which folder needs permission is to use PROCMON.EXE to monitor the file got rejected.

SOLUTION 2:

SET Temp=C:\Temp and SET Tmp=C:\Temp; this cause all user to use the given folder for temp files when running reports.

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

SOLUTION 3:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE

SOLUTION 4:

http://www.johnsoer.com/blog/?p=538