Essential SQL Queries for Managing the Spotfire Repository and Usage

This post collects practical SQL queries for TIBCO Spotfire Server administrators to analyze library content, usage, and users directly from the repository and Action Log databases.

Environment and schema assumptions:

• Spotfire Server repository tables: LIB_ITEMS, LIB_ITEM_TYPES, USERS, GROUPS, GROUP_MEMBERS, LIB_ACCESS
• Optional Action Log database with ACTIONLOG table (enabled via Spotfire Server configuration)
• Standard columns like CONTENT_SIZE, ACCESSED, LAST_LOGIN available in your version
• SQL Server syntax used (adjust functions for Oracle or PostgreSQL)

Always test queries in non-production environment first and adapt to your exact schema and Spotfire version.

1. Library Item Size (Largest Items)

This query lists all library items with their size in MB, sorted by size so you can quickly identify the largest analyses, SBDF files, and other objects.

SELECT
    li.ITEM_ID,
    li.TITLE,
    lit.DISPLAY_NAME              AS ITEM_TYPE,
    li.CONTENT_SIZE / (1024*1024.0) AS SIZE_MB,
    u1.DISPLAY_NAME               AS CREATED_BY,
    li.CREATED,
    u2.DISPLAY_NAME               AS MODIFIED_BY,
    li.MODIFIED,
    li.ACCESSED,
    parent.TITLE                  AS PARENT_FOLDER
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
  ON li.ITEM_TYPE = lit.TYPE_ID
LEFT JOIN LIB_ITEMS parent
  ON li.PARENT_ID = parent.ITEM_ID
LEFT JOIN USERS u1
  ON li.CREATED_BY = u1.USER_ID
LEFT JOIN USERS u2
  ON li.MODIFIED_BY = u2.USER_ID
ORDER BY li.CONTENT_SIZE DESC;

Use this to drive cleanup of large, rarely used library items such as old analyses and obsolete SBDF files.

2. Library / Folder Size

This query aggregates CONTENT_SIZE at the folder level, giving you a view of which folders consume the most space.

SELECT
    li.PARENT_ID                        AS FOLDER_ITEM_ID,
    parent.TITLE                        AS FOLDER_TITLE,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS FOLDER_SIZE_MB,
    parent.CREATED,
    parent.MODIFIED,
    parent.ACCESSED
FROM LIB_ITEMS li
JOIN LIB_ITEMS parent
  ON li.PARENT_ID = parent.ITEM_ID
GROUP BY
    li.PARENT_ID,
    parent.TITLE,
    parent.CREATED,
    parent.MODIFIED,
    parent.ACCESSED
ORDER BY FOLDER_SIZE_MB DESC;

Run this periodically to identify candidate folders for archive or refactoring.

3. Report Utilization (Recently Accessed Reports)

The next query lists analyses (DXP files) that have been accessed in the last 30 days, using the ACCESSED timestamp in LIB_ITEMS.

SELECT
    li.ITEM_ID,
    li.TITLE,
    li.ACCESSED
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
  ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX = 'dxp'
  AND li.ACCESSED >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY li.ACCESSED DESC;

You can adjust the DATEADD window to whatever "recently used" means in your environment.

4. Idle Reports (Candidates for Archive)

To find analyses that have not been accessed in the last 90 days (or at all), use this query.

SELECT
    li.ITEM_ID,
    li.TITLE,
    li.ACCESSED,
    li.MODIFIED
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
  ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX = 'dxp'
  AND (
        li.ACCESSED IS NULL
        OR li.ACCESSED < DATEADD(DAY, -90, GETUTCDATE())
      )
ORDER BY li.ACCESSED,
         li.MODIFIED;

This is a good starting point for a "report retirement" process in collaboration with business owners.

5. Groups and Their Members

The next query gives a simple view of which users belong to which Spotfire groups, based on the GROUPS, GROUP_MEMBERS, and USERS tables.

SELECT
    g.GROUP_NAME,
    u.USER_NAME,
    u.DISPLAY_NAME
FROM GROUPS g
JOIN GROUP_MEMBERS gm
  ON g.GROUP_ID = gm.GROUP_ID
JOIN USERS u
  ON gm.USER_ID = u.USER_ID
ORDER BY
    g.GROUP_NAME,
    u.USER_NAME;

Use this output to audit access models and compare against your identity provider structure.

6. Access Groups Attached to Folders and Items

To see which groups have permissions on which folders and items, query LIB_ACCESS and join to LIB_ITEMS and GROUPS.

SELECT
    parent.TITLE        AS FOLDER_TITLE,
    li.TITLE            AS ITEM_TITLE,
    g.GROUP_NAME,
    a.PERMISSION        AS PERMISSION_MASK
FROM LIB_ACCESS a
JOIN LIB_ITEMS li
  ON a.ITEM_ID = li.ITEM_ID
LEFT JOIN LIB_ITEMS parent
  ON li.PARENT_ID = parent.ITEM_ID
JOIN GROUPS g
  ON a.GROUP_ID = g.GROUP_ID
ORDER BY
    parent.TITLE,
    li.TITLE,
    g.GROUP_NAME;

If your schema has a dedicated "folder" flag or type, add a filter to focus on folders only.

7. Usage Tracking with Action Logs

Once the Action Log feature is enabled, the ACTIONLOG table becomes your main source for high-fidelity usage tracking.

Daily usage summary by category and action:

SELECT
    CAST(LOGGED_TIME AS date) AS LOG_DATE,
    LOG_CATEGORY,
    LOG_ACTION,
    COUNT(*)                   AS EVENT_COUNT
FROM ACTIONLOG
WHERE LOG_CATEGORY IN ('auth', 'library')
GROUP BY
    CAST(LOGGED_TIME AS date),
    LOG_CATEGORY,
    LOG_ACTION
ORDER BY
    LOG_DATE,
    LOG_CATEGORY,
    LOG_ACTION;

This gives you a quick overview of logins, report opens, and other key activities across time.

8. Report Open Events (Action Log Detail)

To analyze which reports are opened most frequently, filter the ACTIONLOG entries on library-related actions.

SELECT
    a.OBJECT_NAME      AS REPORT_NAME,
    COUNT(*)           AS OPEN_COUNT
FROM ACTIONLOG a
WHERE a.LOG_CATEGORY = 'library'
  AND a.LOG_ACTION   = 'open'
GROUP BY
    a.OBJECT_NAME
ORDER BY
    OPEN_COUNT DESC;

Column names and LOG_ACTION values can differ by version, so check your own table definition first.

9. Unique Users Over a Period

The following query returns the number of distinct users who logged in during the last 90 days, based on Action Log authentication events.

SELECT
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS_90D
FROM ACTIONLOG
WHERE LOG_CATEGORY = 'auth'
  AND LOG_ACTION   = 'login'
  AND LOGGED_TIME >= DATEADD(DAY, -90, GETUTCDATE());

You can change the 90-day window or group by date to build adoption dashboards in Spotfire itself.

10. Idle Users (No Recent Logins)

If your USERS table maintains a LAST_LOGIN column, you can identify enabled users who have not logged in recently.

SELECT
    USER_NAME,
    DISPLAY_NAME,
    LAST_LOGIN
