Developing LMS Custom Reports through Plateau Report Designer (Part-II)

[former_member101925]

In this blog, I will share my insight on redoing reports in PRD on few of the complex situations.

In my previous blog. I have discussed about how to customize the simple requirements using Plateau Reporting Tool like how to create new custom column, insert and delete columns within the table. In this blog, I am going to show few complex scenarios in PRD as I mentioned earlier that to do a complex reports in PRD one need to have sound knowledge in SQL.

Here are few scenarios with results:

Scenario 1:

In my recent deployment, there was a requirement to add ASSIGNMENT TYPE ID column in the report, which is not so complex but a person having very minimal knowledge on PRD is going to face difficulties in handling these kind of requirements.



Let see how we make to changes in the PRD query in order to get the above result.

Firstly, understand the requirement precisely and think how we can get the required data in the report. In order to get the required data check the data dictionary and search for the table name related to item details is stored.

In the above scenario, item details are stored in table PA_STUD_CPNT as shown in the below screenshot.



I have explained how to the export report from LMS system to PRD tool to make changes in my previous blog. Please refer to my previous blog.

Once you the export report is in PRD tool make changes to the query and apply changes as shown below.



 

Note: RTYP_ID is stored in multiple tables, it depends on your requirement to which table you want to refer because in the above query we have referred PA_STUD_CPNT.

A JOIN Clause is used to combine rows from two or more tables, based on a related column between them. Let’s see one example to understand above query.



if you observe the table’s “Customer ID” column in the order table refers to “Customer ID” in “Customer” table .The relationship between the above two tables is the “Customer ID” which is common in the both the tables.

LEFT JOIN Syntax

SELECT Order ID, Order Date, t1.Customer ID

FROM table1 t1

LEFT JOIN table2 t2 on t1. Customer ID = t2. Customer ID

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.







Scenario 2:

In another requirement client requested to add ASSIGNMENT TYPE DESCRIPTION to the column ASSIGNMENT TYPE as shown below



Firstly, check the data dictionary to find out from which table RTYP_DESP is getting pulled from. In the above case it is getting pulled from PA_RQMT_TYPE.



Now go that query to edit and change the query as shown below.



AS is alias name for column in SELECT Statement, it is used to change the label to reflect.





Once we get the required column in query, just drag column and drop in the table.











Note: If you tick referenced than it will create list of ID’s and description that you provide for a drop down list as shown in the above screen shot and if it is unchecked than it is non- referenced which will open text field to type what we desired for custom field.

Once you have created a custom column in LMS instance, in order to add that custom column in report we need to have understanding of the tables used to store the data.

Basically, system uses three tables to store the data in custom column. These three tables are different based on the entity that custom column is related to but if you are including user custom column in the report we would use the below three tables.



Note: System use USER_VALUE the system will use the USER_ID from the PA_USRRF_STUD table if it is referenced field or non-referenced field.

Understand that custom column for student coming from table PA_USRRF_STUD





Note: When we are maintaining custom columns, based on the custom column user value specifies to which category it belongs to.

Example: Custom Column = 10 than user value specify the same as shown below.



 

Conclusion:

Apart from the above scenarios there might be more requirements from the customer for customizing the reports in PRD but one need to gain more knowledge and experience to do so but whatever scenarios i have covered will definitely help you to understand how to handle various scenarios.

I hope this blog will be helpful to gain more knowledge in SQL and also to handle complex reports in PRD.