Unable to view the "Key" values for Parameter Maps on Macro Tab for Calculation

[adam_mc]

Cognos 11.1.1, Chrome

I have created a Parameter Map in Framework Manager using a Query Subject.
It is simply a set of dates created dynamically based on today's date.
When I view it in FM the Keys & Values are correct.
Then, I published the package.

In Report Authoring, I can see the Parameter Map on the Macro Tab.
However, when I try to expand it to see the Keys, it will not expand and allow me to select.

Note: I have tried a manually entered Parameter Map and this expands and works as expected.  However, this will not work for dynamically created dates!

Any thoughts would be gratefully appreciated.
Thanks in advance,
Adam.

[adam_mc]

As an additional note, the Cognos Connection data source has multiple sign-on's setup for connecting to multiple Oracle databases.
This typically isn't a problem as the user is prompted as to which database they wish to connect.

Is anyone else experiencing or not experiencing similar issues on CA 11.1.1?
I have tried both Chrome and Firefox and both are experiencing the same problem.

Thanks in advance,
Adam.   

[CognosPaul]

Is this a CTE or based off a time dimension? My current client is on 11.1.1 hotfix 1, I have similar param maps set up and they're working perfectly. Mine are set using queries that are based on an existing time dimension.

Is it only failing in the macros tab? Will it work if you test in a query? Does #sq($dateLookup{'2019-02-19'})# work?

[adam_mc]

I haven't installed the latest hot-fix for CA 11.1.1 - Perhaps that is the best place to start.
The macro function you gave ran, but returned no results when put in a calculation on a query.
I'll need to research what it should be doing, but I assume no result is not the expected outcome!

As for my FM Query Subject, it's a multiple union SQL statement using a Calendar Date dimension (similar to attached) and it returns the Parm/Value's perfectly in FM.
I've tried a number of syntax variations including creating a SQL that simply creates Parms/Values using a "select * from values()" type syntax (so not using a database table at all).
However, every time I use a query subject to drive the Parameter Map, the keys do not display on the macro tab so that they can be used.

Again, any help, will be gratefully appreciated.
Adam.

[adam_mc]

Update:

When I change the parameter map to return values in the format 'yyyy-mm-dd' rather than 'Mar 21, 2019' putting the function #$TodayDates{'TODAY_TY'}# in the calculation works.
This is not a satisfactory solution as the business users need to be able to see all the available parameters so that they can select appropriately.

It still does not allow me expand the Parameter Map to see all the Key values - In attached file, Manual is "manually"entered Parameter Map which does allow expansion, but the query driven TodayDates does not.

Thanks in advance,
Adam.

[CognosPaul]

Try this SQL:

Code Select Expand


