Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to do same as Excel Web Data XML Import in Pentaho Report Designer?

  1. #1
    Join Date
    May 2009
    Posts
    5

    Question How to do same as Excel Web Data XML Import in Pentaho Report Designer?

    I'd like to be able to pull in data just like I do in Excel when pulling in REST URL's.

    I click this button in Excel:


    I copy a URL in like this when prompted:
    https://impl-cc.domain.com/ccx/servi...lark-impl/Test

    When I execute opening the URL I get a Windows login prompt:


    After giving my login credentials I see this xml:


    After clicking the 'import' button in the previous step it will ask me where to place the data and I just let it start at A1 and I see this:
    http://screencast.com/t/41nKse3Nl

    If I chose to view the properties of the data I get this:
    http://screencast.com/t/2x9jLCqRAwq

    When I view the excel 'xml source' I get this:


    Here is what I see when looking at the 'xml maps':
    http://screencast.com/t/6HVwPtv9SB0

    I would like to know how to do this using Pentaho's Report Designer. I would love to be able to pull in data like this and make it look and layout however I want in Pentaho's Report Designer. This will make or break my companies ability to use this for the reasons we need it.

    Thank you,
    Billy

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Use an XPath datasource (old PRD calls this XQuery, probably because XQuery sounds more cool than XPath) and you should get your data into the report-designer.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    May 2009
    Posts
    5

    Question I don't know how to do that...

    I hate to sound like an idiot but I don't know how to create and manage a xpath datasource. I'm familiar with all other data source types from a RDB standpoint, but web services isn't my strength. If you have a website that explains how to create and use a xpath datasource in Pentaho that would be great! I looked in the documentation and can't find anything that leads me to water. Thanks for your help!

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Out XPath datasource is rather simple. You just point it to a XML file (in old-PRD; in XActions (and soon in PRD-citrus) the beast is able to read from anything, including URLs), and give it an XPath expression as query. The expression must return a set of nodes containing nodes containing text. In your case, the query "/*/*" should be perfect. The "report-entry" nodes will be turned into rows, and their childs will be columns (here just one, "Employee_id").

    In PRD, the report "Main_Customers.report" is a good sample. It uses a file called "customer.xml" as datasource, which has a structure very similar to your file.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    May 2009
    Posts
    5

    Default

    Ok. That was very helpful. Now my problem is getting the authentication to work with the external system I'm pulling from. It gives a windows prompt when going through the browser so is this something I have to resolve in my operating system or is there a way that Pentaho can help me authenticate with the server I'm pulling from? I get an error that the connection was refused even though I have logged into the server already via IE/Windows.

  6. #6
    Join Date
    May 2009
    Posts
    5

    Default

    Does anyone have any idea how to do accomplish my goal?

  7. #7
    Join Date
    May 2009
    Posts
    5

    Question How can I pull in live data using a URL ?

    Does anyone know how to pull in live data via a URL over the web when Windows (server) authentication is required?

  8. #8
    Join Date
    Apr 2009
    Posts
    12

    Default

    I am looking to connect to a MS Analysis Server 2008 (SSAS 2008) through xmla, it is exposed as a web service.

    Are there any examples that pertain to this?

    Thank you,

  9. #9
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    In Citrus we have support for the OLAP4J access method. Grab the latest daily build (not RC1, that does not have the necessary changes), if you want to test it. You will have to register a connection first, using the XmlaOlap4jDriver as Drivername and then you'll have to setup the properties acording to that documentation:

    http://www.olap4j.org/head/api/org/o...p4jDriver.html

    Alternatively, you can ask Kettle to do the XML/A query for you, and then bring that result in via the Kettle-DataSource.


    None of these methods is available in PRD-3.0 or older. For those versions, the answer is simply: Its not possible.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  10. #10
    Join Date
    Apr 2009
    Posts
    12

    Default

    When do you you expect Citrus to be released to the general public?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.