Building reports across multiple cubes and data sources

There are many situations where you might need to build a report off multiple cubes. Sometimes you might need a cube for some parts of a report, and a relational source for another. Relational sources are easy, but how can you build prompts in queries from multiple cubes?

The first thing to do is to analyse the members in your cubes. Different cubes have different member structures.

Let’s take a look at random member unique names as they appear in different databases.

Note, I don’t have Essbase listed here, simply because I don’t have it installed on my laptop.

SSAS [Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&[2011]]&[3]]]
DMR [Sales (analysis)].[Time].[Time].[Month]->[Time].[2011].[20111].[201103]
TM1 [Reporting].[Product].[Product].[Level1]->:[TMR].[Product].[Product].[987]
PowerCubes [Cube].[Date].[Date].[Date]->:[PC].[@MEMBER].[20140101-20140101]

As you can see, MUNs are logically laid out. Assuming the MUNs are consistent, we can use these to build our queries.

Dates and Months

Let’s say we have a date prompt. In the DMR we need to use a date MUN, but in SSAS we need to use the month from it. Using various macro functions, and a few tricks, it will be easy to build the queries.

In the SSAS example above, the year and month values are split. So we need to extract those values from the selected date.

First we need a way to convert the selected date to a timestamp, because the macro date functions all need timestamps and not dates. Using dates would be too easy.

#toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z')#

toUTC converts the specified string to a 0 point timezone. The T and Z shortcuts in the macro are simple shorthand to fill out the timestamp string. Using toLocal converts the string to the system timezone, which is dangerous for something like this as time-zones may cause inconsistent results in the query. So don’t use it.

Once we have the date in timestamp format, constructing the MUN is simple.

#
'[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'YYYY')
+']]&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'mm')
+']]]'
#

Hint – wrap the macro function in sq() so you can see the expression it’s building. For example:

#
sq(
'[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'YYYY')
+']]&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'mm')
+']]]'
)#

becomes

[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&[2011]]&[03]]]

DMR is actually a little easier. Despite the complex MUN above, we can actually reduce it to a single key. Note, this only works when the key is UNIQUE in the hierarchy. If the key appears multiple times with different parents, you will have to construct the full path.

Consider this example: [Cube].[Dim].[Hier].[Level]->[Product Group A].[Product B].[Red] Red may not be unique, so in order to get the correct member, the entire path with all of the pertinent keys would need to be included.

#'[Sales (analysis)].[Time].[Time].[Month]->['+timestampMask(toUTC(prompt('date','date','2011-04-01')+'T00:00:00Z'),'yyyymm')+']'#

The generated month MUN doesn’t match the MUN from the cube, but it will still work. If you validate in Cognos, you’ll get a warning like this:

XQE-WRN-0054 When the data source is queried for member "[Sales (analysis)].[Time].[Time].[Month]->[201104]" it returns member "[Sales (analysis)].[Time].[Time].[Month]->:[OL].[Sales (analysis)_Time].[Time].[Time].[2011].[20112].[201104]". It may not be the member you expect to see. query: "Report" dataItem: "SelectedMonth"

which is remarkably coherent for a Cognos error.

TM1 also lets you reference any member by simply calling its unique key, even if that member has different parents (possibly due to a multi-root hierarchy). In that case, using family functions may not work as expected.

In those cases we are constructing the member from scratch using the key. But what happens if you have a model that isn’t built using the keys as the underlying code? So often I see cubes where the caption of the member also works as the key. First you should smack the cube developer over the head and get them to fix the model.

The problem is that it may be impossible to switch the code of a level to a key, especially if the cube is already in a production environment. In those cases there are still a few possible solutions. One is to use queryValue, so long as your package also contains a relational model. You could use that to run a lookup to get the caption

queryValue(table.description,table.key = prompt('selectedKey','token'))

Older versions of Cognos, without the queryValue macro function can get the same effect by using parameter maps.

$productNameLookup(prompt('productKey','token'))

If the cube does have the key as a member property, you could use the filter function. In general I dislike this solution, and would bend over backwards to avoid it. Filter loops over every member in the referenced set, and if your level contains millions of members, this can be incredibly slow. The solution would be something like:

