And again we can introduce a great new feature of DataPoint; the use of variables in your custom sql statements. Let’s explain what this is.

You want to create a nice data driven presentation. A sales presentation on a given product for distribution to your sales people and management. A presentation like this.

You can use DataPoint to set up a connection to your sales database and you set up multiple queries to retrieve related information. Maybe you want to sum the sales of a given product, list the customers who bought it and calculate the sales revenue and profit.  For that, you can set up several queries, but they will have one thing in common. The name or internal ID of the product that you are creating this presentation for that value is repeated in each custom SQL statement.

So for all custom SQL statements, you are including a selection or where clause like ProductName = ‘DataPoint’, and that in all your queries. Better would be if you could use a variable in your custom SQL statements and that you maintain the value of that often used variables at one place, on a separate form.

That can be done now with DataPoint variables. Click to open the DataPoint menu and click the Properties button of the Presentation group.

Click the Variables button at the bottom of the properties form.

Set up a list here of all your variables. Typical for a variable is that you have a name and a value. Click the Add button to add a new variable. Name it and set its value. You can add as many variables as you want. When done, click the OK button to close.

For this example, we set up a new variable named ProductID with the value 47. We know that that value corresponds to our ‘DataPoint Standard edition for PowerPoint 2016’. Of course you can use string values for variables too, but these database things work typically with numeric and unique IDs. Check your database administrator or database data model for more info on your specific database.

Open your custom SQL statement again and use that variable. You have to use a special notation for this. To indicate that you want to use and replace a variable, you always have to start with the text REPLACE:// followed by the name of the variable.

In this sample we use the SQL statement:

Select * From TblProducts Where productid = REPLACE://ProductID

When we execute this statement, DataPoint replaces the string ‘REPLACE://ProductID’ with the current value of the variable from our variables listing. So DataPoint will send the following statement to our database server:

Select * From TblProducts Where productid = 47

In case that you need to use a string value as variable, you just place the quotes around your REPLACE function like this:

Select * From TblProducts Where productname = 'REPLACE://ProductName'

Note that REPLACE:// should be in capitals. The name of the variable is case sensitive as you entered it at the variables form. When the case does not match, no replacement will be executed.

Click OK to close the form and in the data preview, you will already see that the filter selection is applied and that the data set returned was filtered.

You can now continue to build your data driven presentation. The nice thing now is that you can change the variables in one central place only, valid for all queries in your presentation. A real time saver for you.

Use of system variables

There are a few system variables in DataPoint that you can use in your queries. REPLACE://COMPUTERNAME, REPLACE://USERNAME, REPLACE://PRESENTATIONNAME, REPLACE://PRESENTATIONFULLNAME and REPLACE://PRESENTATIONPATH.

REPLACE://COMPUTERNAME will use your computer’s name in your queries. You could set up a database or datasheet with announcements per display or computer. You will have a list of announcements with 2 columns; computer name and the announcement text. With this system variable, you could set up a query that filters out the announcements of the current computer only. The computer name will be replaced at runtime.

REPLACE://USERNAME fills the variable with the username of the user logged on to the system.

REPLACE://PRESENTATIONNAME will be replaced by the name of the active presentation. This is the name of the file without path and extension.

REPLACE://PRESENTATIONFULLNAME replaces this variable text with the full filename of the active presentation. So, the filename including path and extension.

REPLACE://PRESENTATIONPATH will do the opposite. It will return the path only of the filename of the active presentation. Filename and extension are dropped from the value.

Command line generation tool

And even more. Our users of the DataPoint Enterprise edition can now use the command line tool or DataPoint Generator tool to create snapshot presentations with variables that are used in a command or instruction at the command prompt.

Just add the name of the variable and its associated value to the command line like this:

"C:\Program Files (x86)\PresentationPoint\DataPoint Enterprise edition\DP16GEN.exe" "name-of-our-presentation.pptx" "snapshot.pptx" /REPLACE ProductID=47 /REPLACE CountryID=1