Get Data variation of sonar to create custom excel report

If you are using sonar for your projects and want to generate a excel report.
then you can complete this task by two way,
first one by using web API provided by sonar team
or do R & D with sonar DB(Not a standard way as mention on sonar group dashboard)

First way-
To go with web Api you can use sonar web client,
after that you can use below code to call web service for desired data.


 String url = "http://localhost:9000/";(url of Sonar)
 String login = "root";(Database user name)
 String password = "root";(Database password)
 Sonar sonar = new Sonar(new HttpClient4Connector(new Host(url, login,password)));
 Resource soanrExample= sonar.find(ResourceQuery
            .createForMetrics("projectKey", "statements"));

projectKey=You can get this from projects table or from your pom its combination of
                     <groupId>:<artifactId>

Measure statements = soanrExample.getMeasure("statements");
    System.out.println("statements : " + statements.getMetricKey()
            + " === " + statements.getFormattedValue());
like "statments" you can also pass "test" "coverage",minor_violations etc

Second Way-

As I am facing issue with variations I am not able to get variation data through web API(which shows in bracket 73 statements(+4))

So for this I did some work on DB part.
These are some steps which I followed to get variation data

Step1-
Get Project key with the help of WEB API(or through DB from projects table)

Step2-
Get project_id from projects table with the help of project key

Step3-
Get period1_param,period2_param etc from snapshots table  with the help of project Id,
from here you can get variation period like 15 days or 30 days,Like this

       if(rs.getString("period1_param")!=null){
  if(rs.getString("period1_param").equalsIgnoreCase("7")){
VARIATIONCOUNT7DAYS="1";
 }
 if (rs.getString("period1_param").equalsIgnoreCase("30")) {
VARIATIONCOUNT30DAYS="1";
 }
  }

Step4-
Use this query to generate data per project

select distinct proj.name NAME_OF_PROJ,metric.name metric_name, metric.description Description, projdesc.value value,projdesc.variation_value_1,projdesc.variation_value_2,projdesc.variation_value_3,projdesc.variation_value_4,projdesc.variation_value_5 ,snap.created_at CREATED_DATE from projects proj inner join snapshots snap on snap.project_id=proj.id inner join " + "(select max(snap2.id) as id from snapshots snap2 where snap2.project_id  in ("+projectId+") GROUP BY snap2.project_id ) as Lookup on Lookup.id=snap.id inner join project_measures projdesc on projdesc.snapshot_id=snap.id inner join metrics metric on  projdesc.metric_id =metric.id where  metric.id between '1' and "+metricid_Count+'

This query gives you all required data with the variation value,
You can put additional logic  to get compare variation for different period,

Hope this help any one.
Let me know if I miss anything