@dougp thank you for your response. I'll try and get this working.

Query:  Query1
data items:
[itemnumber]
[htscode00]
[htscode01]
[htscode02]
[htscode03]
[htscode04]
[htscode05]
[htscode06]
[htscode07]
[htscode08]
[htscode09]
[htscode10]


Query:  Query2
data items:
[itemnumber]


Query:  num
SQL =
with
a as (
  select 0 as num
  union all
  select num + 1
  from a
  where num < 10
)
select *
from a(or equivalent for your RDBMS)
data items:
[num]


Query:  Query3
Query2 inner join num on 1=1
data items:
[itemnumber]
[num]


Query:  Query4
Query3 inner join Query1 on itemnumber = itemnumber
data items:
[itemnumber]
[num]
[htscode] =
case [num]
  when 0 then [htscode00]
  ...
  when 10 then [htscode10]
endfilter:  [htscode] is not null

One of my customers is asking for the following:

They want the data in one of the database tables to be stacked vertically in a list report. The database table screen shot is pic1.jpg and as you can see the hts codes are in column/horizontal fashion with a lot of NULL values displayed.

The way they want the data displayed in the report is in pic2.jpg, where the item is listed with the hts codes stacked vertically.

The problem is this... they don't want to see the NULL/blank values. There are 11 possible hts codes and many of them will be NULL. They don't want to see 11 lines in the excel export for item number 10009000, with 1 row of data and 10 blank/NULL rows under it. They only want to see 1 row for item 10009000.

Same for item number 20002410. They only want to see the 5 hts codes in the column in excel, not the 6 blank extra values.

I've tried numerous ways to get this to work...

1) I tried creating variables in the report for the NULL values, then inserted a table in the hts code column of the list report with singletons for each hts code. I added a render variable on the singleton and added a style variable to the table row (box type = None) so the NULL values wouldn't show... that didn't work.
2) I created some custom SQL using a CTE and filtered out the NULL values in the final select statement and joined that custom SQL query to the main query by item number. That got me where I needed to be with stacking the hts codes vertically and not displaying the NULL records, BUT, I ran into an issue where the counts/amounts in the report duplicated because any item number with more than 1 hts code would produce more than 1 item in the join, causing the duplication.

Is what my customer wants possible? If so, how? Thank you!

If you export it as a CSV and open the CSV in an editor such as notepad++  what does the data look like?

Quotebut now the values come through as scientific notation,

In Excel or Cognos?

Hi all,


i'm in the situation that I need to export an excel file from C11 that does not observe my system's settings for number formatting. I.e., my PC is setup in German, so decimal delimiter comma and thousands delimiter point, but I need to export a number column with decimal delimiter point and thousands delimiter none.

I know that I can pretty much forget plain data formatting within Cognos; if excel recognizes the column as number, it ignores whatever cognos formatting is there and applies its own. So I thought I can just cast the value as varchar(99), but now the values come through as scientific notation, e.g., 182.144 becomes 1.82144E+02.

Is there any way I can get what I need?

Quote from: jackson.eyton on 16 Apr 2024 02:39:19 PMYou certainly can use the credentials method as specified for 11.2.4, ...