FROM USERS
WHERE ENABLED = 1
  AND (
        LAST_LOGIN IS NULL
        OR LAST_LOGIN < DATEADD(DAY, -90, GETUTCDATE())
      )
ORDER BY LAST_LOGIN;

Where LAST_LOGIN is not reliable, compute last login from the Action Log by taking the max LOGGED_TIME per user for auth/login events.

11. Duplicate Content Detection (Same Title in Different Folders)

Identify potential duplicate or redundant analyses across the library to help with content consolidation and cleanup.

SELECT
    li.TITLE,
    COUNT(*) AS COPIES,
    STRING_AGG(parent.TITLE, ', ') AS FOLDER_LOCATIONS,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB
FROM LIB_ITEMS li
LEFT JOIN LIB_ITEMS parent
  ON li.PARENT_ID = parent.ITEM_ID
JOIN LIB_ITEM_TYPES lit
  ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX = 'dxp'
GROUP BY li.TITLE
HAVING COUNT(*) > 1
ORDER BY COPIES DESC;

Note: This example uses STRING_AGG for SQL Server (2017+). For Oracle, use LISTAGG instead.

12. Information Links and Data Source Dependencies

Track which analyses depend on specific data sources to understand the impact of data source changes.

SELECT
    ds.TITLE                    AS DATA_SOURCE_NAME,
    ds.CREATED,
    ds.MODIFIED,
    COUNT(DISTINCT li.ITEM_ID)  AS REFERENCED_COUNT
FROM LIB_ITEMS ds
LEFT JOIN LIB_DATA ld
  ON ds.ITEM_ID = ld.ITEM_ID
LEFT JOIN LIB_ITEMS li
  ON li.CREATED_BY = ds.CREATED_BY
  OR li.MODIFIED_BY = ds.MODIFIED_BY
WHERE ds.TITLE LIKE '%DataSource%'
  OR ds.TITLE LIKE '%Connection%'
GROUP BY ds.ITEM_ID, ds.TITLE, ds.CREATED, ds.MODIFIED
ORDER BY REFERENCED_COUNT DESC;

This is useful for impact analysis when planning data source migrations or decommissioning. Modify the WHERE clause to match your data source naming conventions.

13. Storage Trend Analysis (Items By Creation Date)

Analyze library growth patterns to understand when content was created and identify opportunities for archival based on age.

SELECT
    DATEPART(YEAR, li.CREATED) AS YEAR_CREATED,
    DATEPART(MONTH, li.CREATED) AS MONTH_CREATED,
    COUNT(*) AS ITEM_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    AVG(li.CONTENT_SIZE) / (1024.0) AS AVG_SIZE_KB
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
  ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX IN ('dxp', 'sbdf')
  AND li.CREATED IS NOT NULL
GROUP BY
    DATEPART(YEAR, li.CREATED),
    DATEPART(MONTH, li.CREATED)
ORDER BY
    YEAR_CREATED DESC,
    MONTH_CREATED DESC;

This helps identify when your library grew most rapidly and supports governance planning.

Building an Admin Dashboard in Spotfire

These queries are ideal building blocks for a dedicated "Spotfire Admin" analysis. Typical approach:

• Create one or more information links (or data connections with custom SQL) using the queries above.
• Build pages for: library inventory, size & cleanup, report utilization, user & group audit, and usage/adoption stats.
• Run the queries read-only and schedule refreshes as needed so that admins always see current metadata without impacting production usage.

Conclusion

Regular monitoring of Spotfire library content, usage patterns, and user access is critical for maintaining a healthy and efficient analytics platform. The SQL queries provided in this post allow administrators to:

• Identify large, underutilized content candidates for retirement
• Track user adoption and engagement
• Audit permissions and group memberships
• Analyze data source and information link dependencies
• Build data-driven governance dashboards
• Detect duplicate or redundant content

For Spotfire Server administrators responsible for library and user management, these queries form the foundation of an effective metadata and governance toolkit.

Always remember to:

• Test in non-production environments first
• Adapt column names and logic to match your specific Spotfire version and database schema
• Use read-only accounts when running these queries directly against the repository
• Monitor query performance on large libraries and consider indexed views for frequently accessed queries

Happy querying!

Additional SQL Queries from TIBCO Resources

This section includes additional queries sourced from TIBCO Support and the Spotfire Community, featuring both SQL Server and Oracle syntax variations.

14. Largest Library Items (TIBCO Support)

Comprehensive query to identify the largest content items with details about creator, modification history, and parent folder location.

SELECT
    li.CONTENT_SIZE/(1024*1024) AS [CONTENT_SIZE (MB)],
    li.ITEM_ID,
    li.TITLE,
    li.DESCRIPTION,
    lit.DISPLAY_NAME,
    created.DISPLAY_NAME AS [CREATED BY],
    li.CREATED AS [CREATED DATE],
    modified.DISPLAY_NAME AS [MODIFIED BY],
    li.MODIFIED AS [MODIFIED DATE],
    li.ACCESSED AS [ACCESSED DATE],
    li.PARENT_ID,
    parent.TITLE AS [PARENT TITLE]
FROM lib_items li
    JOIN LIB_ITEMS parent ON li.PARENT_ID=parent.ITEM_ID
    JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE=lit.TYPE_ID
    JOIN USERS created ON li.CREATED_BY=created.USER_ID
    JOIN USERS modified ON li.MODIFIED_BY=modified.USER_ID
ORDER BY li.CONTENT_SIZE DESC;

15. Users Consuming Most Space - By Creator

Identifies which users have created the most content, with breakdowns by DXP and SBDF file types and their respective sizes.

