Query to extract users and groups from content store

select cn.NAME as GRUPPE, op.NAME as BENUTZER

from cmreford1 ro

inner join cmobjnames cn on ( cn.CMID = ro.CMID  )

inner join cmobjprops33 op on ( op.CMID = RO.REFCMID )

--WHERE OP.NAME LIKE '%Martins-Volpi%'

Installing Cognos Go Office

 Installing Cognos Go Office for MS Office 2016

1. Copy installation package to your workstation

2. Run the application ‘issetup’ with default settings:

3. After installation open Excel and a new workbook

4. Go to tab ‘IBM Cognos’

5. Go to ‘Options’

6. Add Cognos production connection link:

http://xxxxxxxxxxxxxxxx/cognos/cgi-bin/cognosisapi.dll

You can also Test Connection

7. Save the link

Framework manager - BME-EX-0047 unable to read preferences for the requested interface.

Cognos Framework manager error:

BME-EX-0047 unable to read preferences for the requested interface. please use another repository and/or ProjectAccess Interface.

This is possibly due to corrupt bmt.ini file in configuration directory.

Solution:

Placed the bmt.ini file into configuration directly from different client’s installation location, and renamed the original file.

In distributed cognos system.
Gateway, bi server would be in diffrent servers / machine.
if you faced this issue in FM server then go to other Cognos installed server like gateway server.
Copy bmt.ini file from gateway server and come back to configuration folder FM installed server.
Replace bmt.ini file or take back of file and copy extracted file.
Copy this correct bmt.ini file to all Cognos installed server.

Cheer.

timestamp to date function

timestamp to date function:

cast([Audit].[Threshold Violations].[Time stamp], date) = current_date

Running a Report via Trigger

Running a Report via Trigger


https://www.youtube.com/watch?v=yhV8Q5SeTSU

Cognos audit package name conver to readable format :

Cognos audit package name conver to readable format :


SUBSTRING(
SUBSTRING([Audit].[Run Reports].[Package]
,CHARINDEX('=',[Audit].[Run Reports].[Package]
)+2,1000)
,1,
CHARINDEX(''']',SUBSTRING([Audit].[Run Reports].[Package]
,CHARINDEX('=',[Audit].[Run Reports].[Package]
)+2,1000)))

SQL Server with Cognos Year, Month, Day convertion

SQL Server with Cognos Year, Month, Day conversion

Year
convert(Char(4),Year(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))


Month

case when convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))<10 then
'0'||convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))
else convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))
end

Day

convert(Char(8),Day(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))