nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to display Count of User Account created Grouped by month and Year ?

sunnymishra

Hi, 
My SQL query is below:
<pre class="_prettyXprint">
select user_name, account_created_date from Users</pre>
Using this, I need to create a Bar chart which shows count of "User Account created" based on Month and Year.
 
Month is the X-axis and User accounts Count is the Y-axis. This means, if I select year 2013, then the Bar chart should show me Count of User account created from Jan to Dec for only 2013. And if I choose 2012, then Chart should show me Count of User account create for Jav - Dec for only 2013.
Note: account_created_Date returned from sql query is normal datetime format.
 
Please let me know how to achieve this in Birt ?

Find more posts tagged with

Comments

pricher

Hi,
 
In the Chart's dialog Select Data tab, drag and drop "account_created_date" in the Category (X) Axis field (where it shows ORDERDATE in the attached screenshot). Then select the Sorting and Grouping button to the right of that field, and select grouping Unit = Months and Aggregate Expression = Count. Then, drag and drop user_name to the field below Series1 in the (Y) Axis area.
 
Hope this helps,
 
P.

sunnymishra

Hi,
Seems like what you suggested is what I am already doing. With your solution, the bar chart will show 1 bar for each month. The problem is that my database contains data for 5 years which is 60 months. Imagine how will the chart look with all the 60 bars (for total 60 months for 5 years) on a single chart. Therefore I wondered if there could be a way to show only 1 year's data on the chart and provide facility to change the year and see only that particular year's data. Thanks

pricher

Hi,
 
Then what you need is to use a parameter in your query to filter on a year. The value of the query parameter will come from a report parameter passed when you run the report.
 
Attached example shows a simple example with report parameter pYear used in the query to filter orders on year(orderdate).
 
Good luck!
 
P.