SELECT
    SUM(li.CONTENT_SIZE)/(1024*1024) AS [TOTAL CONTENT_SIZE (MB)],
    SUM(CASE WHEN lit.FILE_SUFFIX='dxp' THEN 1 ELSE 0 END) AS [# DXP FILES],
    SUM(CASE WHEN lit.FILE_SUFFIX='dxp' THEN li.CONTENT_SIZE ELSE 0 END)/(1024*1024) AS [DXP FILE SIZE (MB)],
    SUM(CASE WHEN lit.FILE_SUFFIX='sbdf' THEN 1 ELSE 0 END) AS [# SBDF FILES],
    SUM(CASE WHEN lit.FILE_SUFFIX='sbdf' THEN li.CONTENT_SIZE ELSE 0 END)/(1024*1024) AS [SBDF FILE SIZE (MB)],
    created.DISPLAY_NAME AS [CREATED BY]
FROM lib_items li
    JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE=lit.TYPE_ID
    JOIN USERS created ON li.CREATED_BY=created.USER_ID
GROUP BY created.DISPLAY_NAME
ORDER BY SUM(li.CONTENT_SIZE)/(1024*1024) DESC;

16. Users Consuming Most Space - By Last Modifier

Shows which users have been most active in modifying library content, useful for understanding who is driving storage growth.

SELECT
    SUM(li.CONTENT_SIZE)/(1024*1024) AS [TOTAL CONTENT_SIZE (MB)],
    SUM(CASE WHEN lit.FILE_SUFFIX='dxp' THEN 1 ELSE 0 END) AS [# DXP FILES],
    SUM(CASE WHEN lit.FILE_SUFFIX='sbdf' THEN li.CONTENT_SIZE ELSE 0 END)/(1024*1024) AS [SBDF FILE SIZE (MB)],
    modified.DISPLAY_NAME AS [LAST MODIFIED BY]
FROM lib_items li
    JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE=lit.TYPE_ID
    JOIN USERS modified ON li.MODIFIED_BY=modified.USER_ID
GROUP BY modified.DISPLAY_NAME
ORDER BY SUM(li.CONTENT_SIZE)/(1024*1024) DESC;

17. Large Content Not Accessed Recently (>10MB, Not Used >1 Year)

Critical query for identifying candidates for retirement - shows large files that have not been accessed in over a year or never accessed.

SELECT
    li.CONTENT_SIZE/(1024*1024) AS [CONTENT_SIZE (MB)],
    li.ITEM_ID,
    li.TITLE,
    lit.DISPLAY_NAME AS [ITEM TYPE],
    created.DISPLAY_NAME AS [CREATED BY],
    li.CREATED AS [CREATED DATE],
    li.ACCESSED AS [ACCESSED DATE],
    parent.TITLE AS [PARENT TITLE]
FROM lib_items li
    JOIN LIB_ITEMS parent ON li.PARENT_ID=parent.ITEM_ID
    JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE=lit.TYPE_ID
    JOIN USERS created ON li.CREATED_BY=created.USER_ID
WHERE (li.ACCESSED IS NULL OR li.ACCESSED < DATEADD(YEAR,-1,GETDATE()))
  AND li.CONTENT_SIZE/(1024*1024) > 10
ORDER BY li.CONTENT_SIZE DESC;

18. Spotfire Data Sources Listing (Oracle)

Oracle-specific query to list all Spotfire Data Sources with their connection properties, authentication types, and pool settings. This view extracts data source XML from BLOB storage.

-- Query sample: Get all SQL Server Data Sources
SELECT * FROM SPF_DATA_SOURCES_V 
WHERE DS_TYPE = 'Sqlserver';

-- Useful data source columns:
-- DS_ID, DS_NAME, DS_PATH, DS_TYPE, DS_CONN_URL
-- DS_CREATED_BY_NAME, DS_MODIFIED_BY_NAME
-- DS_AUTHENTICATION, DS_CREDENTIALS_TIMEOUT
-- DS_WRITE_ALLOWED, DS_FETCH_SIZE, DS_BATCH_SIZE

Note: This uses SPF_DATA_SOURCES_V view. For the full view definition including XML extraction, see the Spotfire Community article.

19. User Groups Flattened Hierarchy (Oracle)

Shows which users belong to which groups in a flattened hierarchy, including nested group memberships and email addresses for direct contact.

SELECT * FROM SPF_GROUP_USERS_FLAT_V 
WHERE USER_ENABLED = 1
ORDER BY GROUP_NAME, USER_NAME;

-- Returns: GROUP_ID, GROUP_NAME, USER_ID, USER_NAME,
--          USER_EMAIL, LAST_LOGIN, USER_ENABLED

20. User Emails by Group (Oracle)

Extracts all user email addresses grouped by Spotfire group, useful for sending communications to specific user groups.

SELECT GROUP_NAME, USERS_LIST_CLOB 
FROM SPF_USER_EMAILS_BY_GROUP_V 
ORDER BY GROUP_NAME;

-- Returns: GROUP_NAME and USERS_LIST_CLOB
-- CLOB field contains semicolon-separated emails
-- Ready to copy/paste into Outlook

21. Consolidated User Action Log Events (Oracle)

Provides a consolidated view of action log events across different log categories with readable descriptions of user actions, IP addresses, and item references.

SELECT * FROM SPF_ACTION_LOG_EVENTS_V 
WHERE EVENT_DATE_TIME >= TRUNC(SYSDATE) - 7
ORDER BY EVENT_DATE_TIME DESC;

-- Returns: EVENT_DATE_TIME, IP_ADDRESS, USER_ID, USER_NAME,
--          LOG_CATEGORY, LOG_ACTION, LOG_ACTION_DESC,
--          TOP_FOLDER_NAME, LIB_ITEM_NAME, PAGE_NAME

22. Update Data Source Passwords (Oracle/SQL Server)

Method for programmatically updating data source connection URLs and passwords after environment cloning. Requires BLOB manipulation and HEX encoding.

-- Step 1: Extract current Data Source BLOB
DECLARE @lob_in VARBINARY(MAX);
DECLARE @item_id VARCHAR(36) = 'YOUR_DATASOURCE_ITEM_ID';

SELECT @lob_in = [DATA]
FROM LIB_DATA
WHERE ITEM_ID = @item_id;

-- Step 2: Update with new credentials (update the HEX string with your values)
UPDATE LIB_DATA
SET [DATA] = NEW_HEX_ENCODED_BLOB
WHERE ITEM_ID = @item_id;

Note: Data source passwords are encrypted. You must manually set the desired credentials once in a test data source first to obtain the encrypted format.

23. Library Items by Type Summary

Provides a summary count and total size of library items grouped by type (DXP, SBDF, folders, etc.).

SELECT
    lit.DISPLAY_NAME AS ITEM_TYPE,
    COUNT(*) AS ITEM_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    AVG(li.CONTENT_SIZE) / (1024.0) AS AVG_SIZE_KB,
    MIN(li.CREATED) AS EARLIEST_CREATED,
    MAX(li.MODIFIED) AS LATEST_MODIFIED
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
GROUP BY lit.DISPLAY_NAME, lit.FILE_SUFFIX
ORDER BY TOTAL_SIZE_MB DESC;

24. Most Active Folders by Recent Modification

Identifies folders where the most recent content changes have occurred, helping to focus on actively used areas of the library.

SELECT TOP 25
    parent.ITEM_ID,
    parent.TITLE AS FOLDER_NAME,
    COUNT(li.ITEM_ID) AS CONTENT_COUNT,
    MAX(li.MODIFIED) AS LAST_MODIFIED_IN_FOLDER,
    MAX(li.ACCESSED) AS LAST_ACCESSED_IN_FOLDER,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS FOLDER_SIZE_MB
FROM LIB_ITEMS li
JOIN LIB_ITEMS parent ON li.PARENT_ID = parent.ITEM_ID
GROUP BY parent.ITEM_ID, parent.TITLE
ORDER BY MAX(li.MODIFIED) DESC;

25. Content By Creator - Historical Analysis

Shows which users have created content over time, useful for identifying key contributors and ownership patterns.

SELECT
    u.DISPLAY_NAME,
    u.USER_NAME,
    COUNT(*) AS TOTAL_ITEMS_CREATED,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    MIN(li.CREATED) AS FIRST_CREATED,
    MAX(li.CREATED) AS LAST_CREATED
FROM LIB_ITEMS li
JOIN USERS u ON li.CREATED_BY = u.USER_ID
WHERE li.ITEM_TYPE NOT IN (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE DISPLAY_NAME = 'folder')
GROUP BY u.DISPLAY_NAME, u.USER_NAME
ORDER BY TOTAL_SIZE_MB DESC;

26. Recently Modified Items (Last 7 Days)

Tracks recent changes in the library to monitor active development and ongoing maintenance activities.

SELECT
    li.ITEM_ID,
    li.TITLE,
    lit.DISPLAY_NAME AS ITEM_TYPE,
    u.DISPLAY_NAME AS MODIFIED_BY,
    li.MODIFIED,
    li.CONTENT_SIZE / (1024*1024.0) AS SIZE_MB,
    parent.TITLE AS PARENT_FOLDER
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
JOIN USERS u ON li.MODIFIED_BY = u.USER_ID
LEFT JOIN LIB_ITEMS parent ON li.PARENT_ID = parent.ITEM_ID
WHERE li.MODIFIED >= DATEADD(DAY, -7, GETDATE())
ORDER BY li.MODIFIED DESC;

27. Object Owner Attribution - Complete Inventory

Maps all library objects to their creators with comprehensive metadata for ownership tracking and governance.

SELECT
    u.USER_NAME,
    u.DISPLAY_NAME,
    COUNT(DISTINCT li.ITEM_ID) AS OBJECT_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS OWNER_TOTAL_MB,
    MIN(li.CREATED) AS OLDEST_OBJECT,
    MAX(li.MODIFIED) AS LATEST_MODIFICATION
FROM LIB_ITEMS li
JOIN USERS u ON li.CREATED_BY = u.USER_ID
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.DISPLAY_NAME NOT IN ('Folder')
GROUP BY u.USER_ID, u.USER_NAME, u.DISPLAY_NAME
ORDER BY OWNER_TOTAL_MB DESC;

28. Orphaned Items Detection

Identifies library items with missing parent folders or invalid references.

SELECT
    li.ITEM_ID,
    li.TITLE,
    lit.DISPLAY_NAME AS ITEM_TYPE,
    li.PARENT_ID,
    li.CONTENT_SIZE / (1024*1024.0) AS SIZE_MB
FROM LIB_ITEMS li
LEFT JOIN LIB_ITEMS parent ON li.PARENT_ID = parent.ITEM_ID
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE li.PARENT_ID IS NOT NULL
  AND parent.ITEM_ID IS NULL
ORDER BY li.CONTENT_SIZE DESC;

29. Nested Folder Depth Analysis

Analyzes folder hierarchy depth to identify overly deep structures affecting usability.

WITH folder_hierarchy AS (
    SELECT ITEM_ID, PARENT_ID, TITLE, 1 AS depth
    FROM LIB_ITEMS
    WHERE ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE DISPLAY_NAME = 'Folder')
      AND PARENT_ID IS NULL
    UNION ALL
    SELECT li.ITEM_ID, li.PARENT_ID, li.TITLE, fh.depth + 1
    FROM LIB_ITEMS li
    INNER JOIN folder_hierarchy fh ON li.PARENT_ID = fh.ITEM_ID
)
SELECT depth, COUNT(*) AS folder_count
FROM folder_hierarchy
GROUP BY depth
ORDER BY depth DESC;

30. DXP Files with Embedded Data Size

Lists DXP analyses and shows which ones have embedded data (larger file size), useful for optimizing storage.

SELECT
    li.ITEM_ID,
    li.TITLE,
    li.CONTENT_SIZE / (1024*1024.0) AS DXP_SIZE_MB,
    CASE WHEN li.CONTENT_SIZE > (1024*1024*5) THEN 'Likely Embedded Data' ELSE 'Small' END AS DATA_STATUS,
    u.DISPLAY_NAME AS CREATED_BY,
    li.CREATED,
    parent.TITLE AS PARENT_FOLDER
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
JOIN USERS u ON li.CREATED_BY = u.USER_ID
LEFT JOIN LIB_ITEMS parent ON li.PARENT_ID = parent.ITEM_ID
WHERE lit.FILE_SUFFIX = 'dxp'
ORDER BY li.CONTENT_SIZE DESC;

31. SBDF Files Inventory and Location

Lists all SBDF (Spotfire Binary Data Files) with their sizes, creators, and locations.

SELECT
    li.ITEM_ID,
    li.TITLE,
    li.CONTENT_SIZE / (1024*1024.0) AS SBDF_SIZE_MB,
    u.DISPLAY_NAME AS CREATED_BY,
    li.CREATED,
    li.ACCESSED,
    DATEDIFF(DAY, li.ACCESSED, GETDATE()) AS DAYS_SINCE_ACCESS,
    parent.TITLE AS PARENT_FOLDER
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
JOIN USERS u ON li.CREATED_BY = u.USER_ID
LEFT JOIN LIB_ITEMS parent ON li.PARENT_ID = parent.ITEM_ID
WHERE lit.FILE_SUFFIX = 'sbdf'
ORDER BY li.CONTENT_SIZE DESC;

32. Disabled User Accounts with Active Content

Identifies content created or owned by disabled user accounts, useful for access cleanup.

SELECT
    u.USER_NAME,
    u.DISPLAY_NAME,
    u.ENABLED,
    COUNT(DISTINCT li.ITEM_ID) AS CONTENT_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    MAX(li.MODIFIED) AS LAST_MODIFIED_CONTENT
FROM LIB_ITEMS li
JOIN USERS u ON li.CREATED_BY = u.USER_ID
WHERE u.ENABLED = 0
GROUP BY u.USER_ID, u.USER_NAME, u.DISPLAY_NAME, u.ENABLED
ORDER BY TOTAL_SIZE_MB DESC;

33. Group Membership Analysis Over Time

Shows how many users are in each group, useful for load distribution and governance.

SELECT
    g.GROUP_NAME,
    COUNT(DISTINCT gm.MEMBER_USER_ID) AS ACTIVE_USERS,
    COUNT(DISTINCT gm.MEMBER_GROUP_ID) AS NESTED_GROUPS,
    g.CONNECTED AS LDAP_SYNCED,
    MIN(u.LAST_LOGIN) AS EARLIEST_LOGIN,
    MAX(u.LAST_LOGIN) AS LATEST_LOGIN
FROM GROUPS g
LEFT JOIN GROUP_MEMBERS gm ON g.GROUP_ID = gm.GROUP_ID
LEFT JOIN USERS u ON gm.MEMBER_USER_ID = u.USER_ID
GROUP BY g.GROUP_ID, g.GROUP_NAME, g.CONNECTED
HAVING COUNT(DISTINCT gm.MEMBER_USER_ID) > 0
ORDER BY ACTIVE_USERS DESC;

34. Permission Inconsistencies - Inherited vs Direct

Identifies folders with both inherited and direct permissions assigned.

SELECT
    li.ITEM_ID,
    li.TITLE,
    COUNT(DISTINCT la.USER_ID) AS DIRECT_USER_PERMS,
    COUNT(DISTINCT la.GROUP_ID) AS DIRECT_GROUP_PERMS,
    CASE WHEN li.PARENT_ID IS NOT NULL THEN 'Has Parent (Inheritance Possible)' ELSE 'Root Item' END AS INHERITANCE_STATUS
FROM LIB_ITEMS li
LEFT JOIN LIB_ACCESS la ON li.ITEM_ID = la.ITEM_ID
GROUP BY li.ITEM_ID, li.TITLE, li.PARENT_ID
HAVING COUNT(DISTINCT la.USER_ID) + COUNT(DISTINCT la.GROUP_ID) > 0
ORDER BY DIRECT_USER_PERMS + DIRECT_GROUP_PERMS DESC;

35. Users with Multiple Login Sessions

Identifies users with many logins, useful for understanding engagement and anomaly detection.

SELECT
    USER_NAME,
    COUNT(*) AS LOGIN_COUNT,
    MIN(LOGGED_TIME) AS FIRST_LOGIN,
    MAX(LOGGED_TIME) AS LATEST_LOGIN,
    DATEDIFF(DAY, MIN(LOGGED_TIME), MAX(LOGGED_TIME)) AS DAYS_ACTIVE
FROM ACTIONLOG
WHERE LOG_CATEGORY = 'auth'
  AND LOG_ACTION = 'login'
  AND SUCCESS = 1
GROUP BY USER_NAME
HAVING COUNT(*) > 10
ORDER BY LOGIN_COUNT DESC;

36. Failed Login Attempts by User

Security-focused query to identify possible intrusion attempts or account access issues.

SELECT
    USER_NAME,
    COUNT(*) AS FAILED_ATTEMPTS,
    MIN(LOGGED_TIME) AS FIRST_ATTEMPT,
    MAX(LOGGED_TIME) AS LAST_ATTEMPT
FROM ACTIONLOG
WHERE LOG_CATEGORY IN ('auth', 'auth_wp', 'auth_pro')
  AND LOG_ACTION = 'login'
  AND SUCCESS = 0
GROUP BY USER_NAME
HAVING COUNT(*) > 3
ORDER BY FAILED_ATTEMPTS DESC;

37. Content Access by Permission Level

Shows distribution of content across different permission levels (Browse, Modify, Full Control).

SELECT
    CASE
        WHEN PERMISSION = 'X' THEN 'Access Only'
        WHEN PERMISSION = 'R' THEN 'Browse'
        WHEN PERMISSION = 'W' THEN 'Modify'
        WHEN PERMISSION = 'O' THEN 'Full Control'
        ELSE 'Unknown'
    END AS PERMISSION_LEVEL,
    COUNT(DISTINCT ITEM_ID) AS ITEMS_WITH_PERM,
    COUNT(DISTINCT USER_ID) + COUNT(DISTINCT GROUP_ID) AS PRINCIPALS_GRANTED
FROM LIB_ACCESS
GROUP BY PERMISSION
ORDER BY COUNT(*) DESC;

38. Data Source Usage in Analyses

Counts how many analyses reference each data source, useful for impact analysis.

SELECT
    ds.TITLE AS DATA_SOURCE_NAME,
    COUNT(DISTINCT li.ITEM_ID) AS ANALYSIS_COUNT,
    STRING_AGG(DISTINCT li.TITLE, ', ') AS REFERENCING_ANALYSES
FROM LIB_ITEMS ds
CROSS APPLY (
    SELECT li.ITEM_ID, li.TITLE
    FROM LIB_ITEMS li
    WHERE li.ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE DISPLAY_NAME = 'dxp')
) AS li
WHERE ds.TITLE LIKE '%Data%' OR ds.TITLE LIKE '%Source%'
GROUP BY ds.ITEM_ID, ds.TITLE
ORDER BY ANALYSIS_COUNT DESC;

39. Content Distribution by File Type

Shows the breakdown of storage and item count by file type (.dxp, .sbdf, folders, etc.).

SELECT
    lit.FILE_SUFFIX,
    lit.DISPLAY_NAME,
    COUNT(*) AS ITEM_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024*1024.0) AS TOTAL_SIZE_GB,
    AVG(li.CONTENT_SIZE) / (1024*1024.0) AS AVG_SIZE_MB,
    MIN(li.CREATED) AS OLDEST_ITEM,
    MAX(li.MODIFIED) AS NEWEST_MODIFICATION
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
GROUP BY lit.FILE_SUFFIX, lit.DISPLAY_NAME, lit.TYPE_ID
ORDER BY TOTAL_SIZE_GB DESC;

