Create column headers based on an imported SQL function

[Dan_Tuma]

Hello all.

I have Power BI report that uses a SQL stored procedure that calculates daily attendance percentages by K-12 school. It calculates for the last 5 school days only, and it uses a SQL function to find the last 5 school days based on a school calendar in the SQL database. What I need is for the 5 percentage columns' headers to reflect the date of the day the percentages represent instead of the current column heading. This would mean creating custom headers based on an imported SQL function.

The column header looks like this:

School Name | Percentage 1 | Percentage 2 | Percentage 3 | Percentage 4 | Percentage 5 |

School                 89%                   92%                   95%                 97%                  94%

What I need it to read like is this:

School Name | 10/10/2016 | 10/11/2016 | 10/12/2016 | 10/13/2016 | 10/14/2016 |

 School                 89%               92%               95%               97%               94%

Any thoughts?

Dan

[a_mixed_life]

You'll need to make some transformations after the fact. Are you wanting to use the DateKey as your header as columns in a Matrix?

[Dan_Tuma]

Kris,

I don't have a date table. What I used for column headers in the SSRS version of this report is a function that takes the current date (getdate()) as the first argument and the number of days that should be subtracted from the current date as the second argument. I use it 5 times to get the last five days that school was in session based on a school calendar. The school calendar not only has the holidays marked as "No School" but also profesional days, spring break etc... I think it would be very difficult to recreate that in Power BI.

I have imported the function into PBI and when I call it from a new query I get the correct results; a single date. So I can easily create 5 new queries and get the correct dates from them, I just can't figure out out how to replace the "Percentage 1" 2, 3, 4, 5 headers that the main data query as shown in my original post.

Am I making sense or just confusing the matter?

Dan

[v-micsh-msft]

Hi Dan_Tuma,

Could you please share about which part that you used to put into the SSRS report column header to get the proper date?

In Power BI, we could also use the DAX function Today () to get the date of the current day, which I think should be the same as the getdate function.

TODAY Function (DAX)

I think we could use Today() function to create the column calculation in Power BI Desktop.

What I suggest here is that we just import the fact data into Power BI (if possible), then do the calculation using DAX in Power BI desktop instead of in SSRS.

By the way, if you are now using SSRS 2016 version, take a look at the article below, which now supports to pin SSRS items into Power BI dashboards:

Pin Reporting Services items to Power BI Dashboards

Please reply back if you need any further assistance on this.

Regards

[Dan_Tuma]

Michael,

We are literally just now starting to use SSRS 2016 and are definately planning on using the new PIN functionality.

Please find below the SQL function code and the query code that creates the report I am having trouble with. I think it should answer some of your questions.

Fn_FindSchoolDays function: THE FUNCTION USES THE TABLE REG_CAL_DAYS, MEMBERSHIP_VALUE COLUMN TO DETERMINE WHICH DAYS ARE SCHOOL DAYS. THE REG_CAL_DAYS TABLE CAN BE IMPORTED INTO PBI IF NEED BE.

ALTER function [dbo].[fn_FindSchoolDays]
(
@dtBeginDate datetime,
@iSchoolDays int
)
RETURNS datetime
AS
BEGIN

	DECLARE @dtEndDate datetime
	DECLARE @dtDateHolder datetime
	DECLARE @iDayCount int

	SET @dtDateHolder = @dtBeginDate
	SET @iDayCount = 1

	WHILE (@iDayCount <= @iSchoolDays) 
	  BEGIN
	    --Move to the next day in the range and loop back to check it
		SET @dtDateHolder = DATEADD(d,-1,@dtDateHolder)	  
		--Is the date being checked a School day?
		IF((SELECT Top 1 MEMBERSHIP_VALUE FROM [DATABASENAME].[dbo].[REG_CAL_DAYS]
			WHERE SCHOOL_YEAR = dbo.ATM_getSchoolYear(getdate()) AND BUILDING = 3016 AND CAL_DATE = @dtDateHolder) = 1)
			BEGIN
			  SET @iDayCount = @iDayCount + 1
			END
	  END
	  SET @dtEndDate = @dtDateHolder
	RETURN @dtEndDate

END



THIS QUERY CREATES THE REPORT

THE @DATEx PARAMETERS ARE USED FOR THE COLUMN HEADERS (INSTEAD OF “PERCENTAGE x”) AND ARE IMPLEMENTED AS SUCH IN SSRS.


DECLARE @DATE1 AS DATE = CAST(dbo.fn_FindSchoolDays(CAST(GETDATE() AS DATE),1)AS DATE)
DECLARE @DATE2 AS DATE = CAST(dbo.fn_FindSchoolDays(CAST(GETDATE() AS DATE),2)AS DATE)
DECLARE @DATE3 AS DATE = CAST(dbo.fn_FindSchoolDays(CAST(GETDATE() AS DATE),3)AS DATE)
DECLARE @DATE4 AS DATE = CAST(dbo.fn_FindSchoolDays(CAST(GETDATE() AS DATE),4)AS DATE)
DECLARE @DATE5 AS DATE = CAST(dbo.fn_FindSchoolDays(CAST(GETDATE() AS DATE),5)AS DATE)

 
SELECT	RUB.FIELD_VALUE AS 'NETWORK'
		,RUB.BUILDING
		,RB.NAME
		
