SQL

USE [AZUS_Processing_Prod]
GO
/****** Object: StoredProcedure [dbo].[sp_jasper_reports] Script Date:
11/2/2023 10:52:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
--
===================================================================================
===========================================
--
-- Object Name : [dbo].[sp_jasper_reports]
-- Author : true
-- Date :
-- Test Script : EXEC [sp_jasper_reports]
-- Description : Populate Reports for Jasper DB
-- Last Modified Changes : Populate Version in Jasper Tables
-- Prerna Bharti, 13 Mar 2018
-- Last Modified Changes : Changes for Dynamic Reporting Table
-- Prerna Bharti, 18 Jan 2019
-- Last Modified Changes : Changes for Dynamic Reporting Table at same and
different server
-- Prerna Bharti, 16 Apr 2019
-- Last Modified Changes : Changes for Standard Reporting and Transpose
--
--
===================================================================================
===========================================
*/
ALTER PROCEDURE [dbo].[sp_jasper_reports]

@Report_ID NVARCHAR(100)
,@Success_Flag INT OUTPUT
,@Error_Msg NVARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE @Procedure_Name AS NVARCHAR(500)
DECLARE @Parameter_Names AS NVARCHAR(MAX)
DECLARE @Parameter_Values AS NVARCHAR(MAX)
SET @Procedure_Name = '[dbo].[sp_jasper_reports]'

SET @Parameter_Names = '@Report_ID'
SET @Parameter_Values = @Report_ID
--Procedure which creates the log into t_IC_Param_Log

EXEC IC_Proc_Param_Log @Procedure_Name
,@Parameter_Names
,@Parameter_Values
SET NOCOUNT ON;
BEGIN TRY
SET XACT_ABORT ON
DECLARE @Reporting_Period DATE
DECLARE @Eligibility_ID NVARCHAR(MAX)
DECLARE @Roster_ID NVARCHAR(MAX)
DECLARE @Alignment_ID NVARCHAR(MAX)
DECLARE @Component_Name NVARCHAR(MAX)
DECLARE @Component_Type NVARCHAR(MAX)
DECLARE @Measure_Table NVARCHAR(MAX)
DECLARE @Report_Type NVARCHAR(MAX)
DECLARE @col NVARCHAR(MAX)
DECLARE @mes NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @Reporting_Year NVARCHAR(100)
DECLARE @business_unit NVARCHAR(2000)
DECLARE @Reporting_Month NVARCHAR(100),@Reporting_Semester
VARCHAR(10),@Reporting_Quarter VARCHAR(10),@Reporting_Week VARCHAR(10)
DECLARE @Type_Table NVARCHAR(2000)
DECLARE @geo_label NVARCHAR(100)
DECLARE @emp_label NVARCHAR(100)
DECLARE @role_list NVARCHAR(1000)
DECLARE @measureid NVARCHAR(1000)
DECLARE @schemaName NVARCHAR(1000)
DECLARE @measureinstanceid NVARCHAR(1000)
DECLARE @Year NVARCHAR(100)
DECLARE @Month NVARCHAR(100)
DECLARE @Version NVARCHAR(100)
DECLARE @reportingDBPrefix NVARCHAR(100)
DECLARE @ReportSchemaName NVARCHAR(100)
DECLARE @CurrentDBname NVARCHAR(100)
DECLARE @Quarter INT
DECLARE @TenantFlag INT
DECLARE @count INT
DECLARE @i INT
DECLARE @success_count INT
DECLARE @row INT
DECLARE @schema_count INT
DECLARE @isMasterDynamic BIT
DECLARE @isComponentDynamic BIT
DECLARE @Header_Flag BIT
DECLARE @ServerConfigFlag BIT
DECLARE @InputType NVARCHAR(100)
DECLARE @DatasetID NVARCHAR(100)
DECLARE @InstanceID NVARCHAR(100)
DECLARE @Parent_component_Name NVARCHAR(400)
DECLARE @Report_Table_name NVARCHAR(500)
DECLARE @Where_Clause NVARCHAR(max)
Declare @Report_Design Nvarchar(200)
Declare @Transposed_Count int
Declare @mes_Transposed Nvarchar(Max)
Declare @mes_UnTransposed Nvarchar(Max)
Declare @frequency Nvarchar(Max)
Declare @sql_query Nvarchar(Max)
Declare @periodid Nvarchar(Max)
Declare @ReportName Nvarchar(Max)
-----------------------------------master
table-----------------------------------------------------
--get the current database name
SELECT @CurrentDBname = DB_NAME()
SELECT @Eligibility_ID = Dataset1ID

,@Roster_ID = RosterID
,@Alignment_ID = AlignmentID
,@Reporting_Period = Reporting_Period
,@Report_Type = Report_Type
,@business_unit = BU_list
,@TenantFlag = Tenant_Flag
,@frequency = Frequency
,@Version = Version
,@isMasterDynamic = ISNULL(IsDynamicTable, 0)
,@Report_Design=isnull(Report_Design,'a')
,@ReportName=Report_Name
FROM t_HO_reports_master
WHERE Report_ID = @Report_ID
SET @Reporting_Year = YEAR(@Reporting_Period)