40. Web Player vs Client Usage Analysis

Compares usage patterns between Web Player and client (thick client) logins.

SELECT
    CASE 
        WHEN LOG_CATEGORY LIKE '%_wp' THEN 'Web Player'
        WHEN LOG_CATEGORY LIKE '%_pro' THEN 'Desktop Client'
        ELSE 'Other'
    END AS CLIENT_TYPE,
    LOG_ACTION,
    COUNT(*) AS EVENT_COUNT,
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS,
    MIN(LOGGED_TIME) AS FIRST_EVENT,
    MAX(LOGGED_TIME) AS LAST_EVENT
FROM ACTIONLOG
WHERE LOG_CATEGORY IN ('auth_wp', 'auth_pro', 'library_wp', 'file_pro')
GROUP BY LOG_CATEGORY, LOG_ACTION
ORDER BY CLIENT_TYPE, EVENT_COUNT DESC;

41. Peak Usage Hours Analysis

Identifies peak usage times by analyzing login and activity timestamps for capacity planning.

SELECT
    DATEPART(HOUR, LOGGED_TIME) AS HOUR_OF_DAY,
    DATEPART(WEEKDAY, LOGGED_TIME) AS DAY_OF_WEEK,
    COUNT(*) AS EVENT_COUNT,
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS
FROM ACTIONLOG
WHERE LOG_CATEGORY IN ('auth_wp', 'auth_pro')
  AND LOG_ACTION = 'login'
  AND SUCCESS = 1
GROUP BY DATEPART(HOUR, LOGGED_TIME), DATEPART(WEEKDAY, LOGGED_TIME)
ORDER BY EVENT_COUNT DESC;

42. Scheduled Report Refresh Dependencies

Maps scheduled analyses and their data source dependencies.

SELECT
    li.ITEM_ID,
    li.TITLE,
    li.CREATED,
    u.DISPLAY_NAME AS CREATED_BY,
    CASE WHEN li.CONTENT_SIZE > (1024*1024*10) THEN 'Large' ELSE 'Normal' END AS SIZE_CATEGORY,
    COUNT(DISTINCT ds.ITEM_ID) AS DATA_SOURCE_COUNT
FROM LIB_ITEMS li
LEFT JOIN LIB_ITEMS ds ON li.PARENT_ID = ds.ITEM_ID
JOIN USERS u ON li.CREATED_BY = u.USER_ID
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.DISPLAY_NAME = 'dxp'
GROUP BY li.ITEM_ID, li.TITLE, li.CREATED, u.DISPLAY_NAME, li.CONTENT_SIZE
HAVING COUNT(DISTINCT ds.ITEM_ID) > 0
ORDER BY DATA_SOURCE_COUNT DESC;

43. User Inactivity Detection

Identifies users who have not accessed Spotfire in a specified period for license optimization.

SELECT
    u.USER_NAME,
    u.DISPLAY_NAME,
    u.LAST_LOGIN,
    DATEDIFF(DAY, u.LAST_LOGIN, GETDATE()) AS DAYS_INACTIVE,
    CASE
        WHEN DATEDIFF(DAY, u.LAST_LOGIN, GETDATE()) > 180 THEN 'Inactive (6+ months)'
        WHEN DATEDIFF(DAY, u.LAST_LOGIN, GETDATE()) > 90 THEN 'Inactive (3+ months)'
        WHEN DATEDIFF(DAY, u.LAST_LOGIN, GETDATE()) > 30 THEN 'Inactive (1+ month)'
        ELSE 'Active'
    END AS ACTIVITY_STATUS
