Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |