How to use Custom Report Inputs, Where to go from here?

0

Problem

When I create a custom input, I cannot figure out how to make it turn the results I want.

Simplified Senario

  1. Simple Report from a Simple adhoc view, from a simple domain, from a generic mysql data source
  2. Report Edit --> Controls & Resources --> Add Input Control
  3. Define an Input Control in the next step
  4. Define a Query in the next step
  5. Name the Query: 'Select_The_User_Id_Query'
  6. Link a Data Source to the Query: 'selected the mysql data source'

Done!

Now lets look at how the report looks like

The input looks good. 

Question:

How do I make the custom report filter results to only display user_id = 44 ?

Thanks in Advance

 

EDIT: Progress Made

Creating a custom report query was right in front of my face

SELECT * FROM user_sites WHERE user_id = $P{Select_The_User_Id}
  1. Report --> Edit --> Query
  2. "Click here to create a new query"
  3. Name the Query: 'Whatever_query"
  4. Link to data source: " select my database"
  5. Definee the query
  6. .

This is what I don't understand. The second cascading input I created has a query like "select * from users where id = $P{Select_The_User_Id}", which works. But the report "Query" claims that the parameter doesn't exisit. Why is that?

andey.wei's picture
Joined: Feb 19 2014 - 1:45pm
Last seen: 5 years 1 month ago

The input parameter need to pass to main report and the main report query browser should the query should filter based on  $P(UserId) then you will get seleted userid.

rsuddhala - 5 years 9 months ago

Thank you, I never actually realized I could create a query from the Edit --> Reports, because it was greyscaled. I guess that was me just being dumb. However I'm still having an issue with the query, when I create the query, I get this error: ".... JRRuntimeException: Parameter "Select_The_User_Id" does not exist.". Isn't that what i called the input, in the above example?

andey.wei - 5 years 9 months ago

2 Answers:

1

OK. I did a quick POC and it work as expected.

This is what I did:

- Created an adhoc view and report based on the Simple Domain from JasperServer Samples
- The Adhoc View/report is a simple Table report showing the accounts. I added a filter in the view for the Account State
- As expected the parameter name that was created is "billing_address_state_1". At this point if you execute the report it will render the defailt single select query IC that AdHoc generates.
- I created a new IC in the repository as a List Of Values, I added just 3 of the states to the LoV input control (CA, WA, and DF) with their respective lables. The ID of this input control was of course "billing_address_state_1" that way it matches the parameter the report is expecting.  I created this the same way you created the Single Select query IC in your sample, but used a LoV so I can see the difference :)
- I linked the new IC to my existing report, following the same steps you made
- Now if you execute the report the standard IC is replaced with my new IC and it works as expected !!

Attached is a repository export of my sample. Login as Superuser go to Manage Server -> Import select the .zip file attached and import it into your server. I created all in /Public/Test; you will need to have the JasperperServer Provided samples installed since my report uses the simple domain that comes with those.

Cheers!

 

Attachments: 
AttachmentSize
Package icon adhoc-custom-ic.zip17.42 KB
marianol's picture
4838
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 2 months ago

WOW thank you Marianol for your help. I was able to delete your custom input, and then recreate it. But, I'm missing one last key. Where do I figure out the filter ID. Where do I figure out that the filter id was "billing_address_state_1". When I look at the schema of the data set you provided, it was just "billing_address_state". Can I assume that "_1" will be appended to every filter created from the adhoc view?

andey.wei - 5 years 9 months ago

You can assume _1 for the fist time you use it if you use the value twice, the second one will be _2.
But there is an easier way to know. Just use the RESTv2 Input control service.

Once logged into Japserserver go to
http://localhost:8080/jasperserver-pro/rest_v2/reports/[repo path to your report]/inputControls
,in the sample report I sent it would be http://localhost:8080/jasperserver-pro/rest_v2/reports/public/Test/Custo...
That will give you an XML file with all the input controls of a specific report. in the <id> tag you will find the ID of each of the created input controls.

See the webservices guide for more info.

Remember to mark the question as answered (checkmark next to the answer) so if somebody else is looking for this info is easier to find :)

marianol - 5 years 9 months ago
0

Andrey,

If I undertood correctly, your initial report is an AdHoc report. In that case there is no need to create an input control the way you did it. Just open the AdHoc view that you used, and drag "user_id" to the filter column, or right click on "user_id" in the fields display and select "Create a Filter" from the contex menu.

Then when you save that Adhoc view and create a report, JRS will automatically create an input control for your report.

Best,

marianol

marianol's picture
4838
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 2 months ago

i know jaspersoft creates basic filters like user_id automatically. However, that wasn't why I was asking the question. I was just trying to provide a simple example on how I would proceed when using a custom input. What I'm actually trying to do involves a couple cascading inputs, and a dataset with many joins.

andey.wei - 5 years 9 months ago

OK.. Now I get it.

I have not tried this but in theory this is what you will need.
1) the Adhoc view must to have filters created for the parameters you want to pass.
2) check the IC ID's of the Adhoc View so you can match them form your Custom Input Controls. In Adhoc this will be the ID of the field you have picked form the domain plue and underscore "_" and an id number starting with 1. SO if your fields ID is user_id the parameter name for the filter you create will be user_id_1. You can see this using the REST V2 API Input control service i.e. http://localhost:8080/jasperserver-pro/rest_v2/reports/[My_Report_reposi...
3) create your input controls using that ID in your generated report.

marianol - 5 years 9 months ago
Feedback
randomness