FROM USERS u
WHERE u.ENABLED = 1
  AND u.LAST_LOGIN < DATEADD(DAY, -30, GETDATE())
ORDER BY LAST_LOGIN ASC;

44. Object Edit History Summary

Shows which users have edited which objects most frequently for change tracking.

SELECT
    li.ITEM_ID,
    li.TITLE,
    u.DISPLAY_NAME AS LAST_MODIFIED_BY,
    li.MODIFIED AS LAST_MODIFICATION_DATE,
    DATEDIFF(DAY, li.MODIFIED, GETDATE()) AS DAYS_SINCE_MODIFICATION,
    lit.DISPLAY_NAME AS ITEM_TYPE
FROM LIB_ITEMS li
JOIN USERS u ON li.MODIFIED_BY = u.USER_ID
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE li.ITEM_TYPE IN (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE FILE_SUFFIX IN ('dxp', 'sbdf'))
ORDER BY li.MODIFIED DESC;

45. Content Classification by Age

Segments library content by creation date for maintenance and archival planning.

SELECT
    CASE
        WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 3 THEN 'New (< 3 months)'
        WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 12 THEN 'Recent (3-12 months)'
        WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 24 THEN 'Mature (1-2 years)'
        ELSE 'Legacy (2+ years)'
    END AS AGE_CATEGORY,
    COUNT(*) AS ITEM_COUNT,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    AVG(li.CONTENT_SIZE) / (1024.0) AS AVG_SIZE_KB
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX IN ('dxp', 'sbdf')
GROUP BY CASE
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 3 THEN 'New (< 3 months)'
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 12 THEN 'Recent (3-12 months)'
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 24 THEN 'Mature (1-2 years)'
    ELSE 'Legacy (2+ years)'
END
ORDER BY CASE
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 3 THEN 1
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 12 THEN 2
    WHEN DATEDIFF(MONTH, li.CREATED, GETDATE()) < 24 THEN 3
    ELSE 4
END;

46. Top Contributors by Output Volume

Identifies power users who create the most analyses and content by volume and activity.

SELECT TOP 20
    u.USER_NAME,
    u.DISPLAY_NAME,
    COUNT(DISTINCT li.ITEM_ID) AS ANALYSES_CREATED,
    SUM(li.CONTENT_SIZE) / (1024*1024.0) AS TOTAL_SIZE_MB,
    MIN(li.CREATED) AS FIRST_CREATION,
    MAX(li.MODIFIED) AS LAST_MODIFICATION,
    DATEDIFF(DAY, MIN(li.CREATED), MAX(li.MODIFIED)) AS ACTIVE_SPAN_DAYS
FROM LIB_ITEMS li
JOIN USERS u ON li.CREATED_BY = u.USER_ID
JOIN LIB_ITEM_TYPES lit ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX IN ('dxp', 'sbdf')
GROUP BY u.USER_ID, u.USER_NAME, u.DISPLAY_NAME
ORDER BY COUNT(DISTINCT li.ITEM_ID) DESC;

Summary and Additional Resources

This comprehensive collection of 46 SQL queries provides Spotfire administrators with powerful tools to:

• Inventory Management: Track library content, sizes, and growth patterns
• Storage Optimization: Identify large and unused items for retirement or archival
• User & Group Analysis: Audit access patterns, permissions, and group memberships
• Activity Monitoring: Analyze usage patterns and user behavior via action logs
• Data Source Management: List and update data source configurations across environments
• Governance & Compliance: Generate reports for regulatory and internal audits

Database-Specific Notes:

• SQL Server: Queries 1-17 use SQL Server syntax (DATEADD, GETDATE(), etc.)
• Oracle: Queries 18-21 use Oracle-specific views and syntax (TRUNC, FROM_TZ, LISTAGG, etc.)
• PostgreSQL: May require syntax adjustments for date functions and aggregate functions

Key Resources Referenced:

• TIBCO Support: Example SQL Queries for Library Content Size
• Spotfire Community: Spotfire Metadata Queries
• Spotfire Server Documentation on Action Logs and Metadata

Final Recommendations:

• Always test queries in development/non-production environments first
• Use read-only database accounts when possible for querying sensitive metadata
• Schedule regular execution of monitoring queries for trend analysis
• Build Spotfire analyses using Information Links to these queries for interactive dashboards
• Document query execution dates and baseline values for governance compliance
• Archive historical results to track library growth and changes over time

Happy Spotfire administrating!

---

Disclaimer & Attribution

Disclaimer: The SQL queries provided in this post are compiled from publicly available sources, including TIBCO documentation, community contributions, and industry best practices. I do not claim original ownership of all these queries. Many of these queries have been adapted, modified, or sourced from:

• TIBCO Support: Example SQL Queries for managing Spotfire library content (KB0072647)
• Spotfire Community: User-contributed metadata queries and administration scripts
• Spotfire Server Documentation: Official TIBCO documentation on metadata and action logs
• Industry Standard Practices: Common SQL patterns for BI administration and governance

Purpose of This Post: This blog consolidates these queries in one comprehensive reference guide for Spotfire administrators. The queries are provided as-is for educational and administrative purposes in non-production test environments.

Terms of Use:

• Always test queries in development/non-production environments first
• Modify queries according to your specific Spotfire version and database schema
• Use read-only database accounts when executing metadata queries
• Refer to official TIBCO documentation for your specific Spotfire version
• No warranty is provided for these queries - use at your own risk

Attribution & References:

• TIBCO Support: Example SQL Queries for Library Content Size
• Spotfire Community: Spotfire Metadata Queries
• TIBCO Spotfire Official Documentation
• Thanks to the Spotfire community for sharing knowledge and best practices

Last Updated: December 2025 | Content for Spotfire Server 7.x through 14.x

Read More

Effective Error Reporting

One thing we all agree - Big IronPython codes are boomerang. They Always Come Back to Developer 🥺

The real problem is there is no perfect code ☹️,
What we can do is - do a better Error Reporting to minimize time required to pin point issue. For this purpose, I usually take help of traceback and NotificationService Modules in code ✅
Have a look at example code -
import traceback
from Spotfire.Dxp.Framework.ApplicationModel import NotificationService
notif = Application.GetService[NotificationService]()

try:
a = 1/0 
#Raising Devide by Zero 
except:
tb = traceback.format_exc()
finally:
print tb
notif.AddErrorNotification("Error While Executing","IronPython Code <Name of Script> raised an Error",tb)

See, How it looks  -

Hope it helps 🤓

Read More

Dynamic Links in Spotfire

My friend asked me recently -
"I need to trigger a URL from Spotfire, where URL should change as per User inputs"
Interesting 🤓
Usually when people try doing it from IronPython webbrowser code, It doesn't work in Webplayer. So we need something which works everywhere 🙂
To do this, I will take a generic example. I will trigger a Google search query based on an Input field. Every time search query gets updated, the Search button will also change its whole code.
Lets start -

1. Create a HTML Text Area with an Input field Property, and a DIV for holding our search button.
   <span id="query">
   <SpotfireControl id="041866e281b24b8fa9f839f91af7a30d" />
   </span>
   <br><br>
   <div id="trigger">
   </div>
