Clarity

Expand all | Collapse all

Importing to Excel

  • 1.  Importing to Excel

    Posted Jan 20, 2010 08:29 AM
    Why is it that when I import data/report from Clarity into Microsoft Excel, it only takes about a minute to do so, but I end up spending hours and hours formatting and cleaning to make speaces fit, remove stuff, and make it presentable?   Is there a nicer and more elegant way to transfer data from Clarity reportin to excel?   Any suggestions would reduce so much frustration and heartache as I am spending waaaaay too much time manually doiing something.   I thought Clarity would automate?   Please advise.   Thank you!!!!!!


  • 2.  Re: Importing to Excel

    Posted Jan 20, 2010 11:17 AM
      |   view attached
    Have you looked at Clarity V12 SP5 and Office 2007?  Attached is a small word document with screen shots. The only thing I did to the images was crop them. The new Export to Excel is nice, so is the Export to PowerPoint.Regards,Michael    

    Attachment(s)

    doc
    Excel Export.doc   295 KB 1 version


  • 3.  Re: Importing to Excel

    Posted Jan 21, 2010 01:39 AM
    Following on from Mike's posting: Can anybody comment on the extent to which this operation produces such pretty results when used with Office 2003?


  • 4.  Re: Importing to Excel

    Posted Jan 21, 2010 05:54 AM
    We have version 8 of Clarity with office 2003.   No wonder why I spend hours and hours cleaning up the importated data in excel   to make it presentable.


  • 5.  Re: Importing to Excel

    Posted Jan 21, 2010 06:06 AM
    Depending on the requirement, you can write (in Excel) reformtting macros (in VBA etc) to take the Clarity produced extract and reformat it.   Its not trivial to build the Excel macros, but if you are doing the same reformatting time and again, it might be worth spending the time to build the macro in Excel?


  • 6.  Re: Importing to Excel

    Posted Mar 06, 2015 12:35 AM

    Hi David,

     

    I am trying to import all the OBS data from the clarity to Spread sheet using macro(in VBA).can you provide me a similar template to extract data based on my requirement.

     

    Thanks,

    Charan.



  • 7.  Re: Importing to Excel

    Posted Mar 06, 2015 04:35 AM

    vadsr05 wrote:

     

    Hi David,

     

    I am trying to import all the OBS data from the clarity to Spread sheet using macro(in VBA).can you provide me a similar template to extract data based on my requirement.

     

    Thanks,

    Charan.

    This (old) thread was really concerned with reformatting portlet-output that had been exported-to-excel - by using a VBA macro to reformat the worksheet once it was in Excel.

     

    I think what you are talking about is an excel macro that reads the data from Clarity and presents it in a spreadsheet - this is certainly also possible, but I don't have a "template" for that; you will need to do some work to get that running - or look at one of the external-service providers offerings that "integrate" Excel and Clarity perhaps.

     

    --

     

    But if you are just really interested in OBS data, take a look at this thread for a neat solution ; OBS Portlet - D3js



  • 8.  Re: Importing to Excel

    Posted Mar 06, 2015 06:03 PM

    That portlet is pretty cool, it is perfect for OBS visualisation!

     

     

    vadsr05, if you still need to extract the OBS data to play around with it, you should create an NSQL for it, create a portlet, and export the data to Excel.

     

     

    What kind of data will you need? OBS Structure? Objects linked to the OBS?

     

     

    If you know the OBS max depth, you can create a simple query combining N instances of table prj_obs_units. Something like that:

     

     

    select

      u1.name ||

      (case when u2.id is null then '' else '/' || u2.name end) ||

      (case when u3.id is null then '' else '/' || u3.name end) ||

      (case when u4.id is null then '' else '/' || u4.name end) ||

      (case when u5.id is null then '' else '/' || u5.name end)

    from

      prj_obs_units u1

      left join prj_obs_units u2

      left join prj_obs_units u3

      left join prj_obs_units u4

      left join prj_obs_units u5

      on u4.id = u5.parent_id

      on u3.id = u4.parent_id

      on u2.id = u3.parent_id

      on u1.id = u2.parent_id

    where

      u1.type_id = 5000029

     

     

     

     

    offtopic:

    About the OBS Graph Portlet. I'm working on a similar portlet for showing WBS data, but with some edition features. Maybe in near future it could be packed in some kind of product or similar, we still don't know what to do with it.

     

     

    Do you think that a graphical OBS editor would be something that people would be interested?



  • 9.  Re: Importing to Excel

    Posted Mar 19, 2015 02:16 PM

    A graphical OBS Editor would be advantageous.



  • 10.  Re: Importing to Excel

    Posted Mar 19, 2015 10:15 PM

    Thanks for your feedback!

     

    Right now we are finishing the first version of the WBS Editor.

     

    I'll take in your feedback to help us prioritize our work!



  • 11.  Re: Importing to Excel

    Posted Jan 21, 2010 06:09 AM
    So if I have an existing spreadsheet that from last   months report that I spent hours and hours cleanining up, can I use that one as a "template" to build this "macro" that you are talking about?   If so, how do you build this macro utilizing an existing spreadsheet?   Thank you.


  • 12.  Re: Importing to Excel

    Posted Jan 21, 2010 06:17 AM
    The macros I have used (but I did not build!) in my organisation take a Clarity-extract (of a known consistant format) and apply various formatting to that XLS.   So its not really using an existing spreadsheet as a template, but building up a list of formatting commands that need applying to a Clarity-produced XLS to make it usable.  For example;  Set column width of "Column C" to 4cmSet column width of "Column R" to 3cmIf "Column D" = "Red" Set colour of Column D to RED, add comment to cell "This is now RED"  etc


  • 13.  Re: Importing to Excel

    Posted Jan 21, 2010 07:20 AM
    For those who are on v12 SP5, have you had any issues with exporting portlets with virtual/progress bar columns to the new Excel or Powerpoint? Mine results in a Clarity 500 error and a String index out of range message in the logs. The Export to Excel (Data only) works as expected.I have opened a ticket with CA Support but am wondering if this has been seen (and solved) by anybody else.


  • 14.  RE: Re: Importing to Excel

    Posted Sep 30, 2010 07:03 AM
    Hi!

    We have just come across the same problem. After upgrading from 8 to 12, the export to excel and export to powerpoint results in a 500 error if there are an icons on the portlet. Has CA been able to provide you with any solutions?

    Thanks!