Redmine

Hey RVIG, the notes for Redmine issues are stored in the 'journals' table rather than the 'note' table. You can retrieve the last note for each issue by filtering for entries where the 'journalized_type' column is 'Issue' and 'notes' column is not null, then ordering by 'created_on' in descending order.

To access the last note of Redmine issues for a Power BI report, query the public.journals table joined with public.issues using issue_id. Filter journals by journalized_type as 'Issue' and aggregate using MAX(created_on) and MAX(notes) to get the latest note per issue. Adjust the SQL query to match your database schema and test before using in Power BI.

r vig wrote:

Hello,
I am using Redmine in my company.
For project reason, I have to create a Power BI report with the data contained in Redmine
I succeed in connecting to redmine with BI thanks to the connector PostgreSQL database.
I have now access to the issue of my project thanks to the table public.issue.

I would like to have access to the last note of the issues but I can't find which table I have to use. For information, public.note is an empty table.

Can someone help me to find this table please ?

Thanks in advance

RVIG

Hello! It looks like you’re on the right track with connecting Power BI to Redmine. To access the last note of the issues, you should look into the ‘journals’ table. The notes for Redmine issues are stored there. You can retrieve the last note for each issue by filtering for entries where the ‘journalized_type’ column is ‘Issue’ and the ‘notes’ column is not null, then ordering by ‘created_on’ in descending order to get the most recent entry1.

Here’s a SQL query that might help you:

SELECT *
FROM journals
WHERE journalized_type = 'Issue'
AND notes IS NOT NULL
ORDER BY created_on DESC
LIMIT 1;

This query will give you the latest note for an issue. If you need the last note for each issue, you might need a more complex query that groups the results by issue ID. I hope this helps you with your Power BI report.

Hello,
Thanks a lot for your support. It helps me a lot.
Now, my BI is ready to be published with the last note available :)
Rvig