Recently used reports

ReportServer (EN)
1

Hi,

Is there a way to make a list of links for recently used reports for a particular user? I would gladly add it to my dashbord, next to the bookmarks.

Cheers,

Karolina

2

Hi Karolina,

have a look at ReportServer’s audit log: i.e., create a dynamic list on the ReportServer database with

 SELECT E.*, P.KEY_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID

When running this report you can filter on attribute “ACTION” (look for REPORT_EXECUTION) and a second filter on “KEY_FIELD” (look for report_id). This should then give you a list of report execution events with the user id (field user_id) and the report id (field value). If you want it for a particular user, then simply filter on user_id.

Hope this helps.

Cheers
Arno

3

Hi Arno,

Thanks, it helped a bit to create a dynamic list with recently used reports.

What I actually wanted was a list of bookmarks pointing to these reports. After clicking the “bookmark” I wanted to be redirected to the report’s parameter view.

I tried to create a list as a HTML dadgets, but without success. What works in my IDE, does not want to work in the dadget.

At the end I’ve put a list with hard coded links to reports - only to discover that the linked report gets executed inside the dadget - so I give up at this point :-/

Cheers,

Karolina

4

Hi Karolina,

try the following link

ReportServer.html#reportexec/id:123

Best

Arno

5

OK - got it - thanks!.

For anyone interested, this is the solution for listing 10 recently used reports by a current user:

  1. Create a dynamic list with the ReportServer database & the following query:
SELECT MAX(E.DATE_FIELD) AS MAX_DATE, P.VALUE, R.NAME_FIELD
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P,
RS_REPORT R
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID
AND
P.VALUE IS NOT NULL
AND
P.VALUE = CAST(R.ID AS VARCHAR)
AND
E.ACTION = 'REPORT_EXECUTION'
AND
E.USER_ID = ${_RS_USER.getId()}
AND
P.KEY_FIELD = 'report_id'
GROUP BY P.VALUE, R.NAME_FIELD
ORDER BY MAX_DATE DESC
LIMIT 10

  1. Create a variant of the report with all the columns selected. The variant id will be needed later.

  2. Create a static HTML dadget with the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

<head>
    <title>Report List</title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8"/>
    <script type="text/javascript" src="http://code.jquery.com/jquery-2.2.0.min.js"></script>
    <script type="text/javascript">

        $(document).ready(function () {
            
            $.getJSON("http://YOUR_IP:PORT/REPORTSERVER_BASE_FOLDER/reportserver/reportexport?id=VARIANT_ID&format=json",

                    function (data) {
                        var listItems = "";
                        
                        $(data).each(function (key, val) {
                            var link = "<a href='#' class='reportLink'" + "id=" + val.value + ">" + val.name_field + "</a>";
                            listItems = listItems + "<li>" + link + "</li>"
                        });

                        $("#reportList").html(listItems);
                    });
            
            $("ul").on("click", ".reportLink", function () {
                var reportExec = "http://YOUR_IP:PORT/REPORTSERVER_BASE_FOLDER/ReportServer.html#reportexec/id:";
                var reportId = $(this).attr("id");
                top.window.location = reportExec + reportId;
            });
        });
    </script>
</head>

<body>

<ul id="reportList">
</ul>

</body>
</html>

If all goes well, the clicked report will be displayed in the browser.

Cheers,
karolina

6

Hi Karolina,
I would like to follow your suggestion, but I am new to dynamic list..
Could you help me to make all you did?
I actually made a dynamic list and typing as datasource : reportserver schema then I copied your query into the query panel.
Now, I wonder, where do I add these parameters? Into the query panel/add parameter or into the parameter tab?
Suppose I go on then how do I make the dadget ?
Thanks

You talk about the variant…what is that?
Sorry for these question, but I never used this kind of objects.

7

Hi,

A dynamic list variant is a dynamic list report saved with its configuration.
There’s a section about it in the User guide with all the features described.

For this particular report:

You don’t need to add any parameters, as everything is set up in the query.
The further steps:

