MDX query and JSON result

1

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's picture
-3
Joined: Feb 28 2013 - 6:58am
Last seen: 5 years 11 months ago

1 Answer:

1

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

 

 

pssp25's picture
-3
Joined: Feb 28 2013 - 6:58am
Last seen: 5 years 11 months ago

Hi Paulo,
What is the solution, please?
Regards,
Simon

simon.choi - 6 years 1 week ago

In the folder \jaspersoft-pro\WEBINF\jsp\modules\olap\ 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<Position> listPosition = result.getAxes()[i].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("<br/>");
}
%>

Then you can query with:
http://localhost:8080/jasperserver-pro/olap/viewOlap.html?new=true&paren...

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.

pssp25 - 6 years 1 week ago

I shall give it a try and let you know.
Thanks a lot!

Simon

simon.choi - 6 years 1 week ago
show 1 more...

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

So don't forget to copy:
jasperserver-pro\WEB-INF\lib

gson (jar files) libraries.

You can find them here.
https://code.google.com/p/google-gson/downloads/list

pssp25 - 6 years 1 week ago
Feedback