Problem with OFFSET excel function when using tm1 webforms

[mihas1001]

Hi everybody!

Im trying to create a dinamic chart in tm1 webform. It is a simple histogram, that contains onley one row. Firstly i created such cart in excel with a help of OFFSET function and it works properily. I saved the this excel file and published it on the tm1 server. Then i opened this file in webform and a cant forese te chart to work dinamicly. It dosent react at all.

Did anyone ever face tuch kind of problem? I would be gratefull if anybody could help me.

Thanks anyway.

[lotsaram]

This has come up on the forum many times before. Have you tried a SEARCH before creating your post?

Likewise have you checked out which Excel functions are supported in TM1 Web and which ones aren't according to the IBM documentation?

[mihas1001]

Yes i did. But unfortunately i couldnt find solution of this cpecific case on the forum.

I would appreciate if you can give me a link to the relevant post.

[tomok]

Are you sure that dynamic charting is supported by TM1 Web? Simple charts are supported but I highly doubt that dynamic charting is. TBF, I've never tried it through.

[gtonkin]

It may be worthwhile sharing how you link your series i.e. to a named range
If the named range derived, share the formulae too as these are often OFFSET combined with COUNTA etc.
Your problem may lie in the named range and/or unsupported function within the OFFSET function.

[Mark RMBC]

Offset and CountA works ok on the web in my experience and this can be used to create dynamic charts.

You have said you are creating a 'simple' histogram,I have created in Excel simple bar charts but only ever relatively simple histograms (but maybe that is just me). How are the values for the histogram being derived? Doesn't it usually involve some sort of frequency?

So in addition to providing the named range, the offset formula etc it may be worth sharing the actual graph and any other formulas that may be being used to create any of the actual data values.

cheers, Mark

[Wim Gielis]

You can copy/paste values certain areas in the report and see if it works then, just to rule out that you use functions that are not supported.

What exact TM1 version are you using ? There have been a number of problems with charts on TM1 Web in the later Fix packs of the 10.2.2 series.

[mihas1001]

Offset and CountA works ok on the web in my experience and this can be used to create dynamic charts.

You have said you are creating a 'simple' histogram,I have created in Excel simple bar charts but only ever relatively simple histograms (but maybe that is just me). How are the values for the histogram being derived? Doesn't it usually involve some sort of frequency?

So in addition to providing the named range, the offset formula etc it may be worth sharing the actual graph and any other formulas that may be being used to create any of the actual data values.

cheers, Mark

Thanks for your answer. I attach excel file, so you can see how i created dinamic bar chart.

In fact, can you try to implement it in your tm1 webform? So we will make sure that the chart works properily.

[mihas1001]

Are you sure that dynamic charting is supported by TM1 Web? Simple charts are supported but I highly doubt that dynamic charting is. TBF, I've never tried it through.

If it is not supported it would be very strange, because OFFSET function is supported for shure.

[mihas1001]

It may be worthwhile sharing how you link your series i.e. to a named range
If the named range derived, share the formulae too as these are often OFFSET combined with COUNTA etc.
Your problem may lie in the named range and/or unsupported function within the OFFSET function.

Thanks for your answer.

My dinamic chart is extrimely simple. I dont even use COUNTA.

[gtonkin]

Thanks for the example - I tried a few things without success:
-Changed range's scope from Workbook to sheet
-Referenced Category Axis Labels using the actual cell range, no dynamic range
-Checked the option on the chart to show data in Hidden and Empty cells

I also tried creating a shadow table that returned values from the main table based on the year being within the selection range, otherwise, NA() (btw NA() is a useful way to avoid plots of zero being shown on a graph)

I added a cell reference to the values and categories ranges so that I could see what was being returned-The interesting thing here is that Excel returns the first element in the array whereas TM1 Web returns the last. That being said, changing the "last year" does change what is reported by TM1 Web i.e. the named range seems to be referenced and updated correctly.

I then added two sections, each with an index number to read the values from the array based on index e.g. INDEX(<array>,<indexno>)
This works perfectly in Excel but returns #REF in Web for all points except 1.

Lastly I added a static range and linked it to the INDEX as per paragraph above-all points other than 1 give me #REF.
I am attaching my workings for your reference and possibly other readers who can review and shed some light - at this stage I cannot test any further (work awaits) but looking like a possible bug on 10.2.2 FP4 which I am testing on.

[Mark RMBC]

Just for info, this works fine in 10.1 so looks like a bug as already suggested!

[gtonkin]

Just for info, this works fine in 10.1 so looks like a bug as already suggested!

Hi Mark, when you say "this works fine" - did you try one of the templates uploaded by the OP or myself?

[Mark RMBC]

I uploaded the one from mihas1001


See attached view from web

Histogram Web.docx

(33.57 KiB) Downloaded 364 times

cheers, Mark

[mihas1001]

Thanks for the example - I tried a few things without success:
-Changed range's scope from Workbook to sheet
-Referenced Category Axis Labels using the actual cell range, no dynamic range
-Checked the option on the chart to show data in Hidden and Empty cells

I also tried creating a shadow table that returned values from the main table based on the year being within the selection range, otherwise, NA() (btw NA() is a useful way to avoid plots of zero being shown on a graph)

I added a cell reference to the values and categories ranges so that I could see what was being returned-The interesting thing here is that Excel returns the first element in the array whereas TM1 Web returns the last. That being said, changing the "last year" does change what is reported by TM1 Web i.e. the named range seems to be referenced and updated correctly.

I then added two sections, each with an index number to read the values from the array based on index e.g. INDEX(<array>,<indexno>)
This works perfectly in Excel but returns #REF in Web for all points except 1.

Lastly I added a static range and linked it to the INDEX as per paragraph above-all points other than 1 give me #REF.
I am attaching my workings for your reference and possibly other readers who can review and shed some light - at this stage I cannot test any further (work awaits) but looking like a possible bug on 10.2.2 FP4 which I am testing on.

Tanks for trying. It really seems to be a bug in this version of TM.

[mihas1001]

I uploaded the one from mihas1001


See attached view from web

Histogram Web.docx

cheers, Mark

Thanks, Mark. Thats the real froof that making dinamic chart is supporterd in TM.

Hoping that IBM development team will fix this bug in later version.

[Wim Gielis]

" It really seems to be a bug in this version of TM."

Like I indicated in my previous post above
Last versions have these kind of bugs fixed.

[gtonkin]

Had a moment and thought I would test on PAL 2.0-looks like the issue was not noticed or fixed.
@mihas1001 - suggest you try log an incident. I will also try to raise a APAR this week.

Dynamic histogram_PAL.PNG (54.37 KiB) Viewed 9864 times

[gtonkin]

APAR Logged and confirmed by IBM:
PI77178-TM1 WEB SHEETS USING THE SUPPORTED INDEX FUNCTION ONLY RETURNTHE 1ST ELEMENT IN THE ARRAY