Skip to Content
Technical Articles
Author's profile photo Kandadi Sreelatha

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

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.


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.



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.


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Nikita Bhatnagar
      Nikita Bhatnagar

      This is good 🙂

      Author's profile photo Vinay Potdar
      Vinay Potdar

      Excellent Sreelatha.

      Author's profile photo Kandadi Sreelatha
      Kandadi Sreelatha
      Blog Post Author

      Thanks Vinay!

      Author's profile photo Loredana Loghin
      Loredana Loghin

      Hi Sreelatha,

      I hope you can help me with solving a dilemma about building a report that contains custom columns. I have read the material provided by you and I find it very well documented. That's why I want to thank you very much.

      I use some custom columns that I manage at item level. Using the schema provided by you I was able to get data from the first custom column (number 10 - course category) but when I want to replicate the sql sequence and the other columns (number 20) I am returning a duplicate alias type error. To avoid this error, I used a subselect. This time I do not receive an error message, but instead the report does not show information for the 20th column.

      SELECT *
      (SELECT cpntdetail.*,


      NVL(cu.user_value, '-') as courseCategory,
      (SELECT NVL(cu2.user_value, '-') FROM pa_cpnt_user cu2
      WHERE cu2.cpnt_id = c.cpnt_id
      AND cu2.user_value(+) = urc.user_id and cu2.col_num='20') as learningNeeds


      AND cu.user_value(+) = urc.user_id and cu.col_num='10'

      Best Regards,

      Author's profile photo Anthony Rosado
      Anthony Rosado

      Hello Sreelatha!

      As you seem to be a PRD expert and because I don’t know any other way to contact you, I would like to ask you some PRD related question in this Comments Section:

      I would like to know more about “Before factory” property on LMS Plateau Report Designer Reports.

      For example, this property is used by “CertificateOfompletionByItem” or “UserDataCSV” standard reports to extract the data in two different data sets depending of the value of a report parameter.

      Is it possible to do the same thing based on some item attribute like “Item Classification”? Extract automatically in data set 1 for online items, in data set 2 for blended items, etc.

      If possible, can you help explaining how to do it?

      Thank you.

      Best regards.

      Author's profile photo s sanjeevegowda
      s sanjeevegowda

      Hi Sreelatha,

      i am facing one issue

      I need a syntax query sample to get only yestreday's learning completed in the history report where we use Completion date with string as Date/ Date Time as filter for report according to date.
      Presently i have tested the query using below 3 queries which is not working properly and it is taking timestamp of the report prepared or i don't know. if i add below any one query and run the report on 16th Feb then it will show 15th which is correct but when i run same report on 17th it should show 16th dated data but it will again show 15th date data only.
      Query 1. trunc (a.compl_dte) = to_date (to_char (sysdate - 1,'DD-MM-YYYY'), 'DD-MM-YYYY')

      Query 2. trunc (a.compl_dte) = trunc (sysdate -1)

      Query 3. trunc (a.compl_dte) = trunc (sysdate) -1

      please let me know if you have any idea or faced similar issue in past.



      Author's profile photo Uwe STIEGLITZ

      Dear Sreelatha,

      thanks a lot; this really helped me creating my reports. But I am stuck currently with defining the search parameters. My reports deliver everything I want, but the columns, which I want to use as search parameters, are not simple DB fields, but functions. One is a case, going either for the class closed date or for the online item start date.

      What can I do to use such a column as a search parameter in SF LRN? I used the normal process, but it did not work...

      Thanks in advance, Uwe

      Author's profile photo Maria Del Consuelo Caballero Leal
      Maria Del Consuelo Caballero Leal

      Dear Sreelatha

      Thanks a lot to share us


      I have a report with a background image but it doesn´t show how I can change this image, is not a logo is like a Master page


      Thanks in advance