Clarity

Expand all | Collapse all

Can I have the document used for Excel interface with CA PPM?

Jump to Best Answer
  • 1.  Can I have the document used for Excel interface with CA PPM?

    Posted 01-09-2017 01:57 AM

    Hi,

     

    I want to download the excel sheet used for integration with Clarity. Can you please help me providing the link to download the excel sheet as I've tried to locate the link and landed unsuccessful with the link? Any help provided to download excel interface to Clarity PPM would be great!

     

    Thank you!

    Regards,

    H.Monica



  • 2.  Re: Can I have the document used for Excel interface with CA PPM?

    Broadcom Employee
    Posted 01-09-2017 10:18 AM

    Hi Monica,

     

    Excel interface with PPM is not a product or feature we provide, it could be customization either done by partners or our service organization. Request you to touch base accordingly.

     

    Regards
    Suman Pramanik 



  • 3.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-09-2017 11:23 AM

    Hi Monica,

     

    There isn't any out the box "excel upload" features, but it's fairly straightforward to build one. (on a per use case basis)

    However it's much tougher to do a two way end-user friendly integration due to the rules needed. 

     

    The only bi-directional solution I know of is the IT-ROI excel tool

    CA PPM Projects Integration - PPM Excel Interface 

     

    Andrew



  • 4.  Re: Can I have the document used for Excel interface with CA PPM?
    Best Answer

    Posted 01-09-2017 11:50 PM

    I pull a lot of data out of PPM via web services.  I normally use either C# or Java depending on the client's need. 

     

    When I looked at this post, I was thinking I would just toss up some of my code to give you an example on how to get PPM data into Excel.  But if you aren't a developer or have access to a Java/C# compiler, that might not be to useful!

     

    Instead I tossed together a simple script to demonstrate how to do it.  Since I have to use PowerShell at one of my client, I chose to use it (not really fond of PowerShell but it can do some pretty heavy lifting).

     

    The script is dependent on ClosedXML which has a dependence on Microsoft Open Document SDK.  These dll need to be in the same directory as the script -- unless you change the path in the script.

     

    The script flow is:

    1. Load the DLLs
    2. Create a new spreadsheet in memory.
    3. Login to PPM
    4. Run a NSQL Query
    5. Loop through the results writing them to the spreadsheet
    6. Save the spreadsheet.

    One needs to update the $url to their instance, along with a username and password.

     

    V/r,

    Gene

     

    try
    {
         # Open a new spreadsheet with ClosedXML
         [Reflection.Assembly]::LoadFile("$PSScriptRoot\ClosedXML.dll");
         [Reflection.Assembly]::LoadFile("$PSScriptRoot\DocumentFormat.OpenXml.dll");
        $workBook = new-object ClosedXML.Excel.XLWorkbook;
         $workSheet = $workBook.Worksheets.Add("QueryData"); 

         # Generate a PPM query web service proxy by its NSQL query id == test_last_logged
         $url = "https://cppm.ondemand.ca.com/niku/wsdl/query/test_last_logged?tenantId=clarity";
         $proxy = New-WebServiceProxy $url
         $type = $proxy.GetType().Namespace

         $loginType = ($type + '.Login'); #Powershell way to create a autogenerate type -- ugly!
         $login = new-object ($loginType);
         $authType = ($type + '.Auth');
         $auth = new-object ($authType);
         $queryType = ($type + '.test_last_loggedQuery'); # PPM query web service query object is code+Query
         $query = New-Object($queryType);
         $query.Code = 'test_last_logged'; # set the code == kinda redundant but required

         $login.Username = "admin";
         $login.Password = "password";
         $auth.SessionID =  $proxy.Login($login); # Login and get our sessionID

         $proxy.AuthValue = $auth;
         $queryResult = $proxy.Query($query); # Do the query

         $column = 1;
         $row = 2;
         foreach ($record in $queryResult.Records) { #loop thru the records and write them to the worksheet
              $recordType = $record.GetType();
              $recordProperties = $recordType.GetProperties();
              foreach ($property in $recordProperties) {                   
                   if ($row -eq 2) {$workSheet.Cell(1, $column).Value = $property.Name;}
                   $workSheet.Cell($row, $column++).Value = $property.GetValue($record, $NULL);
              }
              $column = 1;
              $row++;
         }
        #Save the workbook
         If (Test-Path "$PSScriptRoot\test.xlsx") {Remove-Item "$PSScriptRoot\test.xlsx";}
        $workBook.SaveAs("$PSScriptRoot\test.xlsx");
    }
    catch [Exception]
    {
        Write-Host $_.Exception.Message; #something bad happened
        exit 1
    }


  • 5.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-10-2017 04:29 AM

    Thank you for sharing Gene, looks well thought out and clean!



  • 6.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-10-2017 06:57 AM

    Thank you Gene! It's so helpful!



  • 7.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-11-2017 11:05 AM

    Just wondering...

    So did I get it right?

    The powershell script is stored on the users workstation.

    The specific Microsoft Open Document SDK files need to be stored on the workstation.

    The user can seed the id and password in the script so they should be the user's ID and pwd and the user should have the proper rights (which are???)

     

    If the above is correct this sounds more single (power) user solution than  a system solution that would allow any user to get an up to date  query result export to excel file.

     

    Still wondering if there is such other than a gel script which creates the export to excel data only xml file if you do not do devlopment or code?



  • 8.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-11-2017 11:54 AM

    Yep, this file and the associated DLLs are not on the CA PPM server (the are on other server, workstation...).

     

    The user identified the the script needs the ability to use the web services and read rights to any object defined within the NSQL query.

     

    Yes, single user or a server that needs an XLSX datasource for exacmple Tableau.

     

    In our system, I have stood up a remote site (we are still OnDemand) in which a user on PPM click on a link to request (say download excel data). That link passes the user's session an the request to the remote site which pops a window to the PPM user.  Depending on the request either more information is gathered or the request is fulfilled e.g. builds the XLSX and streams it back to the user.

     

    V/r,

    Gene



  • 9.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-12-2017 01:02 AM

    Thanks     gcubed 

    Your solution sounds to work like what I should like, but that is too technical for me.

     

    Just wondering ....

    A scheduled job to Export to Excel with a parameter for the portlet and ability to store the result file in the library might be something the users describe.

    Very much like scheduled report stored in the libary, but for some reason the xls file appears to be more attractive.



  • 10.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-12-2017 03:41 AM

    ^ I think what you are describing is a report ; just that the report output is a grid-like data-dump rather than anything formatted.



  • 11.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-12-2017 01:56 PM

    Don't argue with that. When Niku 6 cam we used to say about reporting (compared to NPM 5) every view/portlet can be considered to be a report if you wish.

    The question is more about the easiest way to develop the report which is always available to all as an Excel file with up to date data without any user action - or the closed thing to that.



  • 12.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-12-2017 02:29 PM

    ^ yeah, I'm just trying to say that I think the easiest way is just to build a simple report - in theory this can be done by non-developers, anything else will immediately start to get "technical"



  • 13.  Re: Can I have the document used for Excel interface with CA PPM?

    Posted 01-13-2017 02:36 PM

    The challenge just disappeared as the requirement turned out to be a CSV file (interface).

    The Excel file would have to have been converted CSV ;-)