camnott

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.

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.

Not positive. Where would I find this?

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!

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.

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.

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: <group name="Group2"> <groupExpression><![CDATA[$F{PROJECTID}]]></groupExpression> <groupHeader> <band height="132"> <subreport> <reportElement positionType="Float" x="-20" y="0" width="476" height="44" isRemoveLineWhenBlank="true" uuid="f23d4dd8-78da-4966-b78b-15dea93bcc17"/> <subreportParameter name="p_prj"> <subreportParameterExpression><![CDATA[$F{PROJECTKEY}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["repo:r&i.jrxml"]]></subreportExpression> </subreport> <subreport> <reportElement positionType="Float" x="-20" y="88" width="476" height="44" isRemoveLineWhenBlank="true" uuid="71e03d07-b3dc-4091-ba76-4f33fa5ee396"/> <subreportParameter name="p_prj"> <subreportParameterExpression><![CDATA[$F{PROJECTKEY}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlserver://#####;databaseName=####","####","####")]]></connectionExpression> <subreportExpression><![CDATA["repo:Defects.jrxml"]]></subreportExpression> </subreport> <subreport> <reportElement positionType="Float" x="-20" y="44" width="476" height="44" isRemoveLineWhenBlank="true" uuid="95eccdc4-efae-418d-a581-471d5ab54f30"/> <subreportParameter name="p_prj"> <subreportParameterExpression><![CDATA[$F{PROJECTKEY}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["repo:Issues.jrxml"]]></subreportExpression> </subreport> </band> </groupHeader> </group> Thank you, Chris

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}

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]

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

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]

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

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?

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.

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 : <variable name="v_return" class="java.lang.String" resetType="None" calculation="System"/> <printWhenExpression><![CDATA[new Boolean($V{v_return}!= null)]]></printWhenExpression> <returnValue subreportVariable="v_return" toVariable="v_return" calculation="System"/> Sub Report : <variable name="v_return" class="java.lang.String" resetType="None" calculation="System"> <variableExpression><![CDATA[$F{Type}]]></variableExpression> </variable>

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.

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.

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.

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?

$F{Week 1} is BigDecimal

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

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 Group 1 Here is a copy of some of the code : <variable name="Week 1" class="java.math.BigDecimal" resetType="Group" resetGroup="Group1" incrementType="Group" incrementGroup="Group1" calculation="Sum"> <variableExpression><![CDATA[$F{Week 1}]]></variableExpression> <initialValueExpression><![CDATA[0]]></initialValueExpression> </variable> <textField> <reportElement x="671" y="1" width="48" height="21" uuid="adc5d99a-2d2a-4205-9a75-3c7d2042367e"/> <textFieldExpression><![CDATA[$V{Week 1}]]></textFieldExpression> </textField> Any Idea's?

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.

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 ProjectIDFROM niku.ODF_CA_COP_PRJ_STATUSRPT srLEFT JOIN niku.INV_INVESTMENTS iinv ON sr.ODF_PARENT_ID=iinv.IDLEFT JOIN niku.PAC_MNT_PROJECTS pmp ON iinv.ID=pmp.IDLEFT JOIN niku.PRCHARGECODE prc ON iinv.CHARGECODEID=prc.PRIDWHERE 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.Mapat 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.Mapat 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?

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?