Mobile

Labels
AJAX(112) App Studio(6) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(8) OAuth Scopes(1) OAuth2(11) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(10) PKCE(2) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(178) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(80) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Mobile
Saturday, May 16, 2015PrintSubscribe
“Hello World” Single Page App with jQuery Mobile

jQuery Mobile is the foundation of apps created with Code On TimeTouch UI is the primary user interface of generated apps. It relies on capabilities of jQuery Mobile to partition an HTML page into multiple virtual pages. Touch UI also takes advantage of the navigation architecture implemented in  jQuery Mobile framework. Navigation between virtual pages happens without reloading of the physical page container even when Back and Forward buttons of a web browser are pressed. Transitions between virtual pages are animated by jQuery Mobile, which provide “native” feeling to the apps. Virtualization of mouse and touch events of modern web browsers is another core feature of jQuery Mobile, that enables handling of user interactions in line-of-business applications with Touch UI.

Let’s create a line-of-business app that showcases the single page application model and the above-mentioned jQuery Mobile features.

A single page app created with Code On Time application generator is based on jQuery Mobile.

Creating Page Template

First,  download application generator and configure a sample Web Site Factory project for Northwind database.  Choose an option to generate only the data controllers when you are stepping through pages of Project Wizard.

Configuring a sample project without pages in Code On Time app generator.

This is how the app will look when it is started in a web browser.

A line-of-business app with Touch UI created with Code On Time app builder. Application uses jQuery Mobile framework for enhanced mobile-friendly user interface.

Follow instructions and login as admin/admin123%.  Instructions will disappear from the home page and you will see an additional menu item that allows managing user accounts and roles.

Site content in a line-of-business app created with Code On Time.

User and role management screen in a line-of-business app created with Code On Time.

Let’s add a new page to our project. Activate project designer and create a new page.

Creating a new SPA page in Project Designer of Code On Time.

Enter SinglePageApp as the page Name, select “(blank)” for Template, set Generate property to “First Time Only”, and click OK button. The icon of the page will have a lock image displayed on it to indicate that it is safe to modify the page in any text editor after it has been generated.

SPA page that can be customized in any external editor without loosing changes during code generation iterations.

Drag the page to the desired location in the navigation system of the app. Right-click the page and choose “View in Browser” option to preview the page. App generator will create a file for the page, start IIS Express development web server and launch the default web browser. This is our new page.

SPA page based on blank template in an app created with Code On Time.

Right-click the page once more and choose Edit in Visual Studio option.

Activating Visual Studio to modify the page of an app created with Code On Time application generator.

This is the HTML markup of the page defined in ~/Pages/SinglePageApp.html file.

<!DOCTYPE HTML>
<html>
<head>
    <title>Single Page App</title>
</head>
<body data-authorize-roles="*">
    <!--The contents of this page will be overwritten by app generator.
        Set page property "Generate" to "First Time Only"
        to preserve changes.-->
    <div data-app-role="page" data-activator="Button|Single Page App">
        This is the content of <i>SinglePageApp</i> page.
    </div>
</body>
</html>

The default empty page specifies a single virtual page container element with data-app-role attribute set to “page”. You may know that jQuery Mobile uses data-role attribute for the same purposes. Touch UI framework relies on APIs available in jQuery Mobile to correctly initialize the page container as a virtual page. Only authenticated users are authorized to see the virtual page of this SPA.

Multiple Virtual Pages in a Single SPA Page

Let’s replace the default virtual page with the three virtual pages instead.

<!DOCTYPE html>
<html>
<head>
    <title>SPA1</title>
