Maximim limit of a report is 25000 but I need to export yearly data and it is a pain to export the data separately for every 4-5 months and then compile in excel.

[Prem Prakash gautam]

Hi,

I continiously export the yearly data for performance and trend analysis but due to this limit of 25000 data records, I am having a lot of issues and have to do a repetition of the same things and then compile the data in excel. Is there a way to increase this data limit, I checked on my tool and it says 25000 is the maximum limit, can you increase it or let me know if there is any other way of doing it. I did try with advance reporting/analytics but cannot get more records. 

[Steve Giller]

Hi Prem, the limit cannot be increased, pulling more than 25,000 records requires multiple exports.

The best way to do this is to use the Data Export Tool's ability to push the results into a database and schedule this to run monthly, then at the end of the year the data is just waiting for you.

 

[SamS]

Hi @Prem Prakash gautam,

One solution would be to have a local data store (Database MS SQL/MySQL) and use the https://github.com/hornbill/goHornbillDataExport to seed that database (this feeds off the same reports that you have already created - i.e. there is also the same 25k limit). The reporting can then be done from that local data store. The data export tool can THEN be run on a schedule to "top up" the local data store on a regular basis.

Depending on what you are wanting to report on, I would also urge you to think about your local datastore as more than a single "requests" table. You can create more tables to hold different sets of data - eg some tables could hold aggregate data (eg amount of requests logged per month; so the reporting off that could made be simpler).

[samwoo]

Hi @SamS,

I’ve been using the Data Export Tool without any issues at all yesterday.

When I first started the work yesterday, I had to change all Dates, True/False, and Yes/No columns in the Report in Hornbill to “Raw” to successfully perform the extracts into the local MSSQL database. Since then, I’ve kept everything up to date (2024) and there has been no issues. I was able to extract all Requests, by year, into a local MSSQL table (doing it throughout the day to prevent any potential performance drops).

Today, I amended the report filter to look at all Requests modified in the last 7 days, planning to run this regularly to update the Requests table on the MSSQL database. That’s when I encountered the following error for each record. It’s worth noting that no changes were made to the Data Import tool configuration or the local database table since it was working fine yesterday.

NamedExec Error: mssql: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

This error occurs for every record produced by the Hornbill Report.

I've observed the log file today, and I cannot see any dates in the data that doesn't look like the raw date required for the import. It would be good if the tool can point out which field the attempted conversion from nvarchar to datetime is going wrong.

I then tried to access the Github repo for the Hornbill Data Export tool to check for any updates, but it seems to no longer exist. Including your links above to the page.

Have there been any changes to the tool or Hornbill Report? Is there a new tool being developed to replace it?

Thanks for your help,

Samuel

[samwoo]

Nevermind @SamS, I have figured the issue out!

Turns out the Security Login I was using in the set up of the schedule within SQL Server Agent had the default language set to British English (British) - I changed it to English (us_english) and let it run the process and it worked  

So yes, the tool, database and report remained the same, but it was the account that was running process via SQL Server Agent in MSSQL Server Management Studio that caused the issue.