...(I'm forced to use powershell, please... someone help me...)...

I put my code between calls to these functions.  It asks for my user name and password when I run it.  Using a CA API key could improve this.  It should probably require the namespace name as a parameter rather than hard-coding it.  But I have only one namespace, so this works for me.

function start-CognosSession {<#
    .SYNOPSIS
    Start a Cognos REST API session.
 
    .DESCRIPTION
    docs at http://<cognos_analytics_server>:<port>/api/api-docs
    Asks for user name and password
    Exits on empty password

    .PARAMETER serverName
    Cognos server to use.
 
    .EXAMPLE
    start-CognosSession "CognosServer.company.com"
 
    #>
    [CmdletBinding()]
    param(
        [parameter(position=0, mandatory=$true)]
        [PSCustomObject]$serverName
    )
    # Write-Host "start-CognosSession start"
    $CognosSession = [PSCustomObject]@{}

    $protocol = "https"
    $port = "9300"
    $uriBase = "$protocol`://$serverName`:$port"
    $contentType = "application/json; charset=utf-8"

    $CognosSession | Add-Member -MemberType NoteProperty -Name 'protocol' -Value $protocol
    $CognosSession | Add-Member -MemberType NoteProperty -Name 'serverName' -Value $serverName
    $CognosSession | Add-Member -MemberType NoteProperty -Name 'port' -Value $port
    $CognosSession | Add-Member -MemberType NoteProperty -Name 'uriBase' -Value $uriBase
    $CognosSession | Add-Member -MemberType NoteProperty -Name 'contentType' -Value $contentType
   
    $userNamespace = "MyNamespaceName"
    $userName = Read-Host "User Name"
    $UserPwd = Read-Host "Password" -AsSecureString
   
    $err = $false

    try {
        $userPassword = ConvertFrom-SecureString -SecureString $UserPwd -AsPlainText
    }
    catch {
        Write-Host "Empty password.  Quitting."
        $err = $true
    }

    if (!$err) {
        $bdy = [PSCustomObject]@{}
        $paramarray = @()
        $param = [PSCustomObject]@{}
            $param | Add-Member -MemberType NoteProperty -Name 'name'  -Value "CAMNamespace"
            $param | Add-Member -MemberType NoteProperty -Name 'value' -Value $userNamespace
            $paramarray += $param
        $param = [PSCustomObject]@{}
            $param | Add-Member -MemberType NoteProperty -Name 'name'  -Value "CAMUsername"
            $param | Add-Member -MemberType NoteProperty -Name 'value' -Value $userName
            $paramarray += $param
        $param = [PSCustomObject]@{}
            $param | Add-Member -MemberType NoteProperty -Name 'name'  -Value "CAMPassword"
            $param | Add-Member -MemberType NoteProperty -Name 'value' -Value $userPassword
            $paramarray += $param
        $bdy | Add-Member -MemberType NoteProperty -Name 'parameters' -Value $paramarray
        $body = ConvertTo-Json $bdy
        $uri = "$uriBase/api/v1/session"
       
        $a = Invoke-RestMethod `
                -Uri $uri `
                -Method Put `
                -contentType $contentType `
                -SessionVariable "Session" `
                -Body $body
       
        # next line throws error, but retrieves a XSRF-Token cookie
        Write-Host "Ignore the next error.  The next line throws an error, but retrieves a XSRF-Token cookie."
        $a = Invoke-RestMethod -Uri $uri -Method Get -contentType $contentType -WebSession $Session
        $CognosSession | Add-Member -MemberType NoteProperty -Name 'Session' -Value $Session -TypeName 'Microsoft.PowerShell.Commands.WebRequestSession'
       
        $Cookies = $Session.Cookies.GetCookies($uri)
        $XSRFTOKEN = $Cookies["XSRF-TOKEN"].Value
       
        [System.Collections.IDictionary]$headers = @{
            'x-xsrf-token' = $XSRFTOKEN
            'accept' = 'application/json'
            'Cache-Control' = 'no-cache'
        }
       
        $CognosSession | Add-Member -MemberType NoteProperty -Name 'Headers' -Value $headers

        $CognosSession
    }
    else {
        $null
    }
    # Write-Host "start-CognosSession end"
}

function stop-CognosSession {<#
    .SYNOPSIS
    End a Cognos REST API session.
 
    .DESCRIPTION

    .PARAMETER CognosSession
    CognosSession object created by start-CognosSession
 
    .EXAMPLE
    end-CognosSession -CognosSession $CognosSession
 
    #>
    [CmdletBinding()]
    param(
        [parameter(position=0, mandatory=$true)]
        [PSCustomObject]$CognosSession
    )
    # Write-Host "stop-CognosSession start"
    # log out
    $uri = "$($CognosSession.uriBase)/api/v1/session"
    Invoke-RestMethod -Uri $uri -Method Delete -WebSession $CognosSession.Session
    # Write-Host "stop-CognosSession end"
}
start-CognosSession returns an object with environment parameters, so I can use that later, like this...

$CognosSession = start-CognosSession -serverName $serverName
$uriBase     = $CognosSession.uriBase
$protocol    = $CognosSession.protocol
$serverName  = $CognosSession.serverName
$port        = $CognosSession.port
$Session     = $CognosSession.Session
$headers     = $CognosSession.Headers
$contentType = $CognosSession.contentType
$uri = "$uriBase/api/v1/content"
$content = Invoke-RestMethod -Uri $uri -Method Get -Headers $headers -contentType $contentType -WebSession $Session
$content.content | Format-List
stop-CognosSession -CognosSession $CognosSession


I somehow missed this thread.  Maybe could have helped speed my learning...

I can't get this working in 12.0.4 (didn't try in 11.2.4).  My problem is a timeout.  I am using an Active Directory external directory namespace with probably 14,000 users and many many groups.  I'd guess only users are being listed by this call because this seems to be about user profiles.  But 14,000 seems to be too much.  Although, I can get all 14,000 users and count them using Get-ADUser in PowerShell in about 12 seconds.  So I don't know where the slowdown is using the Cognos REST API.

If patternghost is still watching this thread...  How many users in the directory namespace where you had success?

I'm not seeing in the thin documentation IBM provides...  Is there an option to restrict the number of items returned per call, and iterate through to the next group of results?  Maybe something like:

$uriBase/api/v1/users?identifier=$namespace&top=$top&skip=$skip
I see in 12.0.x there is a new option under Accounts:  /namespace_folder/{id}/items

That looks promising.  I should be able to use...
$uriBase/api/v1/namespace_folder/$namespace/items?limit=$limit&offset=$offset...to limit my call to something of a reasonable size that should return in a reasonable time.

But...

id is required and is also labeled Folder id.  But nowhere is "Folder id" defined.  I tried using my namespace name (which has the appearance of a folder in the Security tab in Cognos Administration) and got:

Invalid id format.

Is anyone here aware of any actual documentation for the Cognos Analytics REST API?

Quote from: patternghost on 27 May 2021 09:56:54 AMAfter trial and error, I found that if I send /users?identifier=<namespace> will return all users in the namespace.  I can work with that.

Can you confirm this still works? I cant seem to get this to work on 11.2.4

My idea is that you have to use all methods of marketing. One might be better than the other but all channels should be used. There are companies who sell information of which company uses which software. Dun & Bradstreet is one of them. Global Software Leads is another. YouTube and other media can also be used. Users forums also. 

I would think that "../images/" leads to <CA Install>\images.  But that wouldn't work because images should be in <CA Install>\webcontent\bi\images.

I remember Cognos having trouble with images years ago.  I'm not 100% certain how (or if) I completely fixed it.  But it seems that presenting images in PDF and XLSX outputs is what the new Image Service is all about.  Have you tried that?