2. Now Add some extra code to making our search button as an actual button. You can use code below -
   <style id="AlienBox">
    .buttonSpan {
   background-color:chocolate !important;
   color: white !important;
   border: 1px solid white!important;
   border-radius: 2px 3px 3px 3px !important;
       font-variant: petite-caps;
   cursor:pointer;
   }
    </style>
   As usual, here is JS code to beat HTML sanitization -
   if(!$('#AlienBox').length){
    $('body').append($(`<style id="AlienBox">
    .buttonSpan {
   background-color:chocolate !important;
   color: white !important;
   border: 1px solid white!important;
   border-radius: 2px 3px 3px 3px !important;
       font-variant: petite-caps;
   cursor:pointer;
   }
    </style>`, {
           id: 'AlienBox' 
      })); 
   }
3. Add this JavaScript to Text Area. Feel free to modify as per your need.
   function ChangeURL(){
   
   var q =$('#query input').val();
   var SearchString = "<span class='buttonSpan' onclick=\"location='https://www.google.com/search?q="+q+"'\"> Search Google for '"+q+"'</span>";
   $('#trigger').html(SearchString);
   }
   setInterval(ChangeURL,500)
4. Done

This is how it looks for different inputs -

Clicking on these buttons will open a Web Browser with Dynamic URL if the Analysis is opened in Clint. From Webplayer, it simple loads the URL populated.

Read More

Adding Styles to Date Picker

While working with date range filters, date-pickers are very helpful. They allow to select date ranges of our interest in a very convenient manner. They are nice right ??


Well what about this one ?

💡 Liked it? Then add this code to a HTML Text area to have this look and feel -
<style>
.DatePicker {
    background: black;
    border: 2px solid #ffffff;
    border-radius: 8px 0px 8px 0px;
    position: absolute;
    overflow: hidden;
    z-index: 1024;
    text-align: center;
    font-size: 11px;
    color: #fff;
    width: 189px;
    font-variant-caps: small-caps;
}
.DatePicker .Header {
    height: 22px;
    padding: 2px 0 2px 0;
    background-color: brown;
    font-size: larger;
    font-weight: bold;
}
.DatePicker .Week td.CurrentMonth {
    color: brown;
}
.DatePicker .Week td {
    width: 21px;
    height: 21px;
    border-radius: 50%;
    text-align: center;
    vertical-align: middle;
    color: black;
}
 </style>
⚽ To beat HTML Sanitization, use below JavaScript code - 
if(!$('#AlienBox').length){
 $('body').append($(`<style id="AlienBox">
.DatePicker {
    background: black;
    border: 2px solid #ffffff;
    border-radius: 8px 0px 8px 0px;
    position: absolute;
    overflow: hidden;
    z-index: 1024;
    text-align: center;
    font-size: 11px;
    color: #fff;
    width: 189px;
    font-variant-caps: small-caps;
}
.DatePicker .Header {
    height: 22px;
    padding: 2px 0 2px 0;
    background-color: brown;
    font-size: larger;
    font-weight: bold;
}
.DatePicker .Week td.CurrentMonth {
    color: brown;
}
.DatePicker .Week td {
    width: 21px;
    height: 21px;
    border-radius: 50%;
    text-align: center;
    vertical-align: middle;
    color: black;
}
 </style>`, {
        id: 'AlienBox' 
   })); 
}

Read More

Styles for Tooltip

Tooltips in Spotfire are great for showing insights on mouse over. We use them a lot for showing information which should appear only when we need. But what if you feel that their look and feel is not matching with you theme ?
Question here is - How to add Style to tooltips ? 🤔🤔
Answer is, below code in a HTML TextArea -
<style id="AlienBox">
 .sf-tooltip {
background-color:chocolate !important;
color: white !important;
border: 2px solid white!important;
border-radius: 8px 0px 8px 0px !important;
    font-variant: petite-caps;
    font-style: italic;
    font-weight: lighter;
    font-size: x-small;
}
 </style>
If you are victim of HTML Sanitization issue, use JavaScript below -
if(!$('#AlienBox').length){
 $('body').append($(`<style id="AlienBox">
 .sf-tooltip {
background-color:chocolate !important;
color: white !important;
border: 2px solid white!important;
border-radius: 8px 0px 8px 0px !important;
    font-variant: petite-caps;
    font-style: italic;
    font-weight: lighter;
    font-size: x-small;
}
 </style>`, {
        id: 'AlienBox' 
   })); 
}
This is what I was able to achieve so far -

Read More

Conditional Coloring of Spotfire Buttons

My friend was willing to color Spotfire Action buttons based on a value. 

It is a small but effective way of representation. You will actually come to know what are your expectations even before clicking on a color coded button.

Challenge here, This color should be picked automatically  

Challenge Accepted !

Lets Start -

