0

I'm trying to create a user stats SSRS report which shows how many times a report has been accessed. I have the main table set up with report name and count of runs, but what I really want is to click on a the report name when rendered and to show the users who have accessed that report, in a different table on this same report. So that data will dynamically update and change when a different report name is clicked.

I have used a drillthrough from the main report to the ReportUsers stats as a separate report, and can create a link using parameters from the Main report with an Action of "Go to Report" but I didn't want to be taken away to a different report, I'd like everything in one place.

Is this even possible? If so please can someone advise where I'm going wrong?!

(You can see on the right I have an empty table which was an attempt at creating a subreport!)

Thanks for any direction

enter image description here

-------EDIT!--------

I have set the ReportName parameters like this, but haven't set anything under Available or Default Values. The report returns everything in the left hand table as expected.

enter image description here

I have set an action on the Report Name in the Tablix, so that when a report name is clicked, it will run itself, but on the right show the number of hits for that report, but I just can't seem to get this bit to work. It just returns the full list of reports on the left and an empty table on the right.

I know I'm missing something but I'm not sure what!

enter image description here enter image description here

Pinkybloo
  • 15
  • 7

1 Answers1

1

You can't do this exactly how you want to but you can simulate it to a degree..

I have done this in the past as follows..

Add a parameter to your report for the reportid you want to show details for in the same way you did for your subreport, make sure the default for this parameter is null or blank.

The report needs to obviously contain everything you want to show including the specific report details.

Set the report up so that is no reportid is passed in then it shows data as you report does now. If you have a subreport embedded to show the details, then you can hide this if the parameter is null.

When the user clicks the report, use "Go To report" but this time get the report to call itself, passing the reportid in. When the report shows, as the parameter is now not null, you can show the details section.

Any subsequent clicks on the report will then just keep calling the same report over and over with the new parameter value.

So, it will reload the report each time, rather than being fully dynamic but other than the page refreshing , it will be close to what you want.

Hope that made sense..

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks for you advice. I still can't get this to work how you mention. I have it set up with a NULL ReportName parameter but I can't get the Action, Go To Report, to populate the parameter with the clicked report name. So it's still not bringing anything back. I'll update my question with more screen shots. – Pinkybloo May 11 '20 at 14:56
  • Looks like you are setting the subreports ReportName parameter (amongst others) to the report parameter from the main report [@ReportName]. This will just pass the initial `NULL` to the subreport. You need to pass in the value form the dataset of the tablix that you click on. So if the main tablix uses a dataset called `dsMain` for example then the parameter value needs to be from there. Typically it will just show as `[ReportName]` ./ Click the fx button and just choose the field from the "Fields(dsMain)" Category (where `dsMain` is the name of the dataset. – Alan Schofield May 11 '20 at 15:30