</head>
<body data-authorize-roles="*">
    <div id="spa1" data-app-role="page" data-activator="Button|Supplier List">
        <ul id="supplier-list" data-role="listview" data-inset="true"
            data-filter="true" data-autodividers="true"></ul>
    </div>
    <div id="spa2" data-app-role="page" data-activator="Button|jQuery">
        <p>
            Learn about jQuery:
        </p>
        <a href="http://jquery.com" class="ui-btn ui-btn-icon-left 
           ui-corner-all ui-icon-arrow-r ui-btn-inline">jQuery</a>
    </div>
    <div id="spa3" data-app-role="page" data-activator="Button|jQuery Mobile">
        <p>
            Learn about jQuery Mobile:
        </p>
        <a href="http://jquerymobile.com" class="ui-btn ui-btn-icon-left 
           ui-corner-all ui-icon-arrow-r ui-btn-inline">
            jQuery Mobile</a>
    </div>
    <script src="~/Scripts/Suppliers.js"></script>
</body>
</html>

Virtual pages spa1, spa2, and spa3 are div elements with data-app-role attribute set to “page”. Data activators are assigned to each page. Refresh ~/pages/single-page-app page in the web browser and you will see a list of activators.

The menu of virtual pages in an SPA app created with Code On Time line-of-business application generator.

Select the first activator and jQuery Mobile framework will activate the page, which will be indicated by the hash value #spa1 in the address bar of the browser.  The page header displays the text specified in the page activator and there is a also an empty list view with a filter. There is no code connected to the list view in the current implementation so there will be no data even if you enter sample text in the search box.

jQuery Mobile filterable listview widget in the SPA app created with Code On Time app builder.

Return back and try one of the other virtual pages. Notice that the physical page does not reload in the browser as you navigate between virtual pages. jQuery Mobile handles changes of the history state in the app with Touch UI.

Simple content page uses jQuery Mobile CSS classes to style a link to an external page.

Click on a link and the application framework will execute an off-band HTTP request to download the content . If other virtual pages are found in the downloaded page then the framework will inject them in the physical page and transition user to the first downloaded virtual page. If the content is not compatible with the application framework then the app will create a virtual page with iframe element configured to display the linked content as shown in the next screenshot.

External website displayed in an SPA app created with Code On Time.

Making Database Request

The primary purpose of Code On Time application generator is to accelerate development of database apps. This application already includes a collection of data controller that can handle interactions with the Northwind database. This picture shows configuration of Suppliers data controller displayed in Project Designer. The data controller has been created by app generator straight from the schema of the database.

The stucture of Suppliers controller displayed in Project Explorer of Code On Time app generator.

Add new JavaScript file ~/Scripts/Suppliers.js to the project in Visual Studio and enter the following code:

(function () {
    var supplierList = $('#supplier-list');
    $('#spa1').on('navigating.app', function () {
        $app.execute({
            controller: 'Suppliers',
            sort: 'CompanyName',
            success: function (result) {
                $(result.Suppliers).each(function () {
                    var supplier = this;
                    var li = $('<li/>').appendTo(supplierList);
                    var a = $('<a class="ui-btn"/>').appendTo(li);
                    $('<h3/>').appendTo(a).text(supplier.CompanyName);
                    $('<p class="ui-li-aside"/>').appendTo(a).text(supplier.Phone);
                    $('<p/>').appendTo(a).text(supplier.ContactName + ' | ' +
                        supplier.Address + ', ' +
                        supplier.City + ', ' +
                        (supplier.Region || '') + ' ' + supplier.PostalCode + ', ' +
                        supplier.Country);
                });
                supplierList.listview('refresh');
                $app.touch.navigate('spa1');
            }
        });
        return false;
    });
})();

The script is designed to work specifically with this single page app. It will make a request to obtain a list of suppliers whenever a user is activating the virtual page Supplier List.

Application framework supports asynchronous pre-loading of data in virtual pages. Developer can fill a page with data before the page is displayed to the end user.  The majority of database apps based on HTML fail to do so and display empty pages that are filled with data after being presented to the user. Event navigating.app is triggered on the virtual page when the framework detects a request to display a virtual page. If the event handler returns false then the navigation is postponed indefinitely.

The script does just that. The handler of navigating.app event is making a request to the server-side components of the app by calling $app.execute method asking for a list of suppliers sorted by CompanyName column. This method is executed asynchronously. Immediately the handler tells application framework to stop navigation without waiting for a list of suppliers to come back from the server.