1. Create a calculated value or Label property which evaluates into a single color (e.g. red) or into a color code value (e.g. #454545). Enclose it is a span with id property-
   
   <span id='property' style="display:none">
      <SpotfireControl id="c281364f0af947c38752561b38be631e" />
   </span>
2. Now enclose your action button inside another span with id ConditionalButtonColor like below. This way you are letting your code know - which button you are going to style 🧐
   
   <span id='ConditionalButtonColor'>
   <SpotfireControl id="edf0f3c8a1b945cb840cd0235fce8d66" />
   </span>
3. Now you simply need this JavaScript code to be added to the text area. It retrieves value from property and set it a background color of action button inside ConditionalButtonColor .
   
   var flag=0
   function conditionalColor() {  
    colorValue=$("#property").text()
    if(flag!=colorValue){
     $('#ConditionalButtonColor .sfc-action-button').css({
       background:colorValue,
   color:"white"
       ,textAlign:"center"
       ,margin:"10px"
       ,padding:"5px"
       ,borderStyle:"outset"
       ,width:"100px"
     });
    }
     flag=colorValue
   }
   
   
   setInterval(conditionalColor,500)
4. Save the text are and you are done ✅

For demonstration, I am updating color value a drop down property. You can update it via a Label Document Property, Calculated value or even from simple sting as well. Here is a screenshot with results -

Read More

Show/Hide Pages with a Button

A friend from Community asked for a Button to toggle visibility of a Spotfire page.  
So, this post is about same 😎
Lets Start -

1. Create a button in TextArea with id Hider. If you are suffering from HTML Sanitization, you can use my alternative code. This one re-purposes span as button 😜
   <span id="Hider" style="background-color:chocolate;
   color:white;
   display:inline-block;
   border: 1px solid rgb(128,128,128);
   border-radius : 8px;
   padding     : 8px;
   line-height : 24px;
   text-shadow : 0 0 2px black;
   font-size:larger;
   margin:5px;
   cursor: pointer;">Toggle Visibility
   </span>
2. Add below JavaScript to TextArea. Here [1] is page index of Second page. You can adjust it as you need.
   $('#Hider').click(function(){
   var state =$('.sf-element-page-tab')[1].style.display;
   console.log(state);
   if(state =='none') {
   $('.sf-element-page-tab')[1].style.display = "";}
   else{$('.sf-element-page-tab')[1].style.display = "none";}
   });
3. Save TextArea and you are done ✅

This is a Before vs After comparison of Page Navigation Area -

Read More

Capturing Hierarchy Axis Changes to Document Property

My friend recently asked an Interesting Question -
I have a chart where I am using a Hierarchy as Category Axis. I want to capture changes in that Axis (Hierarchy) to a Property Control. Is there a way to do this ?
Sounds interesting  🙃
Well , Let's start doing it !

1. Add a Input field property enclosed in a span like below. Code in red is optional based on whether you need to display document property on not.
   <span id='AxisTracker' style='display:none;'>
     <SpotfireControl id="635a7ae743a14feb865833e75f3e9b4d" />
   </span>
2. Add below JavaScript to capture changes. Here code in red indicates the possible states of Hierarchy Axis of interest
   
   function updateProperty(){
   $('.sf-element-text-box').each(function(index){
   //console.log($(this).text());
   switch($(this).text()){
       case "Geo (Region)":
       case "Geo (State)":
       case "Geo (City)":
   console.log($(this).text());
   $('#AxisTracker input').val($(this).text()).blur()
   break;
   }
   });}
   setInterval(updateProperty,500)
3. This is pretty much all 🤓 

Using this approach helps in tracking changes to the Axis and we can trigger IronPython code as well on Document property change. 

Read More

Adding some styles to Notifications

Default Notifications Dialog is boring!

Agree?
If yes, then this post is for you. Try Adding below code to a text area and then have a look at notifications again-
<style id="AlienBox">
.sf-modal-dialog-content textarea {
    color: azure;
    background-color: brown;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-footer {
    font-size: 14.5px;
    background-color: #1e264e;
    color: #BDBFC3;

}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-header {
    background-color: #134373;
}
.sfc-default-theme.sf-element-modal-dialog {
    background-color: #772323;
}
.sf-element-modal-dialog .sf-element-modal-dialog-header-title-text {
    font-size: x-large;
    color: blanchedalmond;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-header .sf-element-modal-dialog-header-close {
    color: #ffffff;
    font-size: xx-large;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-button.sfpc-secondary {
    background-image: linear-gradient(to bottom, #7b3939, #206bb7);
    border-top-color: #234996;
    border-right-color: #4d6ba7;
    border-bottom-color: #AFB2B7;
    border-left-color: #586d96;
    border-bottom-color: #546580;
    color: #ffffff;
    font-size: large;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-button {
    background-image: linear-gradient(to bottom, #7b3939, #206bb7);
    border-top-color: #234996;
    border-right-color: #4d6ba7;
    border-bottom-color: #AFB2B7;
    border-left-color: #586d96;
    border-bottom-color: #546580;
    color: #ffffff;
    font-size: large;
}
</style>
For HTML Sanitization, I have a JavaScript code too -

if(!$('#AlienBox').length){
$('body').append($(`<style id="AlienBox">
.sf-modal-dialog-content textarea {
    color: azure;
    background-color: brown;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-footer {
    font-size: 14.5px;
    background-color: #1e264e;
    color: #BDBFC3;

}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-header {
    background-color: #134373;
}
.sfc-default-theme.sf-element-modal-dialog {
    background-color: #772323;
}
.sf-element-modal-dialog .sf-element-modal-dialog-header-title-text {
    font-size: x-large;
    color: blanchedalmond;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-modal-dialog-header .sf-element-modal-dialog-header-close {
    color: #ffffff;
    font-size: xx-large;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-button.sfpc-secondary {
    background-image: linear-gradient(to bottom, #7b3939, #206bb7);
    border-top-color: #234996;
    border-right-color: #4d6ba7;
    border-bottom-color: #AFB2B7;
    border-left-color: #586d96;
    border-bottom-color: #546580;
    color: #ffffff;
    font-size: large;
}
.sfc-default-theme.sf-element-modal-dialog .sf-element-button {
    background-image: linear-gradient(to bottom, #7b3939, #206bb7);
    border-top-color: #234996;
    border-right-color: #4d6ba7;
    border-bottom-color: #AFB2B7;
    border-left-color: #586d96;
    border-bottom-color: #546580;
    color: #ffffff;
    font-size: large;
}
</style>`, {
        id: 'AlienBox' 
   }));
}

This is what I got with my code -

Read More

Interacting with Subsets with IronPython

What if you have to compare All Data with current Selections (Marking, Filtering etc.) ?
What if you have to compare your two selections?

I know answer will be - using (Subsets).
But, What If you want to make this comparison on-Demand only ?

My Answer is below IronPython code -
from Spotfire.Dxp.Application.Visuals import *
vsc = v.As[VisualContent]().Data.Subsets
vsc.Clear()# To Clear all subsets except defaults
#Use below 2 rows if you need to add additional Subsets
#vsc.AddDataSelection(Document.Data.Markings["Marking Name"])
#vsc.AddDataSelection(Document.Data.Filterings["Filtering Scheme Name"])
for ss in vsc:
print ss.DisplayName,"\n\tEnabled ? ",ss.Enabled,"\n\tInteractive? ",ss.Interactive
if ss.DisplayName =='All data':#Toggle between one selection Enabled status
ss.Enabled = not ss.Enabled
ss.Interactive=False
With above code, we are expecting these results -

Read More

Adding Styles to Legend Area

Recently I have posted a few ways to style different Spotfire elements. Now I have one more, for Legends this time.
This is what I have been able to achieve so far -

Here is the code you need to Add to one of the HTML Text Area -
<style id="AlienBox">
.StyledScrollbar.LegendScroll {
    background-color: darkcyan;
    border: 2px solid darkmagenta;
    border-radius: 12px;
}
.sfc-style-root .sf-element-legend-item {
    padding-top: 1px;
    padding-right: 10px;
    padding-bottom: 6px;
    padding-left: 10px;
    background-color: blueviolet;
    border: 2px solid blue;
    border-radius: 12px;
}
.sf-element.sf-element-title.sf-legend-item-elem {
    background-color: darkblue;
    border-radius: 12px;
}
.sf-element-document .sf-element-axis-tray .sf-element-text-box {
    vertical-align: top;
    color: deepskyblue;
    font-weight: bold;
    font-size: x-small;
}
.sf-element.sf-element-sub-item-section.sf-legend-item-elem {
    font-variant: petite-caps;
    font-style: italic;
    font-weight: lighter;
    font-size: x-small;
}
</style>
For HTML Sanitization issue, we have a solution as always as a JavaScript code -
if(!$('#AlienBox').length){
$('body').append($(`<style id="AlienBox">
.StyledScrollbar.LegendScroll {
    background-color: darkcyan;
    border: 2px solid darkmagenta;
    border-radius: 12px;
}
.sfc-style-root .sf-element-legend-item {
    padding-top: 1px;
    padding-right: 10px;
    padding-bottom: 6px;
    padding-left: 10px;
    background-color: blueviolet;
    border: 2px solid blue;
    border-radius: 12px;
}
.sf-element.sf-element-title.sf-legend-item-elem {
    background-color: darkblue;
    border-radius: 12px;
}
.sf-element-document .sf-element-axis-tray .sf-element-text-box {
    vertical-align: top;
    color: deepskyblue;
    font-weight: bold;
    font-size: x-small;
}
.sf-element.sf-element-sub-item-section.sf-legend-item-elem {
    font-variant: petite-caps;
    font-style: italic;
    font-weight: lighter;
    font-size: x-small;
}
</style>`, {
        id: 'AlienBox' 
   }));
}

Read More