filter([cube].[dimension].[hierarchy].[level],[cube].[dimension].[hierarchy].[level].[keyProperty] = #prompt('selectedKey','token'#)

Ultimately by constructing MUNs using macros, or filtering on properties, you can safely query multiple cubes or relational models against the same keys and dates.

IBM Cognos Analytics 11.1.4 Fix List

November 7 EDIT: Rob mentioned below that IBM is re-releasing 11.1.4 to fix some drill through issues:

I just got a note on an open IBM Ticket: “We’d also like to inform you that CA 11.1.4 has been re-released today with additional fixes not included in the initial release of 11.1.4 pertaining to Drill Through type issues. We are encouraging customers download and update with the 11.1.4 re-release.”

Thank you Rob!

Some good news! I’ve heard a rumor that IBM is considering publishing the fix lists again. I delayed a couple of weeks in case they were planning on doing it for the 11.1.4 release, but no luck so far. Hopefully by 11.1.5 they’ll have their ducks in a row and I won’t have to do this again.

Without further adieu, the list.

1 PH17738 PH17738: BLANK SCREEN IS PRESENTED WHEN TRYING TO UPDATE THE TENANT INFORMATION IN COGNOS 11.1.3
2 PH12714 PH12714: SELECT VALUE PROMPT CHOICES MISALIGNED IN IE11
3 PH07575 PH07575: UNABLE TO GET OPTIONS FOR A REPORT SHORTCUT AS A RESULT OF SEARCH
4 PH16123 PH16123: DASHBOARDS WIDGETS ARE NOT BEING FILTERED WHEN USING SUBSETS
5 PH15909 PH15909: REPORT URL WITH PROMPT=FALSE WILL STILL PROMPT IN 11.0.13
6 PH16007 PH16007: FLY OUT PANEL FOR TENANT SETTINGS AND PROPERTIES IS EMPTY FOR ALL OBJECTS
7 PH16164 PH16164: THE CAST OF A TIMESTAMP IN THE FILTER IS CAUSING LOCAL PROCESSING WITH GOOGLE BIGQUERY
8 PH14680 PH14680: DASHBOARD AGAINST A PA CUBE IS THROWING A LOT OF XQE ERRORS
9 PH16489 PH16489: PROMPT REPORT RUN IN BACKGROUND THROWS TYPE NULL ERROR IN EXCEL
10 PH16333 PH16333: INVALID USE OF KEYWORD DISTINCT
11 PH16433 PH16433: UNABLE TO NEST DATA ITEMS IN CHART TYPE CLUSTERED BAR CHART
12 PH16712 PH16712: UNABLE TO USE CAST TO CONVERT A TIMESTAMP TO DATE IN GOOGLE BIGQUERY
13 PH16716 PH16716: NAVIGATE THROUGH ?TEAM CONTENT?,HOWEVER, CLICKING ON THE CUSTOM FOLDER FROM THE LEFT BAR, NO CONTENTS ARE …
14 PH16504 PH16504: ENABLE CAST OF TIMESTAMP TO A DATE IN BIGQUERY TO REDUCE DECOMPOSITION
15 PH16215 PH16215: RE-WRITE LITERAL USING EMBEDDED QUOTE SO THAT BIGQUERY WILL ACCEPT IT VS PARSER ERRORS THEIR DRIVER/SERVER …
16 PH12604 PH12604: XQE-PLN-0115 AN INVALID VALUE WAS ASSIGNED TO PROMPT
17 PH12238 PH12238: REDUCE TIME FOR THE FIRST REPORT RUN
18 PH12226 PH12226: INCORRECT TRANSLATION INTO SWEDISH IN THE FORMAT DIALOG WINDOW WHEN EDITING A DASHBOARD
19 PH12222 PH12222: INCORRECT THOUSANDS SEPARATOR IN DASHBOARD SUMMARY WIDGETS WHEN USING SWEDISH LANGUAGE
20 PH12322 PH12322: USERS WHO HAVE NO ABILITY TO ADD COLUMNS TO THE FILTER DOCK STILL SEE THE MESSAGE "DRAG AND DROP DATA HERE TO …
21 PH12324 PH12324: SUMMARY VISUALIZATION IS EMPTY WHEN DATA USED AN APOSTROPHE (‘)
22 PH12300 PH12300: VALUES OF RUNNING-COUNT ARE INCONSISTENT BETWEEN CQM AND DQM WHEN QUERIES ARE JOINED.
23 PH11880 PH11880: ADVANCED SETTING LARGE WORKSHEET CAUSES BLANK CELLS WHEN GROUP REPEATING CELLS IS DISABLED
24 PH11676 PH11676: AGGREGATE ADVISOR AGAINST A GERMAN CUBE MODEL WILL FAIL
25 PH12317 PH12317: QUERY CANNOT BE PLANNED WITH 4 OR MORE CASE WHEN
26 PH11899 PH11899: THE SCHEDULE DOES NOT RUN WHEN RUN ONCE IS CLICKED IN MY SCHEDULES AND SUBSCRIPTIONS
27 PH12617 PH12617: EMPTY CONTEXT BASED MENU FOR PACKAGE WHEN NOT HAVING READ PERMISSION
28 PH05908 PH05908: SEARCH IS UNABLE TO FIND SOME OBJECTS
29 PH04487 PH04487: COPYING OR DELETING MULTIPLE REPORTS FROM THE C11 PORTAL GENERATES ONE OPERATION IN THE COGAUDIT.LOG
30 PH15886 PH15886: ORACLE STORED PROCEDURE IMPORTS WITH NO INPUT PARAMETERS IN DQM AND NOT AT ALL IN CQM IN 11.1.3.
31 PH13815 PH13815: DPR-ERR-2079 FIREWALL SECURITY REJECTION. YOUR REQUEST WAS REJECTED BY THE SECURITY FIREWALL.
32 PH11434 PH11434: XQE-DAT-0001 ERROR OCCURRED WHEN ADDING MYSQL’S STORED PROCEDURETO A QUERY SUBJECT AND TESTING THIS IN FM.
33 PH11506 PH11506: REPORT FAILS WITH JAVA.LANG.ARRAYINDEXOUTOFBOUNDSEXCEPTION XVECTORCURRENTDATE
34 PH11633 PH11633: INVALID IDENTIFIER WITH SNOWFLAKE USER-DEFINED FUNCTION
35 PH15536 PH15536: THE TEXT ABOVE AND BELOW THE PROMPT TELLS THE USER TO SELECT A PACKAGE, WHEN IT SHOULD TELL THEM TO SELECT A …
36 PI93412 PI93412: JAVA.LANG.NULLPOINTEREXCEPTION WHEN ATTEMPTING TO UPDATE OBJECT IN FM WITH SALESFORCE DATASOURCE THAT HAS BEEN …
37 PI82083 PI82083: SEARCHING FOR A STRING IN COGNOS 11.0.6 RETURNS AN UNEXPECTED SET OF RESULTS IF CONTENT LANGUAGE IS CHANGED
38 PI93676 PI93676: REPORT SET TO "RUN WITH FULL INTERACTIVE" = "NO" RUNS IN THE NEWVIEWER WHEN RUN IN HTML BACKGROUND.
39 PH05974 PH05974: SMTPRULES FEATURE DOES NOT WORK IN COGNOS ANALYTICS
40 PH03165 PH03165: BROWSER REFRESH (F5) CAUSES THE REPORT TO OPEN IN EDIT MODE
41 PH10335 PH10335: ENRICH PACKAGE WITH MULTIPLE DATA SOURCES
42 PH10380 PH10380: CM JAVA CORING WHEN ATTEMPTING TO SAVE OR RELINK DATA SOURCES
43 PH10774 PH10774: "AMBIGUOUS CONNECTIONS" IN A DATAMODULE (NATIVE SQL) WHEN USING MULTIPLE DATASOURCE CONNECTIONS
44 PH10161 PH10161: ALIAS ATTRIBUTES OF TOP LEVEL MEMBERS DO NOT SHOW IN REPORT
45 PH10546 PH10546: SDS-CNC-0408 WATCH RULES FAIL TO SEND EMAILS BUT REPORTS AND SCHEDULES CAN BE SENT
46 PH10228 PH10228: UNABLE TO EDIT TEXT FILTERS IN QUERY STUDIO WHEN MORE THAN ONE DATA SOURCE CONNECTION DEFINED
47 PH08719 PH08719: ERROR: WE CAN’T CREATE YOUR SCHEDULE RIGHT NOW. TRY AGAIN LATER.WHEN CREATING A SCHEDULE USING GOOGLE OIDC …
48 PH10353 PH10353: RUNNING A SIMPLE REPORT WITH MAPS (11.1.X, 11.0.X, OR LEGACY) WILL CAUSE IE TO CRASH AND THROW OUT OF MEMORY …
49 PH07937 PH07937: WITH RSVP.PARAMETERS.LOG SET TO TRUE, SOME REPORTS DO NOT LOG ANY PARAMETERS IN THE AUDIT STORE TABLE …
50 PH11678 PH11678: POWERCUBE DATA TAKES LONGER TO LOAD IN A DASHBOARD WHEN CONTENT LANGUAGE SET TO SPANISH
51 PH15213 PH15213: XQE-PLN-0098 USING MONTH FUNCTION IN FILTER AGAINST SQLSERVER AND DQM
52 PH12454 PH12454: SEA-GEN-0001 ERROR IS SHOWN IN COGNOSSERVER.LOG WHEN CA11.1.2 INSTALLED ON ENGLISH OS WITH FRENCH REGIONAL …
53 PH15670 PH15670: GOOGLE BIGQUERY CAST EXPRESSION SUPPORTS A TARGET TYPE OF STRINGBUT NO PRECISION SPECIFICATION
54 PH15635 PH15635: MISSING VISUALIZATION PROPERTIES
55 PH14996 PH14996: EXPRESSION LIKE ‘%20’ IN A DATA MODULE CALCULATION IS CONVERTED TO LIKE ‘+’
56 PH02552 PH02552: EMPTY LINES ARE TRUNCATED FROM THE REPORT DESCRIPTION() USED IN A RICH ITEM TEXT IN THE REPORT EXPRESSION
57 PH02628 PH02628: ERROR WHEN RUNNING A REPORT TO EXCEL FROM PORTAL WHEN REPORT CONTAINS JAVASCRIPT IN CUSTOM CONTROL
58 PI92696 PI92696: ODD AND INCONSISTENT THEME CUSTOMIZATION BEHAVIOR FOR NON-SYSTEMADMINISTRATORS.
59 PI99418 PI99418: REPORT NAME MISSING UNDER MANAGE –>ACTIVITIES–>CURRENT WITH "RUNNING IN BACKGROUND UN-TICKED" IN CA 11.11
60 PH01474 PH01474: WONDER WHY "BLAH BLAH" IS IN PROPERTIES FILE <C11>/WEBAPPS/P2PD/WEB-INF/SCRIPTS/PROCESSORS/HISTORY.GROOVY
61 PI95562 PI95562: LICENSES FOR ANALYTICS USER COUNT TOWARDS ANALYTICS ADMINISTRATOR
62 PH10985 PH10985: ERROR THROWS BY A IF-THEN-ELSE EXPRESSION IN A ACTIVE LIST REPORT
63 PH11105 PH11105: IE CRASHING WITH OUT OF MEMORY ERRORS WHEN USING DASHBOARDS WITHMULTIPLE MAP VISUALIZATIONS
64 PH11029 PH11029: WHEN DRILLING UP IN DASHBOARD THE FILTER REMAINS.
65 PH08971 PH08971: DATA MODULE BASED ON A DATA SET AND A PACKAGE IGNORED THE DECIMAL AND THOUSAND DELIMITER
66 PH06861 PH06861: CJAP – JDBC SAMPLE DOES NOT WORK IN CA 11.1 – LOGON ATTEMPTS RESULT IN AAA-SYS-0001
67 PH12452 PH12452: CLOSING OPEN REPORTS IN A TAB BRINGS USER BACK TO WELCOME PAGE INSTEAD OF SET HOMEPAGE
68 PH13841 PH13841: SQLSERVER STORED PROCEDURE IN DATABASE WITH DASH/HYPHEN IN NAME FAILS TO TEST IN DQM
69 PH12812 PH12812: DASHBOARD VISUALIZATION SET WITH REFRESH AUTOMATICALLY IS NOT UPDATED WITH LATEST DATABASE DATA
70 PH12556 PH12556: CANNOT HAVE DATA SOURCES AND SECURITY OBJECTS SHARING THE SAME NAME
71 PH12706 PH12706: WHEN A FILTER CONDITION IN A DASHBOARD IS BASED ON A DATA ITEM CONTAINING AN APOSTROPHE ( ‘ ) NO DATA IS …
72 PH12661 PH12661: RECENT IE UPDATE IS CAUSING SCRIPTING ERRORS WHEN LAUNCHING FRAMEWORK MANAGER
73 PH14844 PH14844: ‘TYPEERROR’ OCCURS BY PRESSING ‘ENTER’ KEY AFTER SELECTING ANY OPTIONS ON 11.1.3 CUSTOM FILTER.
74 PH14861 PH14861: UPGRADE FROM 11.0.13 TO 11.1.3 REPORTS FAIL WITH XQE-GEN-0005 INTERNAL ERROR FOUND: COM.COGNOS.XQE.AST.SQL….
75 PH14724 PH14724: DATA CONTAINER ERROR WHEN TRYING TO COPY VISUALIZATION WHICH CONTAINS A DRILL THROUGH DEFINITION
76 PH14721 PH14721: USING INCLUDE OR EXCLUDE FILTER ON A CROSSTAB REPORT OUTPUT RETURNS XQE-PLN-0004 FOUND AN INTERNAL ERROR ON …
77 PH14644 PH14644: DRILLTHROUGH CONTAINED IN A SINGLETON FAILS WITH TYPEERROR USINGIE11 BROWSER
78 PH14537 PH14537: VIEW TABULAR DATA FAILS FOR A QUERY WHICH CONTAINS A DATA ITEM THAT INCLUDES PARAMETER DEFINITIO
79 PH12842 PH12842: SOURCE REPORT FOR PDF IN A DRILL THROUGH SETUP GOES BLANK FOR SAVED OUTPUT VERSION
80 PH13253 PH13253: A BATCH JOB WITH BURST REPORTS EXECUTE IN PARALLEL WILL INTERMITTENTLY FAIL WITH ERROR XQE-GEN-0005
81 PH12909 PH12909: SELECT AND SEARCH PROMPT DOES NOT RESPECT RUN WITH FULL INTERACTIVITY SETTINGS IN BACKGROUND
82 PH12862 PH12862: ALIGNMENT OF SELECT AND SEARCH PROMPT WAS CHANGED AFTER APPLYINGFP1
83 PH12849 PH12849: BACKGROUND COLOR OF VALUE PROMPT IN PROMPT PAGE CHANGES WHEN RUNIN BACKGROUND.
84 PH11914 PH11914: IFRAME WITH PARAMETER AND VALUE SET DOES NOT AUTOMATICALLY RUN THE REPORT.
85 PH13018 PH13018: FILTER FUNCTIONALITY AND RENDERING IS INCORRECT
86 PH12108 PH12108: UNABLE TO CONVERT EXPLORATION TO CUSTOM REPORT IN PA FOR EXCEL AGAIN CA 11.1.2
87 PH13218 PH13218: QUERY STUDIO MODEL FILTER PROMPT APPEARS REPEATEDLY AFTER APPLYING IF 1010
88 PH13087 PH13087: UNABLE TO COPY ASSETS BETWEEN EXISTING REPORTS
89 PH13111 PH13111: CONNECTION TO PA CUBE LOST.
90 PH13145 PH13145: VECTORIZATION IN REPORTS.
91 PH13142 PH13142: NUMEROUS JAVA.UTIL.ABSTRACTCOLLECTION.ADDALL ERRORS
92 PH13214 PH13214: REPORT RUN TO EXCEL FORMAT WHICH HAS BEEN SAVED TO THE FILE SYSTEM CONTAINS END OF FILE MARKER AT 45,000 ROWS.
93 PH12523 PH12523: MULTI SELECT VALUE PROMPTS SHOWS USING 11.X STYLE WHEN REPORT SET TO 10.X STYLE
94 PH12705 PH12705: THE LINE SPACING ON MULTI-VALUE, VALUE PROMPTS IS INCONSISTENT BETWEEN COGNOS ANALYTICS 11.1.1 AND COGNOS …
95 PH13187 PH13187: THE OPTION ( SHOW MISSING DATA AS GAP) IN LINE CHARTS DOESN’T WORK
96 PH12880 PH12880: RACF PROVIDER IS NO LONGER AVAILABLE IN COGCONFIG NO RACF PROVIDER ON AIX AND ZLINUX.
97 PH13237 PH13237: CANNOT SEE "CREATE SHORTCUT" OPTION WITH ONLY "READ" PERMISSIONS
98 PH12937 PH12937: "ORA-00942: TABLE OR VIEW DOES NOT EXIST" OCCURS EVEN THOUGH THERE IT IS.
99 PH13706 PH13706: XQE-PLN-0004 FOUND AN INTERNAL ERROR ON NODE ‘V5EDGE’. GETTYPE()? (MIGRATED REPORTS FROM 10.2.2 > 11.0.13)
100 PH13411 PH13411: XQE-PLN-0174 THE COLUMN C2 MUST BE CONTAINED IN AN AGGREGATE FUNCTION OR THE GROUP BY CLAUSE.
101 PH13710 PH13710: CALCULATION IN DATA MODULE ARE NOT DISPLAYED WITH CORRECT HEADERAND DATA FORMAT
102 PH12954 PH12954: DSS-GEN-0002 UNABLE TO FIND COLUMN <> IN DATA SET <>. ERROR WHENTRYING TO ADD ANOTHER VIEW TO A DATA MODULE.
103 PH17134 PH17134: PROMPTS IN CUSTOM CONTROL NOT WORKING PROPERLY WHEN REPORT RUN TO EXCEL FROM THE PORTAL
104 PH11902 PH11902: HTML COMPARE IN LIFECYCLE MANAGER GIVES FALSE POSITIVES. IT DOESNOT DETECT ALL FONT OR FORMATTING DIFFERENCES
105 PH11900 PH11900: OPTION TO RUN REPORTS IN DQM DOES NOT WORK IN COGNOS ANALYTICS LIFECYCLE MANAGER
106 PH12841 PH12841: CONTENT MANAGER WITH GATEWAY INSTALLATION OF 11.1.2 FAILS TO RESTART
107 PH11875 PH11875: REPORT PROMPTS IN LIFECYCLE MANAGER DO NOT FUNCTION PROPERLY WHEN THE HOSTNAME FOR THE COGNOS ANALYTICS SERVER …
108 PH13636 PH13636: NULL POINTER EXCEPTION USING TM1 CONNECTION TO PA
109 PH12473 PH12473: XQE-PLN-0307 REFERENCE TO MULTIPLE CUBES AND NAMESPACES
110 PH13432 PH13432: POWER CUBE PACKAGE ERRORS OUT WHEN DISPLAYING METADATA IN DASHBOARD
111 PH13678 PH13678: XQE-GEN-0005 FOUND AN INTERNAL ERROR FOR QUERY WITH MASTER DETAIL AND CROSS JOIN
112 PH13755 PH13755: XQE-PLN-0296 THE ARGUMENT AT POSITION 4 OF ‘MEMBER’ FROM THE DATA ITEM ‘DATAITEM’ CANNOT BE COERCED INTO A …
113 PH13699 PH13699: ERROR "XQE-GEN-0005 FOUND AN INTERNAL ERROR: JAVA.LANG.NULLPOINTEREXCEPTION" USING SHORTCUTS IN FM PROJECT …
114 PH13748 PH13748: !MAPSUCCESS ERROR WHEN USING PA SUBSET PROPERTIES NESTED IN A SINGLE EDGE XTAB REPORT
115 PH14622 PH14622: QUERY STUDIO NOT DISPLAYING CANCEL BUTTON
116 PH13430 PH13430: CUSTOM VIEW RE-SET TO DEFAULT WHEN CLOSING A REPORT VIEWED FROM A SHARED LINK
117 PH14496 PH14496: NEW URL PATH FROM FOLDER TO FILE SYSTEM DOESN’T WORK, THROWS CAFERROR IN CLASSIC VIEWER, INVALID URL ERROR IN …
118 PH14610 PH14610: SERIOUS SYSTEM INSTABILITY. CM-SYS-5173 CONTENT MANAGER FAILED..
119 PH14464 PH14464: JAVA.LANG.INDEXOUTOFBOUNDSEXCEPTION WHEN USING A SID/CM/RID URL IN R13
120 PH14145 PH14145: REFERENCEERROR IS DISPLAYED WHEN CHANGING NUMBER STYLE IN AUTHORING
121 PH14244 PH14244: AFTER DRILL THROUGH, TOGGLING A CONDITIONAL BLOCK IN TARGET REPORT CAUSES PROMPT PAGE TO SHOW
122 PH14257 PH14257: DIFFERENCE IN TIME TAKEN TO OPEN AN ANALYSIS
123 PH14108 PH14108: DASHBOARD DRILL THROUGH TO REPORT NOT APPLIED TO ALL TABS/ ALL WIDGETS
124 PH14236 PH14236: WHEN USING A PARENT MEMBER WITH SPECIAL CHARACTER (APOSTROPHE), THE CHILD MEMBERS ARE NOT DISPLAYED IN …
125 PH13991 PH13991: DATA TAG EDIT TO REMOVE AN OBJECT IS NOT DELETED
126 PH14065 PH14065: EXCEL 2007 DATA TRANSLATION INTO RUSSIAN IS WRONG IN OLD UI AREAS
127 PH14096 PH14096: CONTEXT MENU FOR THEMES AND EXTENSIONS DOES NOT OPEN SINCE IF1016 AND STILL EXIST IN IF1018
128 PH14165 PH14165: POOR PERFORMANCE IN PA USING REST APIS AFTER MUN CHANGES
129 PH14173 PH14173: UNABLE TO DRILL THROUGH TO TARGET REPORT WHEN FULL INTERACTIVITYIS SET TO NO. DPR-ERR-2079 FIREWALL SECURITY …
130 PH13944 PH13944: THEME WITH TEXT CONTAINING AN APOSTROPHE DOES NOT DISPLAY PROPERLY IN 11.1.2
131 PH13689 PH13689: WHITE SPACE ADDED TO THE TEAM CONTENT SLIDE-OUT PANE WHEN SWITCHING THE BROWSER TO FULL SCREEN
132 PH14052 PH14052: SPELLING MISTAKE IN DISPATCHERMESSAGES_DE.PROPERTIES
133 PH16022 PH16022: QUERY SERVICE RUNS OUT OF MEMORY WHILE EXECUTING THE PREDICT QUERY.
134 PH15855 PH15855: UNABLE TO COPY/PASTE A DETAIL FILTER TO ANOTHER REPORT WHEN USING 11.1.3.
135 PH15668 PH15668: COGNOS DOES NOT WORK FOR CAST FUNCTION DUE TO DQM IS SETTING WITH DECIMAL IS OFF.
136 PH15923 PH15923: UNABLE TO ADD CROSSTAB SPACE ON THE LEFT AND RIGHT OF AN EXISTING CROSSTAB SPACE WHEN GUIDED REPORT LAYOUT IS …
137 PH14614 PH14614: JOB DELIVERY EMAIL DOES NOT CONTAIN HTML OUTPUT IN THE BODY OF THE EMAIL. IT SENDS REPORT AS AN ATTACHMENT.
138 PH09780 PH09780: BACKGROUND REPORT CONTINUES TO RUN AFTER BEING CANCELLED IN DISTRIBUTED ENVIRONMENT
139 PH09785 PH09785: CROSS TAB SHOWS NO DATA WHEN FILTERED ON A STRING CONTAINING AN APOSTROPHE ‘ (SOURCE: DATA MODULE ON UPLOADED …
140 PH10061 PH10061: NUMBER AND SPECIAL CHARACTER SORT ORDER FOR COPY/MOVE IS DIFFERENT TO THE MAIN UI FLYOUT
141 PH10009 PH10009: IF A CUSTOM FOLDER NAME IS TOO LONG IT IS NOT DISPLAYED ON THE TOP OF THE LIST WHEN CLICKING ON CUSTOM FOLDER …
142 PH15235 PH15235: ERROR IN REPORT AFTER MIGRATING FROM CA 11.0.13 TO CA 11.1.1
143 PH14439 PH14439: DMR BASED DASHBOARD ERROR, WHEN PROMPT MACROS ARE USED IN THE DATA SOURCE AND SCHEMA DEFINITION IN FM.
144 PH15371 PH15371: ADDING A ROLE TO A CUSTOM FOLDER IN CUSTOMIZATION WILL CAUSE A VERY SLOW LOGIN PROCESS
145 PH15672 PH15672: DEFINED A CONCATENATION WHICH DQM IS PUSHING TO GOOGLE BIGQUERY,THE DB VENDOR REJECTS THE EXPRESSION
146 PH15539 PH15539: ‘RUN AS’ AND ‘NEW JOB’ PANELS ARE EMPTY IF EMBEDDED_CONTENT/PRELOGINSAMPLE.HTML HAS BEEN ACCESSED IN ANOTHER …
147 PH15064 PH15064: REPORT PRODUCE A CROSS JOIN IF A DATA MODULE IS USED WITH A PACKAGE AND UPLOADED FILE
148 PH15752 PH15752: DRILL THROUGH ON A VISUALIZATION FAILS WITH TYPEERROR WHEN USING"DO NOT PASS PARAMETER"
149 PH11990 PH11990: CREATING A JOB AND ADDING REPORTS TO BE SEND VIA EMAIL WITH A LINK TO THE REPORT, THE EMAIL CONTAINS "XHTML" …
150 PH10647 PH10647: DRILL-THROUGH TO SAME REPORT FAILS BY CM-CAM-4005 AFTER LOGON SESSION TIMEOUT IN LTPA SSO ENVIRONMENT.
151 PH11750 PH11750: DRILL THROUGH ISSUE (BAD PERFORMANCE OR EVEN ERROR) IN COGNOS ANALYTICS 11.0.13 AFTER UPGRADE FROM COGNOS BI …
152 PH05396 PH05396: REPORT APPEARS IN SEARCH RESULTS IF USER DOES NOT HAVE PERMISSION TO ACCESS FOLDER STRUCTURE AND REPORT
153 PH15418 PH15418: UNABLE TO DELETE, DOWNLOAD AND UPDATE ANY THEMES AFTER APPLYING 11.0.13 IF1019
154 PH15230 PH15230: ALL TEXTS UNDER VISUALIZATION CHART LEGEND OPTIONS ARE NOT TRANSLATED FOR NON-ENGLISH PRODUCT LANGUAGE
155 PH15270 PH15270: CUSTOMIZATION FOLDER NOT VISIBLE
156 PH15249 PH15249: OPEN TARGET REPORT FROM SOURCE REPORT, THE NAME DOES NOT CHANGE TO TARGET REPORT, IF THERE IS VIEW VERSIONS …
157 PH14988 PH14988: WHEN CANCEL REPORT FROM VIEW TABULAR DATA, COGNOS DIRECT BACK TOWELCOME PAGE INSTEAD OF CUSTOM DEFAULT HOMEPAGE
158 PH15046 PH15046: UNABLE TO RUN ./TRIGGER.SH FROM COMMAND LINE.
159 PH14995 PH14995: SLOW PERFORMANCE AGAINST PLANNING ANALYTICS WITH REPORT THAT CONTAINS MANY SIMPLE CALCULATIONS
160 PH08235 PH08235: EXTERNAL DATA WITH CQM PACKAGE CAUSE DATA ITEMS LIST AS UNDEFINED AND RE-PUBLISH THROUGH ERROR
161 PH08347 PH08347: SCHEDULING AN EVENT RETURNS – SAXEXCEPTION: INVALID ELEMENT
162 PH14997 PH14997: ADDING OR MODIFYING ADVANCED CONDITIONS ON CHARTS THROWS TYPENULL ERRORS
163 PH06701 PH06701: DELIVERY SERVICE(DLS) IS NOT CALLED WITHOUT RECIPIENT ON E-MAILTASK
164 PI99537 PI99537: QS REPORT PASSING DATE PARAMETER AS STRING IF PROMPT TYPE IS SELECT VALUE
165 PH11901 PH11901: IN A SCHEDULE THE RECIPIENT LIST WILL BE OVERLAPPING WHEN THE FOLDERS ARE IN TO MANY SUBFOLDERS
166 PH10890 PH10890: DRAGGING A TEXT ITEM TO A LAYOUT COMPONENT REFERENCE GIVE TYPEERROR: CANNOT READ CHILD PROPERTY ‘CHILDNODES’ …
167 PH11951 PH11951: XQE-DAT-0001 INVALID USE OF KEYWORD DISTINCT OR ALL WITH FUNCTION COUNT
168 PH11689 PH11689: SNOWFLAKE User Defined FUNCTIONS DO NOT APPEAR IN METADATA IMPORT
169 PH11865 PH11865: FILTER AND GROUPBY CLAUSE DROPPED FOR A COMPATIBLE QUERY MODE CONNECTION AGAINST VERTICA DATABASE
170 PH15144 PH15144: XQE-GEN-0005 NULLPOINTEREXCEPTION ERROR WHEN ITEMS HAVE DETAIL AGGREGATION = NOT APPLICABLE
171 PH12839 PH12839: HTML REPORT OUTPUT IS NOT WELL FORMED. IF YOUR REPORT USES "HTMLITEM" ELEMENTS ENSURE THAT THEY RESULT IN …
172 PH11992 PH11992: ORA-12704 CHARACTER MISMATCH WHEN USING STRING LITERAL IN ANY CONSTRUCT IN ORACLE DQM
173 PH12355 PH12355: XQE-DAT-0001 ORA-12704: CHARACTER SET MISMATCH WHEN RUNNING AUDIT REPORTS IN 11.0.13 FP1
174 PH13140 PH13140: XQE-GEN-0002 AN UNEXPECTED EXCEPTION OCCURRED: JAVA.LANG.NULLPOINTEREXCEPTION
175 PH16277 PH16277: DATA MODULE BASED DASHBOARD FAILS WITH ERROR ‘V5 SYNTAX ERROR FOUND IN EXPRESSION’ WHEN ADDING FILTER ON …
176 PH12308 PH12308: CNC-ASV-0050 ERROR WHEN USING THE CONDITION 1=1
177 PH11956 PH11956: ANALYSIS STUDIO CROSSTAB WITH A MEASURE WHICH INCLUDES A AGGREGATION RULE LAST BASED ON DIMENSION – ERROR
178 PH12265 PH12265: VERTICAL SCROLLBAR DOES NOT APPEAR WHEN APPLYING SECURITY FILTERS ON DATAMODULE
179 PH11682 PH11682: COMPLEX CALCULATION LOCALLY PROCESSED IN DQM
180 PH11326 PH11326: DB2 ERROR WHEN USING DATA ITEM VALUE AS COLUMN HEADER AND CROSS JOIN
181 PH12903 PH12903: DATA MODULE CUSTOM SQL VALIDATION FAILS WITH LARGE SQL QUERY WHEN USING A WEB SERVER
182 PH13208 PH13208: LEGEND TITLE IS VISIBLE IN PDF/EXCEL FORMAT EVEN THOUGH ITS BEEN DISABLED FOR VISUALIZATION
183 PH13677 PH13677: LIMITED FUNCTION SET RETURNS WARNING FOR TRUNC
184 PH16454 PH16454: XQE-PLN-0248 ERROR OCCURS IN A REPORT USING ?COUNT DISTINCT? ON THE ON-DEMAND TOOLBAR
185 PH13788 PH13788: SUMMARY ROW OF A TABLE VISUALIZATION IN A DASHBOARD DOES NOT CORRECTLY AGGREGATE CUSTOM CALCULATION COLUMN.
186 PH14117 PH14117: IN 11.1.2, REPORT STILL RUNS IN HTML USING SHARE LINK WHEN DEFAULT REPORT FORMAT IS SET TO PDF
187 PH09528 PH09528: COGNOS 11.1 R2 DOES NOT PRODUCE THE CORRECT OUTPUT WHEN TOP FUNCTION AND FILTER IS USED
188 PH15052 PH15052: USER WITHOUT UPLOAD FILE CAPABILITY CAN SEE DRAG AND DROP FILES,OPEN QUICK LAUNCH OR BROWSE SECTION ON HOME …
189 PH08789 PH08789: EMAIL BODY SHOWING AS NULL WHEN REPORT IS CONTAINED WITHIN A JOB
190 PH06703 PH06703: NO ERROR MESSAGE IF YOU SET AGENT ON EVENT STUDIO WITHOUT RECIPIENTS
191 PH11249 PH11249: GEN-ERR-0002 ERROR RUNNING REPORTS AFTER UPGRADE TO 11.0.13
192 PH10395 PH10395: WITH A SEARCH, USER CAN SEE CONTENT IN A RESTRICTED FOLDER
193 PH04636 PH04636: SAPBW: DIMENSIONS GROUPS CANNOT BE IMPORTED BECAUSE DYNAMIC QUERY MODE SET LANGUAGE TO EN-US
194 PH09674 PH09674: WHEN A USER HAS A LARGE NUMBER OF HTS ITEMS (12K) THE INBOX WIDGET IN WORKSPACE WILL RETURN A DIALOG BOX …
195 PH12268 PH12268: CREATING A DASHBOARD OFF A DATA MODULE REQUIRES A CLOSE OF THE DASHBOARD TO SEE DATA AFTER RELOAD
196 PH12807 PH12807: UNABLE TO CHANGE THE TITLE COLOR OF A DASHBOARD VISUALIZATION USING ‘FAVORITES’ LIST
197 PH16989 PH16989: WEBPAGE ERROR: UNABLE TO GET PROPERTY ‘XXXXX’ OF UNDEFINED OR NULL REFERENCE
198 PH11507 PH11507: REPORT FAILS WITH JAVA.LANG.ARRAYINDEXOUTOFBOUNDSEXCEPTION XVECTORCASTLONGTODOUBLE
199 PH16785 PH16785: DRILL THROUGH PASSING LARGE LIST OF VALUES
200 PI86287 PI86287: RQP-DEF-0326 ERROR (RUNNING/VALIDATING A REPORT WITH SQL QUERY OBJECT THAT FAILS) WHEN TESTING A USER-DEFINED …
201 PH08728 PH08728: WARNING POP-UP WHEN RUNNING INTERACTIVE REPORT AS A USER WITH NOREAD PERMISSION ON THE REPORT OR DATA MODULE
202 PH10334 PH10334: CREATING JOB WITH ‘SAVE THE REPORT AS A REPORT VIEW’ CLEARS OUT JOB STEPS IN U
203 PH13016 PH13016: COPY AND PASTE FROM ONE QUERY TO ANOTHER BETWEEN 2 REPORTS NOT POSSIBLE
204 PH11522 PH11522: UNNECESSARY OIDC LOGIN WHEN RUNNING A PROMPTED CLASSIC VIEWER REPORT IN THE BACKGROUND THAT RETURNS AN ERROR …
205 PH12184 PH12184: HTML report output is not well formed
206 PH13412 PH13412: LIMITED FUNCTION SET RETURNS WARNING FOR TO_CHAR
207 PH13697 PH13697: ADVANCED SETTING FOR WHITELISTING EMAIL DOMAINS
208 PH14388 PH14388: JAVASCRIPT ERROR POPS UP WHEN RUNNING REPORT WITH FULL INTERACTIVITY IN FIREFOX 68
209 PH15930 PH15930: INCORRECT DASHBOARD CALCULATION PREVIEW OCCURS REGARDLESS OF DASHBOARD, DATA, FUNCTION AND TEMPLATE TYPE.
210 PH15506 PH15506: JS ERRORS WHEN SELECTING DATA CONTAINER IN QUERY EXPLORER
211 PH06122 PH06122: USERS WHO HAVE RUN PERMISSION ON REPORT ARE ABLE TO OPEN IT UP IN EDIT MODE AFTER REFRESHING THE BROWSER WHILE …
212 PH15189 PH15189: CALCULATION USING A CASE STATEMENT WITH AGGREGATE MINIMUM ONLY RETURNS 1 VALUE IN LIST VISUALIZATION
213 PH14970 PH14970: EASY INSTALLATION CHANGE YOUR PASSWORD FUNCTIONALITY REMOVED WITH 11.1.X
214 PH07751 PH07751: ERROR DUE TO COGNOS SQL WITH WHERE CLAUSE
215 PH11141 PH11141: ERROR DESCRIPTION SHOWN IN CUBE DESIGNER SAYS COGNOS 10 INSTEAD OF COGNOS 11
216 PH14682 PH14682: NAVIGATION BAR SETTINGS CAUSES DISAPPEARANCE OF DASHBOARD WIDGETFILTER ICONS
217 PH09329 PH09329: DRILL TARGETS REFERRING TO REPORT ID INSTEAD OF REPORT REF PATH
218 PH11656 PH11656: NO DATA AVAILABLE WHEN DRILLING FROM DASHBOARD TO REPORT ON NULLVALUE

11.1.1: https://cognospaul.com/2018/11/27/ibm-cognos-analytics-11-1-1-fix-list/
11.1.2: https://cognospaul.com/2019/06/26/ibm-cognos-analytics-11-1-2-fix-list/
11.1.3: https://cognospaul.com/2019/07/22/ibm-cognos-analytics-11-1-3-fix-list/

CognosPaul’s Bag o’ Tricks!

CognosPaul’s Bag o’ Tricks!

Back in August I presented at BACon on the extensions that I wrote for PMsquare, and now I’m very happy to announce that we’re giving them away for free! We actually started giving them away for free at the conference, but I’ve been a bit too lazy busy to actually get around to writing this.

First you might ask, what are extensions? As I wrote about in a previous article extensions allow you to modify the UI, adding or removing features as you like. One of the best things about adding new features is using the internal JavaScript API in Report Studio, making it do what you want it to do instead of what IBM wants it to do.

One of the features of that article was an early version of the Clipboard Editor. The basic structure is there, but it’s been improved immensely since. My good friend Michael Hoggard and I worked on it to add a new feature, and to clean it up a little.

changes in clipboard editor

But that’s not all! I’ve also added another new and awesome feature. One of my biggest issues with building reports is testing out different prompt scenarios. We could run the report and manually set each prompt, but that can be incredibly time consuming. You could force Cognos to prompt you, by validating or by clicking on “view generated SQL/MDX” but that’s clunky. You also can’t view the currently set parameters, and if you want to change anything you have to clear the parameter values and start again.

To get around this clunky interface, I’ve decided to write my own. I’m very proud to present the aptly named “Parameter Editor”!

parameter editor

This interface allows you to add, remove, and change session level parameters in the report. What this means is you can now see what scenario you’re running the report in, without having to go through the tedious process of running the report, selecting the prompts, and rerunning. But that’s not all! When running in interactive mode, you an also use it to see exactly what parameters were sent through a drillthrough. When previewing a report, you can also play with the parameters. See what happens when you change them from one scenario to another. In the below animation I’m testing several different scenarios, where one fails for some reason!

Param editor

As you can see you can set both the display and use values. The plus button allows you to add multiple values for each parameter. If both boxes are empty, the script ignores it and doesn’t set a value for the parameter. If only one of the boxes is empty, it will use the value from the other box.

These are session level parameters as opposed to report level. It’s an important distinction. The parameters are set on the report development session only – they aren’t saved with the report.

I do have additional tools that I’m planning on adding to it. Data Modules present a host of opportunity. I feel there is a lot of room for improvement in the UI, especially when it comes to quickly editing multiple items.

And finally, you can download this extension from the PMsquare website here: https://pmsquare.com/freebies