Query filtered by LoggedInUser.getUsername()

0
Hi,

We have a lot of users and we need to create only one report! We need to use the LoggedInUser.getUsername() or LoggedInUser.getFullName() to filter out records, so that the user can see his (an only his) data in the report.

We have tried to use (from the posts existing here) this query:

Code:
SELECT<br />
     details.`Detail` AS details_Detail,<br />
     users.`name` AS users_name,<br />
     users.`db` AS users_db<br />
FROM<br />
     `details` details LEFT OUTER JOIN `users` users ON details.`idUser` = users.`idUser`<br />
WHERE<br />
     users.name = '$P{LoggedInUser}.getFullName()'</td></tr></tbody></table><br />
<br />
and we have declared the parameter like this:<br />
 size=300]   size=323]<img src="http://www.jasperforge.org/components/com_joomlaboard/uploaded/images/parameter-e7b4ffca05cf8643b7c2d7a18ad396b4.JPG" width="Array"></img> <br />
<br />
When we publish the report on the server we receive this error: <br />
<table cellpadding="0" cellspacing="0" align="center" width="525" bgcolor="#cccccc"><tr><td> com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : <br />
	 1. Parameter type not supported in query : LoggedInUser class com.jaspersoft.jasperserver.api.metadata.user.domain.User<br />
</td></tr></table><br />
<br />
We have studied the SalesByMonths example, but there is a different approach to LoggedInUser. <br />
<br />
Please help us with an example or tutorial on how to do this in iReports and publish it on the server. <br />
<br />
Thank You!<br />
P.S. If there is another approach to this matter please show us! :woohoo:
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago

28 Answers:

0
Here is the parameter declaration: size=323]http://www.geseidl.ro/images/jasper/parameter.JPG
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0
"WHERE users.name = '$P{LoggedInUser}.getFullName()'" will not work, you'll have to do something like this:
  • Define a new parameter named LoggedInUserName, and set it's default value expression to $P{LoggedInUser}.getFullName().

  • Use $P{LoggedInUserName} in the query (note that you don't need to use quotes around the parameter placeholder).
  • [/ul]

    HTH,
    Lucian
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Thanks, but it doesn't work yet...

I have created a 1st parameter $P{LoggedInUser} from class com.jaspersoft.jasperserver.api.metadata.user.domain.User with no default value (also tried prompt and no prompt)

Then I created the 2nd parameter $P{LoggedInUserName} of class java.lang.String with the default value of $P{LoggedInUser}.getFullName() (also tried prompt and no prompt)

The query was:
SELECT
details.`Detail` AS details_Detail,
users.`name` AS users_name,
users.`db` AS users_db
FROM
`details` details LEFT OUTER JOIN `users` users ON details.`idUser` = users.`idUser`
WHERE
users.name = $P{LoggedInUserName}


*Note! When I used the default value of "Name1", the report printed out ok, so the query is good.

In the SQL Query log we have:
SELECT
details.`Detail` AS details_Detail,
users.`name` AS users_name,
users.`db` AS users_db
FROM
`details` details LEFT OUTER JOIN `users` users ON details.`idUser` = users.`idUser`
WHERE
users.name = NULL


Please help us to understand what are we doing wrong ... :)
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0
Could you test whether $P{LoggedInUserName}.getFullName() is null or not? A simple way to do this is to display it in a report text field.
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
$P{LoggedInUserName}.getFullName() ? Perhaps you reffered to $P{LoggedInUser}.getFullName() ...

Yes, $P{LoggedInUser}.getFullName() returns as String the name of the user... Everything ok here, in the text field.

Perhaps there are some differences in the query parser?
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0
Yes, I meant $P{LoggedInUser}.getFullName(). You could also try to show $P{LoggedInUserName} in a text field..

I don't understand why LoggedInUserName's default value expression doesn't work. Could you post your JRXML so that we'd be able to replicate the issue? How are you running the report?
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Hello,

Does anyone know how to make such a query? The suggestions from lucian do not work.

Anyone else has any idea?

Thanks
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0
:laugh:
Sorry lucian, I haven't seen your post! I am new here...
[url=http://www.geseidl.ro/images/jasper/Test01_jrxml_1215511262390.jrxml]Here is the jrxml...

Please help!
:)

