JavaScript in SSRS Reports

JavaScript can be used in SSRS Reports to meet atypical requirements.

Note : Type the following code "Go To URL"  in Action Tab

1. Open a report in a new Tab from other report

="javascript:void(window.open('http://to be opened report URL from report Manager'))"

2. Alert Box

="javascript:void alert('Some Alert Message')"

3. Confirmation Box

="javascript:var check=confirm('Do you want to navigate to New report?'); if (check == true) {window.open('http://to be opened report URL from report Manager')}"

4. Prompt Box

="javascript:var FirstName=prompt('what is your First Name?');LastName=prompt('what is your Last Name?'); if(FirstName!='') {void alert(FirstName+','+LastName)} "

5. Using Parameters in javascript

="javascript:var check=confirm('Do you want to generate the report for the selected DateRange?"& Parameters!StartDate.Value & "-" & Parameters!EndDate.Value & "'); if (check == true) {window.open('http://to be opened report URL from report Manager','_self')}"

Pie Chart with data labels outside in ssrs

Fig I

Fig II

Inorder to convert Fig I to Fig II, Select LabelStyle as Outside.

Note : Select CollectedStyle as SingleSlice if you want to group smaller units into a single unit as "Others"
Select CollectedStyle as CollectedPie to get another small pie chart that gives details of "Other" sector.

Differences between Temp Tables and Temp Table Variables in SQL Server

Some facts that i have noticed while working with Temp Tables and Temp Table Variables in SQL Server

-- Create Temp Table
-- It gets Stored in TempDB ---> Temporary Tables
CREATE TABLE #Test(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20)
)

--Skips FK. FOREIGN KEY constraints are not enforced on local or global temporary tables.
CREATE TABLE #Test1(
ID INT REFERENCES #Test(ID) ,
NAME VARCHAR(20)
)

--Declare Table Variable
--It also gets stored in TempDB
DECLARE @Test TABLE(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20) UNIQUE NONCLUSTERED
)
--NON Clustered Indexing
--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON @Test(Name) -- Not possible, however we can create PK and UNIQUE constraint

--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON #Test(Name) -- possible

--Insert Records in #Test and @Test
DECLARE @i int = 0;
DECLARE @j VARCHAR(MAX);
WHILE @i < 1000000
BEGIN
    SET @i += 1;
SET @j = CAST(@i AS VARCHAR(MAX));
INSERT INTO @Test VALUES ('NAME'+@j);
END;
--SELECT * FROM @Test WHERE NAME='NAME3'
--EXEC sp_Test @Test

--SELECT Records
;With CTE1 AS(
SELECT * FROM @Test)
SELECT * FROM CTE1

SELECT * FROM @Test WHERE ID = 10  -- Fetching records from @Test Takes double time than #Test

SELECT * FROM #Test

;With CTE1 AS(
SELECT * FROM #Test)
SELECT * FROM CTE1

--Join Table Variables and Temp Tables
SELECT T.ID,T.NAME
FROM @Test T
JOIN #Test S ON S.ID = T.ID

--Table Variable with FK NOT ALLOWED
DECLARE @Test1 TABLE(
ID INT, --REFERENCES #Test(ID),
NAME VARCHAR(20)
)

--Connot Create Triggers on temp
CREATE TRIGGER trgAfterInsert ON #Test
FOR INSERT
AS
PRINT 'Records Inserted'
Go

--TABLE Valued Parameters
CREATE TYPE Testing AS TABLE(
ID int,
NAME VARCHAR(20))

CREATE PROCEDURE sp_Test
(
@T AS Testing READONLY
)
AS
BEGIN
SELECT * FROM @T
END

SQL to DAX - Sample Queries

Following queries gives an idea how to convert a SQL query to DAX.
Note: In last query WorkingDays represents the total Business days for a given period of time.

/* SELECT * FROM Projects
EVALUATE
(
'Projects'
)
*/

/*SELECT ProjectNames FROM Projects
EVALUATE
(
SUMMARIZE
(
'Projects'
,[ProjectName]
)
)
*/

/* SELECT COUNT(distinct [WorkDate]) AS Holidays
FROM [dbo].[Invoices] (NOLOCK)
WHERE [ProjectId] = 8
EVALUATE
(
ROW("TotalHolidays", COUNTROWS(
FILTER('Invoices',[ProjectId]=8)))
)
*/

/* WITH CTE1 AS (
SELECT [EmpName]
, MIN([WorkDate]) AS StartDate
, MAX([WorkDate]) AS EndDate
FROM [dbo].[Invoices] (NOLOCK)
GROUP BY [EmpName]),

CTE2 AS (
SELECT [EmpName]
, SUM([Hours]) AS BillingHours
FROM [dbo].[Invoices] (NOLOCK)
WHERE [BillingType] = 'Billable'
GROUP BY [EmpName])

SELECT C.EmpName
, C.StartDate
, C.EndDate
, T.BillingHours
, T.BillingHours/((SELECT (DATEDIFF(dd, C.StartDate, C.EndDate) + 1)
-(DATEDIFF(wk, c.StartDate, C.EndDate) * 2)
-(CASE WHEN DATENAME(dw, C.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, C.EndDate) = 'Saturday' THEN 1 ELSE 0 END)
-@Holidays)*8) AS Utilization
FROM CTE1 C
JOIN CTE2 T ON C.EmpName = T.EmpName

*/
DEFINE
MEASURE 'Invoices'[StartDate] = MIN('Invoices'[WorkDate])
MEASURE 'Invoices'[EndDate] = MAX('Invoices'[WorkDate])
MEASURE 'Invoices'[BillingHours] = CALCULATE(SUM('Invoices'[Hours]),'Invoices'[BillingType] = "Billable")
MEASURE 'Invoices'[WorkingDays] = ((1. * (MAX('Invoices'[WorkDate])-MIN('Invoices'[WorkDate])))+1)
-((WEEKNUM(MAX('Invoices'[WorkDate])))-(WEEKNUM(MIN('Invoices'[WorkDate]))))*2
-(IF(WEEKDAY( MIN('Invoices'[WorkDate]))=1,1,0))
-(IF(WeekDAy( MAX('Invoices'[WorkDate]))=7,1,0))
EVALUATE
SUMMARIZE
(
'Invoices'
,[EmpName]
,"StartDate"
,'Invoices'[StartDate]
,"EndDate"
,'Invoices'[EndDate]
,"BillingHours"
,'Invoices'[BillingHours]
,"Utilization"
,'Invoices'[BillingHours]/'Invoices'[WorkingDays]
)

Sample DAX Queries