Best practice forum (Archived)

Competencies - Reporting on Not Competency / Compliant

 
Patrick Appleyard
Competencies - Reporting on Not Competency / Compliant
by Patrick Appleyard - Sunday, 5 August 2012, 1:54 PM

I am in the early stages of using Totara and I now looking at how we can use competencies in the future.

I have tested assigning a Competency to a Position that is linked to a Course.  The Competent / Compliance then appears on a report. 

In addition to showing those that are Competent / Compliance, I want the report to show those that have the Competency assigned to their Position but have not completed the required course(s).  Thus providing a full list of those Competent / Compliant and those Not Competent / Not Compliant.

There is probably an easy way to achieve this, but I am missing something.  

Simon Coggins
Group Totara
Re: Competencies - Reporting on Not Competency / Compliant
by Simon Coggins - Sunday, 2 September 2012, 7:35 PM

Hi Patrick,

I'm afraid this isn't currently possible, although I agree it would be a very useful addition!

Probably the best way to implement this is as a new report source. I've created a feature request ticket to track it and hopefully we'll have some time to look at it soon.

Simon

Martinique Johns
Re: Competencies - Reporting on Not Competency / Compliant
by Martinique Johns - Tuesday, 11 September 2012, 10:07 AM

Is there any additional information on how to use Report Sources?  I understand they are located 'yousite/local/reportbuilder/rb_sources'.

Simon Coggins
Group Totara
Re: Competencies - Reporting on Not Competency / Compliant
by Simon Coggins - Tuesday, 11 September 2012, 6:03 PM

There's some developer documentation on how to write report sources here:

http://docs.moodle.org/dev/reportbuilder#Beginner_Topics

They are not 100% complete but they should give a developer enough information to write a basic report source.

Simon

Nikhil Bansal
Group Partners
Re: Competencies - Reporting on Not Competency / Compliant
by Nikhil Bansal - Sunday, 21 February 2021, 12:14 AM
Hi,


I have written a report with the source as per the documentation. 

However, on running it is not showing any data. Can you please suggest? 

Report is shown in v13 report builder competencies with columns & filters. I can also see the fields in the database. However, it is not showing any data even after running cron job multiple times.

Is there any additional step required or setting or can debug some other way?

Regards,

Nikhil

Craig Eves
Group Totara
Re: Competencies - Reporting on Not Competency / Compliant
by Craig Eves (Totara Support) - Sunday, 21 February 2021, 2:44 PM

Hi Nikhil

One thing to check is that the user running the report has permissions to view the data.

You can try and run the report with developer debugging on to check for any errors. 

If you copy the the report url and add the string &debug=1 at the end then the SQL will be produced and check if anything doesn't look right.

Regards


Nikhil Bansal
Group Partners
Re: Competencies - Reporting on Not Competency / Compliant
by Nikhil Bansal - Sunday, 21 February 2021, 3:42 PM

Hi Craig,

Thanks, after some debugging I had got it working last night.

Few questions: 
1. Can we do inner with the non-totara table within the rb_base_source report?
2. Also, can we do advanced inner join with group by within the rb_base_source report? 
I have taken the competency status report & sub-deployed on local & trying to just show the latest proficiency result (Not all).

I was trying developer guide - https://help.totaralearning.com/display/DEV/Advanced+Joins

I think it is not updated. Can you please suggest advanced joins documents with examples? I am trying to do below.

SELECT *
  FROM ( SELECT competency_id as competency_id_max,user_id as user_id_max
              , MAX(last_aggregated) AS max_last_aggregated
           FROM public.mdl_totara_competency_achievement
         GROUP BY competency_id,user_id ) AS m
INNER
  JOIN public.mdl_totara_competency_achievement AS t
    ON t.competency_id = m.competency_id_max
	 AND t.user_id = m.user_id_max
   AND t.last_aggregated = m.max_last_aggregated

Regards,

Nikhil

Craig Eves
Group Totara
Re: Competencies - Reporting on Not Competency / Compliant
by Craig Eves (Totara Support) - Monday, 22 February 2021, 5:30 PM

Hi Nikhil

The documentation hasn't been completed - unfortunately i don't have the SQL knowledge to answer this 

Does this query give the expected results when you run this in the database ?

regards



Nikhil Bansal
Group Partners
Re: Competencies - Reporting on Not Competency / Compliant
by Nikhil Bansal - Monday, 22 February 2021, 6:07 PM

Hi Craig,

Yes, the SQL give desired results in the SQL database. I have tested it and was trying to write the same way that is expected by Totara.

While asking question on Helpdesk I have been asked to refer developer guide or documentation. 

However, unfortunately the developer guide isn't complete.

So, will it be possible for your developer team to complete that page at least or they can try SQL query I have provided and give equivalent query as per Totara report builder requirements? 

Regards,

Nikhil

Craig Eves
Group Totara
Re: Competencies - Reporting on Not Competency / Compliant
by Craig Eves (Totara Support) - Monday, 22 February 2021, 6:47 PM

Hi Nikhil

The report documentation has been incomplete for a while now so is currently a low priority.

You have created a helpdesk ticket that we can provide advice on with your specific query. 

Regards


Nikhil Bansal
Group Partners
Re: Competencies - Reporting on Not Competency / Compliant
by Nikhil Bansal - Monday, 22 February 2021, 7:27 PM

Hi Craig,

Thanks for your reply here. I have updated the ticket with more details.

Regards,

Nikhil