camnott

July 16, 2018

Actually found my own way to get around this. Added new parameters p_connection p_user p_password and changed the expression to

java.sql.DriverManager.getConnection($P{p_connection},$P{p_user},$P{p_password})

Works perfectly.

July 16, 2018

Hello,

I have a report that uses several connections to pull data. The issue I am running into is this : When one of the connections fails, it shows an error in AdvancedReporting that shows the connection expression, including password....

java.sql.DriverManager.getConnection("jdbc:db2://server/database","username","password")

How can I stop this from showing on error, or at least blank out the expression? This is a fairly significant security breach... Not sure why it would show the entire expression.

Thank you.

June 20, 2018

Not positive. Where would I find this?

June 19, 2018

Hello,

I need help with a subreport connection. I have a main report using a different data source, and am trying to connect via connection expression as follows :

java.sql.DriverManager.getConnection("jdbc:db2://hostname/db", "username", "password")

When I am in jaspersoft studio, it seems to work, at least the query functions... However, from Clarity Advanced Report I keep getting :

"Error when evaluation expression: java.sql.DriverManager.getConnection("jdbc:db2://hostname/db", "username", "password")"

I really need to get this worked out, as I have used something similar in the past... not sure why this is not working in Clarity.

Thank you!

June 14, 2018

I have a subreport that is working fine by itself, but when I try to run the main report, I get an error...

Here is the connection expression : java.sql.DriverManager.getConnection("jdbc:db2://hostname/db", "username", "password")

I have been trying to get this to work all day... the error I receive is :

Cannot evaluate expression : java.sql.DriverManager.getConnection("jdbc:db2://hostname/db", "username", "password")

Any help would be appreciated.

June 4, 2018

Hello,

I created a new report in JasperSoft Studio (Which runs perfectly fine in preview mode, and in SQL Server Management Studio)..

However, I get an error when trying to run from Clarity Advanced Reporting.

Here is part of the Query that I believe is causing the issue (Removed rest due to containing secured information) :

SET NOCOUNT ONDECLARE @start_date date = 'Jan 1, 2011';DECLARE @end_date date = dateadd(year,40,@start_date);DECLARE @Nwkdy as Table (dtday DATE);WITH  AllDays    AS (      SELECT @start_date AS DtDay          , MONTH(@start_date) as mnth          , DAY(@start_date) as dayofM          , DATENAME(weekday, @start_date) as dtname        UNION ALL     SELECT              DATEADD(DAY, 1, DtDay)          , MONTH(DATEADD(DAY, 1, DtDay)) as mnth          , DAY(DATEADD(DAY, 1, DtDay)) as dayofM          , DATENAME(weekday,DATEADD(DAY, 1, DtDay)) as dtname             FROM   AllDays             WHERE  DtDay < @end_date               )INSERT INTO @Nwkdy   SELECT DtDay FROM AllDays     where            (                 (mnth =1 and dayofM = 1)                                              -- new years        or      (mnth =5 and dayofM >= 25 and dtname = 'Monday')                      -- memorial day        or      (mnth =7 and dayofM = 4)                                              -- Indendence day        or      (mnth =9 and dayofM  <= 7 and dtname = 'Monday')                      -- Labour day        or      (mnth =11 and dayofM between 22 and 28 and dtname = 'Thursday')       -- Thanksgiving        or      (mnth =12 and dayofM = 25)                                            -- Xmas        )        OPTION (MAXRECURSION 0)[/code]

Any help would be appreciated.

AGAIN : This runs perfectly fine in JasperSoft Studio Preview AND SQL Server Management Studio.

March 29, 2018

Hi,

I have a group that contains 3 separate sub reports - each with a vastly different query. This is basically a detailed listing of information - if specific conditions apply. When there is no information to display, I still get a blank page in my report.

How can I remove the group when all 3 subreports are empty?

Code below:

Thank you,

Chris

March 15, 2018

Is this value a field in the main report?

If so - Try this:

Create a parameter in the main report named p_clientID. Make sure that it is set to "Is For Prompting" is selected. Then click on the sub report object, and then the subreport tab, click parameters and add a new parameter with the same name. Then click expression (a box with [...] will show) and use the field from the main report.

In your subreport, modify your query to something like "WHERE client_id = $P{p_clientID}"

This should pass the parameter from the main report to the sub report.

If it is not a field in the report - Create same parameters, but the expression would be $P{p_clientID} or $P!{p_clientID}

March 15, 2018

Hello,

I have two fields, $F{UPCOMINGEVENTS} and $F{ACCOMPLISHMENTS}. Each of these fields have a label, which is in the same group. The fields are set to Blank When Null, which works fine.

I am trying to find a way to also remove the labels when the field is blank. I found a response that was like this $F{UPCOMINGEVENTS}==null?"":$F{LabelName} this did not work as it could not resolve the label as a field.

