dogfuel Posted January 12, 2008 Share Posted January 12, 2008 I need to restrict users to a specfic organization_id (an integer key). I do not mind joining from the fact table to a row level security table that associates user ids with an organization_id - is it simple to pickup up the JS user id as a parameter? Thanks! Link to comment Share on other sites More sharing options...
dogfuel Posted January 20, 2008 Author Share Posted January 20, 2008 I'll rephrase ....Is it possible to use the logged in JasperServer userid as a parameter in the report query (to constrain results to a subset of rows based on this id)? Thanks! Link to comment Share on other sites More sharing options...
lucianc Posted January 21, 2008 Share Posted January 21, 2008 Yes. Code:<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/><parameter name="LoggedInUsername" isForPrompting="false"> <defaultValueExpression>$P{LoggedInUser}.getUsername()</defaultValueExpression></parameter><queryString>SELECT .. WHERE .. $P{LoggedInUsername} ..</queryString> HTH,Lucian Link to comment Share on other sites More sharing options...
dogfuel Posted February 13, 2008 Author Share Posted February 13, 2008 Is there a way I can call the LoggedInUser.getuserID()or .getUserEMAIL()?I would like to retrieve the user keys and/or user emails. Thanks. Link to comment Share on other sites More sharing options...
swood Posted February 14, 2008 Share Posted February 14, 2008 The User interface looks like: Code:public interface User extends AttributedObject { public String getUsername(); public String getFullName(); public String getEmailAddress(); public boolean isExternallyDefined(); public boolean isEnabled(); public Set getRoles();} ShermanJasperSoftPost edited by: swood, at: 2008/02/14 07:25 Link to comment Share on other sites More sharing options...
dogfuel Posted February 16, 2008 Author Share Posted February 16, 2008 perfect - thank you Link to comment Share on other sites More sharing options...
jeffv Posted April 28, 2008 Share Posted April 28, 2008 I would like to use all/part of a special role as a parameter in a report. I would like to read through the Set of roles for a user, identify the 'special' role based on its format or a character and then return a string/integer to a parameter. Like getUsername. What would be best; extend/modify the User Interface to include a function to do this, use a scriptlet, or create a stand alone java class? Extending or modifying the User interface is not ideal because I don't want to fix it each time I upgrade. My goal is to reuse this parameter across many reports as a data source filter. Link to comment Share on other sites More sharing options...
jeffv Posted April 29, 2008 Share Posted April 29, 2008 I have a solution that works, but is not ideal because it requires maintaining a separate user name table in Postgres. I will take suggestions for improvement. I created a 'client user' table in Postgres that contains the user name as it appears in Jasper, the client key and boolean for active users. This table is added to all 'client' reports with a where clause to filter 'user_name' = $P{LoggedInUsername}. I would much rather parse out the client key from a user role. I tried using an input control, but it wouldn't work due to a SQL syntax error at "= $". Probably didn't have a value for the parameter at that time. Just guess. Link to comment Share on other sites More sharing options...
mmflynn Posted April 29, 2008 Share Posted April 29, 2008 I haven't quite followed everything you want to do, but I may be able to help with your current problem. Can you please post the definition (or a screen shot) for the parameter, the SQL for your query (the where clause should suffice), and a bit more info on the error? Re: the error, did you run the report from iReport or JasperServer? Mary Link to comment Share on other sites More sharing options...
jeffv Posted April 30, 2008 Share Posted April 30, 2008 I want to reuse reports across many different client users. Each client user must only see their subset of data so the reports need to be filtered to their key. My plan is to assign a key specific role to client users. The text for these roles would contain the clients unique key (6 digit interger). All client specific reports would be created with this filter; many users, one report, specific client data. Here are the details of the input control:* Single Select Query* Mandatory = yes* Query = Code:SELECT * FROM tbl_jasper_users WHERE user_name = $P{LoggedInUsername}* Parameter works; it currently displays in a text field in the report Errors being returned:com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : qrySelectKey net.sf.jasperreports.engine.JRException: Error executing SQL statement for : qrySelectKey org.postgresql.util.PSQLException: ERROR: syntax error at or near "= $"[/ol] Full error is attached. Post edited by: jeffv, at: 2008/04/29 18:55 [file name=inputcontrolError.txt size=30341]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/inputcontrolError.txt[/file]Post edited by: jeffv, at: 2008/04/29 18:58 Link to comment Share on other sites More sharing options...
swood Posted April 30, 2008 Share Posted April 30, 2008 What Mary was asking was: Did you declare the LoggedInUser parameter in the JRXML? Like: Code:<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/> It has to be called "LoggedInUser". To get the user name as a parameter, create another parameter: Code:[code]<parameter name="LoggedInUsername" class="java.lang.String"/> <defaultValueExpression ><![CDATA[$P{LoggedInUser}].getUsername()]></defaultValueExpression> Then you can use $P(LoggedInUsername} in your query. ShermanJasperSoftPost edited by: swood, at: 2008/04/29 19:42 Link to comment Share on other sites More sharing options...
jeffv Posted April 30, 2008 Share Posted April 30, 2008 Yes I did. I have used $P{LoggedInUsername} in a text field in the same report to make sure I am receiving the user name. It works fine. I now want to use that parameter in the WHERE clause of an input control that will filter the list of keys available for that user. JRXML file contains:Code:<parameter name="LoggedInUser" isForPrompting="true" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/> <parameter name="LoggedInUsername" isForPrompting="false" class="java.lang.String"> <defaultValueExpression ><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression> </parameter> I am connecting to a Postgres database.Post edited by: jeffv, at: 2008/04/29 20:02 Link to comment Share on other sites More sharing options...
swood Posted April 30, 2008 Share Posted April 30, 2008 Ahh.. I see the final SQL is wrong. Try: Code:...WHERE user_name = '$P{LoggedInUsername}' ShermanJasperSoft Link to comment Share on other sites More sharing options...
swood Posted May 1, 2008 Share Posted May 1, 2008 Teodor pointed out to me that you don't need the single quotes in the query. And I did not read all your details properly, so I gave the wrong advice - sorry! Looking at it again, you cannot filter the query input control in the way you are wanting. Could you log an enhancement? ShermanJasperSoft Link to comment Share on other sites More sharing options...
jeffv Posted May 1, 2008 Share Posted May 1, 2008 Thanks, at least I'll quit trying to go that route. How can I extract the string from the collection/set of Roles return by the LoggedInUser? Where do I log an enhancement request? Link to comment Share on other sites More sharing options...
zhu001 Posted July 2, 2008 Share Posted July 2, 2008 Hi, Does anyone know if it is possible to reference custom attributes from ldap entry through the LoggedInUsername in a sql? If not, could it be done any other way?I'm trying to reference attributes like Region, Location, Timezone any custom attribute....Something like select * from table where region = '$P{LoggedInUsername}.region' Thanks Post edited by: zhu001, at: 2008/07/01 18:37Post edited by: zhu001, at: 2008/07/01 18:38 Link to comment Share on other sites More sharing options...
shivaligandhi Posted April 27, 2010 Share Posted April 27, 2010 hi,i have implemented the row level security in the same way, but when i use those reports in my dashboard (using jasperserver pro 3.7.2)i get the following error even after supplying all the default values:The report is missing default values for atleast one mandatory parameter. Please suggest the resolution Link to comment Share on other sites More sharing options...
janna_m Posted July 24, 2012 Share Posted July 24, 2012 I have similar problem in getting the logged in organization/tenant id & using them in the SQL query.I used a parameter & captured the organization/tenant id as below, <parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/><parameter name="LoggedInTenantId" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{LoggedInUser}.getTenantId()]]></defaultValueExpression> </parameter>And printed this parameter in the main report - the proper value gets printed. I have a input control whose SQL Query is as below, select id, name from team_mst where TEAM_ID in (select team_id from access_mst where USER_ID = (Select USER_ID from user_mst where USER_NAME = '$P!{LoggedInUsername}' and ENTERPRISE_ID = $P!{ LoggedInTenantId} ) and ENTERPRISE_ID = $P!{ LoggedInTenantId} ) and VIR_TEAM_TYPE in (0,2) and DEACTIVATED = 0;Before loading the main report the input control query itself fails with error as,2012-07-24 17:00:37,881 ERROR errorPage_jsp,http-8080-1:487 - stack trace of exception that redirected to errorPage.jspnet.sf.jasperreports.engine.JRRuntimeException: Parameter "LoggedInTenantId" does not exist. at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.checkParameter(JRAbstractQueryExecuter.java:627) atnet.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendParameterClauseChunk(JRAbstractQueryExecuter.java:347) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendQueryChunk(JRAbstractQueryExecuter.java:262) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.parseQuery(JRAbstractQueryExecuter.java:247) The parameter have been defined. I'm not sure what is required here... & how can we resolve this...PLease anyone can show some pointer or help ...I have attached the JRXML that I'm using.. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now