Open your spreadsheet in Excel.
Click on the triangular Select All button in the upper left corner in your spreadsheet, or press CTRL + A.
In the Home tab, go to the Format section > Hide & Unhide > Unhide Rows.

A row can look hidden if its height is set to zero, even if you didn’t hide it manually. So even if you right-click and choose Unhide, it won’t work in this case.

Here’s how to fix this:
1. Select the rows around the missing one.
2. Go to Home > Format > Row Height (or right-click the row number and choose Row Height).
3. Set the height to 15 (Excel’s default).
4. Click OK, and the row should appear again.
If you have the Freeze Panes option on, try disabling it to see if the static state of panes is preventing the rows from completely showing up.
Filters can hide rows based on criteria, sometimes unintentionally. To remove filters:1)Go to the Data tab on the Ribbon.2) Click on “Filter” to toggle off any active filters, revealing any rows hidden by these filters.3) Removing filters can instantly bring back a significant portion of your “missing” data.

More Information:

https://xodo.com/blog/how-to-unhide-all-rows-in-excel

https://www.xelplus.com/hide-unhide-excel/

https://www.simplesheets.co/blog/how-to-unhide-all-rows-in-excel

https://blog.octanesolutions.com.au/dynamizing-dynamic-reports-hacks

How to Create a List Report using a TM1 Dynamic Report

https://www.ibm.com/docs/sl/planning-analytics/2.0.0?topic=wf-tm1rptrow

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-tm1rptview

TM1RPTVIEW defines the view displayed in a Dynamic Report.

Syntax

TM1RPTVIEW(ViewID,ZeroSuppression,TM1RPTTITLE,...)

Argument	Description	Required/Optional
ViewID	A name for the view using the format `server:cube:unique_id`.	Required
ZeroSuppression	A Boolean flag to turn on or off the zero suppression property for the view. 1 = on, 0 = off	Required
TM1RPTTITLE	For each title dimension in the Dynamic Report, include a reference to a TM1RPTTITLE function as an argument to TM1RPTVIEW.	Required
FormatRange	The formatting range for the Dynamic Report. You can use a cell reference or a named range for this parameter. When you create a Dynamic Report, a named range called TM1RPTFMTRNG is created to include all formatting range cells. You can use this named range as an argument.	Required
IDColumn	The column in the Dynamic Report that contains format IDs. You can use a cell reference or a named range for this parameter. When you create a Dynamic Report, a named range called TM1RPTFMTIDCOL is created to include all formatting range cells. You can use this named range as an argument.	Required

TM1RptRow sets the Active Form control row definition. The control row definition governs the behavior of all rows in the Active Form. This worksheet function is used to create Active Forms.

This worksheet function is valid in worksheets only.

Syntax

TM1RptRow(ReportView, Dimension, Subset, SubsetElements,
Alias, ExpandAbove,MDXStatement, Indentations, ConsolidationDrilling)

Argument	Description
ReportView	A reference to a cell that contains a TM1RptView formula.
Dimension	A dimension, specified using the format tm1_server_name:dimension_name.
Subset	A named subset. If this argument is empty, all elements of the dimension will be used.
SubsetElements	A cell range reference that specifies a list of elements to constitute a subset. When this argument is supplied, the named subset specified by the Subset argument is ignored. If this argument is empty, the elements from the subset specified by the Subset argument are used.
Alias	A string that defines the alias used for the subset. When this argument is supplied, it overrides the default alias property defined by the subset specified by the Subset argument. If this argument is empty, the alias from the subset specified by the Subset argument are used.
ExpandAbove	A Boolean flag to turn on or off the subset Expand Above property. When this argument is supplied, it overrides the default Expand Above property defined by the subset specified by the Subset argument. If the argument value is 1, consolidated elements expand upward when drilling. If the argument value is 0, consolidated elements expand downward when drilling. If this argument is empty, the Expand Above property from the subset specified by the Subset argument is used.
MDXStatement	An MDX statement that applies to the subset specified by the Subset argument. When this argument is supplied, it overrides the default MDX filter defined by the subset specified by the Subset argument. If this argument is empty or omitted, the elements from the subset specified by the Subset argument are used.
Indentations	An integer value to indicate how many indentations are applied to each level when drilling down on a consolidated element. If the argument value is 0, no auto-indentation is performed. This is an optional argument. When the value is missing, one indentation is applied to each level as you drill down on a consolidated element.
ConsolidationDrilling	A Boolean flag to turn on or off drilling on consolidated elements. When this argument value is 1, users can drill down on consolidated elements in the Active Form. When this argument value is 0, users can not drill down on consolidated elements in the Active Form. This is an optional argument. When the argument is missing, the default behavior is to allow drilling on consolidated elements.

