Microsoft SQL: Large Data Sets

This will require Builder privileges for setup and implementation.

So you have a problem with a table that has a large amount of data. When you built your chart in iDashboards you received a message stating that the returned rows was over 1000 (3000 for pivot).  

How can you see the data for the rows returned after the 1000 line of data?

There is a process that most of the SQL types has adopted called OFFSET and LIMIT. Using them together in a Custom SQL Query you can control the amount of data returned then page through the results so they can properly be visualized on the chart. I used Microsoft SQL Server with the AdventureWorks2014 Database with the Sales.Store table. This table has 701 records in it.

First thing that you will want to do is setup your paging picklist. We will setup the paging for 100 records at a time. Start by creating a new picklist.

Data used for my Picklist:

Now let's create the Picklist: 

Let's add the Picklist to the Dashboard:

Now to build the Chart:

We will be using the following syntax to page through the data.

SELECT * FROM Sales.Store
order by name asc
OFFSET ${param:Paging}

When you complete your Chart, it will look like this:

Selecting a different page will bring you to the page results.

This type of setup will allow to standardize the look of a dashboard by returning a configured number of rows but not restricting the view the the data past the first returned set.

For More Information:

• iDashboards Builder Manual 13.14 Picklists
• iDashboards Builder Manual 14.3 Analytics
• SQL Query Help
• Microsoft AdventureWorks Sample Databases
  

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.