TM1 Forum

Hi All

Client is experiencing a weird scenario. He is getting #Value in all his cells on a report. However when he opens the same report in Tm1web or PAW, it works perfectly. I did the usual checks on the report to see if there was and DBRW referencing other DBRW etc, but it is a actually a very simple report. He has manipulated some of the columns to reference other cells which weren't part of the original VIEW formula. I did a check in perspectives and it gave the same issue until I removed the VIEW formula and replaced with the Server:Cubename only. Then perspectives worked great. I uploaded and then reopened in PAfE and the problem still exists. I haven't seen this before. has anyone seen this and figured a solution. I've tested a few things
Changing to DBR, excel auto calc on and off ect. But nothing fixes it. But again works perfectly when opened in Tm1web or PAW

As you have probably noticed, #Value can literally mean anything or everything is broken.

My two "go to" steps before trouble-shooting formulas are:
1) Open Task Pane, Check server connected to then Click Refresh All icon
Sometimes your session has gone idle and this fixes it other time you are actually disconnected when all other indicators say you are not.
Clicking the Refresh All seems to do the necessary if you have a connection issue.

2) Clear the cache from Options
I find I need to do this quite often as I am often adding new metadata that only seems to become available after clearing the cache.
This seems to clear many # Values during my development.

Other issues I have had are PAfE for some reason not reading the Alias and I have had to switch to Principal Name. You can double check using a DIMIX if in doubt.

HTH

Thanks for the reply.

Sadly 1 and 2 didn't change anything.

I tried using just the Principal name. It seemed to initially look like it was working. But the moment I refreshed the workbook it all went back to #Value.

IBM helpdesk don't even seem to know what is going on

Sorry if I missed a comment but does any dbrw return a value in pax, even on a new report. If not check that all the excel addins are enabled on the users machine.

IBM cognos office reporting bi addin
IBM cognos office reporting tm1 addin
IBM framework for office

burnstripe wrote: ↑Mon Jun 21, 2021 1:01 pm Sorry if I missed a comment but does any dbrw return a value in pax, even on a new report. If not check that all the excel addins are enabled on the users machine.

IBM cognos office reporting bi addin
IBM cognos office reporting tm1 addin
IBM framework for office

Good call - I "assumed" that "He is getting #Value in all his cells on a report." meant other reports worked but one never knows.

When you hit PAfE 2.0.65 then Addins change again:

Thanks. All addins are there, other reports open with no problem. The other reports were built originally in Perspectives.

The issue seems to be with this new report the client has tried to create. I've tried all the little "tricks" that I can think of, but no luck

Nothing to do with Security?

More usual tricks - Have they tried on another machine, and possibly different credentials?

Have you tried converting each part of the DBRW to a static value using F9 on each reference? Put a DIMIX on to each member being referenced to confirm it exists etc?

Yes, we have tried on multiple machines and different logins.
We know the sheet, links and members work 100% because when the report is opened in TM1web or PAW, it is perfect.
It is only in PAfE that the #Values appear. If you select an individual cell it evaluates, but the moment you refresh the sheet, it all changes back to #Value. The report is really simple, it's not referencing any members which in turn are derived from tm1.

Is the report the only report in the book, or are there other reports/tabs? If one report is broken I have seen it result in errors retrieving data for others... If there are other reports perhaps try removing sheets to see if that makes any difference.

Another thing to try is recalc mode. The are separate recalc settings available for TM1Web, which may differ from the settings in PAX. Generally with PAX I have automatic calculation on unless I'm editing a report, and with perspectives I generally have it set to manual. Perhaps try different excel recalc settings to see if it has an impact...

How are the references used in the DBRW created. Have you tried converting the references to static values if they contain formulae. If any of the references contain a DBRW PAX may fail to generate a report because it is unable to derive what the reference is before it attempts to pull the DBRW data from the server.

I take it with the report using a View statement then the report is a slice not an active report. If it does happen to be an active report though does any of the DBRW in the active report refer to tm1rptview, if not at least one should.

Have you also checked that none of the named references in excel equal #N/A

Another thought to throw out there, are you on a cloud environment, if so does the version of PAX match the version of PAW (or within a few iterations). It could be your pax version is outside the capability window with PAW

What version of PAX are you on? I think there was a bug fixed in version 63.

Also, why not attach the actual spreadsheet?

Maren