By the way, is it possible to send a file/image to the forum? until now I only could post links to my website.
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0
You are using $P{LoggedInUser} in the query, and not $P{LoggedInUsername} as you should. Fix this and see whether it works.

By the way, is it possible to send a file/image to the forum? until now I only could post links to my website.


It should be possible, aren't the image and file attachment controls working?

Regards,
Lucian
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Hello,

Just to say that I already have a report like the one of Robert which runs a query on the meta database and I filter with the name of the user.

I did exactly as Lucian said, that is :
- create a 2nd parameter "$P{FullUserName}" initialized by defaut with "$P{LoggedInUser}.getFullName()"
- and in my query I have :
"where jasperserver.JIUser.fullname=$P{FullUserName}"

and it works fine.
Gaby38's picture
338
Joined: Mar 22 2008 - 1:49am
Last seen: 11 years 3 months ago
0
Hello,

Just to say that I already have a report like the one of Robert which runs a query on the meta database and I filter with the name of the user.

I did exactly as Lucian said, that is :
- create a 2nd parameter "$P{FullUserName}" initialized by defaut with "$P{LoggedInUser}.getFullName()"
- and in my query I have :
"where jasperserver.JIUser.fullname=$P{FullUserName}"

and it works fine.
Gaby38's picture
338
Joined: Mar 22 2008 - 1:49am
Last seen: 11 years 3 months ago
0
Thank you both!

It works now. I have misplaced the parameters.

Nice work!
robertpopa22's picture
Joined: Dec 17 2007 - 4:52pm
Last seen: 11 years 6 months ago
0

Does this feature work on the community version?  It seems I have misplaced my parameters too, I can not make it work.

huw@trimet.org's picture
Joined: Feb 8 2007 - 9:24am
Last seen: 1 year 2 months ago
0

Yes this feature is in the community version.

Gaby38's picture
338
Joined: Mar 22 2008 - 1:49am
Last seen: 11 years 3 months ago
0

Is there a way to pass a parameter into Input Control's query? In other words I want to generate different multi-select input controls by passing the pasperserver username into the query.

valbumbu's picture
Joined: Dec 18 2008 - 4:04pm
Last seen: 10 years 6 months ago
0

Is it possible to pass a parameter into the query of a input control (which is multi-select list). So thr list contents will differ depending on the value of the parameter, e.g. $P{LoggedInUserUsername}. I'm able to display the correct value of the username, but looks like the input control's query not receiving it properly, and the content of the list is empty.

valbumbu's picture
Joined: Dec 18 2008 - 4:04pm
Last seen: 10 years 6 months ago
0

What you are after is what we call "cascading input controls". There is no solution for this right now, but we are looking to package up some extensions to JasperServer you can add to do this.

 

Sherman

Jaspersoft

swood's picture
2377
Joined: Jun 21 2006 - 12:48pm
Last seen: 6 years 8 months ago
0
As an appendum to the original post , in case anyone else is trying this, the initial parameter of type class com.jaspersoft.jasperserver.api.metadata.user.domain.User *has* to be named LoggedInUser or a null value will be returned from any method called on it. Strange.
elam's picture
3
Joined: Feb 23 2009 - 9:19am
Last seen: 10 years 3 months ago
0

I have coded like Gaby38 but LoggedInUser seems to not exist.

When I attemp to run the report JS3.5 says:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Report design not valid : 1. Parameter not found : LoggedInUser

Is there a part in the docs where I can find a list of all JS' built-in parameters?

federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago
0

Sorry, i hadn't create LoggeInUser parameter but only the second parameter that use it.

I named the second parameter USERNAME.

I have discover that if you put USERNAME, before LoggeInUser, it's impossible for it to read LoggeInUser parameter.

I think that the order of parameters is the order of instantiation. When I read USERNAME, LoggeInUser wasn't instantiated yet.

Read the correct code here below:

Code:
<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User" isForPrompting="false"/>
<parameter name="USERNAME" class="java.lang.String" isForPrompting="false">
	<defaultValueExpression><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression>
</parameter>


Post Edited by helyair at 11/06/2009 10:05
federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago
0