SET @Reporting_Month = DATENAME(MONTH, @Reporting_Period)
select @Reporting_Semester = CASE WHEN
DATEPART(quarter,@Reporting_Period) >= 3 THEN 'H2' ELSE 'H1' END,
@Reporting_Quarter = 'Q'+cast(DATEPART(quarter,@Reporting_Period)as
varchar),
@Reporting_Week = 'W'+cast(DATEPART(week,@Reporting_Period) as
varchar)
--fetch the schema name from T_Report_Table_Details
SELECT @ReportSchemaName = TableName
, @ServerConfigFlag = ISNULL(ServerConfigFlag, 0)
FROM T_Report_Table_Details
WHERE Report_Type LIKE 'Dynamic%'
-----------------------------------------------------------------------
-----------------------------
--------------- to get the translated month name for
Mexico-----------------------------------------
-----------------------------------------------------------------------
-----------------------------
--IF ((@Report_Type = 'Score Card' OR @Report_Type = 'ScoreCard' OR
@Report_Type = 'ScoreCards') AND @TenantFlag = 2)
--BEGIN
--SELECT @Reporting_Month = UPPER(Month_Italian)
--FROM Month_name
--WHERE Month_English = DATENAME(MONTH, @Reporting_Period)
--END
-------------------------------------create temp table for all

components--------------------------------------
CREATE TABLE #component_detail (
id INT identity(1, 1)
,component_name NVARCHAR(MAX)
,component_Type NVARCHAR(MAX)
,measure_table NVARCHAR(2000)
,isComponentDynamic BIT
,inputtype VARCHAR(100)
,datasetid NVARCHAR(100)
,instanceid NVARCHAR(100)
,Parent_Component_Name Nvarchar(400)
,Report_Design Nvarchar(100)
,Current_Mtable Nvarchar(500)
,Report_Table_name NVARCHAR(500)
,Where_Clause NVARCHAR(max)
,Seq Numeric(25,13)
)
-----------------------------------------------------------------------
----------------------------------
--Take only those components for which Measure is attached
--get the component name and measure attached to each report component
-----------------------------------------------------------------------
----------------------------------
INSERT INTO #component_detail (
component_name
,component_type
,measure_table
,isComponentDynamic
,inputtype
,datasetid
,instanceid
,Parent_Component_Name
,Report_Design
,Current_Mtable
,Report_Table_name
,Where_Clause
,Seq
)
SELECT DISTINCT Component_Name
,component_type
,CASE WHEN input_type = 'Measure' then 'ic_output.' +
current_Mtable
WHEN input_type = 'Input data' then 'dbo.' + current_Mtable
ELSE current_Mtable
END
,ISNULL(isDynamic,0)
,input_type
,Dataset_Id
,Instance_Id
,Parent_Component_Name
,@Report_design
,Current_Mtable
,Report_name
,filter_clause
,Seq
FROM REPORT_COMPONENT_MAPPING
WHERE reportid = @Report_ID
AND Current_Mtable IS NOT NULL AND Current_Mtable != 'None'
AND isS3='false' order by Seq -----to remove those components for
which no measure is attached
SELECT @count = count(*)

FROM #component_detail
PRINT '--- Component Detail Count ----- '

PRINT @count
SET @schema_count = 0
SET @row = 0
SET @success_count = 0
SET @i = 1
-------------------------------------data push in different

schema-----------------------------------------------
SELECT @schema_count = count(*)
FROM t_report_bu_mapping
WHERE report_type = @report_type
AND BU = 'All'
PRINT '----- @schema_count -----'

PRINT @schema_count
IF @schema_count = 1
BEGIN
SELECT @schemaName = SchemaName
AND BU = 'All'
END
ELSE
BEGIN
SELECT @schemaName = SchemaName
AND BU = @business_unit
END
PRINT '----- Schema name ----- '

PRINT @schemaName
----------------------update Status of
t_ho_reports_master-------------------
UPDATE t_ho_reports_master
SET error_log = NULL
,STATUS = 'Processing'
,Summary = NULL
-----------------------------------------------------------------------
-------------------
-----------------------Delete previous records from Jasper
table--------------------------
-----------------------------------------------------------------------
-------------------
WHILE (@i <= @count)

BEGIN
SELECT @component_name = component_name
,@Component_Type = Component_Type
,@isComponentDynamic = isComponentDynamic
,@Parent_Component_Name=Parent_Component_Name
,@Report_Table_name = Report_Table_name
,@Where_Clause = ISNULL(Where_Clause,'')
WHERE id = @COUNT-@i+1
PRINT '---- Component Type1 ---- '

PRINT @component_name
PRINT '---- Report Type1 ---- '
PRINT @Report_type
PRINT '---- schemaName1 ---- '
PRINT @schemaName
PRINT '---- isMasterDynamic ----'
PRINT @isMasterDynamic
PRINT '---- isComponentDynamic ----'
PRINT @isComponentDynamic
PRINT '---- Parent_Component_name ----'
PRINT @Parent_Component_name
Print @Report_design
Print @Component_name
Print @schemaName
Print '-----------Report Table Name-----------'
Print @Report_Table_name
IF(@isMasterDynamic = 0) AND (@isComponentDynamic = 0)

