Tuesday, June 10, 2008

CalculatedMeasures in Flex OLAP

I wrote a small sample which shows how a Custom Aggregator can be written and used to simulate a Calculated measure in Flex OLAP. Of-course, this solution may not work for all requirements but can be used atleast in some scenarios. The source is here.

35 comments:

bkelley said...

Hi, I am new to Flex; Do you know if there is a way to configure AdvancedDataGrid so that when someone opens a new folder, the previously open folders close? I have a complex datagrid, and I would like users to be able to just have one major category folder open at a time? Thoughts? thanks. Brian

Sreenivas said...

You need to make sure that only the latest opened node remains in the ADG.dataProvider.openNodes property. I would try listening to the itemOpening or itemOpen event and set openNodes to empty.

JT_008 said...

Hi Sreenivas,

I have an AdvancedDataGrid that gets data from an an hierachical XML.


<:AdvancedDataGrid id="MyADG" height="100%" sortableColumns =
"false" source="{new HierarchicalData(MyXML)}>


MyXML is hierarchical (location->department->sub department->revenue).
I am trying to create summary rows (for location, dept, sub dept) and
all the examples I see (including your blog examples) talk about getting Flat data, creating
grouping collection, group fields and then summary fields.


Since my data is already hierarchical and grouped , do I still have to
group them to create summary fields? I guess the real question is -
Does Hierarchical data structure has any advantages when compared to
Flat structure, when it comes to creating summary fields? I want to
avoid grouping if it is not needed.


Is there an example that uses hierarchical data and creates summary
fields?


Thanks a bunch!

Sreenivas said...

Unfortunately summary for hierarchical data is not supported out of the box. The reason being the following.

When users supply flat data in a Collection and perform grouping a new set of Collections are created for each parent and object references copied over to the new Collections.

Due to this, Flex now has access to a new Collection which can be modified without affecting the source collection. The summaries are computed and added to these collections.

For any other form of data (read hierarchical) it is difficult to achieve this without creating duplicate collections at some point of time.

You may want to tweak/combine code from HierarchicalCollectionView and GroupingCollection and try to achive this if you have time to spare for this activity.

JT_008 said...

thanks!

Rahul Singh said...

Hi Sreenivas,
Is there a way to take this further and link data in an OLAP grid directly to a Chart?

Rahul

Sreenivas said...

Take a look at http://flexmadeeasy.blogspot.com. This has a OLAPChart component within the PivotComponent.

Rahul Singh said...

Hi Sreenivas,
I have two questions on sorting data/rows in an OLAP Grid:

1) How do we sort rows or columns by dimension (in somce cases the sorting will be alphabetical, in others numeric, in yet others, using some custom function)

2) How do we sort rows and columns based on the *measures* -- for example, we'd like to present rows in an OLAP grid in the decrasing order of product sales (you have products in rows, and assume you only have one column called "Sales")?

Sreenivas said...

1. You can sort the input to the query in any way you like. The result would be returned in the same order.

2. As of now this is not supported.

Mark said...

I have an OLAPDataGrid set up to show measures in different colors depending on value, but what I really want to show is just an icon (consumer reports style) indicating rank, with a mouseover which would pop up the detail. Do you know if this is possible using OLAP?

Sreenivas said...

You can use OLAPDataGrid.itemRendererProvider and OLAPDataGridItemRendererProvider to provide custom itemRenderers to for the cells.

I think that should help you achieve what you want.

idleWeed said...

Hi Sreenivas,
I followed what you suggested above, but I can only get an entire row or column to change to the appropriate icon. I'm having trouble figuring out how to get each individual cell to render an icon based on it's value. (1,2 or 3). I look at the object passed to the renderer but cannot see where the cell value is. I'm coming up empty for answers everywhere I have looked. Any help would be greatly appreciated!

Sreenivas said...

Sorry I had been busy at work hence didn't get time to update this one.

You can take a look at the listData sent to the renderer. The label field of this struct will give you the value which would be displayed in the cell.

shawqi said...

Hi ,
I have created an olap Datagrid in Adobe flex,
my question is if it possiple to display non-numaric (String)data in the olap Datagrid ,
I tried to investigate the custom Aggregator (by implementing IOLAPCustomAggregator ), which has the defult numeric aggtregation functions, but could not find a way to display a string.
also could I do this by the Item renderers ?

Sreenivas said...

As of now the OLAP implementation supports only Number.