DYNAMIC LIST

  1. If you have the dynamic list report added, then open it. Open the “Configure list” panel.
  2. Next you need to add columns by clicking “select columns” button - select all columns. You may click the “preview” and see if everything is OK (you should see a list of your 10 recently used reports).
  3. Then you need to save the configured dynamic list as a variant. This is done by clicking the “save” button in the right upper corner of the configuration panel.
  4. When you do it, you already have the variant. Its ID is in brackets next to its name, in the left upper corner. The ID will be needed in the next steps.

DASHBOARD

  1. Open the dashboard module.
  2. If you don’t have any dashboard, add it by choosing “Add dashboard” from “Tools” menu in the right upper corner.
  3. Then add a dadget - static HTML dadget.
  4. At first the dadget will be empty - it is OK. In the right upper corner of the dadget window you have 3 icons - click the on in the middle.
  5. You should see a pop-up window with a place for HTML code. Copy the HTML code from above and paste it inside the window.
  6. Then you need to replace (only what is in capital letters) in:
"http://YOUR_IP:PORT/REPORTSERVER_BASE_FOLDER/reportserver/reportexport?id=VARIANT_ID&format=json"

and

"http://YOUR_IP:PORT/REPORTSERVER_BASE_FOLDER/ReportServer.html#reportexec/id:";

with your IP, port, ReportServer base folder and the variant’s ID. Save (or submit) it.
7. Then you should see the list of links. It may be necessary to refresh the data by clicking the refresh icon.
8. After clicking a link you will be redirected to the chosen report.

Hope it helps.
Karolina

8

First of all, many thanks for your quick reply…
I am following your steps, but it seems to be an error on the query…After I pasted the sql code and go to “configure list” panel and click on “select columns” I got the following oracle error code : ora-00907

http://i64.tinypic.com/m7xglv.jpg

I tried myself to fix it ,but without success.

9

Hi,

Following the link here

this might work in Oracle:

SELECT * FROM (
SELECT MAX(E.DATE_FIELD) AS MAX_DATE, P.VALUE, R.NAME_FIELD
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P,
RS_REPORT R
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID
AND
P.VALUE IS NOT NULL
AND
P.VALUE = CAST(R.ID AS VARCHAR)
AND
E.ACTION = 'REPORT_EXECUTION'
AND
E.USER_ID = ${_RS_USER.getId()}
AND
P.KEY_FIELD = 'report_id'
GROUP BY P.VALUE, R.NAME_FIELD
ORDER BY MAX_DATE DESC)
WHERE ROWNUM <= 10

However, I can’t test it myself and have little experience with Oracle, so I can’t help you more, if at all.

Cheers,
Karolina

10

Hi karolina,
Thank you for this nice idea. I have done all the steps you provided, the query is working well in the report, but I can’t see the list in my dadget !? could you please check my code HTML below?

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

<head>
      <title>Report List</title> 
<meta http-equiv="content-type" content="text/html;charset=utf-8"/>
    <script type="text/javascript" src="http://code.jquery.com/jquery-2.2.0.min.js"></script>
    <script type="text/javascript">

        $(document).ready(function () {
            
            $.getJSON("http://report-server.dbmreflex.com/reportserver/reportexport?id=1119763&format=json",

                    function (data) {
                        var listItems = "";
                        
                        $(data).each(function (key, val) {
                            var link = "<a href='#' class='reportLink'" + "id=" + val.value + ">" + val.name_field + "</a>";
                            listItems = listItems + "<li>" + link + "</li>"
                        });

                        $("#reportList").html(listItems);
                    });
            
            $("ul").on("click", ".reportLink", function () {
                var reportExec = "http://report-server.dbmreflex.com/reportserver/ReportServer.html#reportexec/id:";
                var reportId = $(this).attr("id");
                top.window.location = reportExec + reportId;
            });
        });
    </script>
</head>

<body>
<ul id="reportList">
</ul>

</body>
</html>
11

Hi,

What is the name of the folder where you put your ReportServer app in Tomcat’s webapps folder?

If it is “reportserver”, then this line should look like this:

$.getJSON("http://report-server.dbmreflex.com/reportserver/reportserver/reportexport?id=1119763&format=json"

Hope it helps,
Karolina

12

Hi Karolina,

Yes and it works now many thanks.
I have another question please if you can help? I have two data sources in my RS (relational databases)
I want to create a report using both datasources.I read hear that is possible using a script. Have you done any example before as I really need to join two tables from tow DB.

Regards.
Hamza