search

objectscript / LightPivotTable

Lightweight pivot table representation for MDX2JSON source for InterSystems Cache
MIT License
3 stars 4 forks source link

Update Rendering Mechanism #12

Open nikitaeverywhere opened 6 years ago

nikitaeverywhere commented 6 years ago

LPT's rendering mechanism must be updated to move further. These things need to be done:

nikitaeverywhere commented 6 years ago

Working on test pivot:

http://146.185.143.59:57772/csp/samples/_DeepSee.UI.Analyzer.zen?$NAMESPACE=SAMPLES&PIVOT=LPT%2FFormattingTest.pivot

2018-01-23_121305

VS

2018-01-23_121343

The formatting works well on cells but not on headers.

nikitaeverywhere commented 6 years ago

@ohoferek, a problem with rows/cols formatting is kind of tough problem I faced a long time ago, didn't solve it completely and temporary made a useable "workaround" for half of the use cases. This issue may last for a while until we figure out how to deal with it.

I am pretty sure this can't work as expected without modifying MDX2JSON. I urge @eduard93 about MDX2JSON, please help :)

The problem: I am unable to merge /mdx endpoint results with /DataSource endpoint results to render the correct view in terms of rows/cols formatting: there's insufficient data to do it.

Here's detailed explanation:

Look at this test pivot. The goal is to make LPT to pick the headers/rows/cols formatting correctly, through it seems like this is not possible yet. Notice these row headers:

2018-01-28_213235

I've made them different colors to demonstrate the problem. Now, open this pivot in DSW while Developer Tools opened. Notice the last two requests to /DataSource and /mdx endpoints.

The task is to take the /mdx result and style it using the data from /DataSource (which returns a pivot definition). In other words, merge results from /mdx and /DataSource into a single data structure. And the problem is to find the correspondence of how to apply the right definition to the right (tuple, header) dimension and its children.

So, in /mdx, under the Cols[1].tuples[3].children (that dimension highlighted on the screenshot) you will find these info about rows:

2018-01-28_214004

But under the /DataSource, at rowLevels[3].childLevels the structure is in slightly different format (it joins to a single dimension):

2018-01-28_214154

And this difference has no keys/id/whatever to merge it together (relate them somehow). If you think it has, think about label dimensions or dimensions with no data (which are not even present in /mdx response): it seems like there's no way to merge them yet.

@eduard93, can MDX2JSON somehow put identifiers on these dimensions to make front end able to figure out how to merge them? Yet, I have seen that MDX2JSON returns some "info" (namely, cells formatting style) right in the /mdx tuples response. But it never returned headers info there, I am wondering whether it is even possible. Still, there are a lot of other info to return. Please, let me know what do you think. Thank you!

eduard93 commented 6 years ago

MDX:

{"MDX":"SELECT NON EMPTY {[DateOfSale].[Actual].[YearSold].&[2013],%LABEL([DateOfSale].[Actual].[YearSold].&[2014],\"\",\"\",,\"background:rgb(255, 128, 255);\",\"color:rgb(0, 0, 0);background:rgb(240, 0, 240);font-family:\"\"courier new\"\";font-size:15pt;\"),NONEMPTYCROSSJOIN([DateOfSale].[Actual].[YearSold].&[2015],{[DateOfSale].[Actual].[MonthSold].&[201505],%LABEL([DateOfSale].[Actual].[MonthSold].&[201507],\"\",\"\",,,\"background:rgb(240, 0, 0);\")}),NONEMPTYCROSSJOIN([DateOfSale].[Actual].[YearSold].&[2016],{%LABEL([DateOfSale].[Actual].[MonthSold].&[201601],\"\",\"\",,\"background:rgb(255, 255, 128);\",\"background:rgb(255, 255, 208);\"),[DateOfSale].[Actual].[MonthSold].Members,%LABEL([DateOfSale].[Actual].[MonthSold].&[201608],\"\",\"\",,\"background:rgb(240, 0, 0);\",\"background:rgb(240, 0, 0);\"),%LABEL([DateOfSale].[Actual].[MonthSold].&[201607],\"\",\"\",,\"background:rgb(255, 255, 208);\",\"background:rgb(240, 240, 0);\")})} ON 0,NON EMPTY HEAD({%LABEL([Product].[P1].[Product Name].&[SKU-192],\"\",\"\",,\"color:rgb(0, 0, 0);background:rgb(255, 128, 128);\",\"font-weight: bold;background:rgb(240, 0, 0);\"),%LABEL([Product].[P1].[Product Name].&[SKU-709],\"\",\"\",,\"color:rgb(0, 0, 0);background:rgb(255, 255, 128);\",\"color:rgb(0, 0, 0);background:rgb(240, 240, 0);font-family:verdana;\"),%LABEL([Product].[P1].[Product Name].&[SKU-451],\"\",\"\",,\"font-weight: bold;font-style: italic;color:rgb(0, 0, 0);background:rgb(128, 255, 128);\",\"color:rgb(0, 0, 0);background:rgb(0, 240, 0);\"),%LABEL(NONEMPTYCROSSJOIN([Product].[P1].[Product Category].&[Pasta],{[Product].[P1].[Product Name].&[SKU-287],%LABEL([Product].[P1].[Product Name].&[SKU-296],\"\",\"\",,\"font-weight: bold;color:rgb(0, 0, 240);background:rgb(176, 255, 255);\",\"color:rgb(0, 0, 0);background:rgb(0, 240, 240);\"),[Product].[P1].[Product Name].&[SKU-928],%LABEL([Product].[P1].[Product Name].Members,\"\",\"\",,,\"background:rgb(255, 128, 128);\"),%LABEL([Product].[P1].[Product Name].Members,\"\",\"\",,,\"background:rgb(255, 255, 128);\"),%LABEL([Product].[P1].[Product Name].Members,\"\",\"\",,,\"background:rgb(128, 255, 128);\")}),\"\",\"\",,,\"font-weight: bold;background:rgb(128, 128, 255);\")},2000) ON 1 FROM [HOLEFOODS]"}

Added debugStyle property to each cell (on a test server). @ZitRos, does it contain relevant information (note that it's not equal to style property)?

image

nikitaeverywhere commented 6 years ago

@eduard93, thank you! It seems it does. Styles of rows/cols/headers are pretty much the only thing I tried to extract from the pivot definition's rowLevels/colLevels for now. But there may be more properties we will need in the future.

Then, can we name that property as "headerStyle" (former debugStyle) and "cellStyle" (former style)? Do not remove "style" prop just yet. Property "info", which copies "style" pretty much, can be dropped, LPT don't use it (and DSW either).

eduard93 commented 6 years ago

so:

@gnibeda? Ideas? Comments?

nikitaeverywhere commented 6 years ago

Yep, just do not remove style prop itself, as at least LPT depend on it. Please, temporary make style and cellStyle props which are identical. (cellStyle just preserves better naming convention)

I'll migrate to cellStyle soon.

nikitaeverywhere commented 6 years ago

@eduard93, we discussed this with Anton @gnibeda,

style → style info → info (turns out our projects depend on this two, we'll refactor them soon) debugStyle → headerStyle +cellStyle (a copy of style to preserve better naming convention, as it means exactly cells styling)

So, it's just about adding headerStyle and cellStyle props.

eduard93 commented 6 years ago

Done, see https://github.com/intersystems-ru/Cache-MDX2JSON/issues/67