BEGIN
IF (@Report_Design='Standard' and @Report_Type <> 'Standad
Data Mart')
BEGIN
print 'abc'
SET @Type_Table =
@ReportSchemaName+'.'+@Report_Table_name
END
ELSE IF(@Report_Type='Payroll Report')
BEGIN
print 'abc1'
SET @Type_Table =
END
ELSE
BEGIN
print 'abc2'
SELECT @Type_Table = MAX(TableName)
FROM T_REPORT_TABLE_DETAILS
WHERE report_type = @Report_type
AND isnull(Component_Type, 'a') =
isnull(@Component_name, 'a')
AND isnull(Parent_Component_Name, 'a') =
isnull(@Parent_Component_name, 'a')
and isnull(Report_design, 'a') =
isnull(@Report_design, 'a')
AND SchemaName = @schemaName
END
PRINT '---- Type Table1 ----'
PRINT @Type_Table
PRINT @Report_Type
SET @sql = 'delete from ' + @Type_Table + ' where

report_id=''' + @Report_ID + ''''
PRINT @sql
PRINT '*****Delete Table Data *****'

--PRINT '@Type_Table ===== ' + @Type_Table
PRINT @Sql
EXECUTE (@sql)
END
ELSE IF(@isMasterDynamic = 1) OR (@isComponentDynamic = 1)
BEGIN
PRINT '*****Delete Dynamic Report Table Data *****'
SET @Type_Table = @ReportSchemaName
+'.t_'+@component_name+'_'+@Report_ID
SET @Type_Table = REPLACE(@Type_Table, ' ','_')
SET @sql = 'IF OBJECT_ID('''+ @Type_Table +''',''U'') IS
NOT NULL DROP TABLE '+ @Type_Table
PRINT @Sql
--to execute the command at linked server if its on
different db
IF @ServerConfigFlag = 1
BEGIN
PRINT '----- @ServerConfigFlag is 1 -----'
EXECUTE (@Sql) AT Linked
END
ELSE
BEGIN
PRINT '---- @ServerConfigFlag is 0 ----'
EXECUTE (@Sql)
END
END
SET @i = @i + 1
END
IF (@Report_Type = 'Standard Data Mart')

BEGIN
IF (@ServerConfigFlag=1)
BEGIN
print 33
SET @sql_query = 'DELETE FROM Linked.'+ @ReportSchemaName +
'.T_SD_Published_Report_Dimension WHERE ReportID =''' + @Report_ID
EXECUTE (@sql_query) AT LINKED
SET @sql_query = 'Select @periodid = Periodid from Linked.'+

@ReportSchemaName + '.T_SD_Time_Dimension
where frequency ='''+ @frequency+ ''' and
cast(Start_Date as Nvarchar(max)) <= '''+ cast(@Reporting_Period
as Nvarchar(max)) + ''' and cast(End_Date as nvarchar(max)) >= '''+
cast(@Reporting_Period as Nvarchar(max))
SET @sql_query = 'insert into Linked.'+ @ReportSchemaName +

'.T_SD_Published_Report_Dimension(ReportID,Periodid)
values ('''+@Report_ID+''','''+@periodid +''')'

END
ELSE
BEGIN
print 44
SET @sql_query = 'DELETE FROM '+@ReportSchemaName+
'.T_SD_Published_Report_Dimension WHERE Report_ID =''' + @Report_ID+''''
print(@sql_query)
EXECUTE (@sql_query)
SET @sql_query = 'insert into '+@ReportSchemaName+

'.T_SD_Published_Report_Dimension(Report_ID,Periodid) Select
'''+@Report_ID+''' ,Periodid from '+ @ReportSchemaName + '.T_SD_Time_Dimension
where frequency ='''+ @frequency+ ''' and
cast(Start_Date as Nvarchar(max)) <= '''+ cast(@Reporting_Period
as Nvarchar(max)) + ''' and cast(End_Date as nvarchar(max)) >= '''+
cast(@Reporting_Period as Nvarchar(max))+''''
Print(@sql_query)
EXECUTE (@sql_query)
END
end
------------------to count the total no of Jasper DB tables for record

insertion---------------------
SELECT @count = COUNT(*)
PRINT '****Inside Loop****'
-----------------------------------------------------------------------
-------------------------------
-------------------------Loop to insert records into Jasper
DB----------------------------------------
-----------------------------------------------------------------------
-------------------------------
SET @i = 1
WHILE (@i <= @count)
BEGIN
PRINT '********Component*********'
PRINT @i
PRINT '**************************'
SET @col = ''

SET @mes = ''
set @Type_Table=''
SELECT @measureinstanceid = b.MeasureInstanceID
,@component_name = a.Component_name
,@measure_table = a.Measure_table
,@measureid = b.MeasureID
,@isComponentDynamic = a.isComponentDynamic
,@InputType = a.inputtype
FROM #component_detail a
,t_measure_execution_detail b
WHERE id = @i
AND a.Measure_table = b.MeasureOutputTable
AND a.inputtype = 'Measure'
Print ('Testing')
Print @Where_Clause
SELECT @Component_Name = Component_name

,@isComponentDynamic = isComponentDynamic
,@InputType = inputtype
,@measure_table = Measure_table
,@datasetId = datasetId
,@InstanceId = instanceId
,@Parent_Component_name = Parent_Component_name
WHERE id = @i
AND inputtype != 'Measure'
print '-------Atul--------'
print @count
print @i
print @Component_Name
IF(@isMasterDynamic = 0) AND (@isComponentDynamic = 0)
BEGIN
IF (@Report_Design='Standard' and @Report_Type<> 'Standad
Data Mart')
BEGIN
SET @Type_Table =
END
ELSE IF(@Report_Type='Payroll Report')
BEGIN
SET @Type_Table =
END
ELSE
BEGIN
SELECT @Type_Table =
ISNULL(MAX(TableName),'Destination_Table')
FROM T_REPORT_TABLE_DETAILS
WHERE report_type = @Report_type
AND isnull(Component_Type, 'a') =
isnull(@Component_name, 'a')
AND isnull(Parent_Component_Name, 'a') =
and isnull(Report_design, 'a') =
isnull(@Report_design, 'a')
AND SchemaName = @schemaName
END
END
ELSE IF(@isMasterDynamic = 1) OR (@isComponentDynamic = 1)
BEGIN
SET @Type_Table = @ReportSchemaName
+'.t_'+@component_name+'_'+@Report_ID
END
print @Report_design
-----------------------------------------------------------------------------------
--------
PRINT 'emp Label ========= '
PRINT @emp_label
PRINT 'Col =============== '
PRINT @col
PRINT 'Type Tables ======== '
PRINT @Type_Table
PRINT 'Mes =============== '
PRINT @mes
PRINT 'Report ID ========= '
PRINT @Report_ID
PRINT 'MeasureTab ======== '
PRINT @measure_table
PRINT 'ReportingMonth ==== '
PRINT @Reporting_Month
PRINT 'Month ============= '
PRINT @Month
PRINT 'Year ============== '
PRINT @Year
PRINT 'Report Type ======= '
PRINT @Report_Type
PRINT 'ReportingYear ==== '
PRINT @Reporting_Year
PRINT 'Version =========== '
PRINT @Version
PRINT 'component_name ==== '
PRINT '@isComponentDynamic==='
PRINT 'parent_component_name==='
PRINT @parent_component_name
if CHARINDEX('.',@measure_table) > 0 and @measure_table not like

'%quota%'
BEGIN
SET @measure_table =REPLACE(@measure_table,'.','.[')
SET @Measure_Table = @Measure_Table + ']'
END
PRINT 'Updated MeasureTable ======== '

-------------- Transposed - to check whether need to transpose or not
Select @Transposed_Count=COUNT(*)
FROM Report_Component_Metric_Mapping
WHERE Component_name = @Component_Name
AND isnull(Parent_Component_type, 'a') =
AND Report_ID = @Report_ID
AND isnull(Parent_Component_type, 'a') =
AND mappedcolumn<>'none'
AND IsTranspose=1
print '**********Transposed count**********'
print @Transposed_Count
---------Alias name of @mes changed to Metric rather than mappedcolumn to handle
mapping of one column to multiple columns-----
BEGIN
PRINT '*************Col Prepare*****************'
SET @col = ''

SET @mes = ''
BEGIN
SELECT @col = COALESCE(@col, '') + ',' +
'"' + MappedColumn3 + '"'
,@mes = COALESCE(@mes, '') + ',' + CASE
WHEN a.format LIKE '€%'
THEN 'case when ' +
replace(formated_metric, MappedColumn, 'a.' + MappedColumn) + '=''€'' then ''€0''
else ' + replace(formated_metric, MappedColumn, 'a.' + MappedColumn) + ' end'
WHEN Metric_Dataformat LIKE 'Percentage'
AND ( a.format IS NOT NULL and
a.format !='None')
THEN 'case when ' +
replace(formated_metric, MappedColumn, 'a.' + MappedColumn) + '=''%'' then ''0%''
WHEN a.format LIKE '$%'
THEN 'case when ' +
replace(formated_metric, MappedColumn, 'a.' + MappedColumn) + '=''$'' then ''$0''
WHEN Metric_Dataformat = 'number'
AND ( a.format IS NOT NULL and
a.format !='None')
THEN 'case when ' +
replace(formated_metric, MappedColumn, 'a.' + MappedColumn) + '='''' then ''0''
When isnull(istranspose,0)=1
And ( a.format IS NULL or a.format
='None')
THEN replace(formated_metric,
MappedColumn, 'cast(a.' + MappedColumn+ ' as Nvarchar(4000))')
ELSE replace(formated_metric,
MappedColumn, 'a.' + MappedColumn)
END + ' ' + 'as' + ' ' + case when
@transposed_count>0 then '"' + Metric + '"' else '"' + MappedColumn + '"' end
FROM REPORT_COMPONENT_METRIC_MAPPING a
WHERE a.REPORT_ID = @Report_ID
AND isnull(a.component_name, 'a') =
isnull(@component_name, 'a')
AND isnull(Parent_Component_type,
'a') = isnull(@Parent_Component_name, 'a')
AND a.mappedcolumn <> 'none'

ORDER BY a.Sequence
END
END
-------------------Standard Report Transpose Format
Handling--------------------------
IF @Transposed_Count>0
BEGIN
SET @mes_Untransposed = ''

SET @mes_Transposed = ''
SET @col = ''
SELECT @mes_Untransposed =
COALESCE(@mes_Untransposed, '') + ',' + '"' + Metric + '"'
, @col = COALESCE(@col, '') + ',' + '"' +
MappedColumn3 + '"'
AND isnull(IsTranspose,0)=0
ORDER BY a.Sequence
SELECT @mes_transposed =
COALESCE(@mes_transposed, '') + ',' + '"' + Metric + '"'
ORDER BY a.Transposed_Sequence
PRINT @mes_transposed
IF OBJECT_ID('tempdb..#Component_Metric_Mapping_Transposed', 'U') IS NOT

NULL
DROP TABLE #Component_Metric_Mapping_Transposed
IF OBJECT_ID('tempdb..#Component_Metric_Mapping_Transposed', 'U') IS NOT
NULL
DROP TABLE #Component_Unpivot_Table
-------Transposed-- to get renamed column and sequence of transposed

columns----------
Select Metric,MappedColumn2,Transposed_Sequence into

#Component_Metric_Mapping_Transposed
ORDER BY a.Transposed_Sequence
END
-----------------------------------------------------------------------------------
----
IF @mes_Transposed<>''
SET @mes_Transposed = right(@mes_Transposed,
len(@mes_Transposed) - 1)
IF @mes_Untransposed<>''
SET @mes_Untransposed = right(@mes_Untransposed,
len(@mes_Untransposed) - 1)
IF @COL<>''
SET @col = right(@col, len(@col) - 1)
IF @mes<>''
SET @mes = right(@mes, len(@mes) - 1)
SET @row = 0
PRINT '******************* Before Insert parameters

values ********************************************'
PRINT 'TypeTable======='
PRINT @Type_table
Print 'Col========='
PRINT @col
Print 'mes======='
PRINT @mes
Print '@Report_ID===='
PRINT @Report_ID
Print '@Version======'
Print @Version
Print '@measure_table===='
Print '@Reporting_Month====='
PRINT @Reporting_Month
Print '@Reporting_Year===='
PRINT @Reporting_Year
PRINT '@isComponentDynamic ==== '
PRINT '@InputType ======'
PRINT @InputType
PRINT '@mes_untranspose ======'
PRINT @mes_Untransposed
PRINT '@mes_transpose ======'
PRINT @mes_transposed
PRINT '@@component_name ======'
PRINT '@@Parent_component_name ======'
PRINT 'WHERE CLAUSE'

PRINT @Where_Clause
if @Where_Clause='None' or @Where_Clause='' or @Where_Clause is null
begin
set @Where_Clause=''
end
else
begin
set @Where_Clause=replace(@Where_Clause ,'where',' where ( ') +')'
end
PRINT @Where_Clause
print @Report_Type
print 'atul'
---------------------------------------Insert Statement
Prepare--------------------------------
IF @isComponentDynamic != 1 ---NON Dynamic
BEGIN
IF @Report_Design='Standard'
BEGIN
print '12345'
print @Report_Type
IF @Transposed_Count>0
BEGIN
---- Transposed Component_Unpivot_Table used to apply

filter clause for transposed data
set @sql=''
IF (@InputType != 'Input data')
SET @sql = 'IF

OBJECT_ID(''tempdb..#Component_Unpivot_Table'', ''U'') IS NOT NULL
DROP TABLE #Component_Unpivot_Table'+CHAR(10)+CHAR(13)+'
SELECT ' + @mes + ' into
#Component_Unpivot_Table FROM ' + @measure_table + ' a '+@Where_Clause+'
INSERT INTO '+ @Type_Table + ' ('
+ @col + ',Metric,Value,Sequence,report_id, Version, Reporting_Month,
Reporting_Year,Reporting_Period,Reporting_Quarter,Reporting_Week,Reporting_Semester
)
SELECT
'+@mes_UnTransposed+',c.mappedcolumn2 as
Transpose_rename,b.value,c.Transposed_Sequence,''' + @Report_ID + ''' ,''' +
@Version + ''' ,''' + @Reporting_Month + ''' ,' + CAST(@Reporting_Year AS
NVARCHAR(10)) + ','''+CAST(@Reporting_Period AS NVARCHAR(10))
+''','''+@Reporting_Quarter+''','''+@Reporting_Week+''','''+@Reporting_Semester+'''
FROM (SELECT
'+@mes_Untransposed+' , Metric , Value
FROM #Component_Unpivot_Table
UNPIVOT(Value for Metric in
('+@mes_transposed+')) as unpvt) b,#Component_Metric_Mapping_Transposed c
WHERE b.Metric=c.Metric'
ELSE
SET @sql = 'IF

OBJECT_ID(''tempdb..#Component_Unpivot_Table'', ''U'') IS NOT NULL
DROP TABLE #Component_Unpivot_Table'+CHAR(10)+CHAR(13)+'
SELECT ' + @mes + ' into
#Component_Unpivot_Table FROM ' + @measure_table + ' a where
a.ObjectId='''+@DatasetID +''' and
a.InstanceId='''+@InstanceID+''''+replace(@Where_Clause,'where',' and ')+'
INSERT INTO '+ @Type_Table + ' ('
+ @col + ',Metric,Value,Sequence,report_id, Version, Reporting_Month,
Reporting_Year,Reporting_Period,Reporting_Quarter,Reporting_Week,Reporting_Semester
)
SELECT
'+@mes_UnTransposed+',c.mappedcolumn2 as
Transpose_rename,b.value,c.Transposed_Sequence,''' + @Report_ID + ''' ,''' +
@Version + ''' ,''' + @Reporting_Month + ''' ,' + CAST(@Reporting_Year AS
NVARCHAR(10)) +','''+CAST(@Reporting_Period AS NVARCHAR(10))
+''','''+@Reporting_Quarter+''','''+@Reporting_Week+''','''+@Reporting_Semester+'''
FROM (SELECT
'+@mes_Untransposed+' , Metric , Value
FROM #Component_Unpivot_Table
UNPIVOT(Value for Metric in
('+@mes_transposed+')) as unpvt) b,#Component_Metric_Mapping_Transposed c
WHERE b.Metric=c.Metric'
print(@sql)
END
ElSE IF (@Report_Type = 'Standard Data Mart')
begin
print '1234'
print @col
print @mes
if @Type_Table like '%Dimension%'
BEGIN
IF (@InputType != 'Input
data')
SET @sql = 'INSERT INTO ' +

@Type_Table + ' (' + @col + ',report_id) SELECT DISTINCT ' + @mes + ' ,''' +
@Report_ID + ''' FROM ' + @measure_table + ' a '+@Where_Clause
ELSE
@Type_Table + ' (' + @col + ',report_id) SELECT DISTINCT ' + @mes + ' ,''' +
@Report_ID + ''' FROM ' + @measure_table + ' a where (a.ObjectId='''+@DatasetID
+''' and a.InstanceId='''+@InstanceID+''''+')'+replace(@Where_Clause ,'where',' and
')
END
ELSE
BEGIN
@Type_Table + ' (' + @col + ',report_id) SELECT ' + @mes + ' ,''' + @Report_ID +
''' FROM ' + @measure_table + ' a '+@Where_Clause
ELSE
@Type_Table + ' (' + @col + ',report_id) SELECT ' + @mes + ' ,''' + @Report_ID +
''' FROM ' + @measure_table + ' a where (a.ObjectId='''+@DatasetID +''' and
a.InstanceId='''+@InstanceID+''''+')'+replace(@Where_Clause ,'where',' and ')
END
end
ElSE
BEGIN
SET @sql= 'INSERT INTO ' + @Type_Table + ' (' +
@col + ',report_id) select distinct ' + @mes + ' ,''' + @Report_ID + ''' FROM ' +
@measure_table + ' a '+@Where_Clause
ELSE
SET @sql= 'INSERT INTO ' + @Type_Table + ' (' +
@col + ',report_id) select distinct ' + @mes + ' ,''' + @Report_ID + ''' FROM ' +
@measure_table + ' a where (a.ObjectId='''+@DatasetID +''' and
END --Standard
END
ElSE IF (@Report_Type = 'Roster Push')

BEGIN
SET @sql = 'INSERT INTO ' + @Type_Table +
' (' + @col + ',report_id, Version) select ' + @mes + ' ,''' + @Report_ID +
''' ,''' + @Version + ''' FROM ' + @measure_table + ' a '+@Where_Clause
ELSE
''' ,''' + @Version + ''' FROM ' + @measure_table + ' a where
(a.ObjectId='''+@DatasetID +''' and
END
ElSE IF (@Report_Type = 'Scorecard')

BEGIN
print 'Scorecard------'
select @ReportName = items from
Split_String(@ReportName,'(') where t_pkey = 1
--print(@ReportName)
IF (@Where_Clause != '' )
SET @Where_Clause = @Where_Clause +
' and a.'+@ReportName+' = ''True'''
ELSE
SET @Where_Clause = @Where_Clause +
' where a.'+@ReportName+' = ''True'''
print 'Where Clause---'
print @Where_Clause

ELSE
PRINT @sql
END
ElSE IF (@Report_Type = 'Performance Summary Report')
BEGIN
print 'Performance Summary Report------'
select @ReportName = items from
Split_String(@ReportName,'(') where t_pkey = 1
IF (@Where_Clause != '')
SET @Where_Clause = @Where_Clause + ' and
a.'+@ReportName+' = ''True'''
ELSE
SET @Where_Clause = @Where_Clause + '
where a.'+@ReportName+' = ''True'''

ELSE
PRINT @sql
END
ELSE IF(@isMasterDynamic = 1)
BEGIN
SET @sql='select '+ @mes+' INTO
'+@Type_Table+' FROM '+@measure_table+' a '+@Where_Clause
ELSE
SET @sql='select '+ @mes+' INTO
'+@Type_Table+' FROM '+@measure_table+' a where (a.ObjectId='''+@DatasetID +''' and
END
ELSE IF @isComponentDynamic = 1
BEGIN
PRINT '*******Dynamic Reports*********'

SET @sql = 'SELECT '+ @mes+' INTO
'+@Type_Table+' FROM '+ @measure_table + ' A ' +@Where_Clause
else
SET @sql = 'SELECT '+ @mes+' INTO
'+@Type_Table+' FROM '+ @measure_table + ' A where (a.ObjectId='''+@DatasetID +'''
and a.InstanceId='''+@InstanceID+''''+')' +replace(@Where_Clause ,'where',' and ')
END
else BEGIN
IF (@TenantFlag = 1) --US Tenant

BEGIN
@Type_Table + ' (' + @col + ',report_id, Version) SELECT ' + @mes + ' ,''' +
@Report_ID + ''' ,''' + @Version + ''' FROM ' + @measure_table + ' a
'+@Where_Clause
ELSE
@Type_Table + ' (' + @col + ',report_id, Version) SELECT ' + @mes + ' ,''' +
@Report_ID + ''' ,''' + @Version + ''' FROM ' + @measure_table + ' a where
END
IF (@TenantFlag = 2) --Mexico Tenant
BEGIN
@Type_Table + ' (' + @col + ',report_id, Version, Reporting_Month, Reporting_Year)
SELECT ' + @mes + ' ,''' + @Report_ID + ''' ,''' + @Version + ''' ,''' +
@Reporting_Month + ''' ,' + CAST(@Reporting_Year AS NVARCHAR(10)) + ' FROM ' +
@measure_table + ' a '+@Where_Clause
ELSE
@Type_Table + ' (' + @col + ',report_id, Version, Reporting_Month, Reporting_Year)
SELECT ' + @mes + ' ,''' + @Report_ID + ''' ,''' + @Version + ''' ,''' +
@Reporting_Month + ''' ,' + CAST(@Reporting_Year AS NVARCHAR(10)) + ' FROM ' +
@measure_table + ' a WHERE (a.ObjectId='''+@DatasetID +''' and
END
END
END--Insert statement Prep
-------------------execute and update ho report

summary------------
PRINT 'col ==' + @col
PRINT 'mes ==' + @mes
------------------Execuute the Reports insert data

statement-------------------
PRINT 'INSERT Statement --------->>>>>>>>>>'
--For dynamic reports
IF(@isMasterDynamic = 1 OR @isComponentDynamic = 1)
BEGIN
PRINT '*******Dynamic Reports*********'
IF @ServerConfigFlag = 1
BEGIN
DECLARE @createCommand NVARCHAR(MAX);

DECLARE @minSeq INT;
DECLARE @maxSeq INT;
DECLARE @columnname NVARCHAR(MAX);
DECLARE @metricType NVARCHAR(MAX);
DECLARE @columnValue NVARCHAR(MAX);
Select @minSeq = min(Sequence), @maxSeq =

max(Sequence) from Report_Component_Metric_Mapping
where Report_ID = @Report_ID and Component_name =
@Component_Name
SET @Type_Table = REPLACE(@Type_Table,' ','_')
SET @createCommand = 'Create table '+@Type_Table+'('
WHILE @minSeq <= @maxSeq
BEGIN
select @metricType = Metric_DataType ,
@columnname = MappedColumn from Report_Component_Metric_Mapping
where Report_ID = @Report_ID and
Component_name = @Component_Name and Sequence = @minSeq
SET @columnValue = ' '+@columnname+'

NVARCHAR(4000),'
SET @createCommand =
CONCAT(@createCommand,@columnValue)
set @minSeq = @minSeq + 1
END
set @createCommand =
SUBSTRING(@createCommand,1,DATALENGTH(@createCommand)-1)
set @createCommand = CONCAT(@createCommand,')')
print '----- create table query ----- '
print @createCommand
Execute(@createCommand) At Linked

SET @sql = 'INSERT INTO '+@Type_Table+' SELECT
'+@mes+'FROM Linked.'+ @CurrentDBname + '.' +@measure_table + ' A ' +
(@Where_Clause)
ELSE
'+@mes+' FROM Linked.'+ @CurrentDBname + '.' +@measure_table + ' A WHERE
(a.ObjectId='''+@DatasetID +''' and a.InstanceId='''+@InstanceID+''''
+')'+replace(@Where_Clause ,'where',''' and ')
print '--------------------------------------------'
PRINT @sql
Execute(@sql) At Linked
END
ELSE
BEGIN
DECLARE @createCommand1 NVARCHAR(MAX);
DECLARE @minSeq1 INT;
DECLARE @maxSeq1 INT;
DECLARE @columnname1 NVARCHAR(MAX);
DECLARE @metricType1 NVARCHAR(MAX);
DECLARE @columnValue1 NVARCHAR(MAX);
Select @minSeq1 = min(Sequence), @maxSeq1 =

max(Sequence) from Report_Component_Metric_Mapping
where Report_ID = @Report_ID and Component_name =
@Component_Name
SET @Type_Table = REPLACE(@Type_Table,' ','_')
SET @createCommand1 = 'Create table '+@Type_Table+'('
WHILE @minSeq1 <= @maxSeq1
BEGIN
select @metricType1 = Metric_DataType ,
@columnname1 = MappedColumn from Report_Component_Metric_Mapping
where Report_ID = @Report_ID and
Component_name = @Component_Name and Sequence = @minSeq1
SET @columnValue1 = ' '+@columnname1+'

NVARCHAR(4000),'
SET @createCommand1 =
CONCAT(@createCommand1,@columnValue1)
set @minSeq1 = @minSeq1 + 1

END
print '---- create commmnd -----'

print @createCommand1
set @createCommand1 =
SUBSTRING(@createCommand1,1,DATALENGTH(@createCommand1)-1)
print '---- create commmnd 1 -----'
set @createCommand1 = CONCAT(@createCommand1,')')

print '---- create commmnd 2-----'
print '----- create table query ----- '

Execute(@createCommand1)
'+@mes+' FROM '+ @measure_table + ' A ' +@Where_Clause
ELSE
'+@mes+'FROM '+ @measure_table + ' A WHERE (a.ObjectId='''+@DatasetID +''' and
print '-------start1----'
PRINT @sql
print '-------start2----'
EXECUTE (@sql)
END
END
--For non-dynamic reports execute normal query
ELSE
BEGIN
PRINT 'For non-dynamic reports'
PRINT @sql
EXECUTE (@sql)
END
SET @row = @@rowcount

print @row
SET @success_count = @success_count + CASE
WHEN @row > 0
THEN 1
ELSE @row
END
PRINT '@@@@@@@@@@@@UPDATING t_HO_reports_master Summary

Field@@@@@@@@@@@'
PRINT 'Row Count '
PRINT @row
PRINT 'Table Type == ' + @type_table
---------Update Summary field of t_ho_reports_master on

successful proc execution-------------
SET summary = CASE
WHEN summary IS NULL
THEN cast(@row AS NVARCHAR(100)) + ' Records
inserted in ' + @component_name
ELSE summary + ', ' + cast(@row AS NVARCHAR(100)) + '
Records inserted in ' + @component_name
END
WHERE report_id = @report_id
SELECT @Header_Flag = Header_Flag
FROM T_Report_TABLE_Details
WHERE TableName = @Type_Table
--Call HEader insert procedure

IF (@isMasterDynamic = 0 AND @Header_Flag = 1)
BEGIN
PRINT '***********Calling sp_Scorecard_Header_Insert
PROC***********'
EXEC sp_Scorecard_Header_Insert @Report_ID, @Type_Table,
@component_name, @measureinstanceid
END
SET @i = @i + 1
END
-----------------------------------------------------------------------
----------------------------------
----------------------------------------------------LOOP
end---------------------------------------------
-----------------------------------------------------------------------
----------------------------------
--If no input is attached to the component

IF (@count = 0)
BEGIN
SET summary = 'No input is attached to any of the component.
Please attach an input.'
END
-----------------------------------------------------------------------
------------
----Calling Header Insertion Proc for Mexico Tenant
-----------------------------------------------------------------------
------------
IF (@TenantFlag = 2)
BEGIN
--Calling payout insertion stored proc
IF (@Report_Type = 'Score Card' OR @Report_Type = 'ScoreCard' OR
@Report_Type = 'ScoreCards')
BEGIN
EXEC payout_insertion @Report_ID
END
--Calling Payroll Header Insertion stored proc

IF (@Report_Type = 'Payroll')
BEGIN
EXEC sp_payroll_header_insert @Report_ID
END
END
-----------------------------------------------------------------------
------------
--Updating Status of t_ho_reports_master
-----------------------------------------------------------------------
------------
BEGIN
PRINT 'success count = '
PRINT @success_count
SET STATUS = CASE
WHEN @success_count = 0
THEN 'Failed'
ELSE 'Pushed'
END
END
SET @Success_Flag = 1
END TRY
--END -- remove
-----------------------------------------------------------------------------
--------------
------------------------------------Catch
Block--------------------------------------------
-----------------------------------------------------------------------------
--------------
BEGIN CATCH
PRINT '******Catch Block*******'
DECLARE @Error_Source AS NVARCHAR(2000)

,@Error_Line AS INT
,@Error_Message NVARCHAR(2000)
,@Entity INT
PRINT @report_type
SET @Error_Source = @report_type + ' - ' + isnull(@component_type, '')

SET @Error_Line = ERROR_LINE()
SET @Error_Message = ERROR_MESSAGE()
SET @Entity = 0
EXEC IC_DB_Error_Log @Error_Source

,@Error_Line
,@Error_Message
,@Entity
SET error_log = @Error_Message
,STATUS = 'Failed'
,summary = isnull(@component_type, '-') + ' -' + @Error_Message
SET @Success_Flag = 0
/*Throw error message */

--SELECT @Error_Source AS ErrorSource
-- ,--@Error_Line AS ErrorLine
-- @Error_Message AS ErrorMessage;
DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
11, -- Severity.
@ErrorState -- State.
);
END CATCH
Declare @sqlmax NVARCHAR(500)
SET @sqlmax = 'EXEC' + ' '+ 'Linked.AZUS_Reporting_Prod' +'.[dbo].

[Update_PBI_Report_KeyData] '+''''+@Report_Type+
+''''+','+''''+@Report_ID+''''+','+''''+@Version+''''
print @sqlmax
EXECUTE (@sqlmax)
END

SQL

Uploaded by

Copyright:

Available Formats

SQL

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL

Uploaded by

Copyright:

Available Formats

USE [AZUS_Processing_Prod]

ALTER PROCEDURE [dbo].[sp_jasper_reports]

SET @Procedure_Name = '[dbo].[sp_jasper_reports]'

--Procedure which creates the log into t_IC_Param_Log

SET NOCOUNT ON;

Declare @ReportName Nvarchar(Max)

SELECT @Eligibility_ID = Dataset1ID

SET @Reporting_Year = YEAR(@Reporting_Period)

-------------------------------------create temp table for all

SELECT @count = count(*)

PRINT '--- Component Detail Count ----- '

-------------------------------------data push in different

PRINT '----- @schema_count -----'

PRINT '----- Schema name ----- '

WHILE (@i <= @count)

PRINT '---- Component Type1 ---- '

IF(@isMasterDynamic = 0) AND (@isComponentDynamic = 0)

SET @sql = 'delete from ' + @Type_Table + ' where

PRINT '*****Delete Table Data *****'

IF (@Report_Type = 'Standard Data Mart')

SET @sql_query = 'Select @periodid = Periodid from Linked.'+

EXECUTE (@sql_query) AT LINKED

SET @sql_query = 'insert into Linked.'+ @ReportSchemaName +

EXECUTE (@sql_query) AT LINKED

SET @sql_query = 'insert into '+@ReportSchemaName+

------------------to count the total no of Jasper DB tables for record

PRINT '****Inside Loop****'

SET @col = ''

SELECT @Component_Name = Component_name

if CHARINDEX('.',@measure_table) > 0 and @measure_table not like

PRINT 'Updated MeasureTable ======== '

SET @col = ''

AND a.mappedcolumn <> 'none'

SET @mes_Untransposed = ''

IF OBJECT_ID('tempdb..#Component_Metric_Mapping_Transposed', 'U') IS NOT

-------Transposed-- to get renamed column and sequence of transposed

Select Metric,MappedColumn2,Transposed_Sequence into

PRINT '******************* Before Insert parameters

PRINT 'WHERE CLAUSE'

---- Transposed Component_Unpivot_Table used to apply

SET @sql = 'IF

SET @sql = 'IF

SET @sql = 'INSERT INTO ' +

ElSE IF (@Report_Type = 'Roster Push')

ElSE IF (@Report_Type = 'Scorecard')

IF (@InputType != 'Input data')

IF (@InputType != 'Input data')

IF (@InputType != 'Input data')

IF (@TenantFlag = 1) --US Tenant

-------------------execute and update ho report

------------------Execuute the Reports insert data

DECLARE @createCommand NVARCHAR(MAX);

Select @minSeq = min(Sequence), @maxSeq =

SET @columnValue = ' '+@columnname+'

IF (@InputType != 'Input data')

Select @minSeq1 = min(Sequence), @maxSeq1 =

SET @columnValue1 = ' '+@columnname1+'

set @minSeq1 = @minSeq1 + 1

print '---- create commmnd -----'

PRINT 'Delete Table Data '

PRINT 'Inside Loop'

/Throw error message /