Rowset FILL Method with JOIN / UNION Keyword

FILL method helps to fill stand alone rowset from database directly.

But sometimes it is required to fill rowset Using JOIN / UNION Keyword.

1. Using JOIN Keyword.

Local Rowset &rs = Createrowset(Record.TL_RPTD_TIME);

&rs.Fill("INNER JOIN PS_TL_RPTD_TIME TM ON TM.EMPLID = FILL.EMPLID WHERE FILL.PUNCH_TYPE = '0' AND FILL.EMPLID = :1", &emplid);

2. Using UNION Keyword.

Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);

Configuring Report Manager

 

Steps to configure and get Reports in Report manager.

1. Enable getting Reports in Report Manager.

2. Add New Folders.

3. Get new files to Process Monitor.

4. Assign/Create required Permission Lists/Roles to Users.

5. Configure the process.

6. Create a new run control id.

7. Run it.

8. Verify it.

 

For any reports to be visible to other people in Report Manager we need to do the following:

1. Activate the following Service Operations

• · Check Active (in Default Service Operation Version)

2. To Add New Folders, add them here. This is just to group by folders and has no security tied to it.

•   Verify they come up

3.  To get new files to Process Monitor

· Add PRL in System Settings –> Distribution File Options –> Add PRL & Description

• Bounce the Process Scheduler
• After running the report verify that the file is pulled up

4.  Create new permission list

• Add Report Manager access and Report Viewer access.

• ·Click Select All to give the Report Viewer Page access.

• Add other Reports access. This gives the option to run the reports. If the users don’t want/need to run the reports this may be skipped.

5.  Add new Role

• Add this role to users

• Verify required users got the Role

6.  Change the Process Destination, set the Destination Source, Folder Name and Distribute To.

7.   Run the process

• Create a new run control id

• Chose the process

• To ensure everything is correct, Click on the Job (Description)

• Click on Distribution of required Process, we must be able to see the given Folder Name and the Role.

8.  Verify the distribution by going to Report Manager –> List

 

Thanks a lot to my friend Ranjan Patro (http://in.linkedin.com/in/ranjanpatro) for creating this document.