Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
Back to discussions
Expand all | Collapse all

queryValue() macro in Framework Manager

  • 1.  queryValue() macro in Framework Manager

    Posted Tue September 14, 2021 01:11 PM
    Edited by System Admin Fri January 20, 2023 04:40 PM
    Hello, using here Cognos Analytics 11.1.5 and Framework Manager 11.1 with a model with DQM.

    Anybody knows if the queryValue() macro is supported in Framework Manager. I'm trying to use it but I always get this error:

    XQE-V5-0005 Identifier not found


    I have tried with
    #sq(queryValue('[Namespace].[Query Subject].[Query Item]'))#

    and

    #queryValue('[Namespace].[Query Subject].[Query Item]')#​


    If I try the query item without the macro it works:

    [Namespace].[Query Subject].[Query Item]

    ​​​​

    ------------------------------
    Alex Santamarta
    ------------------------------
    #CognosAnalyticswithWatson


  • 2.  RE: queryValue() macro in Framework Manager

    Posted Wed September 15, 2021 04:53 AM
    QueryValue() is supported, but the syntax you are using is wrong.
    CognosPaul has a nice intro: Take a look at https://cognospaul.com/2019/06/06/quickie-use-new-macro-function-queryvalue/

    Good Luck

    ------------------------------
    Sandeep Dhirad
    Elkjøp Nordic
    ------------------------------

    Original Message


  • 3.  RE: queryValue() macro in Framework Manager

    Posted Tue September 21, 2021 10:35 AM
    I don't think the syntax is wrong. If I use it in a Cognos report instead of Framework Manager it works and I don't get any error.

    I put this queryValue() macro in a filter in a Query Subject and published the package (the package also gave me a validation error) and then made a report with the query subject and the report service didn't return an error and the filter worked as expected.

    ------------------------------
    Alex Santamarta
    ------------------------------

    Original Message


  • 4.  RE: queryValue() macro in Framework Manager

    Posted Wed September 15, 2021 10:10 AM
    The queryValue function expects only a single value to be returned, so if you have more than one value in your query item, I don't think it will work unless you add a filter expression. 

    # sq ( queryValue ( 'TheCalendar.TheWeekdayName' , 'TheCalendar.TheDate = 2018-06-06' ) ) #

    So, you could try something like this and it should work:
    #queryValue('maximum([Namespace].[Query Subject].[Query Item])')#​
