sgeissle

RequirementThe requirements are the following: To show a HTML 5 Chart for each detail row in the table.Each chart should only represent one value of this specific row of the table.As a table row offers not too much space, the chart should be minimalistic. No legend, axis etc.General approachThe general secret to this is to use a sub-report element in the row and use a chart in the sub-report. Documentation:Highcharts API: https://api.highcharts.com/highcharts/Jaspersoft Studio documentation.Create sub-reportThings to consider when creating the HTML 5 Chart sub-report: Get rid of all white space of the report. If the table row is 100px long and 30px wide then create the chart of this size in the chart properties. Also check the report properties delete all the margins and make the complete report that size.The subreport needs at least measure and category in the data set.As with all charts the measure is the numerical value of what you show (sales / percent of stock / etc.).The category is the descriptive value of the numerical value you are currently. The description (customer / part number / office / etc.) so that the number makes sense.The category needs to be passed into the subreport as a parameter. Therefore, create the parameter and add it to the query with a WHERE clause.We need to adapt the following properties (double click on chart => chart formatting => advanced properties) so that we get rid of all fuss that we cannot show in a table row:chart.spacingBottom, chart.spacingTop, chart.spacingLeft, chart.spacingRight – set to 1 px maybe to get rid of white spacelegend.enabled = false (no space to show the legend)tooltip.headerFormat => delete alltooltip.pointFormat = <b>{point.y}xAxis.visible = false / yAxis.visible = falseCreate main-reportThings to consider when creating the main report. Create main report with table element (instead of the table you can also use the detail band directly)Add table and subdata set. Make sure to add the category in the detail rows, because it has to be passed as a parameter to the sub-report.Add the chart by rightclicking one of the columns and choose to add a column before or after.Drag a subreport from the Palette to the new empty detail field of the unnamed column.Chose the subreport that you had created before and map the category as a parameter.Name the column.This should do the trick. Specific exampleI wanted to show a bar chart showing the difference of total sales to a customer between 2018 and 2017. Difficulties of this example:The difference of total sales can be positive or negative. If negative the chart starts on the right margin of the cell, if positive from the left. That might not be the best user experience there is. To work around that, I added one other column with the same subreport inside directly beside the first sub-report column. Now I added a print when expression in the sub-report elements properties. For the right column when the difference was positive, for the left column when the difference was negative.The bar should show red color when negative, green color when positive. To do that we need to go again to the advanced options of the chart formatting in the subreport:plotOptions.series.threshold = 0plotOptions.series.negativeColor = hex color for redFurthermore, add a green hex code as the first color of the color palette of the simple chart formatting properties.This works perfectly in the preview of the sub-report alone. If you preview the main report this configuration is not shown, but it shows the standard colors. So, this might be a defect of the v7.10 version.

ChallengeHTML5 charts per default display the x- and y-axis values in the tooltip. Still, business requirements might ask to show the values of the column in the tooltip to provide additional information. A Sales Manager for example has a chart which shows the deal size (measure – y-axis) made over time (category – x-axis) that were closed by the different sales representatives (series) in a column chart. In addition the Sales Manager may want to see in the tooltip if the deal was closed by an inside rep or a field sales rep and wants to know if this deal size is rated as high, medium or low. In the dataset the information is given for each deal size value in different fields. So, the requirement is to show this additional information dynamically in the tooltip for each chart point. ApproachSet up the category, the series and the measure (Chart Properties => Chart Data => Configuration) in the HTML5 chartAdd the two additional fields that hold the additional information in the tooltip as hidden measures. To do that, youAdd the fields as measures as usualAnd tick the hidden box. Modify the visible measure (the one that is already displayed in the tooltip)Enter the advanced properties tab and click on add to create a SeriesItemProperty contributor. The property name can be defined freely, but it is recommended to name it appropriately. Later on you need this property name to address it in the tooltip formatter.Select to use a measure value in the dropdown and choose the hidden measure and confirm with OK.Repeat the steps 4 and 5 with all additional measures you like to show in the tooltip (In our example two measures for sales type and for deal ratings are added). Finally navigate to the main chart properties tab and in the left menu open tooltip and formatting.The point format property is what you are looking for. In our use case you add the following line: {series.name}: {point.y} (Rating: {point.rating} / Type: {point.type})That way, the series name and the value is shown. Additionally the correspondent values of the hidden measures will be shown in brackets.