Cognos Analytics

 View Only
Expand all | Collapse all

Column values of List not populating in excel output of report

  • 1.  Column values of List not populating in excel output of report

    Posted Fri March 03, 2023 06:31 AM

    Hi Friends,

    I have a list in a Cognos dashboard. Everything is fine when the report is run in HTML format but when it is  run in XL, two of the columns in the list are not populating with values.I had newly added these two columns to the list and noticed that when run in XL, these two are not populating for some reason.

    Please suggest why this may happen and a solution.



    ------------------------------
    Mack L
    ------------------------------


  • 2.  RE: Column values of List not populating in excel output of report

    Posted Sat March 04, 2023 11:45 PM

    Could anybody please suggest what could be causing this?



    ------------------------------
    Mack L
    ------------------------------



  • 3.  RE: Column values of List not populating in excel output of report

    IBM Champion
    Posted Sun March 05, 2023 09:27 AM

    Can you post the report xml here? If you view the query results in tabular mode, and attempt to export from there, does it still fail to export the columns?



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



  • 4.  RE: Column values of List not populating in excel output of report

    Posted Thu March 09, 2023 10:54 AM

    Hi Paul,

    The original problem, that of the two columns not populating in XL is now resolved but now I find that the final output in XL has reduced records. The tabular data is fine but when I convert it to XL, a lot of records are getting dropped.



    ------------------------------
    Mack L
    ------------------------------



  • 5.  RE: Column values of List not populating in excel output of report

    IBM Champion
    Posted Thu March 09, 2023 11:08 AM
    Try exporting to Excel Data or CSV. You could be hitting the row count limit of the file type you are exporting too.

    Logan Whitaker

    PMO, HEB Supply Chain Planning & Operations

    o: 210.938.6043 m: 210.429.3181

    e: whitaker.logan@heb.com







  • 6.  RE: Column values of List not populating in excel output of report

    Posted Sat March 11, 2023 06:58 AM

    The output is throwing not more than 9000+ records, so row limit cannot be the problem. The data displayed in html/tabular view is reduced several times in the XL output (records get dropped).

    Observed that a particular calculated column in the base query (the report uses multiple queries at report level) when it comes to the final query is simply displaying only certain values and the records with other values are being dropped. for instance if there is a calculated column that gives a, b, c, d values, then the xl output is displaying only records containing a & b values and not the rest even though there are rows containing them.

    I am considering recreating the report but not sure if it will rectify the problem. Any suggestions keeping in mind the above observation will be helpful.



    ------------------------------
    Mack L
    ------------------------------



  • 7.  RE: Column values of List not populating in excel output of report

    IBM Champion
    Posted Mon March 13, 2023 06:50 AM

    Hi Mack, I think a more specific description could be helpful. One thing that comes to my mind is that filter or calculation property before or after aggregation might might play a role. 



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------



  • 8.  RE: Column values of List not populating in excel output of report

    Posted Mon March 13, 2023 12:00 PM

    Hi Philip,

    I found that records which have zeros as values in a measure column in the list do not appear in the XL output. These I can see only in the html ouput. This is causing the difference in record count between html and xl. some additional information - this is happening at the level of the final query and not in the intermediate queries where xl output of tabular data is showing all records. Any suggestions based on this?



    ------------------------------
    Mack L
    ------------------------------



  • 9.  RE: Column values of List not populating in excel output of report

    IBM Champion
    Posted Tue March 14, 2023 11:00 AM

    Hi Mack, I can't conclude a cause from this information. Could you please share the report specification?



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------



  • 10.  RE: Column values of List not populating in excel output of report

    Posted Sat March 18, 2023 05:07 AM

    Philip,

    I used an alternative way to fix the problem after finding that the list header summaries were causing it.

    The solution involved removing the summaries from the list header and inserting a list that contained the same summaries into the list header of the main list. Now the XL output is correct.

    Thank you for your suggestions.

    Regards,

     

     



    ------------------------------
    Mack L
    ------------------------------



  • 11.  RE: Column values of List not populating in excel output of report

    Posted Tue November 07, 2023 11:29 AM
    Edited by Andres Parada Wed November 08, 2023 11:23 AM

    Hey Mack, just out of curiosity - what was the reason for the columns not being populated? I'm having exactly the same problem. A calculated column, deriving from an other query, is being populated in html, but neither xlsx nor pdf.

    All the best

    Roger



    ------------------------------
    Roger Lewin
    ------------------------------