The second phase of client-side processing happens when a response is received from the server. The callback method success iterates through the suppliers in the response and creates corresponding list items marked with CSS classes ui-btn and ui-li-aside available in jQuery Mobile framework. The final step refreshes the list view supplier-list and resumes navigation to the virtual page spa1 by calling $app.touch.navigate method.

The script needs to be hooked to the page ~/Pages/SinglePageApp.html. Place the script reference just before the closing body tag in the page markup to accomplish that. Note that symbol “~” indicates that the path to the script must be resolved from the root of the site instead of being a relative reference. That enables the page to be moved in the project structure without the need for changes in the script reference.

. . . . . . . . . . . . . . . . .  . . . . .  . . 
<script src="~/Scripts/Suppliers.js"></script> </body> </html>

Refresh the page in the browser and navigate to Supplier List. Notice that there is a little pause and then the page with data  appears. This is the result of data preloading and delayed navigation. If download of data is taking longer than three quarters of a second then a progress indicator will be displayed at the top of the page and “back” button will start spinning.

Listview widget of jQuery Mobile displays a list of suppliers in SPA app created with Code On Time.

Filtering in the list happens without interactions with the server as a filter value is typed. Filterable widget from jQuery Mobile makes this possible.

Filterable widget of jQuery Mobile in action in an app created with Code On Time.

Note that this application is capable of displaying data with different display densities on various screen sizes in a responsive fashion. For example, click on the menu button and choose Settings.

jQuery Mobile menu drawer/panel  opened in an SPA app created with Code On Time.

Change display density of application to Comfortable and application theme to Dark. Reduce the width of the window and the sidebar will disappear. The page will look close to how it is presented to users of modern touch-enabled smartphones.

jQuery Mobile responsive design shines through in the app created with Code On Time.

Under The Hood

Those of you with the curious minds may be already asking themselves how this seemingly minimalistic HTML page and lean JavaScript code are getting the job done. There are no references to jQuery or jQuery Mobile library or any other components.

Select View Source option in the menu of your browser or press Ctrl+U on the keyboard. The source of a live page will be displayed. This is what it may look like.

<!DOCTYPE HTML>
<html xml:lang=en-US lang="en-US">
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
    <meta charset="utf-8" />
    <meta name="application-name" content="Hello World SPA" />
    <link id="MyCompanyTheme" href="/appservices/stylesheet-8.5.3.0.min.css" type="text/css" rel="stylesheet" />
    <title>SPA1</title>
</head>
<body>
    <script src="/appservices/combined-8.5.3.0.en-us.js?_spa"></script>
<
div id="PageContent" style="display:none"> <div id="spa1" data-app-role="page" data-activator="Button|Supplier List"> <ul id="supplier-list" data-role="listview" data-inset="true" data-filter="true" data-autodividers="true"></ul> </div> <div id="spa2" data-app-role="page" data-activator="Button|jQuery"> <p> Learn about jQuery: </p> <a href="http://jquery.com">jQuery</a> </div> <div id="spa3" data-app-role="page" data-activator="Button|jQuery Mobile"> <p> Learn about jQuery Mobile: </p> <a href="http://jquerymobile.com">jQuery Mobile</a> </div> <script src="/Scripts/Suppliers.js"></script> </div><footer style="display:none"><small>&copy; 2015 MyCompany. All rights reserved.</small></footer> <script> var __targetFramework="4.5",__tf=4.0;__servicePath="../_invoke";__baseUrl="../";var __settings={appInfo:"HelloWorldSPA|admin",mobileDisplayDensity:"Auto",desktopDisplayDensity:"Condensed",mapApiIdentifier:"", labelsInList:"DisplayedBelow",labelsInForm:"AlignedLeft",initialListMode:"SeeAll",buttonShapes:"true", sidebar:"Landscape",promoteActions:"true",transitions:"",theme:"Azure",maxPivotRowCount: 250000, help:true,ui:"TouchUI"};Web.Menu.Nodes.Menu1=[{title:"Home",url:"/pages/home",description:"Application home page",cssClass:"Wide"},{title:"Membership",url:"/pages/membership",description:"User and role manager"},{title:"Single Page App",url:"/pages/single-page-app",selected:true}];Sys.Application.add_init(function() { $create(Web.Membership, {"displayHelp":true,"displayLogin":true,"displayMyAccount":true,"displayPasswordRecovery":true, "displayRememberMe":true,"displaySignUp":true,"enableHistory":false,"enablePermalinks":false, "id":"mb_b","rememberMeSet":false,"user":"admin"}, null, null, null); }); </script> </body> </html>