I have changes with me which will allow display of strings as well. But that requires copying the files over the source folder of datavisualization and rebuild the swc and using it. Let me know if you are interested in doing it !

sudhakar said...

Sreenivas,
I have a requirement to show data in datagrid and drill down/drill up in from a particular cell in table based on data and further drill down is also possible.I have such drill down/up till 5 levels.
Code example would be helpful to me.
thanx

Sreenivas said...

Are you talking about drill down in ODG or ADG ?

Drill down is not supported in ODG. By using HierarchicalData you can do any drill down in ADG.

sudhakar said...

hi Sreenivas,
thanx for the reply .i am talking about ADG.and i am using HierarchicalData .
I have some issues with that namely
1.Drill down always happens from first column ,i have a requriment that drill down can happen on any cell in the row based on data and result of drill down can either be simple row with data or table .
2.I have drill down till four levels.

Sreenivas said...

Do you have any sample which shows how the drill down would look like when two cells are opened on the same row ? (I am unable to visualize it)

sudhakar said...

Hi i am attaching the sample screen.

Docid DocTemp Docstatus Action
IL CR 0 Doc(s)
Archived
IL CR 1 Doc(s)
Archived
IL CR 2 Doc(s)
Archived
IL CR 3 Doc(s)
Archived



When we clicks on third column (where it says # docs archived) it should expand the table with as many number of rows(say 3). And further drill down is also possible on data is also possible.

sudhakar said...

i have sample word document with screen.I am not sure how to attach it.let me know how do u it.

Sreenivas said...

Send to me at sreenivas.ramaswamy@gmail.com.

Naazim said...

Hi Sreenivas,

I have built an OLAP app that builds the cube and query based on the set of fields that user selects. It allows users to filter values from the currently displayed attributes. I have implemented it in such a way that, when the user selects specific members, I add only those members into the olap set when building the query, and not using slicer axis (because these are part of the column/row attributes).

Now the issue is that, when user selects a member of a column-attribute and if there is no data against a selected row member, "total" ("All") for that row shows up as if no filter is applied instead of showing 0. I see that the default member is used for aggregation here, but how do I prevent that?

If I can send you a screenshot of the grid, it might make a lot more sense. I would really appreciate any advice you have on this.

Thank you,
Naazim

Jai said...

Hi Sreenivas,
As idleweed said I can only get an entire row or column to change to the appropriate icon. I'm having trouble figuring out how to get each individual cell to render an icon based on it's value. (1,2 or 3). I look at the object passed to the renderer but cannot see where the cell value is.

I had searched for listdata but with no success. I had sent you the files i worked with to your email. can you please give us a sample code.

Thanks
Jaibabu

Sumeet Mahajan said...

Hi Guys.. I am new to this blog. However got a great insight to OLAP grid after reading through this blog. Now after trying my hands on this for few days, I have come across following issues:

1. decimals get truncated if the decimal value is "0". For example "49.0" is displayed as "49". Is there any way I can fix this issue?

2. Cannot sort by clicking on the Column header. Any solutions on this ??

Sreenivas said...

I have posted a sample to show coloring cells based on the value here

Sorting is not supported in OLAPDataGrid.

While converting the values to a string the values are getting truncated. You can specify a formatter and see if the problem gets fixed.

Sreenivas said...

Naazim: I think this was a bug in OLAP. It should be fixed in 3.3 release. Please check.

decosta said...

Thanks for sharing nice article on Flex OLAP ". I have also found one website which offier similar services "flex development,Rich Internet Application Development Services".

crzymnmchl said...

Hi Sreenivas, do you have any connection with the awesome PivotComponent? It is rather unusable as the grid does not scroll vertically after rows are added. This is such a good example of Flex potential in the corporate world I am searching everywhere to see if it has been fixed. TIA,

Mic

crzymnmchl said...

re:PivotComponent - dug through source and fixed it :-)

Mic.

Godzhesas said...

If you are interested in flex olap pivot table component, take a look at our updated release at Flexmonster

dam547 said...

hi, how can sum the member (all)??
Example:

measure 1|measure 2|measure3
(all)5 (all) 5 10
3 2 5
2 3 5

the result of measure3 is result of measure1 + measure2.

it is possible??

thanks!

dam547 said...

hi, the comment top is wrong..

i need sum two member (all), it is possible??

thanks!!

Sreenivas said...

You can implement a custom IOLAPCustomAggregator which computes the sum and use this as the aggregator.

Felipe Damiani said...

thanks!