Thursday 30 March 2017

JFree Statcked Bar Chart Example in Jasper Design Studio

This is a quick re-visit of JFree Stacked Bar chart in Jasper.

Query Format : 
select * from 
(
(
SELECT 
'Store Sales' as Sales, 
date(t.the_date) as date,
SUM(sf7.Store_sales) as SalesFact
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 25
)

UNION
(
SELECT 
'Unit Sales' as Sales,
date(t.the_date) as date,
SUM(sf7.unit_sales) as SalesFact  
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 25
)
) a ORDER BY a.date

Query Output:
Sales          date          salesfact

"Unit Sales";"2012-01-01";348.0000
"Store Sales";"2012-01-01";706.3400
"Unit Sales";"2012-01-02";635.0000
"Store Sales";"2012-01-02";1304.5300
"Store Sales";"2012-01-03";1294.1200
"Unit Sales";"2012-01-03";589.0000
"Unit Sales";"2012-01-04";20.0000
"Store Sales";"2012-01-04";42.8700
"Store Sales";"2012-01-05";1987.1900
"Unit Sales";"2012-01-05";966.0000


Chart Design : 

Final Output:


JRXML Code : 

Jasper Design Studio Version : Professional 6.3 (If you are using CE studio code may not work)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2017-03-30T02:48:52 -->
<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="BarChartCustomization_XAxisLabels" pageWidth="800" pageHeight="842" columnWidth="760" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="9e71469d-a266-48ee-9989-9c4913b1190f">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<queryString>
<![CDATA[select * from 
(
(
SELECT 
'Store Sales' as Sales, 
date(t.the_date) as date,
SUM(sf7.Store_sales) as SalesFact
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 5
)

UNION
(
SELECT 
'Unit Sales' as Sales,
date(t.the_date) as date,
SUM(sf7.unit_sales) as SalesFact  
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 5
)
) a ORDER BY a.date]]>
</queryString>
<field name="sales" class="java.lang.String"/>
<field name="date" class="java.sql.Date"/>
<field name="salesfact" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<summary>
<band height="360" splitType="Stretch">
<stackedBarChart>
<chart evaluationTime="Report">
<reportElement x="0" y="0" width="760" height="360" uuid="f13a44b1-bb8f-49b9-8bd8-4b048ef7e26d"/>
<chartTitle/>
<chartSubtitle/>
<chartLegend/>
</chart>
<categoryDataset>
<categorySeries>
<seriesExpression><![CDATA[$F{sales}]]></seriesExpression>
<categoryExpression><![CDATA[$F{date}.toString()]]></categoryExpression>
<valueExpression><![CDATA[$F{salesfact}]]></valueExpression>
</categorySeries>
</categoryDataset>
<barPlot>
<plot labelRotation="60.0">
<seriesColor seriesOrder="0" color="#BF6B83"/>
<seriesColor seriesOrder="1" color="#129D10"/>
</plot>
<itemLabel/>
<categoryAxisFormat labelRotation="60.0">
<axisFormat/>
</categoryAxisFormat>
<valueAxisFormat>
<axisFormat/>
</valueAxisFormat>
</barPlot>
</stackedBarChart>
</band>
</summary>
</jasperReport>

Thursday 23 March 2017

TIBCO Jaspersoft Online demo server (https://mobiledemo.jaspersoft.com/jasperserver-pro/)


Are you a community customer ?
Is your 30-day trail license expired for studio and server ?
Do you want to learn or explore TIBCO jaspersoft Studio Professional features ?
Do you want to learn Enterprise server features (limited in demo as end user) ?
Did you ask your folks to explore self service BI ?
Did you ask your folks to explore self service Dashboards ?

Here you go with Online demo server. You can evaluate TIBCO jasperserver as an end user.

Copy paste or click on below URL
https://mobiledemo.jaspersoft.com/jasperserver-pro/

UserName/Password : joeuser/joeuser


Exploring Self Service BI with Ad-Hoc Views 
Sample Example : Sales Trend By State 

Connecting to this demo server in Studio : 

Exploring out of the examples : 
Viewing the same in server


This way, a new customer or migrating customer or a community folk who wants to explore jasper can dig the enterprise features.

As this server is accessible only with enduser permissions, if you want to explore security side you can refer to the documentation at http://community.jaspersoft.com/documentation?version=33331


- Sadakar Pochampalli 

Thursday 9 March 2017

MySQL workbench - table data import and export procedure

This is a redistribution of import and export features of MySQL workbench.


6.5.1 Table Data Export and Import Wizard


https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html



he wizard is accessible from the object browser's context menu by right-clicking on a table and choose either Table Data Export Wizard orTable Data Import Wizard.
Figure 6.14 Table Data Wizards: Open
Table Data Wizards: Open

Table Data Export Wizard

Export table data to either a JSON or CSV file. The following example exports the sakila.actor table to a CSV file.
Figure 6.15 Table Data Export: Source
Table Data Export: Source

Figure 6.16 Table Data Export: CSV Configuration
Table Data Export: Configuration

Figure 6.17 Table Data Export: Results
Table Data Export: Results

Table Data Import Wizard

Import table data from either a JSON or CSV file. The following example imports the sakila.actor table from a CSV file.
Figure 6.18 Table Data Import: CSV Source
Table Data Import: CSV Source

Figure 6.19 Table Data Import: Destination Table
Table Data Import: Destination Table

Figure 6.20 Table Data Import: CSV Configuration
Table Data Import: CSV Configuration

Note
The Encoding field should correspond with your CSV file.
Figure 6.21 Table Data Import: Results
Table Data Import: Results