You will find a reference to a minifies CSS  file that contains user interface definitions for jQuery Mobile and Touch UI. There is also a link to a combined JavaScript library that includes jQuery, jQuery Mobile, Data Aquarium, and Touch UI frameworks. Container element PageContent is hidden by default.

Application framework will instantiate and initialize virtual pages spa1, spa2, and spa3 when the document is loaded and ready for processing. The static script variables at the bottom of the page provide the default application settings and navigation menu.

Element PageContent incorporates the content of ~/Pages/SinglePageApp.html. Arguably more complex than the original version, the physical page served to web browsers on desktop and mobile devices is still very lean. Code On Time applications will be equipped with ability to work entirely “offline” by the end of 2015. The page structure is perfect for storing directly in the offline browser cache.

The dynamic component of this single page app is the virtual page with the list suppliers. As you can see, the source code of the physical page does not have supplier data in it. Application requests data from the server by calling $app.execute method as explained above.

This is the JSON request sent to the server components of the app by $app.execute method.

JSON request to retrieve a list of suppliers displayed in IE11 development tools.

{"controller":"Suppliers","view":"grid1","request":{"PageIndex":-1,"PageSize":100,"PageOffset":0,"SortExpression":"CompanyName","Filter":[],"ContextKey":"","Cookie":"undefinedcookie","FilterIsExternal":false,"LookupContextFieldName":null, "LookupContextController":null,"LookupContextView":null,"LookupContext":null,"Inserting":false, "LastCommandName":null,"ExternalFilter":[],"DoesNotRequireData":false,"LastView":null,"RequiresFirstLetters":false,"SupportsCaching":true, "SystemFilter":null,"RequiresRowCount":false,"RequiresPivot":false, "PivotDefinitions":null,"RequiresMetaData":true}}        

The response is returned as compressed JSON string. Method $app.execute makes the response available for processing in success callback function. This how the list of suppliers looks when inspected in Visual Studio while debugging the app.

Inspecting JSON response returned from the server components of the SPA app created with Code On Time.

Method $app.execute can be used to select, pivot, insert, update, and delete data. Custom actions can also be invoked and processed by server-side business rules written in SQL or C#/Visual Basic. Email business rules are automatically executed in response to actions when specified.

Project Structure And Deployment

Solution Explorer of Visual Studio will show the location of the SinglePageApp.html page in the project hierarchy. There are no binary files in the project, the entire source code is included. Files with the names Application.*.xml, Controllers.*.xml, and DataAquarium.*.xml are used by application generator at design time only.

Components of jQuery, Data Aquarium, and Touch UI frameworks are located in ~/scripts folder. Files from jQuery Mobile library are located in ~/touch  folder.

The structure of the application created with Code On Time displayed in Solution Explorer of Visual Studo.

Publish the project to produce a set of files that are ready for deployment to a production web server.

Code On Time deployment folder with published application.

Window Explorer will show up with the  published files. The binary folder ~/bin contains compiled application DLLs. Files specific to application generator are not included.

The structure of the published application created with Code On Time line-of-business application generator displayed in Windows Explorer.

Using App Generator to Create Data Pages

Touch UI application framework provides sophisticated data access user interface components based on jQuery Mobile. For example, you can easy create a master-detail page displaying suppliers and linked products with full support for search, sorting, filtering, and editing of data with just a few clicks of a mouse.

Create a new page in Project Designer and call it Suppliers. Activate Controllers tab in Project Explorer and Ctrl-click Supplies and Products  data controllers. Right-click Products controller and choose Copy in the context menu.

