Best practice forum (Archived)

This forum discussion has been removed

The content of this forum post has been removed and can no longer be accessed.

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

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply

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

Permalink | Show parent | Reply