locked
Can't manipulate data exported from Proclarity in Excel RRS feed

  • Question

  • Hello,

     

    I have exported a Proclarity Grid document using the "Export this page as a spreasheet" button in Proclarity Web Standard.

     

    In my Excel document, I'm trying to sum some cells (measures) and it gives me an error.

     

    It might be a ProClarity bug when exporting the data... How can I correct that problem ?

     

    Cheers,

     

    Regards,

    Alexis

     

    Thursday, November 8, 2007 10:57 AM

Answers

  • Hello,

     

    I've finally found the reason why sum was not possible on Excel from a ProClarity export.

     

    ProClarity Web Standard is displaying numbers with the english standard representation: a point for the decimal separator (even if I have the french version). Here is an example:

     

    => 1,234.56 is the english representation.

    => 1 234,56 is the french representation.

     

    When exporting, Excel (french version) can't make the sum because of the english representation.

     

    The solution here is to modify the default representation of numbers on ProClarity Web itself. The XML file to modify is this one:

     

     <driver>\Inetpub\wwwroot\PAS\en\src\PGridCtrl.xml (English Proclarity interface)

     <driver>\Inetpub\wwwroot\PAS\FR\src\PGridCtrl.xml (French Proclarity interface)

     

    <?xml version="1.0" encoding="UTF-8" ?>
    <PGridCtrl>
       <Strings>
               <id0 String="Click to resize"/>
               <id1 String="Returned %d of %d Rows, %d of %d Columns"/>
               <id2 String="Get all data"/>
               <id3 String="."/>
               <id4 String=","/>
               <id5 String="$"/>
               <id6 String="%"/>
       </Strings>
       <Defaults>
          <Colors Background="#F6F4EC">
          </Colors>
       </Defaults>
    </PGridCtrl>

    Here is code to modify to have the french representation by default:

    <?xml version="1.0" encoding="UTF-8" ?>
    <PGridCtrl>
       <Strings>
               <id0 String="Click to resize"/>
               <id1 String="Returned %d of %d Rows, %d of %d Columns"/>
               <id2 String="Get all data"/>
               <id3 String=","/>                <= comma  (instead of point)
               <id4 String=" "/>                  <= space (instead of comma)
               <id5 String="$"/>
               <id6 String="%"/>
       </Strings>
       <Defaults>
          <Colors Background="#F6F4EC">
          </Colors>
       </Defaults>
    </PGridCtrl>

    Incognitus, maybe this can help you with your percentage problem (simply remove it ?).

     

    Thank you,

    @lex(is)

     

     

     

     

     

     

    Friday, November 16, 2007 2:18 PM

All replies

  •  

    Depending on the formatting you used, the fields are text and thus you can't calculate on them.

     

    I'd say it's a bug, yes, because one exports to excel to make calculations. Plus when you use percentages it places them in the SAME cell, also making the cell text and thus you cannot calculate on that either.

     

    I wish they did it some other way.

     

    Thursday, November 8, 2007 4:26 PM
  • Hello Incognitus,

     

    Thank you for your answer.

     

    I'm not sure it is due to measure formating because I have done exactly the same manipulation using ProClarity Desktop Pro with the same document and it works well (can do some calculations on the excel spreadhseet)... I don't have a clue what is the problem... but it might come from the web interface.

     

    Best regards,

    Alexis

     

     

     

    Thursday, November 8, 2007 5:06 PM
  •  

    Check whether the cells in Excel are displaying text or numbers. You can't do math on text.

     

    I had that problem with proclarity, since I was formatting things as currency, it made them be text in excel. Same with the percentages.

    Thursday, November 8, 2007 5:39 PM
  • Hello,

     

    I've finally found the reason why sum was not possible on Excel from a ProClarity export.

     

    ProClarity Web Standard is displaying numbers with the english standard representation: a point for the decimal separator (even if I have the french version). Here is an example:

     

    => 1,234.56 is the english representation.

    => 1 234,56 is the french representation.

     

    When exporting, Excel (french version) can't make the sum because of the english representation.

     

    The solution here is to modify the default representation of numbers on ProClarity Web itself. The XML file to modify is this one:

     

     <driver>\Inetpub\wwwroot\PAS\en\src\PGridCtrl.xml (English Proclarity interface)

     <driver>\Inetpub\wwwroot\PAS\FR\src\PGridCtrl.xml (French Proclarity interface)

     

    <?xml version="1.0" encoding="UTF-8" ?>
    <PGridCtrl>
       <Strings>
               <id0 String="Click to resize"/>
               <id1 String="Returned %d of %d Rows, %d of %d Columns"/>
               <id2 String="Get all data"/>
               <id3 String="."/>
               <id4 String=","/>
               <id5 String="$"/>
               <id6 String="%"/>
       </Strings>
       <Defaults>
          <Colors Background="#F6F4EC">
          </Colors>
       </Defaults>
    </PGridCtrl>

    Here is code to modify to have the french representation by default:

    <?xml version="1.0" encoding="UTF-8" ?>
    <PGridCtrl>
       <Strings>
               <id0 String="Click to resize"/>
               <id1 String="Returned %d of %d Rows, %d of %d Columns"/>
               <id2 String="Get all data"/>
               <id3 String=","/>                <= comma  (instead of point)
               <id4 String=" "/>                  <= space (instead of comma)
               <id5 String="$"/>
               <id6 String="%"/>
       </Strings>
       <Defaults>
          <Colors Background="#F6F4EC">
          </Colors>
       </Defaults>
    </PGridCtrl>

    Incognitus, maybe this can help you with your percentage problem (simply remove it ?).

     

    Thank you,

    @lex(is)

     

     

     

     

     

     

    Friday, November 16, 2007 2:18 PM