Copying two data controllers to clipboard in Project Explorer of Code On Time app builder.

Activate Pages tab in Project Explorer, right-click the new page Suppliers, and choose Paste.

Pasting data controllers on a page in Project Explorer of Code On Time.

Drag data field Suppliers / c102 / view2 (Products) / grid1 / SupplierID onto Suppliers / c101 / view1 (Suppliers) node in the hierarchy of pages.

Establishing a master-detail relationship between the list of suppliers and the list of products in SPA page of an app created with Code On Time.

This will configure the data view of products to be filtered by the primary key of a record selected in the data view of suppliers.

A master-detail configuration of two data views on single page of SPA.

Right-click Suppliers page and select View in Browser. A grid of suppliers will be displayed.

A responsive grid of suppliers in the app created with Code On Time.

Select a supplier to see the linked products.

Master-detail view of a supplier and linked products in the applicaiton created with Code On Time line-of-business app generator.

This responsive data page can be displayed comfortably in a mobile or desktop browser on a screen of any size and resolution. It allows searching, filtering, sorting, and editing of data. The data can presented as grids, lists, cards, charts, maps, and calendars.

List view of suppliers in the app created with Code On Time.

Map view of suppliers in the app created with Code On Time.

The markup of the page is so simple that it is hard to believe.

<!DOCTYPE html >
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>Suppliers</title>
  </head>
  <body data-authorize-roles="*">
    <div data-flow="row">
      <div id="view1" data-controller="Suppliers"> </div>
    </div>
    <div data-flow="row" style="padding-top:8px">
      <div class="DataViewHeader">Products</div>
      <div id="view2" data-controller="Products" data-view="grid1" data-filter-source="view1" 
           data-filter-fields="SupplierID" data-page-size="5" 
           data-auto-hide="container" data-show-modal-forms="true"> </div>
    </div>
  </body>
</html>

Attribute data-controller causes application framework to create virtual pages for each of the data views. Single page app Suppliers automatically zooms into virtual page view1 to display a master list of suppliers. Virtual page view2 is available when a supplier is selected in the user interface. Additional virtual pages are created by application framework in response to various user actions.

Creating Content Pages

Wait, there is more. Touch UI application framework combines jQuery Mobile and popular content framework Bootstrap. This enables creation of content pages with a simple and effective presentation. Add one more page to your project and configure it to use Jumbotron template. Preview the page in a browser and you will see the following:

A bootstrap content page in the app created with Code On Time. Touch UI application framework combines together jQuery Mobile and Bootstrap.

This will surely give you a few ideas about enhancing your line-of-business database application with a few public facing pages to promote the application capabilities. These content pages will look great on mobile and desktop devices.

Integrated Content Management System

We are still not done here. This line-of-business application can be further enhanced with an integrated content management system. Built-in CMS allows creating dynamic multimedia content in a live application without the need for re-deployment. Data and content pages can be stored directly in the application database thanks to their lean HTML-based structure.

Content management system also allows creating custom navigation menus, uploading of modified data controllers, definition of Dynamic Controller Customization rules, and custom security through Dynamic Access Control List.

Learn how to create a directory of suppliers shown at the top of the article in a live application without re-deployment or access to the server file system.

Conclusion

If you are looking to take a full advantage of jQuery Mobile framework then you must take it for a spin with Code On Time line-of-business application generator. Create premium database apps for mobile and desktop devices with Code On Time now!

Tuesday, March 10, 2015PrintSubscribe
Specifying Default Size On Charts

Charts presentation style will display as many reasonably-sized charts as possible on each device size. By default, each chart is of size “small”, which means that the chart will use 1/3 of the available space in each dimension on large screens.

The default charts for Orders page will attempt to show three charts in each dimension on large screens.

On medium-sized devices, the charts will use 1/2 of the available space.

Small charts on medium-size devices will show two in each dimension.

The smallest devices will display only one chart at a time.

Small devices only show one chart at at time.

The user is able to change the size of the charts using the context menu in the top right corner of each chart. “Large” is only available on large devices, and “Medium” is only available on moderately sized devices and tablets.

