Need to pass userid parameter to report so that it only shows user's own data

[hondaman900]

I have this working rather well - thank you for the great package!

However, the db_connection is to the entire database without any filter for user, so a report is populated with all users' data, not just the authenticated user running the report. I need to filter results on user ID. I can't find any reference on how to pass this parameter to the report - can you help please?

[geekcom]

Hi @hondaman900, check this link:

https://community.jaspersoft.com/wiki/using-report-parameters-jaspersoft-studio

and this:

https://github.com/PHPJasper/examples/blob/master/src/Examples.php#L63

[hondaman900]

Thank you. I found the solution was two-part with most of the work on the Jaspersoft Studio end. Passing the parameter in my Laravel code was the easy part. Creating the report with a parameter filter was a challenge in that how to do that, if you're a new user, is not clear in the Jaspersoft Studio documentations, and a step-by-step example would have helped. But I got it working once I figured out how and where in Studio to place a SQL statement and then how to include my filter parameter (in my case ID of the authenticated user) in that statement.

So for the next person looking for this...

In Jaspersoft Studio, in the Outlinepanel, right-click the project name (top root element) and select Dataset and Query from the pop-up menu. This panel will allow you to assemble a SQL statement from the fields in your database. I used this SQL statement to extract one user's records.

I added a user_id parameter by right-clicking Parameters in the project outline, selecting Create Parameter and entering user_id (or whatever you want to name your parameter) as the name, and string as the class. You'll need to uncheck Is For Prompting if filling in the parameter in code (leave it checked for testing the parameter in Preview). I added the parameter to the SQL statement by double-clicking it in the parameterspanel/tab when building the SQL statement. That adds the parameter at the cursor. The resulting SQL statement does the trick:

select * from dstest.real_property_assets where dstest.real_property_assets.created_by_id = $P{user_id}

Once compiled and in my app's designated folder I could filter on the user ID by passing it to the $id variable as follows:

$options = [ 
    'format' => ['pdf'],
    'locale' => 'en',
    'params' => ['user_id' => $id],
    'db_connection' => [
        'driver' => 'mysql', //mysql, ....
        'username' => 'xxxxx',
        'password' => 'xxxxx',
        'host' => 'localhost',
        'database' => 'xxxxx',
        'port' => '3306'
    ] 

which provides the info for the optionsportion of the call to PHPjasper

$jasper = new PHPJasper;

$jasper->process(
    $input,
    $output,
    $options
)->execute();

The result is a PDF report of just my specified (in my case currently-logged-in) user's data.

Hope this helps someone else looking for this functionality.

[geekcom]

Great @hondaman900 , thanks