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?

To accomplish this, you can take advantage of a couple of clauses in SQL:  OFFSET, FETCH and LIMIT. The combination of these clauses will depend on the SQL environment you are in.  Using them inside a Custom SQL Query, you can control the amount of data returned and then page through the results so the data can be properly visualized in the chart. I will provide sample queries for Microsoft SQL Server and PostgreSQL below.

Regardless of the SQL system you are using, the first thing that you will want to do is setup your paging picklist.  For the example I'm going to walk you through, I'm going to keep the page sizes small, 10 records per page.  Keep in mind that you can scale this up as needed.  Start by creating a new picklist.

Let's create our picklist:

In builder mode, select that category you want to build in and select the picklist tab.  Now click the Green Plus symbol on the right hand-side of the screen.

Now that we are in the Picklist Designer, I'm going to create a Static Data picklist by clicking on Create Data.  Column1 will be called Page [String] and Column2 will be called Value [Number].  I'm going to populate 5 pages with 10 rows of data per page for the example.

After setting up the static data, click next.  On the following screen, Name your picklist and assign the Value column to Value and the Page Column to Display and set the Sort On as Display.  Click Save.

I'm going to use a dashboard with a chart that has over 1000 rows of data to show you how to set this up.

Let's add the Picklist to the Dashboard:

1. Click on Dashboard Parameters in the upper left-hand corner.
2. In the Define Dashboard Parameters Box click on the plus sign in the lower right-hand corner.
3. Enter the values:
   • • Name:  Page
     • Label:  Page
     • Data Type: Number
     • Control Type: Dropdown
4. Click the Define button
5. Select Picklist and click the Select Button
6. Find the Picklist you created "Page" and click OK.
7. Click Refresh on Change and press Ok.
8. Under Default Value select the macro: ${select:first}
9. Click Ok twice to close the Parameter Box.

Note: Make sure to save the dashboard at some point or your Picklist settings will not be saved in the dashboard.

Let's modify the chart:

Open up the chart and navigate back to the first screen "Data Set."  To modify this chart to use paging, we need to do two things. 

• First we need to specify the parameter.
  • Click on the Import Parameter button

                   The import feature will only appear when you are editing the chart within the

                   dashboard where you created the parameter.

• • Select the Page Parameter and click Import Selected.
  • Now the Parameter should be specified:

• Next we need to modify the query:

The original query looked like this:

SELECT *
from service_leads
order by name

Microsoft SQL Server:

Modify the query to include the offset and fetch clauses:

SELECT *
from service_leads
order by name
offset ${param:Page} ROWS
FETCH NEXT 10 ROWS ONLY

PostgreSQL Server:

Modify the query to include the limit and offset clauses:

SELECT *
from service_leads
order by name
limit 10
offset ${param:Page}

Once the query is modified, click Retrieve Columns and then finish setting up your chart.

Let's view the result:

As you can see, Page 1 is showing the first 10 records:

Page 2 shows records 11 through 20

Page 3 shows records 21 through 30

This type of setup will allow you to standardize the look of a dashboard by returning a configured number of rows while not restricting the ability view the entire data 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.