The user is able to change the size of the charts using the context menu in the top right corner of each chart.

The developer is also capable of specifying the default size for each chart by adding the keywords “medium” or “large”. Suppose that the Orders controller has manually specified charts matching those that have been automatically created. The highlighted tags below will specify the default sizes for those charts.

Data Field Tag
CustomerID pivot1-col1-sortdescbyvalue-columnstacked-top5 pivot1-medium pivot4-row1-top10-other-sortdescbyvalue-pie3d pivot4-large
EmployeeID pivot2-col1-sortdescbyvalue-area-top7 pivot5-row1-top10-other-sortdescbyvalue-column
OrderDate pivot1-row1-date-all pivot6-row1-line-date-all
RequiredDate pivot2-row1-date-all pivot7-row1-column-date-all
ShippedDate pivot3-row1-date-all pivot8-row1-area-date-all
ShipVia pivot3-col1-sortdescbyvalue-column-top5 pivot9-row1-top10-other-sortdescbyvalue-donut

Upon regenerating the app and refreshing the page, notice that the default sizes have been applied. The first chart is of “medium” size and now takes 2/3s of the screen on large devices.

The first chart has size of "medium" and takes 2/3s of the screen.

The “large” fourth chart takes the whole screen.

The fourth chart is "large" and takes the full screen

Wednesday, March 4, 2015PrintSubscribe
Creating Custom Charts

Apps created with Code On Time generator will automatically compose up to nine charts based on the columns present in your tables. If the default charts are insufficient, then the developer may choose to define their own charts for each data view.

Charts are configured using a simple tagging language applied on the data fields of a view. Let’s start configuring charts for the Orders page of the Northwind sample app.

Creating a Chart

Start the Project Designer. In the Project Explorer on the right side of the screen, switch to the Controllers tab and double-click on the Orders / Views / grid1 / CustomerID data field.

Clicking on the CustomerID data field of the Orders controller.

Change the following property:

Property Value
Tag pivot1-row1-column

The tag does the following: “pivot1” will declare that the data field CustomerID will be used for pivot with ID of “1”. The keyword “row1” declares that the data field will be used to define the first row. Finally, the keyboard “column” declares the chart type.

Press OK to save the data field. On the toolbar, press Browse to regenerate the app and open it in the default browser. When the page comes up, navigate to the Orders page. There will be a single chart of type “column” available.

A simple column chart in Orders.

The data behind the chart is shown in the table below:

The data behind a simple column chart in Orders.

The first column shows the values of each row (the customer company name). The second column is the actual value used to render the chart. No value field was specified by the developer, so a count of records has been used.

Sorting

Notice that the order of rows is alphabetical. Let’s sort the rows in descending order by the value in the second column. Replace the tag using the method described above with the following:

Data Field Tag
CustomerID pivot1-row1-column-sortdescbyvalue

The image below shows the new chart, with the rows sorted.

Orders column chart with the columns sorted by value

The data will look like the following:

The data has been sorted by value.

This has given us the correct order, but this chart can still be improved. Notice that there are too many columns displayed, making it hard to read. Let’s reduce that number.

Reducing the Result

Replace the tag with the following:

Data Field Tag
CustomerID pivot1-row1-column-sortdescbyvalue-top10

The “topX” keyword will only take the top X number of rows, and will throw away the rest. The new chart can be seen below.

The new chart only shows 10 columns.

The table can be seen below.

Only 10 rows are available in the table.

There are now ten rows of data available, as expected.

It may be possible that the user does not want the rest of the rows to be hidden, but grouped into one last column.

The “Other” Column

Using the “other” keyword in combination with “topX” will only display the top X number of rows. For rows that do not make the cut, their values will be summed up into an “Other” row. Use the following tag:

Data Field Tag
CustomerID pivot1-row1-column-sortdescbyvalue-top10-other

The new chart will display this “Other” column.

Only the top 10 customers are displayed, and the rest are grouped into an 'Other' column.

The values in this “Other” column will also be present in the chart data.

