Query to help create RDL

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

 

CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

       @view varchar(50)

)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

       --DECLARE @view varchar(50)

       --SET @view = 'V_EVALUATIONS'

      

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME  + '">

       ' + c.NAME  + '

       ' +

       CASE t.NAME

       WHEN 'varchar' THEN 'System.String'

       WHEN 'int' THEN 'System.Int32'

       ELSE 'true'

       END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

---COPY & PASTE INTO COLUMN SECTION  --

SELECT

'

       1.5in

' as 'COPY & PASTE INTO COLUMN SECTION  -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

 

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

'

      

              + c.NAME + '">

                     true

                     true

                    

                          

                                 

                                        

                                                ' + REPLACE(c.NAME,'_',' ') + '

                                               

                                        

                                 

                                 

                          

                    

            Textbox' + c.NAME + '

           

                          

                           SteelBlue

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

'

      

              +c.NAME+'">

                     true

                     true

                    

                          

                                 

                                        

                                                =Fields!'+ c.NAME + '.Value

                                               

                                        

                                 

                                 

                          

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

 

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

'

      

              =IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

      

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

 

 

GO

 

Query storage size on your tables or tables in the database.

A helpful query on finding the size of your specific tables in SQL Server. Just replace with your string list of tables.

Code Snippet

1. SELECT
2. sum(RowCounts) as RowCounts,
3. SUM(TotalSpaceKB) AS TotalSpaceKB,
4. SUM(UsedSpaceKB) AS UsedSpaceKB,
5. SUM(UnusedSpaceKB) AS UnusedSpaceKB
6. FROM
7. (
8.     SELECT
9.         sum(p.rows) AS RowCounts,
10.         SUM(a.total_pages) * 8 AS TotalSpaceKB,
11.         SUM(a.used_pages) * 8 AS UsedSpaceKB,
12.         (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
13.     FROM
14.         sys.tables t
15.     INNER JOIN      
16.         sys.indexes i ON t.OBJECT_ID = i.object_id
17.     INNER JOIN
18.         sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
19.     INNER JOIN
20.         sys.allocation_units a ON p.partition_id = a.container_id
21.     WHERE
22.         t.is_ms_shipped = 0
23.         AND i.OBJECT_ID > 255
24.         AND t.NAME IN ( <TABLE_NAMES> )
25.     GROUP BY
26.        p.Rows
27. )x

Or to get a list of all tables and their sizes in the database:

Code Snippet

1. SELECT
2.     t.NAME as TableName,
3.     sum(p.rows) AS RowCounts,
4.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
5.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
6.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
7. FROM
8.     sys.tables t
9. INNER JOIN      
10.     sys.indexes i ON t.OBJECT_ID = i.object_id
11. INNER JOIN
12.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
13. INNER JOIN
14.     sys.allocation_units a ON p.partition_id = a.container_id
15. WHERE
16.     t.is_ms_shipped = 0
17.     AND i.OBJECT_ID > 255
18. GROUP BY
19.    t.Name, p.Rows

TSQL Function: Count Work Days from two dates.

A T-SQL function to calculate number of business days (work days) given two dates.
It uses CTE recursive to quickly get a row number count.

CREATE FUNCTION [hrr].[usf_GetWeekdayCount]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
BEGIN

IF(@StartDate IS NULL)
  RETURN 1

IF(@EndDate IS NULL)
  RETURN 1

DECLARE @FlipBitFlag bit
SET @FlipBitFlag = 0

IF(@StartDate > @EndDate)
BEGIN   DECLARE @Temp datetime
   SET @Temp = @StartDate
   SET @StartDate = @EndDate
   SET @EndDate = @Temp
   SET @FlipBitFlag = 1
END
DECLARE @WEEKDAYS_DAY AS INT
 ---Count up to 2^16 = 65536 real fast!!
;WITH N1 (n) AS
(
  SELECT 1

  UNION ALL 
  SELECT 1
),
N2 (n) AS
(
   SELECT 1 FROM N1 AS X, N1 AS Y
),
N3 (n) AS
(
   SELECT 1 FROM N2 AS X, N2 AS Y
),
N4 (n) AS
(
   SELECT 1 FROM N3 AS X, N3 AS Y
),
N5 (n) AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N4 AS X, N4 AS Y
)
  

SELECT @WEEKDAYS_DAY=SUM(1)
FROM N5
WHERE DATEADD(day,n-1,@startdate)<=@enddate
AND DATENAME(dw,DATEADD(day,n-1,@startdate)) NOT IN ('SATURDAY','SUNDAY')

IF(@FlipBitFlag = 1)
  RETURN -1 * @WEEKDAYS_DAY

RETURN @WEEKDAYS_DAY

END
GO

--EDITED: Added check for nulls and reverse dates will give negative value.

IIF vs CASE in SQL SERVER 2012

I've been told that behind the scenes, SQL Server 2012 converts IIF to CASE expressions. I didn't think that there wouldn't be that much of a performance issue. What I found in one of my queries is that if I ran the IIF first then the CASE statement. Each time I ran the IIF statement it ranged from 40 - 60 in the client processing time; meanwhile, the CASE expression would run in 10 - 30.

(Click on images to expand)

 

SSRS: Hide this column function

A bit of code from SQL Server Magazine on handling column specific security based on userid that is found in SSRS.

Function HideThisColumnFrom(strUserID as String) as Boolean
   Select Case strUserID
      Case "domain\user1" : Return False
      Case "domain\user2" : Return False
      Case Else : Return True
   End Select
End Function

Not ideal, and it is suggested using SQL Server to house a table of users, etc....

I think it would be best to write a sql scalar function that accepts a userid and based on permissions/roles send back either a bit-string or a string of roles separated by a delimiter which then can be deciphered by a function on the SSRS side. The function then would determine by sending in the roles into the function and return the True/False.



 

An error occured while attempting to start the report preview worker process.

"An error occured while attempting to start the report preview worker process."

Ran into this problem when using runas with SSDT. As a consultant, the runas is useful for connecting to a customer's domain and sending the necessary credentials without setting the laptop to be on the customer's domain.

The workaround, is to instead, right-click the report and select "run".

Source:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f717c592-5022-43e3-9872-c10c1cf7f653/ssdt-2012-report-preview-not-working?forum=sqlreportingservices&prof=required