with query("keys","use","display") as (
  VALUES
      ('TODAY_TY', #sq(timestampmask($current_timestamp,'yyyy-mm-dd'))#,                cast(#sq(timestampmask($current_timestamp,'yyyy-mm-dd'))# as date))
    , ('TODAY_LY', #sq(timestampmask(_add_years($current_timestamp,-1),'yyyy-mm-dd'))#, cast(#sq(timestampmask(_add_years($current_timestamp,-1),'yyyy-mm-dd'))# as date))
    , ('TODAY_PY', #sq(timestampmask(_add_years($current_timestamp,-2),'yyyy-mm-dd'))#, cast(#sq(timestampmask(_add_years($current_timestamp,-2),'yyyy-mm-dd'))# as date))
  ) 
SELECT
   *
FROM query


I just tried it on 11.0.5 on my laptop (gonna upgrade soon, I swear), and it's working perfectly with both the use and display and the param map value.

[adam_mc]

I tried your SQL (modified as below) in FM, but, I am still getting the same functionality in Reporting - See attached image with newly added TodayDatesMacro Parameter Map.
Is there anything simple that I have missed in creating a Parameter Map that makes the keys visible?

Code Select Expand


with query ("KEYS","USE","DISPLAY") as
(
SELECT * FROM
(VALUES
      ('TODAY_TY', #sq(timestampmask($current_timestamp,'yyyy-mm-dd'))#,                cast(#sq(timestampmask($current_timestamp,'yyyy-mm-dd'))# as date))
    , ('TODAY_LY', #sq(timestampmask(_add_years($current_timestamp,-1),'yyyy-mm-dd'))#, cast(#sq(timestampmask(_add_years($current_timestamp,-1),'yyyy-mm-dd'))# as date))
    , ('TODAY_PY', #sq(timestampmask(_add_years($current_timestamp,-2),'yyyy-mm-dd'))#, cast(#sq(timestampmask(_add_years($current_timestamp,-2),'yyyy-mm-dd'))# as date))
) a
) 
SELECT
   *
FROM query


[CognosPaul]

No, it should be working fine. I'm beginning to suspect it might be the multiple datasources causing the problem. It might be attempting to run the query in the database without actually prompting for the data source connection, then failing. What happens if you create a param map on another table from the data source? What happens if you disable all but one of the connections (only for the test, of course).

[adam_mc]

Still no luck!
Tried disabling all but one of the connections and then tried using another source with only a single connection - Both result in the same experience within Report Authoring.

Also, I have applied CA 11.1.1 FP2, but based on documentation, I was not expecting this to help.

Thanks again,
Adam.

[CognosPaul]

let's try this. reopen the report, but don't try to expand the param map yet. Open the dev toolbar and go to the network tab, clear it out so you don't have to much junk to go through.

When you expand the parameter map it should perform an ajax request. The row your looking for would be "reports"

Can you paste in the response you get from that?

[adam_mc]

Need a little help as to what you mean.
I'm not sure what you mean by "Dev Toolbar" and then, consequently, the "Network Tab".

Thanks!

[bus_pass_man]

The browser's developer's tools utilities.  Usually you can get at it via F12 but there's menu items.  In firefox you click on the hamburger menu at the top right hand corner and choose web developer.  In chrome its the inverted ellipsis icon, also at the tip right hand corner, and choose more tools/developer tools.  I don't know IE or Safari or Edge.

The default tab is usually console.   There's also a tab called network.   As you might imagine, it shows the traffic going on between your browser and whatever server you're accessing.

You clear by clicking the garbage can (firefox) or the clear icon (chrome).   You would want to do that just before you click to expand the macro where you don't see the stuff you want to see.

Once you do you action you'll see stuff.   You'd want to click on each and look at the headers and response.   There might be an error message which is in the response but is not showing up in the UI.   The response might contain an empty array ( in that case there will be some thing like {[]} somewhere in the response).

[CognosPaul]

Exactly this.  The idea is that we might be able to figure out where the error is coming from based on the results of the AJAX request.

[adam_mc]

Attached is a files depicting what is occurring on opening a Parameter Map driven by a Query (Not Working).

Let me know if there is any further information that would be useful.
Thanks again,
Adam.

[adam_mc]

Attached is a file depicting what is occurring on opening a Parameter Map driven by Manually Entered Values (Working).

Let me know if there is any further information that would be useful.
Thanks again,
Adam.

[CognosPaul]

This is interesting - I've just tried it on my client's 11.1.1 instance and I'm seeing the same thing. In 11.0.5 (again, the ancient version on my laptop) it performed a request whenever I opened the param map, but now it's not even attempting to - just like in your screenshot.

It looks like it loads the details of the maps when you open a data item in the editor and passes all of the keys with it. I'm guessing that it should detect the queryItemMap node inside the pmap and attempt to run an AJAX request.

Code Select Expand

<parameterMap hidden="0" name="SolutionLastMonth">
  <queryItemMap/>
</parameterMap>
<parameterMap hidden="0" name="secureMetrics">
  <parameterMapEntry key="CognosPaul" value="1"/>
  <parameterMapEntry key="secureMetrics" value="1"/>
</parameterMap>


I think that the only course of action would be to open a PMR.

On the positive side, the maps work when used in the query - it's only during development that they're failing to expand.

[adam_mc]

Paul...

Thanks for all your help!
I know it's not much of a consolation, but it's a little empowering to realize that I haven't been doing anything stupid and/or without thought.
As you say, at least we can use the Parameter Maps and there are a manageable amount that we can keep track of and provide appropriate documentation as to how to use.

I will open a PMR with IBM and see what they come back with.
I will update this post with any feedback that they have.

Thanks again,
Adam.

[adam_mc]

I have opened a Case (no longer a PMR or a Service Request) with IBM.
IBM has been able to reproduce the issue and is indeed confirming this as a defect in CA 11.1.1.

I have just received the following response from IBM - "After the APAR is logged, the development evaluates if it's a genuine defect (which pretty much it is) and approves it. Once, approved they will fix the defect in one of the future releases".

Adam.

[CognosPaul]

Thanks for the update. Hopefully it'll get into 11.1.2 (I hear it failed internal testing which is why it's delayed).

[adam_mc]

From IBM:
The following APAR has been logged successfully and it is in Open state now (i.e Development has approved this apar as a defect and it will be fixed in a future release).

PH10805 CREATING A PARAMETER MAP IN FM USING A QUERY ITEM CAN NOT BE EXPANDED IN REPORT AUTHORING

[adam_mc]

This is now an issue again on CA 12.0.2

CA 12.0.2

I have a Parameter Map created in Framework Manager showing a set of relative dates to today's date.
This appears to be working fine in Framework Manager with the PARM and VALUE showing as expected in all cases.

However, when I want to use the Parameter Map within Report Studio, I cannot select the entries I want.
I can see the full Parameter Map Name looking like I can explode it out to select the particular Parm.
When I attempt to explode nothing happens other than the '>' disappearing

However, I can manually enter the Parm into a calculation (for this example) and have it return the expected result as below:

#$TodayDynamicDates{'TODAY_TY'}#

I experienced a similar issue on an older version of Cognos, but IBM instructed that this issue had been resolved. Is it recurring again in the new CA 12.0.2 release/version?

I found in my history:
From IBM:
The following APAR has been logged successfully and it is in Open state now (i.e Development has approved this apar as a defect and it will be fixed in a future release).

PH10805 CREATING A PARAMETER MAP IN FM USING A QUERY ITEM CAN NOT BE EXPANDED IN REPORT AUTHORING

Using Parameter Maps enables much faster run times due to not having to do calculations all the time.
It is inconceivable that the business users should know all the potential Parms and manual key them in rather than selecting from a defined list.

Any thoughts on how to resolve this would be greatly appreciated.
Thanks, 
Adam.