Topic
7 replies Latest Post - ‏2013-10-23T08:55:31Z by Rob Kostecki
SystemAdmin
SystemAdmin
1959 Posts
ACCEPTED ANSWER

Pinned topic Email Link Errors Out in Cognos 10.1 Excel 2007 report output

‏2011-11-08T19:00:16Z |
Hi Everyone,
Currently I am working on Cognos 10.1. I am using User Support email link (for ex: mailto:support@yahoo.com) in my report. No errors in running any format other than Excel 2007. While running in Excel 2007, User Support email link errors out. The Error message is "Reference is not valid".

Can anyone help on this issue.
Thanks in Advance.
Updated on 2011-11-15T18:51:58Z at 2011-11-15T18:51:58Z by MalcolmWaring
  • SystemAdmin
    SystemAdmin
    1959 Posts
    ACCEPTED ANSWER

    Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

    ‏2011-11-09T05:08:22Z  in response to SystemAdmin
    While clicking the email link in Excel 2007 report output, the link errors out. The full Error message is "Reference is not valid".

    Its very Urgent. Can anyone help me on this issue
    Thanks
    • SystemAdmin
      SystemAdmin
      1959 Posts
      ACCEPTED ANSWER

      Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

      ‏2011-11-09T05:21:11Z  in response to SystemAdmin
      A google search turns up many examples.

      http://www.google.com/#hl=en&sugexp=ppwl&cp=18&gs_id=29&xhr=t&q=Excel+email+link+%22Reference+is+not+valid%22&pf=p&sclient=psy-ab&source=hp&pbx=1&oq=Excel+email+link+%22Reference+is+not+valid%22&aq=f&aqi=&aql=&gs_sm=&gs_upl=&bav=on.2,or.r_gc.r_pw.,cf.osb&fp=664c62411796fbc8&biw=1387&bih=995

      This is not a Cognos issue.
      • SystemAdmin
        SystemAdmin
        1959 Posts
        ACCEPTED ANSWER

        Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

        ‏2011-11-09T06:55:10Z  in response to SystemAdmin
        Thanks. Can you guide me, How to resolve in Cognos 10.1 Report? I need to fix this issue ASAP.
        • SystemAdmin
          SystemAdmin
          1959 Posts
          ACCEPTED ANSWER

          Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

          ‏2011-11-09T15:28:41Z  in response to SystemAdmin
          Anyone can provide me the solution?
          • MalcolmWaring
            MalcolmWaring
            21 Posts
            ACCEPTED ANSWER

            Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

            ‏2011-11-14T16:20:16Z  in response to SystemAdmin
            What happens if you open the report in Excel 2007, make a change to something, and save it? Does the link work?

            The reason I am asking is that we recently discovered that Cognos generated Excel 2007 reports would not work on BlackBerry, iPhone, or even in the Lotus Notes viewer. Once they are opened and saved in Excel, they work fine.

            Excel 2007 is an open format called Open XML (finally, thanks Microsoft) but it appears that Cognos 10.1 did not follow the spec correctly, and introduced a few bugs. This email link MAY be another issue.

            We are compiling what we find to send to IBM. In our case there are two problems, the first is that there is a internal setting in each sheet that specifies the used range. Cognos just puts A1 on every sheet which is not valid. The second is that the names of the internal xml files must be lower case. They are using Sheet1.xml instead of sheet1.xml.

            We need to deal with this before a bug fix can be implemented, and one way to do it is to automate opening the file in Excel. This is a really BAD thing to do on an unattended server so we are just fixing the internal text, which is really simple if you know what problems to fix.

            If you can send a sanitized simple version of your report with just the problem and a few other cells, I would be glad to take a quick look at it (malcolm at waringsystems.com).
            • MalcolmWaring
              MalcolmWaring
              21 Posts
              ACCEPTED ANSWER

              Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

              ‏2011-11-15T18:51:58Z  in response to MalcolmWaring
              I was able to replicate the problem and figured out what is happening. There are internal and external hyperlinks in Excel. For some reason, they didn't figure out that mailto: is external, so it is formatting it as if it were internal. This effectively makes the link look in the file instead of launching email.

              The formatting is wrong in the xml files. External hyperlinks are stored in sheet1.xml.rels with a Relationship pointer in sheet1.xml. Internal hyperlinks don't require the relationship.
      • Rob Kostecki
        Rob Kostecki
        1 Post
        ACCEPTED ANSWER

        Re: Email Link Errors Out in Cognos 10.1 Excel 2007 report output

        ‏2013-10-23T08:55:31Z  in response to SystemAdmin

        In case somebody was still looking this issue was addressed and resolved in PM54866. The vendor recommendation is:

        * Upgrade to Cognos BI Server 10.2                             
        * or  Upgrade to IBM Cognos Business Intelligence 10.1.1 Fix Pack 2

        http://www-01.ibm.com/support/docview.wss?uid=swg1PM54866

         

        Kind regards,

        Rob Kostecki