#1 2022-06-30 09:38:36

abraimo
Member
Registered: 2021-12-21

List of All Reports Query

Greetings,

Can you please help with a way to get the list of all reports or variants, which users have access to it and on which teamspaces they are, from the internal datasource?

I tried making a query in order to achieve that, but I cannot find the relationship between teamspace and reports anywhere on the internal database.

Can you please help?

Thanks in advance.

Offline

#2 2022-06-30 09:42:34

eduardo
Administrator
Registered: 2016-11-01
Website

Re: List of All Reports Query

Hi abraimo,

you can use the following script datasource examples for this:
https://github.com/infofabrik/reportser … ace.groovy
https://github.com/infofabrik/reportser … ace.groovy

Regards,
Eduardo

Offline

#3 2022-06-30 09:47:50

abraimo
Member
Registered: 2021-12-21

Re: List of All Reports Query

Hi Eduardo,

Thanks for the quick feedback.

Correct me if I am wrong, but the script data source is not available on the community version, is that correct?
If that is the case, is there other way around that?

Regards,

Ayrton

Offline

#4 2022-06-30 09:53:47

eduardo
Administrator
Registered: 2016-11-01
Website

Re: List of All Reports Query

Hi Ayrton,

that is correct, scripting is available in the EE version.
You can try to create a query for that. For the relationships between objects you can take a look at the source code: https://github.com/infofabrik/reportserver

That would be much more difficult, though. Also, the internal tables/relationships change between ReportServer versions.

You can download a trial version of EE from here:
https://reportserver.net/en/download/

Here you see the comparison and pricing (scroll down):
https://reportserver.net/en/pricing/

Regards,
Eduardo

Offline

#5 2022-06-30 10:29:06

abraimo
Member
Registered: 2021-12-21

Re: List of All Reports Query

Thanks Eduardo.

I created a query on SQLSERVER for that. However it works well only if all the reports are on the root folder of the teamspaces. It will need changes if that is not the case.

"
SELECT rs_user.username,rs_user.firstname,rs_user.lastname, rs_teamspace_member.*,

rs_teamspace.NAME_FIELD,rs_report.id, rs_report.NAME_FIELD, rs_report.description, rs_report.uuid

FROM rs_user

inner join [ReportServer].[dbo].[RS_USERMANAGER_NODE]
  on rs_user.id=[RS_USERMANAGER_NODE].ENTITY_ID

    inner join [ReportServer].[dbo].RS_GROUP_2_OU
   on [RS_USERMANAGER_NODE].parent_id=RS_GROUP_2_OU.ous_id

  inner join [ReportServer].[dbo].[RS_GROUP]
  on RS_GROUP_2_OU.group_id=[RS_GROUP].id


left JOIN rs_teamspace_member
ON [RS_GROUP].id = rs_teamspace_member.folk_id


left JOIN rs_teamspace
ON rs_teamspace_member.team_space_id = rs_teamspace.ENTITY_ID

left join [ReportServer].[dbo].[RS_TS_DISK_ROOT]
on rs_teamspace.ENTITY_ID=[RS_TS_DISK_ROOT].team_space_id

left join [ReportServer].[dbo].[RS_TS_DISK_NODE]
on [RS_TS_DISK_ROOT].id=[RS_TS_DISK_NODE].parent_id

left join [ReportServer].[dbo].[RS_TS_DISK_REPORT_REFERENCE]
on [RS_TS_DISK_NODE].ENTITY_ID=[RS_TS_DISK_REPORT_REFERENCE].id


left JOIN rs_report
ON [RS_TS_DISK_REPORT_REFERENCE].report_id = rs_report.id

"

Regards,

Ayrton

Offline

#6 2022-06-30 10:32:26

eduardo
Administrator
Registered: 2016-11-01
Website

Re: List of All Reports Query

Hi Ayrton,

For the relationships between objects (this is what you are missing I think) you can inspect the source code of the corresponding entities: https://github.com/infofabrik/reportserver

More info:
The javadocs can be found here: https://reportserver.net/api/current/javadoc/index.html
List of entities: https://reportserver.net/api/current/entities.html
List of hooks: https://reportserver.net/api/current/hooks.html
List of services: https://reportserver.net/api/current/services.html

Regards,
Eduardo

Offline

#7 2022-07-07 10:44:27

Johann
Member
Registered: 2022-05-30

Re: List of All Reports Query

Hi Eduardo,

I am using the Enterprise version and created the script, script data source, and a dynamic list report. This worked for the reportsInTeamspace, but when I tried the usersInTeamspace script report, I ran into the following error: https://ibb.co/51PbP6F

Is there an issue with the script I am using?

Regards,
Johann

Offline

#8 2022-07-18 14:01:53

eduardo
Administrator
Registered: 2016-11-01
Website

Re: List of All Reports Query

Hi Johann,

you have groups in your teamspaces, that's why the script was not working, as it was only listing users.
You can use version 1.0.1 of the script for listing group information as well.

Regards,
Eduardo

Offline

#9 2022-07-20 10:45:02

Johann
Member
Registered: 2022-05-30

Re: List of All Reports Query

Hi Eduardo,

Thank you for the update!

Regards,
Johann

Offline

Board footer

Powered by FluxBB