MDX query and JSON result

[pssp25]

Hi all,

Currently I have a Cube and I can via Adhoc view throw queries and have results in tables or charts.

My question is:

What is the simplest way of thowing this MDX queries and having the result in JSON.

Kind regards,

Paulo

 

[pssp25]

Got it!
I have mdx queries and I am getting Json result.
Will post the solution soon. 

Sky is the limit.

Will post solution soon.

Kind regards,
Paulo

 

 

[simon.choi]

Hi Paulo,

What is the solution, please?

Regards,

Simon

[pssp25]

In the folder jaspersoft-proWEBINFjspmodulesolap I changed the file viewOlap.jsp to the following.

 

 

<%@ page language="java" contentType="application/json;charset=UTF-8" pageEncoding="UTF-8" trimDirectiveWhitespaces="true" %>

 

<%@ page import="com.google.gson.Gson" %>

<%@ page import="mondrian.olap.*" %>

 

<%@ page import="java.util.List" %>

<%@ page import="java.io.PrintWriter" %>

 

<%!

public static String ResultToJson(Result result) {

StringBuffer sbResult = new StringBuffer();

int axis1length = 0;

int axis2length = 0;

Gson gson = new Gson();

sbResult.append("{ "axis" : { "); // =============================== Axis

for(int i =0; i< result.getAxes().length; i++ ) {

 

List listPosition = result.getAxes().getPositions();

sbResult.append(" "axis" + i +"" : [");

 

for (int p =0; p < listPosition.size(); p++ ) {

sbResult.append("");

for (int m = 0; m < listPosition.get(p).size(); m++) {

Member member = listPosition.get(p).get(m);

sbResult.append(" { "un" :");

sbResult.append(gson.toJson(member.getUniqueName()));

sbResult.append(", "n" : ");

sbResult.append(gson.toJson(member.getName()));

sbResult.append(", "c" : ");

sbResult.append(gson.toJson(member.getCaption()));

sbResult.append(", "pun" : ");

sbResult.append(gson.toJson(member.getParentUniqueName()));

sbResult.append(" },");

// sbResult.append("n");

}

sbResult.append(",");

}

sbResult.append("],");

}

sbResult.append("} ");

sbResult.append(", "values" : ");

 

axis1length = result.getAxes()[0].getPositions().size();

axis2length = result.getAxes()[1].getPositions().size();

 

sbResult.append("["); // =============================== Values

for (int i = 0; i < axis2length; i++ ) {

sbResult.append("[");

for (int j=0; j< axis1length; j++) {

int[] cellindex = new int[] {j,i};

Cell cell = result.getCell(cellindex);

sbResult.append(gson.toJson(cell.getValue()));

sbResult.append(",");

}

sbResult.append("],");

}

sbResult.append("]");

sbResult.append("}"); // Global

String res = sbResult.toString();

res = res.replace("[]","").replace(",,",",").replace(",]","]").replace(",}","}").replace(""""," ");

return res;

}

%>

 

<%

try {

String mdx = request.getParameter("mdx");

if (mdx == null) {

if (mdx.equals("")) {

out.print("{"error":"empty mdx query"}");

return;

}

}

Connection connection = DriverManager.getConnection("Provider=mondrian;Jdbc=jdbc:mysql://localhost/reportvisiondm?user=root;password=;Catalog=file:c:/temp/ReportVisionDMSchema.xml;JdbcDrivers=com.mysql.jdbc.Driver;" , null);

Query query = connection.parseQuery("WITH SET [selectedMeasures] AS '{[Measures].[WorkItemsCount]}' SELECT NON EMPTY CrossJoin([selectedMeasures], [Department].[Department].Members) ON COLUMNS, NON EMPTY {Hierarchize({[CreatedDate.YMD].[Year].Members, Descendants([CreatedDate.YMD].[2012], [CreatedDate.YMD].[Month]), Descendants([CreatedDate.YMD].[2013], [CreatedDate.YMD].[Month]), Descendants([CreatedDate.YMD].[2011], [CreatedDate.YMD].[Month])})} ON ROWS FROM [WorkPermits]");

Result result = connection.execute(query);

out.print(ResultToJson(result));

return;

} catch (Exception ex) {

out.print(ex.getMessage());

out.print("
");

}

%>

 

Then you can query with:

http://localhost:8080/jasperserver-pro/olap/viewOlap.html?new=true&parentFlow=searchFlow&name=%2Fpublic%2FSamples%2FOLAP%2FViews%2FOLAP_View_Report1&ParentFolderUri=%2Fpublic%2FSamples%2FOLAP%2FViews&viewAsDashboardFrame=true

 

On the JSP file you can configure the datasource and the schema file.

You can pass a MDX query via parameter and you will have your result in JSON.

Let me know if it helps.

[simon.choi]

I shall give it a try and let you know.

Thanks a lot!

 

Simon

[pssp25]

Forget to inform that I am using the google Java to Json libraries.

 

So don't forget to copy:

jasperserver-proWEB-INFlib

 

gson (jar files) libraries.

 

You can find them here.

https://code.google.com/p/google-gson/downloads/list