Author Topic: Unable to view the "Key" values for Parameter Maps on Macro Tab for Calculation  (Read 192 times)

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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.



Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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.   

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,789
  • Forum Citizenship: +257/-1
    • Paul's Cognos Blog
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?

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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.


Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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.

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,789
  • Forum Citizenship: +257/-1
    • Paul's Cognos Blog
Try this SQL:
Code: [Select]
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.

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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]
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



Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,789
  • Forum Citizenship: +257/-1
    • Paul's Cognos Blog
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).

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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.

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,789
  • Forum Citizenship: +257/-1
    • Paul's Cognos Blog
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?

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 201
  • Forum Citizenship: +11/-0
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!

Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 277
  • Forum Citizenship: +34/-0
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).




 


       
Twittear