Hi,

I have a requirement to write a report using InfoAssist. User can create pivot table when the report is run (as this is an Active Report output) but we need to display the information using a pivot table, by default.

Below is sample data which is displayed on the report (or the source data).

  

PROCESS_DATE PROCESS_ID	START_DATETIME		STATUS_ID	TICKET_ID	DERIVED_DATA (Seq_ID)
2020-05-01   PVTRJVC	2020-05-01 01:12	4		BC909889	1
2020-05-01   PVTRJVC	2020-05-01 01:15	7		KD113478	2
2020-05-01   PVTRJVC	2020-05-01 01:20	2		KD113478	3
2020-05-01   PTRJVCK	2020-05-01 01:02	5		BC789889	1
2020-05-01   PTRJVCK	2020-05-01 01:15	8		BC789889	2
2020-05-01   PTRJVCK	2020-05-01 01:20	1		PPTQT890	3
2020-05-02   MSKUSER	2020-07-01 01:02	4		LM878982	1
2020-05-02   MSKUSER	2020-08-01 01:15	9		VACHAM91	2
2020-05-02   MSKUSER	2020-08-01 01:20	5		AAPKQT04	3

Notes:

1. PROCESS_DATE and PROCESS_ID are related. The PROCESS_ID is always unique.
2. START_DATETIME is related to events and STATUS_ID values are related.
3. A TICKET_ID will go through various statuses.
4. DERIVED_DATA which is a sequence id based on PROCESS_ID. When the PROCESS_ID changes the DERIVED_DATA value is reset to 1 else incremented.

The report should by default display only the following output.

 

PROCESS_DATE	STATUS_ID	COUNT
2020-05-01	4		1
2020-05-01	5		1
2020-05-02	4		1

So basically, the report should only display the count of distinct PROCESS_IDs by Status and Process Date values.

I hope that I am able to present the sample data and report output needed neatly.

Kind regards,

Manoj.

This message has been edited. Last edited by: Manoj,

Does something such as this gives you the proper result ?

TABLE FILE abc
SUM CNT.DST.PROCESS_ID AS 'COUNT'
BY PROCESS_DATE
BY STATUS_ID
END

Hi Martin,

Thanks for your reply. I guess (as I have struggled with how I can translate the "SUM CNT.DST.PROCESS_ID" syntax in InfoAssist - I could manage only "CNT.DST.PROCESS_ID") it won't give proper results due to relationships between PROCESS_ID to TICKET_ID (1:M) and TICKET_ID to STATUS_ID (1:M) there is always some extra 'Count'.

 

ALTER session SET nls_date_format = 'YYYY-MM-DD HH24:MI';
WITH core_data (process_date, process_id, start_datetime, status_id, ticket_id) AS
		(SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:12', 'YYYY-MM-DD HH24:MI'), 4, 'BC909889'	FROM dual	UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:15', 'YYYY-MM-DD HH24:MI'), 7, 'KD113478' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:20', 'YYYY-MM-DD HH24:MI'), 2, 'KD113478' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:02', 'YYYY-MM-DD HH24:MI'), 5, 'BC789889' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:15', 'YYYY-MM-DD HH24:MI'), 8, 'BC789889' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:20', 'YYYY-MM-DD HH24:MI'), 1, 'PPTQT890' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:02', 'YYYY-MM-DD HH24:MI'), 4, 'LM878982' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:15', 'YYYY-MM-DD HH24:MI'), 9, 'VACHAM91' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:20', 'YYYY-MM-DD HH24:MI'), 5, 'AAPKQT04' FROM dual)
	SELECT  derived_data.process_date
		, derived_data.status_id
		, SUM (derived_data.seq_id) AS cnt
		FROM
			(SELECT  core_data.process_date
				, core_data.process_id
				, core_data.start_datetime
				, core_data.status_id
				, core_data.ticket_id
				, ROW_NUMBER () OVER (PARTITION BY core_data.process_id ORDER BY core_data.start_datetime ASC) AS seq_id
				FROM core_data
			) derived_data
		WHERE derived_data.seq_id = 1
		GROUP BY derived_data.process_date
		, derived_data.status_id
		ORDER BY derived_data.process_date
		, derived_data.status_id ;

 

Gives me the below result (which is expected).

 

PROCESS_DATE|STATUS_ID|CNT
2020-05-01 00:00|4|1
2020-05-01 00:00|5|1
2020-05-02 00:00|4|1


 

Whereas

 

ALTER session SET nls_date_format = 'YYYY-MM-DD HH24:MI';
WITH core_data (process_date, process_id, start_datetime, status_id, ticket_id) AS
		(SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:12', 'YYYY-MM-DD HH24:MI'), 4, 'BC909889' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:15', 'YYYY-MM-DD HH24:MI'), 7, 'KD113478' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PVTRJVC', to_date ('2020-05-01 01:20', 'YYYY-MM-DD HH24:MI'), 2, 'KD113478' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:02', 'YYYY-MM-DD HH24:MI'), 5, 'BC789889' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:15', 'YYYY-MM-DD HH24:MI'), 8, 'BC789889' FROM dual UNION ALL
		SELECT  to_date ('2020-05-01', 'YYYY-MM-DD'), 'PTRJVCK', to_date ('2020-05-01 01:20', 'YYYY-MM-DD HH24:MI'), 1, 'PPTQT890' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:02', 'YYYY-MM-DD HH24:MI'), 4, 'LM878982' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:15', 'YYYY-MM-DD HH24:MI'), 9, 'VACHAM91' FROM dual UNION ALL
		SELECT  to_date ('2020-05-02', 'YYYY-MM-DD'), 'MSKUSER', to_date ('2020-07-01 01:20', 'YYYY-MM-DD HH24:MI'), 5, 'AAPKQT04' FROM dual
		)
	SELECT  derived_data.process_date
		, derived_data.status_id
		, SUM (derived_data.cnt) AS cnt
		FROM
			(SELECT  core_data.process_date
				, core_data.status_id
				, COUNT (DISTINCT core_data.process_id) AS cnt
				FROM core_data
				GROUP BY core_data.process_date
				, core_data.status_id
			) derived_data
		GROUP BY derived_data.process_date
		, derived_data.status_id
		ORDER BY derived_data.process_date
		, derived_data.status_id;

 

gives me the following result.

 

PROCESS_DATE|STATUS_ID|CNT
2020-05-01 00:00|1|1
2020-05-01 00:00|2|1
2020-05-01 00:00|4|1
2020-05-01 00:00|5|1
2020-05-01 00:00|7|1
2020-05-01 00:00|8|1
2020-05-02 00:00|4|1
2020-05-02 00:00|5|1
2020-05-02 00:00|9|1


 

Kind regards,

Manoj.

Does anyone think that using the WebFOCUS report programming language the expected results can be displayed on the report?

I am trying to know it by creating basic reports.

Any inputs will be greatly appreciated.

Kind regards,

Manoj.

Manoj

It has been a week and no one has been able to give you additional guidance. If this is still an issue please open a case with techsupport if you haven't already.

Thanks Chuck.
We don't have the IBI Tech Support. So closing this discussion.
Regards,
Manoj.

I have used HOLD file concept in WebFOCUS. I was planning to focus on the language, but fortunately InfoAssist tool is sufficient to satisfy my requirement.