#queryValue('minimum([Namespace].[Query Subject].[Query Item])')#​
#queryValue('total([Namespace].[Query Subject].[Query Item])')#​


    ------------------------------
    Jonathan McKnight
    5x IBM Champion
    Business Intelligence Analyst
    Nashville, TN
    www.linkedin.com/in/jonathanmcknight
    ------------------------------

    Original Message


  • 5.  RE: queryValue() macro in Framework Manager

    Posted Tue September 21, 2021 10:40 AM
    My experience is that if the expression inside the queryValue() macro returns more than one value (with or w/o filter), it will take only one of them.

    ------------------------------
    Alex Santamarta
    ------------------------------

    Original Message


  • 6.  RE: queryValue() macro in Framework Manager

    Posted Wed September 22, 2021 04:41 AM
    Is this in a multi-package report? Can you try this in a completely new report pointing only to that package?

    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 7.  RE: queryValue() macro in Framework Manager

    Posted Fri September 24, 2021 12:00 PM
    Hi Paul,

    The package contains only a relational framework manager model. But the problem is not in the report, but in the validation in Framework Manager.

    Even though Framework Manager returns this error during the validation of the expression and the validation of the package:
    XQE-V5-0005 Identifier not found

    When I publish the package (with the offending expression in the embedded filter of a query subject) and then I make report using this query subject, the reports behaves as expected and doesn't give any error.

    So, for me the problem doesn't exist any more. Only that every time I validate the package I get a validation error.

    ------------------------------
    Alex Santamarta
    ------------------------------

    Original Message


  • 8.  RE: queryValue() macro in Framework Manager

    Posted Fri September 24, 2021 06:44 PM
    Okay, I understand what's happening. There are a few cases where certain calculations will fail validation in Framework Manager, but still execute as expected in the reports. Usually this is with package contextual calculations, such as queryValue.

    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 9.  RE: queryValue() macro in Framework Manager

    Posted 19 days ago

    Hi @Paul Mendelson, 

    We are facing "XQE-V5-0005 Identifier not found" in 12.1.1. when we are using multiple data modules in a report and one of them contains the queryvalue function. I found that the order, in which the report queries are processed, is playing a significant role but didn't find a clear pattern of this error. It can possibly be resolved by changing the report default data module or by the report query property "Use for Parameter Info". I'm writing to you because you've been asking for the same constellation (except packages instead of data modules). Do you remember what you were thinking of and could you give me some hints please? 

    Best regards, 

    Philipp 



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


  • 10.  RE: queryValue() macro in Framework Manager

    Posted 19 days ago

    Hi @Paul Mendelson, 

    We are facing "XQE-V5-0005 Identifier not found" in 12.1.1. when we are using multiple data modules in a report and one of them contains the queryvalue function. I found that the order, in which the report queries are processed, is playing a significant role but didn't find a clear pattern of this error. It can possibly be resolved by changing the report default data module or by the report query property "Use for Parameter Info". I'm writing to you because you've been asking for the same constellation (except packages instead of data modules). Do you remember what you were thinking of and could you give me some hints please? 

    Best regards, 

    Philipp 



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


  • 11.  RE: queryValue() macro in Framework Manager

    Posted 19 days ago

    Hi @Paul Mendelson, 

    We are facing "XQE-V5-0005 Identifier not found" in 12.1.1. when we are using multiple data modules in a report and one of them contains the queryvalue function. I found that the order, in which the report queries are processed, is playing a significant role but didn't find a clear pattern of this error. It can possibly be resolved by changing the report default data module or by the report query property "Use for Parameter Info". I'm writing to you because you've been asking for the same constellation (except packages instead of data modules). Do you remember what you were thinking of and could you give me some hints please? 

    Best regards, 

    Philipp 



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


  • 12.  RE: queryValue() macro in Framework Manager

    Posted 16 days ago

    An old post, but I thought I would share the response I had from IBM support on using queryvalue in framework manager. I tried loggin a bug but this is design intent. I think this also affects quite a few of the newer macro functions, such as include...

    The calculation with the macro references an item in another query subject. When DQM submits the query to DQM, it submits only the portion of the model it thinks is required to satisfy the query. So the underlying table is not sent up to DQM in this case, hence the error. When you publish the package, it includes the referenced query subject and so DQM has enough information to unwind the macro.

    The good news is, there is a Governor in FM, Process the entire model when publishing, that will allow you to test the macro in FM. You can toggle this on when testing in FM. You can also turn it off before publishing if you don't want the governor on for the published package. Here is more info on the governor.

    https://www.ibm.com/docs/en/cognos-analytics/12.1.0?topic=behavior-governors#SetGovernors__title__19



    ------------------------------
    Marc Reed
    Reporting Lead
    ------------------------------

    Original Message


  • 13.  RE: queryValue() macro in Framework Manager

    Posted 16 days ago

    Thanks Marc, Are you aware of something similar like the mentioned governor for data modules or reports? Something for "use the whole modell and not just only what you think is neccessary"? 



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


  • 14.  RE: queryValue() macro in Framework Manager

    Posted 6 hours ago

    Hi Philipp,
    The governor that was mentioned by Marc is only used by FM and only applies to FM models.
    It was a way to speed up some of the validation that takes place, which was at that time quite noticeable for very large FM models (>5MB if I recall).
    By default this flag is not set and reduces the size of the temporary model that is sent from FM to the Cognos instance. With the much improved speed of networks nowadays there might be less need for this size reduction. (B.t.w. computing the subset of the model takes a bit of time on the FM side as well).
    @Alex, did setting that governor solve the issue with the queryValue macro ?



    ------------------------------
    Henk Cazemier
    ------------------------------

    Original Message


  • 15.  RE: queryValue() macro in Framework Manager

    Posted 5 hours ago
    Hi Henk, 
     
    Thank you for your response. In the meanwhile I'm a bit off the governor theory. 
     
    In our case queryvalue used in a data module is sometimes searching for the lookup-table in the wrong data module when multiple data modules are used in the same report (validation phase before database connect). The error is very unstable and occurs mostly in reports with many data modules and queries. My tries to contruct an easy example for Armin Kamal didn't succeed. I can "fix" it if I copy all lookup tables in all data modules but this is no good solution. Do you have any idea? 
     
    By the way (and not neccessarily relevant for the solution here): Jens Bäumler found that you can indeed use governors in data modules if you include a FM (dummy) package with a governor set in the data module and then it applies for the whole data module. I haven't tried but it appears to be a nice trick :-) 
    https://www.cognoise.de/warum-governors-wichtig-sind-und-wie-man-sie-auch-in-cognos-datenmodulen-nutzen-kann-33319
     
    Best regards, 
    Philipp 


    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


Related Content