How to retreive the report list.

Sreejith_S · January 16, 2017, 1:06pm

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

karolina · January 16, 2017, 1:51pm

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

wonky · January 17, 2017, 10:56am

Karolina, Yes that should work perfectly. Can you please provide sample code, links, or more details?
Thanks!

karolina · January 17, 2017, 11:02am

I’ll prepare an example within the next few days

Karolina

Sreejith_S · January 17, 2017, 3:21pm

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

karolina · January 17, 2017, 5:33pm

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

Sreejith_S · January 18, 2017, 12:58pm

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

Sreejith_S · January 19, 2017, 12:54pm

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

karolina · January 21, 2017, 5:01pm

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

Sreejith_S · January 25, 2017, 11:04am

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

wonky · January 25, 2017, 3:41pm

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.

Sreejith_S · February 3, 2017, 4:26pm

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

karolina · February 3, 2017, 5:01pm

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

Sreejith_S · February 6, 2017, 9:02am

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

Sreejith_S · February 8, 2017, 11:01am

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

IF_Eduardo · February 8, 2017, 12:13pm

Hi Sreejith S,

could you please post the script report you are using which is throwing the error?

Thanks,
Eduardo

Sreejith_S · March 29, 2017, 3:39pm

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

IF_Eduardo · March 30, 2017, 6:55am

Hi Sreejith,

thanks for the script. Could you please also post the query of the report throwing the error ?

Cheers,
Eduardo

Sreejith_S · March 30, 2017, 10:31am

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 >= 100000

Regards,
Sreejith

IF_Eduardo · March 30, 2017, 11:38am

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:

You can address the table name directly:

select * from $!{TMP_TABLENAME}
WHERE $X{IN, USER_ID, userid}
and REPORT_ID >= 100000

The resulting query:

select * from rs_tmptbl_1_1_0
WHERE ...

Address the basic query of this tmp-table:

select * from ($!{query}) myTmpTable
WHERE $X{IN, USER_ID, userid}
and REPORT_ID >= 100000

The resulting query:

select * from (SELECT * FROM rs_tmptbl_1_1_0) myTmpTable 
WHERE ...

More information on 4.5.2: https://reportserver.net/en/guides/admin/chapters/Data-Sources/

Cheers,
Eduardo

How to retreive the report list.

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

Query could not be executed: Table ‘bitnami_reportserver.rs_tmptbl_1_1_0’ doesn’t exist To get help please contact an administrator.

Query could not be executed: Table ‘bitnami_reportserver.rs_tmptbl_1_1_0’ doesn’t exist
To get help please contact an administrator.