Barchart displaying year wise trending

Hi,

My database has different countries with various factors for different years.

I want to show that in Barchart on y-axis I want to show 2-factors and on x-axis I want to show all years for example 2008 to 2012

When I select a country it should display trending for all years...

Help me how to proceed..

Find more posts tagged with

Comments

mwilliams

Are the 2 factors in your dataSet like:

factor | value | year
factor1 | 1 | 2008
factor2 | 2 | 2008
factor1 | 2 | 2009
factor2 | 1 | 2009

or like:

year | factor1 | factor2
2008 | 1 | 2
2009 | 2 | 1

?

If the first way, you'd put value as the y-axis, year as the y-axis and factor as the y-series grouping.

If the second way, you'd make 2 series in your chart, one for factor1 and one for factor2 and the year would be on the x-axis.

Let me know if you have questions.

Eswar B

Hi Williams, Thanks for quick reply

Actually its the second way but its like this

country|year|factor1|factor2|factor3
USA |2008| abc | abc | abc
USA |2009| abc | abc | abc
USA |2010| abc | abc | abc
USA |2011| abc | abc | abc
UK |2008| abc | abc | abc
UK |2009| abc | abc | abc
UK |2010| abc | abc | abc
UK |2011| abc | abc | abc

Whenever I try to keep factors on Y-series and years on X-axis then it is repeating USA how many times its there in the database. What I want is for One USA it should show factors in years 2008, 2009 2010 2011 on x axis... I hope you understood my problem..

mwilliams

I got it. What is your BIRT version? Sorry, I forgot to ask that in my last post.

Eswar B

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80191" data-time="1311013628" data-date="18 July 2011 - 11:27 AM">
I got it. What is your BIRT version? Sorry, I forgot to ask that in my last post. </blockquote>
Thanks, 
 
My Birt version 2.6.1 
 
hope you can help me out..

mwilliams

There is a problem with what I put above for how your data is set up. Are you using SQL? If so, if you can make it look like the other way, you can use the y-series grouping as I said. With your setup, there will be extra bars and legend entries using multiple series. If you're not using SQL, let me know. I'll show you how to use a scripted dataSet to do this.

Eswar B

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80200" data-time="1311020024" data-date="18 July 2011 - 01:13 PM">
There is a problem with what I put above for how your data is set up. Are you using SQL? If so, if you can make it look like the other way, you can use the y-series grouping as I said. With your setup, there will be extra bars and legend entries using multiple series. If you're not using SQL, let me know. I'll show you how to use a scripted dataSet to do this. </blockquote>
 
I am using SQL and Iam new to this charting stuff and also to birt. 
 
I tried it in same way factors on y-series and years on x-series but the graph displayed for all the counties for each year is same it is not at all varying for each country. 
 
Can you please help me. how to proceed can you send me a sample example..

mwilliams

Here's a quick example of using a union in a SQL dataSet to get the values into the same column to use the y-series grouping. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select CLASSICMODELS.ORDERDETAILS.PRICEEACH as value,
YEAR(CLASSICMODELS.ORDERS.ORDERDATE) as OrderYear,
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER as ordernumber,
'factor1' as factor
from CLASSICMODELS.ORDERDETAILS,
CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS.ORDERS.ORDERNUMBER
Union
select CLASSICMODELS.PRODUCTS.MSRP as value,
YEAR(CLASSICMODELS.ORDERS.ORDERDATE) as OrderYear,
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER as ordernumber,
'factor2' as factor
from CLASSICMODELS.PRODUCTS,
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS
where CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS.ORDERS.ORDERNUMBER
and CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE
</pre>

Eswar B

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80202" data-time="1311022926" data-date="18 July 2011 - 02:02 PM">
Here's a quick example of using a union in a SQL dataSet to get the values into the same column to use the y-series grouping. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select CLASSICMODELS.ORDERDETAILS.PRICEEACH as value,
YEAR(CLASSICMODELS.ORDERS.ORDERDATE) as OrderYear,
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER as ordernumber,
'factor1' as factor
from CLASSICMODELS.ORDERDETAILS,
CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS.ORDERS.ORDERNUMBER
Union
select CLASSICMODELS.PRODUCTS.MSRP as value,
YEAR(CLASSICMODELS.ORDERS.ORDERDATE) as OrderYear,
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER as ordernumber,
'factor2' as factor
from CLASSICMODELS.PRODUCTS,
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS
where CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS.ORDERS.ORDERNUMBER
and CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE
</pre></blockquote>
 
Thank you very much it helped me understand what I missed. 
It worked out fine. I am sorry for late reply. 
 
 
I have one more question I am generating a report using tomcat. After a report is generated for example 
 
country name and its factors under that county are displayed when report is generated. Here when I click on that factor it should give a popup window showing the related chart. 
 
Can you help me how to proceed..

mwilliams

You could make a report for the table that shows the factors under the country name, then add a drill through hyperlink that links to a second report that contains only a chart. You'd just have parameters in the second report that you'd pass through the drill through to limit the chart report to whatever you want. In the hyperlink editor, there is an option for if you'd like the drill through in a new window, same window, etc. You'd choose for it to be in a new window. Let me know if you have questions.

Eswar B

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80348" data-time="1311196207" data-date="20 July 2011 - 02:10 PM">
You could make a report for the table that shows the factors under the country name, then add a drill through hyperlink that links to a second report that contains only a chart. You'd just have parameters in the second report that you'd pass through the drill through to limit the chart report to whatever you want. In the hyperlink editor, there is an option for if you'd like the drill through in a new window, same window, etc. You'd choose for it to be in a new window. Let me know if you have questions. </blockquote>
 
Thanks Williams, 
 
I have tried it but I did not get that right. My point of view is after running the report the result of the report is as below 
country year 
Factor1 24 button/hyperlink 2009 
factor2 30 button/hyperlink 
factor3 40 button/hyperlink 
 
In the above result if I click on the button/hyperlink it should take the value of the factor 1 and generate line chart for all the years in the report. 
 
But I did not get how to pass that value of factor to the second report. 
 
Hope you understood...

mwilliams

If you create a second report that takes certain parameters to get the necessary data, you'd choose a data element in your report to put a hyperlink on to link to this report. So, you'd choose the data element you want to link, go to the hyperlink section of the property editor, choose the drill through radio button, then find the report in the file search. Once you've done this, there is a parameter box where you can click in the first column and choose a parameter in the drill down report that you want to pass. You can then build an expression for the value that you pass. In this you can choose any column from your row data to pass. Hope this helps. Let me know if you're not understanding something.

Eswar B

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80551" data-time="1311622768" data-date="25 July 2011 - 12:39 PM">
If you create a second report that takes certain parameters to get the necessary data, you'd choose a data element in your report to put a hyperlink on to link to this report. So, you'd choose the data element you want to link, go to the hyperlink section of the property editor, choose the drill through radio button, then find the report in the file search. Once you've done this, there is a parameter box where you can click in the first column and choose a parameter in the drill down report that you want to pass. You can then build an expression for the value that you pass. In this you can choose any column from your row data to pass. Hope this helps. Let me know if you're not understanding something. </blockquote>
Hi Williams, 
 
I have tried as you said but it is giving me a blank report it is not passing any of the report value as parameter. Can you please help me with a simple example. 
 
Thanks 
Eswar B