evan.warrick

Thank you!

iReport is erroring when running SQL using a temp table. Does anyone know if this SQL is not supported by Jasper? Item_Production is in a flat format while CN_Log is in a attribute-value format. So I need to transform Item_Production to attribute-value format for the join with CN_log to work. If(OBJECT_ID('tempdb..#tempItemProductionKV') Is Not Null) Drop Table #tempItemProductionKV CREATE TABLE #tempItemProductionKV ( RowNumber INT PRIMARY KEY IDENTITY (1, 1), Item_Number VARCHAR (100), [Attribute Name] VARCHAR (1000), [Attribute Restricted Name] VARCHAR (1000), [Previous Value] VARCHAR (1000), [New Value] VARCHAR (1000), [Attribute Value in Production] VARCHAR (5000), [update Datetime] DATETIME); DECLARE @ColumnName AS NVARCHAR(MAX), @selectCol AS NVARCHAR(MAX), @dml AS NVARCHAR(MAX) SET @selectCol= ( select STRING_AGG('CAST([' + CAST(x.restricted_name as NVARCHAR(MAX)) + ] as NVARCHAR(MAX)) as [' + CAST(x.restricted_name as nvarchar(max)) + ]', ',') FROM (SELECT DISTINCT fa.restricted_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a INNER JOIN B_FORMAT_ATTR fa on fa.FORMAT_ATTR_ID = RIGHT(a.COLUMN_NAME, LEN(a.column_name)-2) WHERE VIEW_NAME = 'ITEM_Production' AND COLUMN_NAME like 'F%' AND RESTRICTED_NAME<> 'Item_Number') x) SET @ColumnName= ( select STRING_AGG('[' + CAST(x.restricted_name as NVARCHAR(MAX)) + ]', ',') FROM (SELECT DISTINCT fa.restricted_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a INNER JOIN B_FORMAT_ATTR fa on fa.FORMAT_ATTR_ID = RIGHT(a.COLUMN_NAME, LEN(a.column_name)-2) WHERE VIEW_NAME = 'ITEM_Production' AND COLUMN_NAME like 'F%' AND RESTRICTED_NAME<> 'Item_Number') x) SET @dml = 'SELECT Item_Number,b.Name as [Attribute Name],KeyRestrictedName as [Attribute Restricted Name],c.CN_Old_Value as [Previous Value],c.CN_New_Value as [New Value], KeyValue as [Attribute Value in Production],c.CN_Changed_Datetime as [update Datetime] FROM (SELECT Item_Number,' + @selectCol + ' FROM ITEM_Production) x unpivot ( KeyValue for KeyRestrictedName in (' + @ColumnName + ') ) u Join B_FORMAT_ATTR b on b.RESTRICTED_NAME=u.KeyRestrictedName and PROFILE_ID=10117 Join CN_Log c on c.CN_PK1= Item_Number and c.CN_Attribute_Name=b.Name Where CN_Changed_Datetime >= ''2021-08-15'' and CN_Changed_Datetime <= ''2021-12-15'' Order by InternalRecordId ASC' Insert Into #tempItemProductionKV EXEC sp_executesql @dml Select * from #tempItemProductionKV

My DB date format example: 2021-09-01 14:52:00.157 I'm using 2 parameters in report both with Parameter Class = java.util.Date Parameter1 = Date Range From Parameter2 = Date Range To How do I configure iReport to handle my DB date format? My report query: select * from CN_Log inner join Item_Staging on CN_Log.CN_PK1 = Item_Staging.Item_Number where CN_PK1 = '5VX740' and CN_Change_ID = '595448' and Last_Updated >= $P{Date Range From} and Last_Updated <= $P{Date Range To}

This is resolved.

I'm creating a Jasper Report but 1 of the columns in the source VIEW table is in XML format. Can iReport parse the XML?

I'm running an old version due to client requirements: Jaspersoft iReport Designer Professional 5.2.0

Here is the DB JDBC connection: JDBC Driver = MS SQLServer (2005) (com.microsoft.sqlserver.jdbc.SQLServerDriver) JDBC URL = jdbc:sqlserver://db.enterworks.internal:1433;databaseName=JasperReports;SelectMethod=cursor

My datasource connection tested ok but my SQL test query results in this error: The active connection is not a JasperReports DataSource provider.