Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] InfoAssist: Can we create a pivot table that displays summary only?
Go
New
Search
Notify
Tools
Reply
  
[Solved] InfoAssist: Can we create a pivot table that displays summary only?
 Login/Join
 
Silver Member
posted
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,


Newbie.
WebFOCUS 8.2.0.3.
Client 8.2.0.4.
 
Posts: 43 | Location: UK | Registered: August 30, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2347 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Newbie.
WebFOCUS 8.2.0.3.
Client 8.2.0.4.
 
Posts: 43 | Location: UK | Registered: August 30, 2018Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Newbie.
WebFOCUS 8.2.0.3.
Client 8.2.0.4.
 
Posts: 43 | Location: UK | Registered: August 30, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1951 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks Chuck.
We don't have the IBI Tech Support. So closing this discussion.
Regards,
Manoj.


Newbie.
WebFOCUS 8.2.0.3.
Client 8.2.0.4.
 
Posts: 43 | Location: UK | Registered: August 30, 2018Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Newbie.
WebFOCUS 8.2.0.3.
Client 8.2.0.4.
 
Posts: 43 | Location: UK | Registered: August 30, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] InfoAssist: Can we create a pivot table that displays summary only?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.