You are not logged in.
Pages: 1
Hi,
Is it possible to retrieve a list of reports which a user has access on it from the RS application to our project.
We are trying to show a list of reports available to a user in our project , so that when he get in to our project he must be able to see the list of reports which he has access to the same.
Regards,
Sreejith
Offline
I use a script datasource + Dynamic List for a report that shows datasources a given user has access to, so I guess it should be possible to create a similar report showing reports a given user has access to.
How your application will access this list of reports? Would JSON retrieved via url work in your case?
Karolina
Offline
Karolina, Yes that should work perfectly. Can you please provide sample code, links, or more details?
Thanks!
Offline
I'll prepare an example within the next few days
Karolina
Offline
Hi Karolina ,
Thanks for the reply,
Using dynamic list we are able to retrieve all reportlists as json by url.
Now, we want to retrieve those lists specific to a user.
Is there way to display all tables and data of internal database.
Regards,
Sreejith
Offline
Hi Sreejith,
Do you have a user id as one of your dynamic list columns?
If so, you could probably use filters in url, as described here
Anyway, it would be helpful if you posted your DL query and URL.
Karolina
P.S. For displaying all tables (there are approx. 380 tables in the RS database) you may use any database management software or any IDE with good database suport
Offline
Hi Karolina ,
Thanks for the reply
We have installed reportserver with the help of bitnami , by the connection url we come to know that (jdbc:mysql://127.0.0.1:3307/bitnami_reportserver ) ,the database is bitnami_reportserver , but we are not able to list any such database in our mysql server.
Regards,
Sreejith
Offline
Hi Karolina ,
We were able to get into the internal database , we are trying to get a relationship between rs_user and rs_report , by which we could get the list of report a user has access to.
We are not getting such tables in which we could relate rs_users and rs_report.
Could you please help us to sort this out.
Regards,
Sreejith
Offline
Hi,
Here is the script for script datasource with reports that users have access to.
You may then create a dynamic list based on this datasource and:
- either add a parameter (userId) and use it in the report query
or
- return all reports and users and then use filter in the url.
import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasource;
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableRow
import net.datenwerke.rs.base.service.reportengines.table.output.object.TableDefinition
import net.datenwerke.security.service.security.SecurityService;
import net.datenwerke.security.service.security.SecurityServiceSecuree;
import net.datenwerke.security.service.security.rights.Execute;
import net.datenwerke.security.service.security.rights.Read;
import net.datenwerke.security.service.security.rights.Right;
import net.datenwerke.security.service.usermanager.entities.User;
import net.datenwerke.rs.core.service.reportmanager.entities.reports.Report;
/**
* ScriptDatasource that returns all the reports users have access to
*/
/* get services */
SecurityService securityService = GLOBALS.getRsService(SecurityService.class);
/* prepare datasource definition: column names and data types */
TableDefinition tableDefinition = new TableDefinition(
['USER_ID', 'USER_NAME', 'REPORT_ID', 'REPORT_NAME', 'REPORT_DESC'],
[Long.class, String.class, Long.class, String.class, String.class]
);
def result = new RSTableModel(tableDefinition);
/* load all report entities and add them to the result object */
/* get users */
GLOBALS.getEntitiesByType(User.class).each{
User user = it
/* get user data */
def userId = user.getId()
def userName = user.getName()
/* get all reports */
GLOBALS.getEntitiesByType(Report.class).each{
Report report = it
/* report data: id, name, description */
def reportId = report.getId();
def reportName = report.getName();
def desc = report.getDescription();
/* check read and execute rights to reports */
def hasExecuteAccess = securityService.checkRights(user, report, SecurityServiceSecuree.class, Execute.class);
def hasReadAccess = securityService.checkRights(user, report, SecurityServiceSecuree.class, Read.class);
/* result row in the script datasource */
def resultLine = [userId, userName, reportId, reportName, desc];
/* adding only the reports users have rights to see and to execute */
if(hasExecuteAccess && hasReadAccess){
result.addDataRow(new RSTableRow(tableDefinition, resultLine.toArray()));
}
}
}
return result;If you have a lot of users and reports, consider creating additional table in a database and periodically load users and reports to the table instead of using the script datasource.
Karolina
Offline
Hi Karolina,
Thanks , we are able to retrieve the list of all reports and users.
We are trying to add filter for a user_id specific result using "fi_i" in the url , its not taking any effect.
Could you please help us to sort this.
Regards,
Sreejith
Last edited by Sreejith S (2017-01-25 11:06:01)
Offline
We also want to retrieve the report list with Teamspace names, so that we can categorize the list (actually, some type of category field would be best) -
This query does what we are looking to do:
SELECT rs_user.username, rs_teamspace_member.*, rs_teamspace_2_member.teamspace_id, rs_teamspace.NAME_FIELD, rs_report.NAME_FIELD, rs_report.description, rs_report.uuid
FROM ((((rs_user INNER JOIN rs_teamspace_member ON rs_user.id = rs_teamspace_member.user_id) INNER JOIN rs_teamspace_2_member ON rs_teamspace_member.ENTITY_ID = rs_teamspace_2_member.members_id) INNER JOIN rs_teamspace ON rs_teamspace_2_member.teamspace_id = rs_teamspace.ENTITY_ID) INNER JOIN rs_report_mngr_node ON rs_user.id = rs_report_mngr_node.owner_id) INNER JOIN rs_report ON rs_report_mngr_node.ENTITY_ID = rs_report.id
WHERE (((rs_user.username)="YOURUSERNAME"));
I'd appreciate any thoughts on how to construct a URL to bring back the teamspace and/or report category information.
Offline
Hi Karolina ,
We were able to execute reports using the script datasource , while running the report each time a new temporary table has been getting created for example "rs_tmptbl_1_1_0" , we are executing the reports using this table. Since they are temporary ,and gets deleted after certain period we are getting errors while executing the reports.
Could you please help us to sort this issue.
Regards,
Sreejith
Offline
Hi,
This is a known issue (I encounter it from time to time as well).
Could you post some more information? i.e.
- the datasource cache settings (-1, 0 or another number)
- the error message (do you have a message that a table rstmptbl_N_N_N does not exist or that it already exists?)
- if you get the message that the table does not exist, could you check in the internal database if it exists or not?
- some more information (i.e. if you execute the report once again, do you still have the error, or not?)
This may be helpful in solving the issue.
Karolina
Offline
Hi Karolina ,
#The datasource cache is set to -1
#Currently the table is present there with name "rs_tmptbl_1_1_0"
After a certain period of time unfortunately the tables are getting deleted from the internal database
Also I am able to execute the same report via RS Application , but not via url ,Its just showing the following error:-
----------------------------------------
The report could not be executed: net.datenwerke.security.service.security.exceptions.ViolatedSecurityException
----------------------------------------
This error is showing even though the user has whole access.
Could you please help us
Regards,
Sreejith
Last edited by Sreejith S (2017-02-08 10:58:09)
Offline
Hi Karolina ,
The table "tmptbl_1_1_0" got deleted now from the internal database of RS.
Its showing the following error now:-
-----------------
Query could not be executed: Table 'bitnami_reportserver.rs_tmptbl_1_1_0' doesn't exist
To get help please contact an administrator.
-----------------
Please help us to sort this out.
Regards,
Sreejith
Offline
Hi Sreejith S,
could you please post the script report you are using which is throwing the error?
Thanks,
Eduardo
Offline
Hi Eduardo,
Following is the script which is giving the error
import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasource;
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableRow
import net.datenwerke.rs.base.service.reportengines.table.output.object.TableDefinition
import net.datenwerke.security.service.security.SecurityService;
import net.datenwerke.security.service.security.SecurityServiceSecuree;
import net.datenwerke.security.service.security.rights.Execute;
import net.datenwerke.security.service.security.rights.Read;
import net.datenwerke.security.service.security.rights.Right;
import net.datenwerke.security.service.usermanager.entities.User;
import net.datenwerke.rs.core.service.reportmanager.entities.reports.Report;
/**
* ScriptDatasource that returns all the reports users have access to
*/
/* get services */
SecurityService securityService = GLOBALS.getRsService(SecurityService.class);
/* prepare datasource definition: column names and data types */
TableDefinition tableDefinition = new TableDefinition(
['USER_ID', 'USER_NAME', 'REPORT_ID', 'REPORT_NAME', 'REPORT_DESC'],
[Long.class, String.class, Long.class, String.class, String.class]
);
def result = new RSTableModel(tableDefinition);
/* load all report entities and add them to the result object */
/* get users */
GLOBALS.getEntitiesByType(User.class).each{
User user = it
/* get user data */
def userId = user.getId()
def userName = user.getName()
/* get all reports */
GLOBALS.getEntitiesByType(Report.class).each{
Report report = it
/* report data: id, name, description */
def reportId = report.getId();
def reportName = report.getName();
def desc = report.getDescription();
/* check read and execute rights to reports */
def hasExecuteAccess = securityService.checkRights(user, report, SecurityServiceSecuree.class, Execute.class);
def hasReadAccess = securityService.checkRights(user, report, SecurityServiceSecuree.class, Read.class);
/* result row in the script datasource */
def resultLine = [userId, userName, reportId, reportName, desc];
/* adding only the reports users have rights to see and to execute */
if(hasExecuteAccess && hasReadAccess){
result.addDataRow(new RSTableRow(tableDefinition, resultLine.toArray()));
}
}
}
return result;Regards,
Sreejith
Offline
Hi Sreejith,
thanks for the script. Could you please also post the query of the report throwing the error ?
Cheers,
Eduardo
Offline
Hi Eduardo,
This were the query used to get the data, but the database doesn't hold any such table "rs_tmptbl_1_1_0" actually
select * from rs_tmptbl_1_1_0
WHERE $X{IN, USER_ID, userid}
and REPORT_ID >= 100000Regards,
Sreejith
Offline
Hi Sreejith,
tmp tables will be deleted after a while (if their cache setting != -1), so you should not execute a query addressing the tmp table name.
There are two possibilities for this:
1. You can address the table name directly:
select * from $!{TMP_TABLENAME}
WHERE $X{IN, USER_ID, userid}
and REPORT_ID >= 100000The resulting query:
select * from rs_tmptbl_1_1_0
WHERE ...2. Address the basic query of this tmp-table:
select * from ($!{query}) myTmpTable
WHERE $X{IN, USER_ID, userid}
and REPORT_ID >= 100000The resulting query:
select * from (SELECT * FROM rs_tmptbl_1_1_0) myTmpTable
WHERE ...More information on 4.5.2: https://reportserver.net/en/guides/admi … a-Sources/
Cheers,
Eduardo
Offline
Pages: 1