Any thoughts?

                <textField>                    <reportElement positionType="Float" x="440" y="82" width="120" height="18" uuid="f2f0234a-624f-4651-a768-f7532717483d"/>                    <textElement>                        <font isBold="true"/>                    </textElement>                    <textFieldExpression><![CDATA["Upcoming Activities:"]]></textFieldExpression>                </textField>                <textField isStretchWithOverflow="true" isBlankWhenNull="true">                    <reportElement positionType="Float" x="440" y="100" width="340" height="40" uuid="7f192789-debc-4da5-9836-da64a626b9d2">                        <property name="local_mesure_unity" value="inch"/>                    </reportElement>                    <box padding="0">                        <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>                        <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>                        <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>                        <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>                    </box>                    <textFieldExpression><![CDATA[$F{UPCOMINGEVENTS}]]></textFieldExpression>                </textField>[/code]

March 8, 2018

Thank you "Friendly User" - This was the issue. It is now working the way that I need it to.

February 23, 2018

Caused by: java.lang.ClassCastException: com.microsoft.sqlserver.jdbc.SQLServerConnection cannot be cast to net.sf.jasperreports.engine.JRDataSource

I am trying to create a connection to a data source for my subreport - here is parameter expression :

(Blocked out company information for this)

<parameter name="subreport_datasource" class="java.sql.Connection" isForPrompting="false">        <defaultValueExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlserver://Host;databaseName","user","pword")]]></defaultValueExpression>    </parameter>[/code]

February 23, 2018

Thank you for your comment, that just produced a different error - which leads me to believe that the first error was not the issue in the first place... :

com.microsoft.sqlserver.jdbc.SQLServerConnection cannot be cast to net.sf.jasperreports.engine.JRDataSource

February 23, 2018

Hello.

This issue stems from my report concept. I am trying to create a main report that houses minimal information. I then have a sub report that pulls in some related information from other db tables.

I am trying to add another subreport that uses a separate datasource from the other two reports. I have searched online and found a solution that I thought would work, and I still think that it would work, if not running into this issue :

java.sql.DriverManager.getConnection("jdbc:sqlserver://HostName;dbName","username","password")[/code]

Now - Before anyone answers saying that I should not have the //HostName portion - I know this... This is my problem...

When the DBA's in my organization named this, they added that backslash ( ) into the hostname. I cannot get around this if I want to connect.

