Can I use Excel or a CSV file as a data provider source for Reporting Dashboards

[Jillian_Burley]

Can I use Excel or a CSV file as a data provider source for Reporting Dashboards.  I have a number of system extracts in this format that I would like to use as the source data for a reporting dashboard.  Is there a way to do this?

[Dante_Dirskell · Accepted solution]

Hi Jillian,

At this time using an Excel or CSV file as a data provider source isn’t supported. If this is a feature you would like to see added to a future release of OnBase please contact your first line of support and place an enhancement request.

Thank you for using Community!

-Dante D.

[Sean_Killian]

Hi, Jillian,

While I believe Dante is correct that CSVs and Excel files aren't supported as data sources directly, you can import the data in the CSVs into OnBase in a way that you can query the data in Reporting Dashboards if you have DIP and can write custom SQL data providers:

1. Create a virtual EForm document type. Each row of your CSV will become a document of this document type. After you create this, note the document type number that appears in the upper right of the Document Types window, right above the Info button.
2. Define keyword types for each column that you want to actually report on from the CSV (i.e. if the CSV has ten columns, but you only want 3 in reporting dashboards, then you only need to make sure you have 3 keyword types defined. One for each column). Note their Keyword Type Numbers in the upper right of the Keyword Type Configuration window.
3. Define a Single-Instance Keyword Type Group in OnBase Configuration that has a keyword type for all of the columns that you want to report on in Reporting Dashboards. After you create it, note the Keyword Group # in the upper right of the Keyword Type Group Configuration window.
4. Associate that keyword group with the virtual E-Form document type from step 1.
5. Define a DIP job that reads the columns from your CSV file into the keywords associated with your virtual eform document type.
6. Create a custom SQL data provider that uses the following query:

select    kgd.kg<kw1> as Keyword1,    kgd.kg<kw2> as Keyword2,    kgd.kg<kw3> as Keyword3from    hsi.itemdata id inner join    hsi.keygroupdata<kg#> kgd on id.itemnum = kgd.itemnumwhere    id.itemtypenum = <dt#>    and    id.status <> 16

Where:

• <kw1> is the keyword type number from the first keyword you want to keep, <kw2> is the number of the second, etc. You can put whatever name you want for each keyword after "as" but it can't have spaces in it, even if you put it in square brackets or double quotes.
• <kg#> is the keyword group number of your single instance keyword group that holds the rows from the CSV.
• <dt#> is the document type number of the virtual EForm document type.

An example from my system is:

select    kgd.kg126 as SourceID,    kgd.kg129 as SourceDate,    kgd.kg130 as PriorityLevelfrom    hsi.itemdata id inner join    hsi.keygroupdata106 kgd on id.itemnum = kgd.itemnumwhere    id.itemtypenum = 128    and    id.status <> 16

Then you can use this data provider in all of your dashboards.

You can add the datestored column from the itemdata table to the query to make sure you're only looking at records from today or the past few days, etc.

This is clunky to first set up but it's been useful for us with our OnBase Online customers because they just drop a CSV in a folder and a scheduled task sends it to their OnBase Online system and DIPs it in so their dashboards update with the latest info.