Values for the "Other" column are displayed as the last row in the table.

Defining Titles

Notice that a title has been automatically generated from the chart definition. The title, as well as the horizontal and vertical axis labels, can also be defined.

Replace the tag with the following. Notice that multiple space-separated tags are defined.

Data Field Tag
CustomerID pivot1-row1-column-sortdescbyvalue-top10 pivot1-title:"My Top 10 Customers"   pivot1-haxistitle:"Customers"        pivot1-vaxistitle:"Orders"

The new chart will use the specified titles:

An Orders chart with custom titles.

The new title will be shown above the table as well:

The chart data also shows a custom title.

This chart looks complete. Let’s add another chart that shows how many orders were received over time.

Working With Dates

Orders table in the Northwind database contains the OrderDate field. Let’s show how many orders were received per quarter. Apply the following tags to the relevant data fields:

Data Field Tag
OrderDate pivot2-row1-quarter-line

The “quarter” keyword will group the values by quarters. The “line” keyword is the chart type.

A chart showing the count of orders by quarter.

In this chart, the orders have now been grouped by the quarter. Notice that there are no years – we need to define another group in order to split the orders by year, and then by quarter.

The chart data shows how the orders have been grouped by quarter.

Make the following changes:

Data Field Tag
OrderDate pivot2-row1-year-line pivot2-row2-quarter

This configuration will define a chart that uses the OrderDate field twice – first time to group by year, and then to group by quarter. The results can be seen below.

The chart groups orders by year, and then by quarter.

The new chart now shows quarters from each year that Northwind has been in business.

The chart table groups orders by year, and then by quarter.

Revealing Gaps

Suppose that the data has some gaps in the time period. The example below was filtered to customer “Frankenversand”, and it is clear that “1997, Q1” is missing. A missing row is easy to spot in the table, but it would be harder to spot in the chart.

The chart data reveals a missing row - '1997, Q1'.

Use the “all” keyword to ensure that gaps are displayed in the result.

Data Field Tag
OrderDate pivot2-row1-year-line pivot2-row2-quarter-all

Empty data periods are now revealed on the chart.

The chart now presents gaps in the data.

Empty gaps are revealed in the chart data.

This chart looks good with the current data set. What happens when the data takes on a different shape?

Automatic Date Sizing

The view has been filtered to only include data for 3 months. The end result is not so useful or attractive when using year/quarter.

When the data has been filtered to three months, grouping by quarter is not that helpful.

The table is equally unhelpful.

When the data has been filtered to three months, grouping by quarter is not that helpful.

This is where the “date” keyword comes to the rescue. Instead of manually specifying the date groupings, we will command the API to compose several result and use the best fit. Replace the tag with the following:

Data Field Tag
OrderDate pivot2-row1-line-date

It appears that the chart using year/month/week has the most useful presentation according to the algorithm.

The auto date algorithm has decided to use year/month/week as the best grouping.

The table now reveals more useful information as well.

The chart data shows values grouped by week.

Specifying a Column Field

It may be helpful to split apart each data point by introducing a column field. Let’s show the number of orders from the top three customers. Change the tags for pivot2 as follows:

Data Field Tag
CustomerID pivot2-col1-top3-sortdescbyvalue
OrderDate pivot2-row1-columnstacked-date

The chart will now show a breakdown of orders from the top three customers at each date.

The chart now displays orders broken down by customer over time.

The table will now have multiple columns.

The data shows the orders broken down into columns by customer over time.

Specifying a Value Field

Instead of simply showing a count of orders, let’s use the sum of Freight as the value. To do this, we will need to define the data field Freight as a value field. Specify the tags below. The “sum” keyword determines the type of value. By default, the chart will use “count”.

Data Field Tag
CustomerID pivot2-col1-top3-sortdescbyvalue
OrderDate pivot2-row1-columnstacked-date
Freight pivot2-val1-sum

The chart will now display values according to the sum of Freight field.

The chart now uses the sum of Freight as the value.

The table will reveal the correct values.

The table shows the use of sum of Freight as the value.

Continue to Charts Everywhere