I was able to create a Data Adapter using this, but cannot create an expression using the same. Kicks out the following : Invalid escape sequence (valid ones are b t n f r " ' \)

Is there a workaround, or another method that will be equally successful? The report runs, and the first subreport shows its data, but second subreport shows nothing, no matter what I do (because it is not getting data from the correct data source)

Any Ideas?

December 1, 2017

Yes to both:

Sub report band is called, and variable is actually in the band to show the value pulled.

Main report pulls a separate query, sends a parameter to be used as a criteria in the sub report. Sub report sends variable back and prints the main report band based on whether or not the variable is null.

December 1, 2017

So - I was finally able to get the report to compile and run, however... The variable on the main report is returning null every time, therefore my band will not print..

Code from main report :

Sub Report :

November 30, 2017

Hello,

I am trying to send a variable from a sub report to the main report, and then use the variable in a print when expression to eliminate blank space.

The variable is set up exactly the same on master and sub reports name=

v_return, class = java.lang.String, calculation = no calculation function, expression = blank, Initial value expression = blank, increment type = none, incrementer factory class name = blank, reset type = none

In my print when expression - new Boolean($V{v_return}!= null)

When I run the report, I get the following Error - net.sf.jasperreports.engine.JRRuntimeException: Increment class not found.

Any idea's? I have been searching all day to find a solution, but nothing has helped.

September 14, 2017

Found the issue....

WHERE $X{IN, SRP.cop_report_date, TIME_PERIOD}

So.... I had this parameter as a collection. Got rid of this, and created two new parameters. Period Start and Period End, Changed line to

WHERE SRP.cop_report_date BETWEEN $P{PERIOD_START} AND $P{PERIOD_END}

Works like a charm. I guess I should have done it this way originally. Was just trying to make it easier.

September 14, 2017

If you are refering to what type of database, it is a sql server 2008. and this query runs perfect in SQL Server Management Studio.

It almost seems like the parameter is not being passed to the sub query.

I will do some further testing.

September 13, 2017

I have an EXCEPT clause in a sub query, it doesn't seem to be working...

SQL:

SELECT INV.ID [investment Key]
, INV.CODE [investment ID]
  , INV.NAME [investment Name]
, RES.FULL_NAME [investment Manager]
, STAGE.NAME [stage]
, PHASE.NAME [Phase]
FROM niku.INV_INVESTMENTS INV
LEFT JOIN niku.SRM_RESOURCES RES ON RES.USER_ID = INV.MANAGER_ID
LEFT JOIN niku.ODF_CA_PROJECT ODP ON INV.ID = ODP.id
LEFT JOIN (
SELECT *
FROM niku.CMN_LOOKUPS_V LKV
WHERE LKV.LOOKUP_TYPE = 'INV_STAGE_TYPE'
           AND LKV.LANGUAGE_CODE = 'en'
) STAGE ON stage.LOOKUP_CODE = INV.STAGE_CODE
           LEFT JOIN (
SELECT *
FROM niku.CMN_LOOKUPS_V LKV
WHERE LKV.LOOKUP_TYPE = '******* <Intentionally Blocked out> *****_SDLC_PHASES'
           AND LKV.LANGUAGE_CODE = 'en'
) PHASE ON ODP.******* <Intentionally Blocked out> *****_sdlc_phase = PHASE.LOOKUP_CODE
INNER JOIN (
SELECT INV.ID [investment ID]
FROM niku.INV_INVESTMENTS INV
WHERE INV.STATUS = 1
AND INV.IS_ACTIVE = 1
AND INV.CODE LIKE 'PRJ_%'
AND INV.ENTITY_CODE LIKE ******* <Intentionally Blocked out> *****

EXCEPT

SELECT SRP.ODF_PARENT_ID [investment ID]
FROM niku.ODF_CA_COP_PRJ_STATUSRPT SRP
WHERE $X{IN, SRP.cop_report_date, TIME_PERIOD}
           ) Reports ON INV.ID = Reports.[investment ID]
           WHERE $X{IN, res.FULL_NAME, INV_MANAGER}
           AND $X{IN, INV.CODE, INV_ID}
           ORDER BY [investment Manager]

Report runs perfectly, with one issue... The Except Clause.

Any Idea's?

September 5, 2017

$F{Week 1} is BigDecimal

August 30, 2017

So - Figured out the problem. The sum is coming back with "NULL"

....... Had to remove initial value of 0.

The probelm remains though, still no sum is happening

August 30, 2017

I have a variable to sum ETC's.

It pulls in etc's by resource by investment.

When I try to run the preview, I get this error : Reason: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source Text $V{Week 1}

Basically, my SQL is doing a sum on the timeslice for each resource/investment/role. This works flawlessly in SQL Server Management Studio, but when trying to sum in Jaspersoft, it does not group the investments.

I tried creating the variables as such :

Week 1

java.math.BigDecimal

Sum

$F{Week 1}

0

Group 1

Here is a copy of some of the code :

Any Idea's?

March 30, 2017

So - This is a foolish mistake I made. Thank you for your answer Mariano, but I figured out what I was doing wrong.

When I was trying to pass parameters down to the sub report, I apparently mapped this parameter to the report by mistake, instead of passing it down.

When it tried to call the report, it was trying to map the parameter, throwing the error.

March 28, 2017

I have the following Parameter :
<parameter name="p_class_choice" class="java.util.Collection" nestedType="java.lang.String">
<parameterDescription><![CDATA[]]></parameterDescription>
</parameter>

my Query :

SELECT sr.challenges Challenges,

pmp.CLASS Class,

prc.PRNAME ChargeCode,

sr.goodnews Goodnews,

sr.cop_report_date ReportDate,

iinv.NAME ProjectName,

iinv.CODE ProjectID

FROM niku.ODF_CA_COP_PRJ_STATUSRPT sr

LEFT JOIN niku.INV_INVESTMENTS iinv ON sr.ODF_PARENT_ID=iinv.ID

LEFT JOIN niku.PAC_MNT_PROJECTS pmp ON iinv.ID=pmp.ID

LEFT JOIN niku.PRCHARGECODE prc ON iinv.CHARGECODEID=prc.PRID

WHERE sr.cop_report_date >=$P{p_start_date}

AND sr.cop_report_date <=$P{p_end_date}

AND $X{IN, pmp.CLASS,p_class_choice}

I had the pmp.CLASS field sorted with "AND NOT pmp.CLASS in (abc,123,etc...) " prior.
Needed to make a multi select query for expanded reporting purposes.

Causes :

net.sf.jasperreports.engine.JRException: java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.util.Map

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:468)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:443)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:335)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Caused by: java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.util.Map

at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:556)

at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:429)

at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:409)

at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:307)

at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:259)

at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:456)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2067)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillGroupHeader(JRVerticalFiller.java:649)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillGroupHeaders(JRVerticalFiller.java:571)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:296)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:152)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963)

at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)

at java.lang.Thread.run(Unknown Source)

Any thoughts?

March 22, 2017

I am having a strange issue... I am able to connect to our server that hosts the Clarity Database, but am unable to see the niku tables....
It shows all of the INFORMATION_SCHEMA tables, however.

I tried to write a small query to test if they were just unseen (not sure why they would be) but the tables are "non-existant".
I use MS Access to connect via ODBC, and have no issue connecting to any of these tables... I am using the following format for the JDBC Driver

jdbc:hsqldb:<servername>/niku

Am I just using the wrong driver for this?

Profiles

Forum

Events

Featured Visualizations

Knowledge Base

Documentation (PDF Downloads)

Blog

Documentation (Test Area)

Documentation

Dr. Jaspersoft Webinar Series

Downloads

Posts posted by camnott

Products

Explore