How make the cub files smaller?

December 18, 2025 by Roger·Comments Off

Product:

Planning Analytics 2.1.14

Microsoft Windows 2022 server

Issue:

CUB file size does not shrink when we remove a dimension element, like a year from a cube. Why?

Solution:

You need to update a data value in the cube, for TM1 to recognize the data change, and then after you do a SaveDataAll the cub file is smaller.

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=functions-savedataall

You have to do a data (not metadata) change that “flags” to TM1 whether it needs to re-save a particular cube. If there has been no change flagged since the last data save, then the cube will not be saved even if you do a Save All. This is a time saving feature, since it would be pointless for TM1 to re-save each and every cube regardless of whether it had changed. After all, the save process is often the primary performance bottleneck. You don’t actually lose much by retaining the data in the .cub file; all that will happen upon startup is that the data that no longer has a valid element will fail to load.

The deletion of data via the deletion of elements is not (flagged as) an actual data change. (Were it otherwise, after each metadata change the server would need to somehow go through and see whether any populated cells had been lost, which would be a ridiculously time consuming task.) Consequently the cube would not be flagged for a save after you made your change, and thus it retained the same size. If you checked the time and date of the .cub file it would have been from the save prior to you deleting the elements.

Remember that the RAM TM1 reports to the OS as being “in use” is just the RAM it has reserved for use. TM1 doesn’t tend to give back RAM that it has allocated (again, a bias toward speed and not efficiency), so you’re unlikely to see a drop in reserved RAM, even if it is no longer used.

https://exploringtm1.com/tm1-file-extensions-understand-the-files-in-your-tm1-model/

https://blog.octanesolutions.com.au/tm1-object-extensions

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=SSD29G_2.1.0/com.ibm.swg.ba.cognos.tm1_dg_dvlpr.2.0.0.doc/c_tm1_dev_ob_app_on_svr_n5730.htm

https://exploringtm1.com/tm1-health-how-to-keep-your-tm1-server-optimized/

https://www.linkedin.com/pulse/limits-tm1-christoph-hein

netstat -ano | findstr “:53”

December 12, 2025 by Roger·Comments Off

Product:

Planning Analytics Workspace

Microsoft Windows 2022 server

Issue:

Check if port is used by docker or other process?

Solution:

On the PAW server login and start the CMD prompt;

Enter below to check if port 53 (default port for docker is used)

 netstat -ano | findstr ":53"

the ‘LISTENING’ status means it’s in use, and the PID (Process ID) shows which application, which you then check with tasklist /fi “PID eq [PID_number]” to identify the service.

Ensure that all ports in paw.ps1 file is open in the local firewall on the PAW server to ensure smooth operation.

More information:

https://learn.microsoft.com/en-us/windows-server/networking/dns/network-ports

https://simpledns.plus/kb/47-error-message-could-not-start-dns-service-on-ip-address-port-53-udp-port-is-used

https://kb.synology.com/en-global/DSM/tutorial/Whether_TCP_port_is_open_or_closed

https://www.ninjaone.com/blog/how-to-find-dns-servers-used-in-windows-11/

Error in WAproxy.log file

December 12, 2025 by Roger·Comments Off

Product:

Planning Analytics 2.1.14 workspace

Issue:

Why 404 error in the log file D:\RUNNING\PAW2114\log\wa-proxy\WAproxy4.log (the file name is increased with a number for each new log file)

You find this on a row
statusCode: 404,

Suggested solution:

If you check a few lines before the statuscode you see lines like this;

2025-12-08T13:10:12.023Z [3] – info: Incoming request: [xxx.xxx.xxx.xxx] GET /rolemgmt/v1/profiles/@me/avatar?t=17651941997 [37e3346000009af]
2025-12-08T13:10:12.029Z [3] – warn: Proxy: “GET http://user-admin:3333/rolemgmt/v1/profiles/@me/avatar?t=17651941997” : 404 (3 ms) [37e3346000009af]

Then the user that is accessing the PAW environment, have not created a “picture” of himself, and above error will be in the log file WAproxy.log as long he does not have set a “picture” for his user. Solution is for the end user to add a picture to her login profile in PAW:

Click on you name in the top right corner and select “profile and settings” from the dropdown menu.

Click on profile photo.