Hi,

 - defined new parameter and named username, and set Parameter Class Tyape: java.lang.String, and set Use as aPrompt:True

and set Default Value Expression: $P{LoggedInUser}.getFullName(), then click OK.

 - use '$P!{username}' in query.  where user.name = '$P!{username}'

it should work, I tried it. :)

 

Samer

samerzohdi's picture
Joined: Apr 30 2009 - 4:48am
Last seen: 10 years 1 month ago
0
Yes, i have just found the solution and i have resolved another problem, read carefully my last post.
federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago
0

swood
Wrote:

What you are after is what we call "cascading input controls". There is no solution for this right now, but we are looking to package up some extensions to JasperServer you can add to do this.

 

Sherman

Jaspersoft

 

 

Is there any news?...

I have tried to use $P{LoggedInUsername} on a input control query.

Query runs correctly, but every browser (Safari, Firefox, IE) gives same error. See image below.

Update: this error occurs only on JS 3.7, it doesn't occur on JS 3.5.

Post Edited by helyair at 08/30/2010 13:16



Post Edited by helyair at 08/30/2010 13:38



Post Edited by helyair at 08/30/2010 16:01
federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago
0


If this is abt cascading input control ..it is available as feature in JR Pro 3.7. , JS CE 3.7 and JS Pro 3.7.1 [ with many bug fixes ]

Ramnik Kaur

Senior QA Engineer

ramnik's picture
331
Joined: Mar 16 2007 - 9:10am
Last seen: 12 years 3 months ago
0

 Hey,

i followed the guidelines you've mentioned in this post, but i still get and error:

 

1. i created a parameter called LoggedInUser with parameter class: com.jaspersoft.jasperserver.api.metadata.user.domain.User

2. i created a parameter called FullUserName wuth parameter class: java.lang.String

    and 'default value expression' $P{LoggedInUser}.getFullName().. 

 

i created a simple report with only text labels of the parameters.

when i try to compile the report i get an exception:

net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 
     1. java.lang.ClassNotFoundException: com.jaspersoft.jasperserver.api.metadata.user.domain.User
    at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:258)
    at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:140)
    at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:215)
    at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:131)
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:514)
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)

i'm using latest jasper server and ireport (3.7.6), has the class changed? 

thanks.

 

eyale's picture
50
Joined: Dec 4 2010 - 10:56pm
Last seen: 8 years 6 months ago
0

 Have you compiled it with iReport?

Try it on JasperServer.

federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago
0
What do you mean?
deploy it to the server and run it there?
eyale's picture
50
Joined: Dec 4 2010 - 10:56pm
Last seen: 8 years 6 months ago
0
Yes!
If you read exception you will see that iReport can not find the JasperServer ...User class because it is not present on iReport classpath, so if you upload jrxml on JasperServer all will work!
federico.cattozzi's picture
Joined: Sep 22 2009 - 4:34am
Last seen: 5 years 2 months ago

Hi Federico,

as per your suggestion, i have created the same. please find below jrxml file. i am getting empty report only. i not getting loggedinUserName. please advice me

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report7" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="c1202fd9-44f4-42c6-a3c2-45c21bc60347">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User" isForPrompting="false"/>
<parameter name="Username" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression>
</parameter>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="76">
<textField>
<reportElement uuid="b128f89e-0122-47be-a02e-e76026359414" x="22" y="21" width="364" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$P{Username}]]></textFieldExpression>
</textField>
</band>
</title>
<columnHeader>
<band height="50">
<staticText>
<reportElement uuid="6749d95a-5d66-49af-840a-d3afb0347883" x="268" y="26" width="100" height="20"/>
<textElement/>
<text><![CDATA[Test]]></text>
</staticText>
</band>
</columnHeader>
<summary>
<band height="130" splitType="Stretch">
<staticText>
<reportElement uuid="4a30d70a-23b5-4fa1-a80f-c2add14714d2" x="240" y="42" width="100" height="20"/>
<textElement/>
<text><![CDATA[Test]]></text>
</staticText>
</band>
</summary>
</jasperReport>

rajesh.sm - 5 years 7 months ago
Feedback
randomness