Problem with OFFSET excel function when using tm1 webforms
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Problem with OFFSET excel function when using tm1 webforms
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.
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.
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Problem with OFFSET excel function when using tm1 webforms
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?
Likewise have you checked out which Excel functions are supported in TM1 Web and which ones aren't according to the IBM documentation?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
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.
I would appreciate if you can give me a link to the relevant post.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
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
- MVP
- Posts: 1201
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
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.
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.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Problem with OFFSET excel function when using tm1 webforms
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
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
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
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.
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
Thanks for your answer. I attach excel file, so you can see how i created dinamic bar chart.Mark RMBC wrote: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
In fact, can you try to implement it in your tm1 webform? So we will make sure that the chart works properily.
- Attachments
-
- dinamic histogram.xlsx
- (11.15 KiB) Downloaded 369 times
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
If it is not supported it would be very strange, because OFFSET function is supported for shure.tomok wrote: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.
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
Thanks for your answer.gtonkin wrote: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.
My dinamic chart is extrimely simple. I dont even use COUNTA.
- Attachments
-
- dinamic histogram.xlsx
- (11.15 KiB) Downloaded 333 times
- gtonkin
- MVP
- Posts: 1201
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
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.
-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.
- Attachments
-
- Dynamic histogram.xlsx
- George's workings
- (13.64 KiB) Downloaded 354 times
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Problem with OFFSET excel function when using tm1 webforms
Just for info, this works fine in 10.1 so looks like a bug as already suggested!
- gtonkin
- MVP
- Posts: 1201
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
Hi Mark, when you say "this works fine" - did you try one of the templates uploaded by the OP or myself?Mark RMBC wrote:Just for info, this works fine in 10.1 so looks like a bug as already suggested!
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Problem with OFFSET excel function when using tm1 webforms
I uploaded the one from mihas1001
See attached view from web
cheers, Mark
See attached view from web
cheers, Mark
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
Tanks for trying. It really seems to be a bug in this version of TM.gtonkin wrote: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.
-
- Posts: 7
- Joined: Tue Feb 07, 2017 12:15 pm
- OLAP Product: Ibm cognos tm1
- Version: 10.2.2.
- Excel Version: 2010
Re: Problem with OFFSET excel function when using tm1 webforms
Thanks, Mark. Thats the real froof that making dinamic chart is supporterd in TM.Mark RMBC wrote:I uploaded the one from mihas1001
See attached view from web
cheers, Mark
Hoping that IBM development team will fix this bug in later version.
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
" 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.
Like I indicated in my previous post above
Last versions have these kind of bugs fixed.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- gtonkin
- MVP
- Posts: 1201
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
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.
@mihas1001 - suggest you try log an incident. I will also try to raise a APAR this week.
- gtonkin
- MVP
- Posts: 1201
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Problem with OFFSET excel function when using tm1 webforms
APAR Logged and confirmed by IBM:
PI77178-TM1 WEB SHEETS USING THE SUPPORTED INDEX FUNCTION ONLY RETURNTHE 1ST ELEMENT IN THE ARRAY
PI77178-TM1 WEB SHEETS USING THE SUPPORTED INDEX FUNCTION ONLY RETURNTHE 1ST ELEMENT IN THE ARRAY