-- DATE 5		 
		,(
SELECT

(SELECT CASE 
	WHEN (SELECT SUM(MEMBERSHIP_VALUE) 
	FROM [DATABASENAME].[dbo].[REG_CAL_DAYS] 
	WHERE BUILDING = RUB.BUILDING 
	AND SCHOOL_YEAR = CMSD_DBA.dbo.ATM_getSchoolYear(GETDATE()) 
	AND CALENDAR = 'R' 
	AND CAL_DATE = @DATE5) = 0.00 THEN 0.00
	ELSE
	

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE5 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE = @DATE5
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/

CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE5 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)AS FLOAT)
				
	END)
	)
		AS 'PERCENTAGE 5'
		
-- DATE 4		 
		,(
SELECT

(SELECT CASE 
	WHEN (SELECT SUM(MEMBERSHIP_VALUE) 
	FROM [DATABASENAME].[dbo].[REG_CAL_DAYS] 
	WHERE BUILDING = RUB.BUILDING 
	AND SCHOOL_YEAR = CMSD_DBA.dbo.ATM_getSchoolYear(GETDATE()) 
	AND CALENDAR = 'R' 
	AND CAL_DATE = @DATE4) = 0.00 THEN 0.00
	ELSE
	

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE4 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE = @DATE4
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/

CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE4 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)AS FLOAT)
			
	END)
	)
		AS 'PERCENTAGE 4'
		
-- DATE 3		 
		,(
SELECT

(SELECT CASE 
	WHEN (SELECT SUM(MEMBERSHIP_VALUE) 
	FROM [DATABASENAME].[dbo].[REG_CAL_DAYS] 
	WHERE BUILDING = RUB.BUILDING 
	AND SCHOOL_YEAR = CMSD_DBA.dbo.ATM_getSchoolYear(GETDATE())  
	AND CALENDAR = 'R' 
	AND CAL_DATE = @DATE3) = 0.00 THEN 0.00
	ELSE
	

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE3 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE = @DATE3
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/

CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE3 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)AS FLOAT)
			
	END)
	)
		AS 'PERCENTAGE 3'
		
-- DATE 2		 
		,(
SELECT

(SELECT CASE 
	WHEN (SELECT SUM(MEMBERSHIP_VALUE) 
	FROM [DATABASENAME].[dbo].[REG_CAL_DAYS] 
	WHERE BUILDING = RUB.BUILDING 
	AND SCHOOL_YEAR = CMSD_DBA.dbo.ATM_getSchoolYear(GETDATE()) 
	AND CALENDAR = 'R' 
	AND CAL_DATE = @DATE2) = 0.00 THEN 0.00
	ELSE
	

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE2 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE = @DATE2
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/

CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE2 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)AS FLOAT)
			
	END)
	)
		AS 'PERCENTAGE 2'
		
-- DATE 1		 
		,(
SELECT

(SELECT CASE 
	WHEN (SELECT SUM(MEMBERSHIP_VALUE) 
	FROM [DATABASENAME].[dbo].[REG_CAL_DAYS] 
	WHERE BUILDING = RUB.BUILDING 
	AND SCHOOL_YEAR = CMSD_DBA.dbo.ATM_getSchoolYear(GETDATE()) 
	AND CALENDAR = 'R' 
	AND CAL_DATE = @DATE1) = 0.00 THEN 0.00
	ELSE
	

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE1 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE = @DATE1
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/

CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) = @DATE1 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)AS FLOAT)
	
	END)
	)
		AS 'PERCENTAGE 1'
		


		,@DATE5 AS DATE5
		,@DATE4 AS DATE4
		,@DATE3	AS DATE3
		,@DATE2	AS DATE2
		,@DATE1 AS DATE1
		
---------------------------------------------------------------------------------

,(SELECT CASE WHEN
(SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) BETWEEN @DATE5 AND @DATE1 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING) = 0 THEN 0
ELSE

((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) BETWEEN  @DATE5 AND @DATE1
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)		
-

ISNULL((SELECT SUM(ATT_CODE_VALUE) --GET TOTAL ABSENCES FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].ATT_STU_DAY_TOTALS ASD
		WHERE ATTENDANCE_CODE IN('E','U')	
		AND	ATTENDANCE_DATE BETWEEN  @DATE5 AND @DATE1
		AND ASD.BUILDING = RUB.BUILDING),0.0)
)

/


CAST((SELECT COUNT(*) --GET TOTAL ENROLLMENT COUNT FOR SPECIFIED @DATE5
		FROM [DATABASENAME].[dbo].att_stu_day_memb ASD
		WHERE CAST(MEMBERSHIP_DATE AS DATE) BETWEEN  @DATE5 AND @DATE1 
		AND MEMBERSHIP_VALUE = 1
		AND ASD.BUILDING = RUB.BUILDING)

		AS FLOAT)
END		
)

	
		AS 'ROW PERCENTAGE'

----------------------------------------------------------------------------------
 
FROM	[DATABASENAME].[dbo].REG_USER_BUILDING RUB
		RIGHT OUTER JOIN
		[DATABASENAME].[dbo].REG_BUILDING RB
		ON
		RUB.BUILDING = RB.BUILDING

WHERE	SCREEN_NUMBER = 1000 -- USED TO GET THE NETWORK GROUPS
		AND FIELD_NUMBER = 4
		AND FIELD_VALUE IS NOT NULL
		AND FIELD_VALUE != ''
		AND FIELD_VALUE != 'INACTIVE'
		AND RB.BUILDING_TYPE IN('E','EH','H','HB')
		--AND	RUB.BUILDING NOT IN(5298,5299,5300)

GROUP BY RUB.FIELD_VALUE,RUB.BUILDING,RB.NAME


END

Please let me know your thoughts and if this info helps.

Thank you

Dan Tuma