Click on upload photo and select a photo of you (that is saved on the computer in png format)
(or get a fun avatar from https://unsplash.com/ or https://www.pexels.com/)

Click Done.

Now you have a small picture in PAW – and there should be less error in the log file.

When in PAW open a book, you have in the URL the ID of the book, like http://servername.domain.com/?perspective=dashboard&id=237dd67d-f35-40a5-95a3-5e76c6e8289

This ID can be found in the log file WAproxy.log under your PAW installations log folder. Then you can on the line with this book ID, see who have open it.

If you open WAproxy.log file in Notepad++ and search in file for all occurrence of the text: c/v2/content/dashboard/

You will get a list of all books that have been open and by who – the login ID will be last on the line.

More Information:

404 – page or data not found

401 – unauthenticated, you have not logged in to access this page or data

200 – success – page or data loaded fine

304 – you already have the page cached in your web browser, no need to send it again

500 – A generic error message, given when an unexpected condition was encountered. Read the WAproxy file for more information and search IBM web site for an explanation.

https://www.siteground.com/kb/304-status-code/

https://en.wikipedia.org/wiki/List_of_HTTP_status_codes

https://www.canva.com/features/photo-effects/?utm_medium=partner&utm_source=pixabay&utm_campaign=canva_attribution

How get Http ARchive

December 12, 2025 by Roger·Comments Off

Product:

Planning Analytics Workspace

Microsoft Windows 2022 server

Issue:

How record web activate to send to support to troubleshoot issues in the web application.

Solution:

Use a tool to catch the traffic between the Web Browser and the server, try like https://www.telerik.com/fiddler/fiddler-classic

or more network tools like https://www.wireshark.org/download.html ….

- Wireshark: Open Wireshark, choose the network interface you want to monitor, and start the capture. You can use capture filters to narrow down the traffic you see (e.g., by IP address or port). Stop the capture and save the results in a .pcap file for later analysis.

Fiddler: This tool is excellent for capturing and inspecting both HTTP and HTTPS traffic from any browser or application. It allows you to filter, save, and replay traffic sessions.

tcpdump: A command-line utility that captures raw packets. You can specify an interface (-i) and write the output to a file (-w). For example, tcpdump -i wlan0 -w capture.pcap would capture traffic from the wlan0 interface to a file named capture.pcap

You can also use the network tool built inside the web browser, they save the collected data to a HAR file.

HAR files (“Http ARchive”) are a JSON-formatted archive file format for logging a web browser’s interaction with a site.

Typically, they are useful in troubleshooting performance problems (and errors) when using web-based software (such as PAW).

The steps vary, depending on the web browser that the user is using.

Chrome
1.    Launch Google Chrome.
2.   In Chrome, go to the page where you are experiencing trouble
3.   Select the Chrome menu (⋮) at the top-right of your browser window, then select More Tools > Developer Tools.

4. The Developer Tools will open as a docked panel at the side or bottom of Chrome. Click on the Network tab.
5. Tick/select the option Preserve log (see below):

6. The recording should autostart and you will see a red circle at the top left of the Network tab (see above).

If not, click the black circle, to start recording activity in your browser.
7. Refresh the web page that you are on

– Perform the task which is causing the issue

– The goal is to reproduce the problem you’ve been experiencing while Google Chrome is recording activity.
8. Once you have reproduced the problem (while recording), right-click within the Network tab and click Save as HAR with Content to save a copy of the activity that you recorded:

9. Save the HAR file somewhere convenient.
10. Now click the Console tab and right-click anywhere in the console log. Select the popup option “Save as…” and name the log file something sensible:

11. Close the developer panel.

12. Typically, the customer will now upload the two files (especially the HAR file) into their support case.

Firefox
1.    Launch Firefox.
2.   In Firefox, go to the page where you are experiencing trouble.
3.   Select the Firefox menu (Three horizontal parallel lines) at the top-right of your browser window, then select Developer > Network.
4.   The Developer Network Tools will open as a docked panel at the side or bottom of Firefox. Click on the Network tab.

– Before starting the capture, be sure to enable the setting: Enable persistent logs in the Toolbox Options > Common Preferences (click on gear icon on the Toolbox toolbar to open the Toolbox Options pane)

– Click on the Network tab after setting the option.
5. The recording will autostart once you start performing actions in the browser.
6. Refresh the web page that you are on

– Perform the task which is causing the issue

– The goal is to reproduce the problem you’ve been experiencing while Firefox is recording activity.

7.   Once you have reproduced the issue and you see that all of the actions have been generated in the Developer Network Panel (this should just take a few seconds), right-click anywhere under the “File” column and click on “Save All as Har”
8.    Save the HAR file somewhere convenient and close the developer panel.
9.    Typically, the customer will now upload the HAR file into their support case.Internet Explorer (IE)

We shall use Microsoft F12 Developer Tools and the Network Tool to capture the browser headers. Then export the captured traffic as HAR file (or XML if using older version of IE).

1.    Launch Microsoft Internet Explorer.
2.   Press the F12 key on your keyboard. This should open the Developer Tools panel in IE. If not, find it on the browser menu: F12 Developer Tools
3.   In the Developer Tools panel, click the Network panel and then deselect the Clear entries on navigate option. (on by default).
4.   Click the Network panel/button and then the Start Capturing button (or press Ctrl + E). Note: the icon looks like a green triangle
5.   Click the IE Refresh button. The goal is to reproduce the problem you’ve been experiencing while IE is recording activity
6.   The Developer Tool panel should now show a list of the URLs that are included in the page you have in your browser.
7.   Click the Stop button when the issue has been reproduced (or press Ctrl + E). Note: the icon looks like a red square
8.   Click the Export captured traffic icon and save the file somewhere convenient (or press Ctrl + S). Note: the icon looks like a floppy disk.
9.   Typically, the customer will now upload the HAR file into their support case.
10.   Click on the Console tab and look for any errors/warnings reported. If errors are shown, please right-click on the console errors and select Copy all or send a screenshot of them.
11.   Close the Developer Tools panel.

More Information:

https://github.com/radekstepan/fetch-fiddler

https://www.telerik.com/fiddler/usecases/https-traffic-recording

https://www.wireshark.org/docs/wsug_html_chunked/

https://httptoolkit.com/

https://help.okta.com/oag/en-us/content/topics/access-gateway/troubleshooting-with-har.htm

Apache Guacamole®

How add the TM1 name instance to the toolbar

December 5, 2025 by Roger·Comments Off

Product:

Planning Analytics 2.1.14 Tm1web

Microsoft Windows 2022 server

Issue:

In TM1WEB how see what application i have logged in to?

Solution:

Add the TM1 instance name in the toolbar with you name.

Go to Tm1 web server

Go to folder D:\Program files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration

Open file tm1web_config.xml

Change <add key=”TM1DatabaseLabel” value=”Y” />

Save the file

You do not need to restart the TM1WEB service (IBM Planning Analytics Spreadsheet Services) to see the effect.

Only logout and in to TM1WEB.

http://servername.domain.com:9510/tm1web

More Information:

<!– If “Y” or “1” is specified, then the name of the logged in Database will appear next to the logged in user name in the top right of TM1 Web. –>
<add key=”TM1DatabaseLabel” value=”Y” />

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=ctwsas-tm1databaselabel-parameter

SubsetMDXSet

November 10, 2025 by Roger·Comments Off

Product:

Planning Analytics 2.1.11
Microsoft Windows 2022 server

Issue:

How many element in a dimension subset?

Solution:

Use the function SubsetMDXSet to get the number of elements in the selection, it will also convert the MDX subset to a static subset.

SubsetMDXSet applies a specified MDX expression to a public or temporary subset.

If the passed MDX expression is valid, the specified subset is saved as a dynamic subset defined by the MDX expression.

If the passed MDX expression is an empty string, the subset is converted to a static subset that contains the elements that are in place when SubsetMDXSet is executed.

The SubsetMDXSet returns the number of elements that the subset contains.

In below example the subset becomes static as we use ” as parameter.

We can use the number of elements, to count them and add them to a new dimension, with a total element first.

DimensionElementInsertDirect( tDim, '', 'Total', 'C');
sSubset = 'temp_Account';
sMDX = '{TM1FILTERBYLEVEL(TM1SUBSETALL([Account]) , 0)}';
SubsetCreatebyMDX(sSubset, sMDX, 1 );
vNumElems = SubsetMDXSet( tDim, sSubset, '' );
i = 1;
while (i<=vNumElems);
vElem = SubsetGetElementName( tDim,sSubset, i );
DimensionElementComponentAdd( tDim, 'Total',vElem, 1 );
i = i +1;
end;

More Information:

SubsetMDXSet – IBM Documentation

Slow MDX Subsets? Best Practice Guide to Maintain Sets in TM1

Wim‘s Excel, TM1 & soccer site

User Guide > Using the Manager with TM1 > Managing MDX Library

TM1 subset trick: client-specific dynamic selections and sums with one single subset | GMC² GmbH – intelligent entscheiden

How to order/sort data in cube view used as a data source in Turbo Integrator (TI)? – Ever Analytics

Failure initializing Performance Monitor

October 24, 2025 by Roger·Comments Off

Product:
Planning Analytics 2.1.14 server
Microsoft Windows 2022 Server

Issue:

In tm1server.log file you have this error at every startup of the TM1 service:

TM1.Server Failure initializing Performance Monitor: Installed counters do not match registry definition

Solution:

Looks like the installation did not go well.

Start a CMD as a administrator on the TM1 server.

Go to folder: D:\Program files\ibm\tm1_64\bin64

Enter command:

regsvr32 TM1PerfmonDLL.dll

Click OK.

Restart the TM1 service, and check if the error is gone from the tm1server.log file. Search for TM1.Server Security Mode to find the lines.

example from the tm1server.log file;

…..TM1.Server Certificate Authority:
TM1.Server Client Export Server Certificate: 0
TM1.Server Security Mode: 5
TM1.Server Use Generic Login Connect Error:
TM1.Server Server CAM URI: http://servername:9300/p2pd/servlet/dispatch
TM1.Server Server CAM URI Retry Attempts: 3
TM1.Server CAM use SSL: 0
TM1.Server Client CAM URI: http://servername:80/ibmcognos/bi/v1/disp
TM1.Server Web CAM URI: http://servername:80/ibmcognos/bi/v1/disp… …………..
TM1.Server Client Ping CAM Passport: 900
TM1.Server Start time: Fri Oct 24 2025 02:22:44 PM
TM1.Server Failure initializing Performance Monitor: Installed counters do not match registry definition
TM1.Server The server will use Parallel Interaction.
TM1.Server Client Message Port does not accept Tm1Top connections.
TM1.Server SmartCache is disabled
TM1.Server Starting HTTP Session Timeout thread. Thread Id: 6264
TM1.Server Dynamic Client Message Port: 57066
TM1.Server MTQ is enabled with 8 threads ……

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=utility-running-tm1-perfmon

kate-agent does not respond after upgrade

October 16, 2025 by Roger·Comments Off

Product:
Planning Analytics version 2.1.14
Microsoft Windows 2022 server

Issue:
After upgrade from previous version of TM1, you do not get the PAA_Agent to work. It does not respond on port 9012.

In power shell you test with: tnc servername.domain.com -port 9012

Suggested solution:

The path to the bin folder have changed for TM1, to D:\Program Files\ibm\tm1_64\bin64

Therefor when you have updated the PAA Agent, run the following scripts from a Command Prompt.
<install_dir>\paa_agent\bin\PAAAgentSetJavaHome.bat

This should update the server.env file with the correct path to the JAVA JRE

The configuration is done in the D:\Program Files\ibm\tm1_64\paa_agent\wlp\usr\servers\kate-agent\bootstrap.properties file.

This file need to be updated manually every time you upgrade, as it is overwritten with default values.

If you have more TM1 tools installed on the server, it can point to the other folder and still work.

Then after that file is updated, you can start the PAA_Agent service.

If above does not work;

You may need to uninstall PAL totally and start over.
Stop all TM1 services.

To remove the TM1 Admin Server service, run the tm1admsd.exe -remove command.
To remove the Planning Analytics Administration agent service, run the <install_dir>\paa\bin\PAAAgentDelete.bat script.

Run Uninstall_IBM_Planning_Analytics.exe in the <install_dir>\uninstall directory.

Ensure that the PAA Agent is uninstalled from the Windows service list.

Then run the installation again of TM1/PAL from analytics-installer-4.0.27-win.exe

Create the ADMIN service again.

Update the bootstrap.properties file with SMTP information

Run the bat files;

<install_dir>\paa_agent\bin\PAAAgentSetJavaHome.bat

<install_dir>\paa_agent\bin\PAAAgentRegister.bat

Start and test again.

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=itscpa2l-registering-running-planning-analytics-administration-agent-as-windows-service

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=upal2-upgrading-planning-analytics-workspace-local-from-version-20-21

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=later-uninstalling-data-tier-components

CogKnowHow

Just another WordPress site

Category: TM1

Copy book from one application to other

How unhide rows

Syntax

Syntax

How make the cub files smaller?

netstat -ano | findstr “:53”

Error in WAproxy.log file

How get Http ARchive

How add the TM1 name instance to the toolbar

SubsetMDXSet

Failure initializing Performance Monitor

kate-agent does not respond after upgrade