Including a calculated row header in a Dynamic Report

Share this Post

The project I’m currently working on has a requirement to develop a report titled Monthly Financial Statistics for the healthcare industry. The requirement was  to generate this report for various organizations against a particular year and month. Furthermore, the report headers will be dynamic for each section depending on the organization that has been selected. Sample Monthly Financial Statistics Reports for ‘Company Parkson’ and ‘Company A&M’ are shown below:

Monthly Financial Statistics Report for Company Parkson

Monthly Financial Statistics Report for Company A&M

The data for the sample reports was extracted from a Stored Procedure, so this report is considered as a relational report in Aplication Studio. The above sample for Company Parkson illustrates  the “Admissions” section of the report. The row headers are: Acute-Medical, Acute-Surgical, Unassigned/Hospice, NICU, GeriPsych and SNF/Swing. The sub-total called “Acute-Total” and “Total” needed to be calculated manually either within the report or in the data source itself.

The first problem I encountered was accommodating the ‘Acute-Total’ row header which breaks down into the sum of Acute-Medical, Acute-Surgical, and Unassigned/Hospice row headers. I couldn’t do this calculation from the report-end, as I had to include this specific sub-total in-between row headers. The only option to overcome this first obstacle was to make the  required transformations to the data source to include a separate record for ‘Acute-Total’.

As a solution to my first issue, I modified the stored procedure to insert the ‘Acute-Total’ record with the appropriate calculation; which is the sum of Acute-Medical, Acute-Surgical and Unassigned/Hospice row headers. Thereafter, I maintained a condition which would only show the Acute-Total record if Acute-Medical, Acute-Surgical and Unassigned/Hospice was available in the output. The SQL code is shown below:

USE [DWD_Staging]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_Admissions]

@Organization varchar(Max),
@Year varchar(4),
@Month varchar(15)

AS
BEGIN

Select *
from
(
SELECT [STATS_LINE_ITEM]
,CAST(ISNULL([CurMonth],0) AS decimal(18,0)) AS CurMonth
,CAST(ISNULL([PrevMonth],0) AS decimal(18,0)) AS PrevMonth
,CAST(ISNULL([YTD],0) AS decimal(18,0)) AS YTD
,CAST(ISNULL([YTDPrev],0) AS decimal(18,0)) AS YTDPrev
,
CASE STATS_LINE_ITEM
WHEN ‘Acute — Medical’THEN 0
WHEN ‘Acute — Surgical’THEN 1
WHEN ‘Unassigned/Hospice’THEN 2
WHEN ‘NICU’THEN 4
WHEN ‘GeriPsych’THEN 5
WHEN ‘SNF/Swing’THEN 6 else 1000 end as SortingOrder
FROM [DWD_Staging].[dbo].[MonthlyStatistics]
WHERE [Year] =@Year AND [MONTH]=@Month AND [ORGANIZATION] IN (SELECT * FROM dbo.splitstring(@Organization,’,’))
AND STAT_NAME = ‘Admissions’
GROUP BY ORGANIZATION,STAT_NAME,STATS_LINE_ITEM,Year,MONTH,CurMonth,PrevMonth,YTD,YTDPrev

union all

Select
‘Acute-Total’as [STATS_LINE_ITEM]
,SUM(CurMonth) AS CurMonth
,SUM(PrevMonth) AS PrevMonth
,SUM(YTD) AS YTD
,SUM(YTDPrev) AS YTDPrev

,case when (coalesce(SUM(CurMonth),0) + coalesce(SUM(PrevMonth),0) + coalesce(SUM(YTD),0) + coalesce(SUM(YTDPrev),0)) = 0 then -1 else 3 end as SortingOrder
from (
SELECT distinct
CAST(ISNULL([CurMonth],0) AS decimal(18,0)) AS CurMonth
,CAST(ISNULL([PrevMonth],0) AS decimal(18,0)) AS PrevMonth
,CAST(ISNULL([YTD],0) AS decimal(18,0)) AS YTD
,CAST(ISNULL([YTDPrev],0) AS decimal(18,0)) AS YTDPrev

FROM [DWD_Staging].[dbo].[MonthlyStatistics]
WHERE [Year] =@Year AND [MONTH]=@Month AND [ORGANIZATION] IN (SELECT * FROM dbo.splitstring(@Organization,’,’))
AND STAT_NAME = ‘Admissions’ and STATS_LINE_ITEM in
(‘Acute — Medical’, ‘Acute — Surgical’,’Unassigned/Hospice’)
) as A
)as Total
where SortingOrder <> -1
Order by Total.SortingOrder

END

Next, in Application Studio, I created a relational list which referred to this Stored Procedure and included the list as a HyperBlock to the report.

Relational List for Admissions in Application Studio

The next issue I encountered was when the Total needed to be calculated within the report. If I had used a direct SUM function, the Acute-Total value would have been added to the final total; which would be incorrect. The final total for admissions needed to be the sum of all the row headers excluding the Acute-Total. After doing some research, I came across a function named ‘SUMIF2’ which will sum up values based on a specific condition which seemed like the perfect solution to overcome this issue.

Before directly using the SUMIF2 formula I had to make sure I maintained a flag to identify the ‘Acute-Total’ row in my report, as that was the row which needed to be avoided in my calculation. Therefore, I added another column (hidden column) within the relational list to maintain this flag which would be ‘TRUE’ if the row header was Acute-Total, and ‘FALSE’ if it was any other row header. The condition is shown below:

=IF(G15=”Acute-Total”TRUE,FALSE)

The output of the result, after incorporating the condition above is shown below

TRUE/FALSE Condition In Application Studio

I was finally able to use the SUMIF2 Function in my report for the Total calculation. The arguments required for this function related to the above-mentioned scenario are given below:

  • Comparison Range — This refers to the column which contains the TRUE/FALSE In general this is basically the condition of the calculation.
  • Comparison Operator — This would be “=” as we want to add up the cells which are equal to the required condition.
  • Comparison Value — The FALSE values that need to be added up.
  • Value Range — This refers to the cell that the SUMIF2 function is required for. It would be the cell which contains the values that need to be added up.

For this particular scenario the SUMIF2 function would be as follows:

=SUMIF2(B15:C15,”=”,FALSE,C15)

By making the required modifications to the Stored Procedure and by using the SUMIF2 Function, I was able to develop this report to the given requirement; overcoming all the obstacles I faced along the way. This function helped me to develop an identical report which our customer had requested, without having to propose any changes to the given requirement.


Author:
Amritha Fernando
Associate Consultant – Business Intelligence

Amritha has always had a passion for learning. Joining Fortude in 2015 as an Associate BI Consultant, her passion for learning has allowed her to solve unique problems faced by Fortude’s BI clients. In addition to Infor BI and Birst, she has gained experience in Microsoft Power BI, SQL Server, SSAS, and SSIS. Her expertise spans across healthcare, apparel, food & beverage domains.