CA Service Management

Expand all | Collapse all

Jasper reports Data loading issue

Bakhtawar Butt

Bakhtawar ButtApr 01, 2019 05:49 AM

Bakhtawar Butt

Bakhtawar ButtApr 09, 2019 02:55 AM

  • 1.  Jasper reports Data loading issue

    Posted Apr 01, 2019 02:06 AM

    Hi ,

     

    We are using Jasper Reports 6.4.3 with CA SDM 17.1. When we want to retrieve data by using "Active Request aging for groups" and select (*) to retrieve all data it stucks on loading data and doesn't show anything same is the case with other.

    It just shows 1 page and all other pages are on loading  and not showing anything.

     

     

     

     

    Please help in this matter ASAP



  • 2.  Re: Jasper reports Data loading issue

    Posted Apr 01, 2019 05:49 AM

    any suggestions??



  • 3.  Re: Jasper reports Data loading issue

    Broadcom Employee
    Posted Apr 01, 2019 02:29 PM

    JasperSoft 6.6 is not certified/supported with 17.1.

     

    Did you try to see if the issue also happens with the supported JasperSoft 6.4.3 version?



  • 4.  Re: Jasper reports Data loading issue

    Posted Apr 08, 2019 08:24 AM

    Hy,

     

    We are using version 6.4.3 of jasper reports.

    We want to export report with both inactive and active status from jasper reports.

     

    Kindly help

     

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 5.  Re: Jasper reports Data loading issue

    Posted Apr 09, 2019 12:34 AM

    Hy team,

     

    Data loading issue is resolved but now we can export/fetch only active status reports. 

    How can we export inactive status reports?

     

    as we don't find anything that help us.

     

    Kindly help in this matter.



  • 6.  Re: Jasper reports Data loading issue

    Posted Apr 09, 2019 02:55 AM

    any updates?



  • 7.  Re: Jasper reports Data loading issue

    Posted Apr 09, 2019 11:34 PM

    Hi bakhtawar,

    If you look at the SQL for the report you will see something like this:

    To select inactive as well as (or instead of) active tickets, either remove the highlighted condition or change it to 'cr.active=0'.

    Hope that's what you're looking for!

    Regards,

    James



  • 8.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 12:09 AM

    Fine.

    But from where do we find sql of that report?

    What is the process to show the sql of report. As I am beginner I don’t know about jasper report much.

     

     

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 9.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 12:54 AM

    by changing it to cr.active=0 can we fetch both active and inactive reports? or just inactive?

    As we want both inactive as well as active.



  • 10.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 01:34 AM

    The condition 'cr.active=0' will give inactive tickets only.  Remove the condition 'cr.active=1' entirely to get both active and inactive tickets in the same report.  Bear in mind that there may be a very large number of inactive tickets if your site has been running SDM for a long time but without running archive/purge rules.

     

    To learn how to edit a report you should probably start here: CABI JasperReports Server for CA Service Management. This document may also be useful: Configure Jasper Studio for editing ... reports (KB000010048).

     

    Regards,

    James



  • 11.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 01:41 AM

    I understand this but how could we find that code? In our Db server?

     

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 12.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 02:19 AM

    The code for that specific report will be held in the database of the JasperReports Server.  If you have not done so already, you will need to download and install Jaspersoft Studio and configure it to access your JasperReports server (ideally your site should have a Dev/Test server), using the documentation linked above as your guide.  Consult Jaspersoft documentation and tutorials - some Jaspersoft tutorials are linked from the documentation above.  Jaspersoft Studio enables you to access the SQL that lies behind the components of the reports.  Please observe the cautionary note in the documentation, which recommends that you do not directly edit the out-of-box reports.  Instead, save a copy of the out-of-box report with a different name and edit the copy.  If this is the first time you have used Jaspersoft you will find there is something of a learning curve to climb, but the basic concepts are not so very different from those of other reporting environments.

    Regards,

    James



  • 13.  Re: Jasper reports Data loading issue
    Best Answer

    Posted Apr 10, 2019 04:04 AM

    Here are some screenshots to illustrate the process - this is not a substitute for the documentation linked previously :-).

    Find the out-of-box report in the Repository Explorer in Jaspersoft Studio, copy and paste into a 'test' folder.

    Run the copied report to verify that it works:

    My test data includes only 2 open tickets with priority 2:

    Edit the list sub-report.

    In the edit pane, click on the icon for the 'Dataset and Query editor':

    Edit the where clause, commenting out 'cr.active=1 AND'

    Save and publish back to the repository.

    Run the report again from Jaspersoft Studio.  Now my report includes Closed tickets:

    The chart component has a separate query which will need the same edit, to keep the charts and the detail lists consistent with each other.

    Hope that helps!

    Regards,

    James



  • 14.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 06:24 AM

     

     

    We have this type of layout so how can i open data set and query editor to change my where clause?



  • 15.  Re: Jasper reports Data loading issue

    Posted Apr 10, 2019 10:13 PM

    That is a screenshot from the CABI / Jaspersoft Report Server.  You need to download, install and configure Jaspersoft Studio - which is a separate product - to edit the reports.  Please review the following documentation (external link): How to configure Jaspersoft Studio.



  • 16.  Re: Jasper reports Data loading issue

    Posted Apr 11, 2019 07:34 AM

    Thank you so much @JAMES it worked fine for our environment we are able to fetch both active and inactive data.

    But now the query is our calendar is not working fine. Whether we put date on calendar or not it fetches all the data that is present in SDM.

     

    Our calendar filter is not working properly.

     

    Kindly provide me some solution how to sort out the calendar issue so that i can fetch data according to our requirement.

     

    Thanks in Advance!



  • 17.  Re: Jasper reports Data loading issue

    Posted Apr 12, 2019 12:21 AM

    camja06@ca.com  is there any solution for calendar issue?



  • 18.  Re: Jasper reports Data loading issue

    Posted Apr 12, 2019 04:10 AM

    Hi bakhtawar,

    Apologies, I've been off-line for a couple of days - will try to get you an answer by Monday.

    Regards,

    James



  • 19.  Re: Jasper reports Data loading issue

    Posted Apr 12, 2019 04:42 AM

    I knew I'd seen this somewhere recently :-)  Check out the entry marked as 'Correct Answer' in this thread:

    Jasper Studio, filtering on a date range .  The trick is to prompt for a Date parameter, and also have a String parameter with a default value expression that converts the content of the Date parameter into the String.  And then use the String parameter in the select statement, using the 'ts' operator to convert the String back into a timestamp.  (That sounds more complicated than it really is - try the example in the thread).  The String parameter must not be flagged 'Is For Prompting'.

     

    Regards,

    James



  • 20.  Re: Jasper reports Data loading issue

    Posted Apr 16, 2019 07:06 AM

    As you said I have created two parameters but still couldn’t find it helpful.

    I cannot able to view the query you is shown in link.

    After creating parameters we won’t be able to find that query and also date filter is not working fine.

    What to do now?

     

    Can you please elaborate with screenshots?

     

     

    Regards,

     

    Bakhtawar Butt



  • 21.  Re: Jasper reports Data loading issue

    Posted Apr 17, 2019 05:30 AM

    Here is one way to add selection by date to a report using the SDM connector, using parameters. 

    1. In Jaspersoft Studio, open the report and open the Dataset and Query Editor.  Go to the 'Parameters' tab:

    You can hide the built-in parameters by clicking the icon above the parameters list (highlighted above). 

    2. Click the 'Add' button and add a parameter with the following properties:

    This is the parameter that will prompt for the earliest open date.  Because it is defined as 'java.sql.Timestamp' it will launch a calendar prompt.

    Enter this as the 'Default Value Expression':

    NOW()

    3. Add a second parameter with the properties shown below:

    Enter this as the 'Default Value Expression':

    DATEFORMAT($P{pOpenDateMin},"yyyy-MM-dd HH:mm:ss")

    This parameter does not prompt. Its purpose is to convert the value that was entered in the Date parameter into a String.

    4. In the query SQL you can now use the String parameter to control the date selection.  For example:

    The 'ts' operator converts the value of the String parameter 'pOpenDateMinStr' to a timestamp.

    5. Preview the report.

    The date parameter will be prompted for and the report will show only records opened after the selected date.  I have added the parameter value to the Title band.

     

    Hope that gets you going.

    Regards,

    James



  • 22.  Re: Jasper reports Data loading issue

    Posted Apr 17, 2019 12:23 AM

    Any update on this ?

     

     

    Regards,

     

    Bakhtawar Butt



  • 23.  Re: Jasper reports Data loading issue

    Posted Apr 17, 2019 05:36 AM

    Please see above (I was just finishing it when your message came in :-) )



  • 24.  Re: Jasper reports Data loading issue

    Posted Apr 17, 2019 06:13 AM

    We have tried the code but failed to get output.

    I am attaching screenshots for your reference

    J1 shows the code and J4 shows the error we receive when we login jasper reports and select group to export tickets .

     

    Your swift response is highly appreciated.

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 25.  Re: Jasper reports Data loading issue

    Posted Apr 17, 2019 10:49 PM

    I can't see enough of that error message to make out a possible cause.  I take it that you are modifying 'Active Requests Ageing by Groups'.  I'll apply a similar mod on my testbed and see how it goes.



  • 26.  Re: Jasper reports Data loading issue

    Posted Apr 18, 2019 12:29 AM

    Yes we are doing it on 'Active Requests Ageing by Groups'

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 27.  Re: Jasper reports Data loading issue

    Posted Apr 18, 2019 05:38 AM

    OK, I guess you would have seen somewhere towards the end of that SQL error message something like the following text: {ts'null'}

    Because the 'ageing' report comprises a main report and two sub-reports, the prompts are issued by the 'Main' report, and we need to pass the resulting values to the subreports.  What I did to make this work in a test platform follows.  All of this is of course done in a 'test' folder.

    • Edit the sub-report 'Active_Req_Aging_Group_Sub_List.jrxml':
      • Add just one parameter - 'pOpenDateMinStr' - to the sub-report.
      • Include that parameter in the SQL query for the sub-report, using the 'ts' operator, as you have probably already done.
      • Save and publish.
    • Edit the main report 'main_jrxml':
      • Add the Date parameter 'pOpenDateMin' to the main report and flag it to prompt.  Make the type 'java.util.Date'.
      • Add a variable, 'vOpenDateMinStr', of type 'java.lang.String'.  If you have the parameter 'pOpenDateMinStr' in the main report, delete it.  Set the Expression for 'vOpenDateMinStr' to be DATEFORMAT( $P{pMinOpenDate}, "yyyy-MM-dd HH:mm:ss").
      • Save and publish.
    • Now you need to pass the variable 'vOpenDateMinStr' from the 'main' report, to the sub-report, and put the value into the parameter 'pOpenDateMinStr':
      • In the main report Design view, click on the outline of the sub-report.  You will see the Properties for the sub-report in a tab at the bottom right-hand corner of the Jaspersoft Studio window (assuming you are running with the default layout).  Click on 'Subreport' to show the subreport section of the properties:

    Click the button 'Edit Parameters'.  You should see this:

     

    Click 'Add'. In the 'Parameter Configuration Dialog', first select the parameter 'pOpenDateMinStr' from the drop-down Parameter Name list:

    Then in the 'Parameter Expression' field enter this (or use the expression editor to create it):

    $V{vMinOpenDateStr}

    Click OK, then click Finish.

    Save and publish the main report.

    Run the report in JasperReports. (I found after making these edits that I couldn't 'preview' the report in Jaspersoft Studio - it tells me I have to add an input and I don't yet know why.)  Hopefully you will see the prompts for Group and Date and you will find that the date filter is applied correctly.

     

    Now repeat the process to edit the Chart and pass the variable into the chart's parameter 'pOpenDateMinStr'.

     

    Regards,

    James



  • 28.  Re: Jasper reports Data loading issue

    Posted Apr 18, 2019 07:32 AM

    We have done everything mentioned in the link but we could not find 'pOpenDateMinStr' from parameter list we have only pOpenDateMin what to do now.

     

     

     

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 29.  Re: Jasper reports Data loading issue

    Posted Apr 22, 2019 12:27 AM

    Any update ?

     

     

    Regards,

     

    Bakhtawar Butt

    Officer Technical Service Desk | ITSM /ITG

    Information Technology Group

    3rd Floor, MCB Building, Nila Gunmbad , Lahore

     

    Landline: +92 42 323 0 4610 | IPT: 042-4610



  • 30.  Re: Jasper reports Data loading issue

    Posted Apr 22, 2019 09:44 PM

    Hi Bakhtawar,

    please attach your 'jrxml' files to the thread - I'll take a look.  Could it be that you have added 'pOpenDateMin' to the sub-report, rather than 'pOpenDateMinStr'?

    Regards,

    James



  • 31.  Re: Jasper reports Data loading issue

    Posted May 13, 2019 03:55 AM

    Hi James,

     

    Hope you are doing good.

     

    Sorry for the late reply actually i was quite busy so unable to reply.

     

    You were asking about jrxml file but i didn't able to send it to you because when i copy it from folder to send it to you it won't allow me to copy files. So how can i send it to you?