sqlReports User Guide - Free Version

 
The following only applies to the Free version of sqlReports.

Any software mentioned in this guide is trademarked by its owner.   sqlReports is a standalone customization and integration into other customization downloads without the written consent of Dean Dahlvang and Matt Freund is prohibited.

Guide last updated on 10/20/2017.

Overview

Getting Started

Create / Edit a Report


Running a Report

The Results Page

sqlCharts

Exporting and Importing Reports


Overview

sqlReports 5 is the next generation of the PowerSchool customization created by Dean Dahlvang known as sqlReports.  Dean first introduced sqlReports in 2009 and it’s one of the most popular PowerSchool customizations in use.  It allows an admin to create tabular style reports using SQL, system variables, and user defined parameters.   SQL allows one to build powerful reports by giving the admin the ability to pull data from several tables at once and even from tables not seen in DDE, and the system variables and parameters allows the user to build reports that are user or school specific in the data that is pulled. Those things make sqlReports very powerful, very flexible, and very useful for yourself or other admins.

Below is a list of the various features in sqlReports 5. All reports created in previous versions of sqlReports will work with sqlReports 5.

  • Add After Report Title – the value of Parameter 1 can appear on reports
  • Add Calendar Icon to Parameter – makes entering dates easy
  • Add Gridlines to Reports – may make it easier for users to read
  • Column Filtering – drop-down menus allow you to filter on any column
  • Column Sorting – click on a column heading to sort by that column
  • Current Selection – click on a button to make the results the current selection
  • Drop-Down Parameter Menus – make it easy for users to enter information
  • Dynamic Row Numbering – row numbers update automatically after a filter or sort
  • Export Results – save what’s on the screen to tab or csv files, or print to pdf
  • Fixed Header when Scrolling – lock the header row on really long reports
  • Import / Export Reports – easily import new reports or export current ones
  • Multiple Row Selection – choose to export or print specific rows
  • Reorder Columns – the ability to move columns around on the output
  • Report Notes – keep notes on each report, such as creation date and changes made
  • Restrict Run Access – restrict who can see and run each report
  • Row Grouping – group the results of the report by a field
  • Show/Hide Columns – hide columns on the output
  • sqlCharts Lite – have the results of the report appear in chart format
  • sqlReports Teachers – create sqlReports for teachers to use
  • sqlReports Q – have the results of the report appear in the report queue


Back to Top


Getting Started

After you’ve installed sqlReports, you access it by clicking on the System Reports link on the admin left navigation and clicking on the sqlReports 5 tab:

sqlReports Menu and Links

FYI - there is a free download at this site that will add a sqlReports link under the Reports heading on the left navigation menu and make it easier to get to sqlReports. It can be found on the Free Add-Ons page under the sqlReports menu.

Admins who are in the preferred sqlReports admin group will have links to create a new report, import a new one, or change the preferences.   Links to create a new report or import one will appear at the top of the page or at the bottom. The preferences link, which is used less often, only appears at the bottom of the page. Below is a breakdown of each of the options:

  • Create a new sqlReport – this allows you to create a report from scratch. Once the report is created, it’ll appear listed under the Report area and you’ll be able to edit it by clicking on an edit icon that will appear to the right of the title. Only admins in the preferred security group will see the edit button. While you’re working on a report, it’s a good idea to check the box to “Exclude from List”. That will allow you to run it and edit it and those not in the preferred security group will not see it.
  • Import a new sqlReport – this allows you to import new reports, such as those found at sisresources.com, or you can use it to import a copy of an existing report. Importing a report is very easy to do – it’s just a matter of copying and pasting text. Importing a report is covered in more detail later on in the guide.
  • Configure sqlReports preferences - the only preference to configure is to change the preferred admin security group. If you accidentally change the group and need to change it back, see the installation guide for the steps to do so.


Admins who are not in the preferred sqlReports admin group will not see the above options and will not see the icons to edit reports.

Back to Top


Create a New Report

To create a new report, click on the ‘Create a new sqlReport’ link. An important message will appear to tell you that only certain parts of the report are saved initially. Fill in any of those parts first, click Submit, and then edit the report and add more information.

sqlReports Create New Report Message

You’ll see a tab menu at the top of each report or when you create a new report or edit an existing report.  The next few pages will cover these different tabs and will be referenced in documentation as Edit, Report – Edit, Parameters – Edit, Student Selection – Edit, Run Access – Edit, Results Page – Edit, Notes.

Back to Top


Edit - Report

Below are breakdowns of each part of Edit, Report with information filled in for a sample report of basic student information:

Edit sqlReports Name and Title

You can give each report a name and title. The ‘Add After Report Title’ allows you to add information after the title related to Parameter 1.  You can add a drop-down value or the value typed in the Parameter box.

Edit sqlReports Group and Description

The Report Group allows you to group your reports together on the sqlReports home page to make them easier to manage.  You do not need to use report groups, but it makes it easier to work with reports if you have several in your system.  By default the list will be blank and to add a new group to the list, just type the group name in the blank part of the drop-down menu.  To remove a group, just remove all the reports from the group and the group name will disappear.

The report description will appear when an admin runs the report, so it’s a good place to describe the report.  The description will also appear when a user hovers over the link to the report.

Edit sqlReports Query and Headings

The SQL Query box is where you’ll enter the SQL that will be run when the report runs. It’s best to test your SQL in an SQL client if possible, such as Oracle SQL Developer, in order to troubleshoot the initial query.  You can use system variables, such as ~(curschoolid) in your SQL, as well as user-defined parameters, which are described in more detail below in the Edit, Parameters section.

You do not need aliases as part of your main select because the Column Headings area has the column headings.  However, there is an alias you can use to display external expressions, such as HR(A) instead of 1(A).  Just add externalexpression as an alias to the exression field in your sql, such as cc.expression externalexpression.  The externalexpression alias will trigger coding in the report to display the external expression.

The Column Headings area will help you build the header for the report.  Click on the Build Headings link if the box is empty and you want assistance in building the headings.  When you click on the link, a box will pop up and tell you how many columns were found based on the SQL select clause.  If you click OK, generic headings like the following will be added to the Column Headings box:

<th>Column1</th><th>Column2</th><th>Column3</th><th>Column4</th>

You can then change each Column reference to a column heading you want to use, such as DCID, Student, Grade Level, and Home Phone.   If you add or remove fields from your SQL select clause, you can click on the Build Headings link to redo the header, however, it will recreate what’s in the box and you’ll lose any existing heading information.  If you add some fields and don’t want to lose what’s there, then just add the new heading with the th’s. For example, if city was added in the example above after phone, just add <th>City</th> to the header information rather than click on Build Headings and having to retype all the headings.

There are several class=”” commands you can use in the Column Headings area:

  • <th class=”DCID”> and <th class=”DCID DISPLAY”> - use if you have a column of student dcid’s that you want to use for the current selection. See Option B in the Edit, Student Selection section below for more information regarding the DCID options.
  • <th class=”HIDE”> - allows you to hide a column if you’re using the Show/Hide Columns button or need to use a field for the sql but do not wish to display it.  
  • <th class=”NOCF”> - NOCF is short for No Column Filter and is used to keep a column from having a column filter option with it.
  • <th class=”ROWGRP”> - ROWGRP stands for Row Group and is used when you want to group rows. See the Edit, Results Page section below for more information on both.


The ‘Convert td’s to th’s’ will convert the td’s in the headings of older reports to th’s.  In the versions of sqlReports before sqlReports 4, the column headings were created using td’s, such as <td>Column1</td>.  To take advantage of the DataTables jquery plugin that's used to display the data, th’s are used, and clicking on the link will change the td’s to th’s. It shouldn’t be necessary to use with new reports as th will be used by default if you click on Build Headings.

Edit sqlReports Field Level Security

You can enforce field level security on your reports.  If a person has No Access rights to a field, you can include the field in the report and *'s will appear instead of the actual value on the output.  For it to work you must have coding in the Field Level Security box like above, which follows the format of mapfield=column#>table.fieldname.  If there's more than one column, add a comma, and then another column#>table.fieldname.  The column# will be the column number in the Column Headings box, and table.fieldname will be replaced by actual table and field names.

For example, if you have the home_phone field from the students table in a report and in the Column Headings box that is column 4, it would look like mapfield=4>students.home_phone. If you also have a reference to the SSN field in the report and that's in column 5, then the full command would be mapfield=4>students.home_phone,5>students.dob.

Clicking on the Build Map Field will create a generic mapfield command which you can use as a guide. You'll need to replace the table.fieldname parts with the actual table and field names (do not use aliases - use the actual names for both), and remove any part of the coding that you do not need.

Edit sqlReports Exlude and Last Run Info

‘Exclude From User List’ will hide the report on the report list from other users. It’ll allow you to create new reports and keep others from running them until you’ve finished them.

The ‘Last Run Information’ line will show you the last time the report was run and the user who ran it. Below that are a couple lines that can be expanded to show more information and tips and tricks about the SQL Query box and the Report Headers box.

Back to Top


Edit - Parameters

The Parameter section has two parts to it – Directions and Parameters:

Edit sqlReport Parameter Directions

The ‘Directions’ box was added in version 4.6 and instead of placing detailed directions in the Description box, you can add them to the Directions box instead.  The directions will appear on the Run sqlReport page under the description when a user runs a report.

Edit sqlReport Parameter Values

Parameters allow you to prompt users for information for the report.  Each report can have up to five parameters.  The ‘Name to Display’ part is where you type in a label that users will see when they run the report.  For example, if Enter a Grade Level is entered in the box for Parameter 1, the user will see a box to enter a grade level when they run the report.

You can enter a default value for the parameter, such as 12 in the example above, but once the user runs the report and enters a value in the parameter, that will become the default value.

The checkbox in the ‘Add’ column will add a calendar icon next to the entry box and users can click on the icon to pick a date.  For example, if you have a label of ‘Enter Beginning Date’, you can add a calendar and if a user clicks on the date in the calendar, the date will be copied into the parameter entry box.

The ‘Use Drop Down’ part allows you to choose a built-in drop-downs or one that you create on your own (see the Create Drop-Downs Menus article at sisresources.com information on how to create your own).  If you choose a drop-down, the parameter entry box will be replaced by the drop-down menu.  The Help built into sqlReports has a description of all of the drop-downs and what value they pass to the SQL.

Back to Top


Using Parameters in the SQL Query:

You reference parameters in your SQL Query by using %param1%, %param2%, %param3%, %param4%, and/or %param5%.   For example, using the grade level parameter example above, one would reference Parameter 1 as %param1% in the query like so (be sure to use all lowercase for the word param):

select dcid, lastfirst, grade_level, home_phone
from students
where enroll_status = 0 and schoolid = ~(curschoolid) and grade_level = %param1%
order by lastfirst

When the report is run, the %param1% will be replaced by the actual value the admin gave the parameter when running the report.  For example, if a user typed in 12 in the Enter a Grade Level box in the previous example, then the query would use grade_level = 12.  Before using parameter coding, test your report using actual values and then replace the actual value with the coding.  For example, try the following first:

select dcid, lastfirst, grade_level, home_phone
from students
where enroll_status = 0 and schoolid = ~(curschoolid) and grade_level = 9
order by lastfirst

If it runs okay, then replace the 9 with the parameter coding and run the report and enter a 9 in the Enter A Grade Level part and see if you get the same results.  If your value is in quotes, then also put the parameter coding in quotes.  For example, if you have the following in your query:

where termid = ‘S1’

and you have Parameter 2 set up to where a user enters a term, then use:

where termid = ‘%param2%’

If your query is using an in, such as grade_level in (9,12), then you would code it as grade_level in (%param1%) and in the directions tell the user to enter values in a comma delimited list.  f it’s alphanumeric, such as termid in (‘Q1’,’Q2’), you would use termid in (%param1%) and tell your user to enter the parameter value as ‘Q1’,’Q2’.

Back to Top


Edit - Student Selection

If your report involves the listing of students and you’d like to make those students the current selection to use for group functions, you have a couple options:

Edit sqlReport Student Selection Option A

Option A was the option used prior to sqlReports 4 and it creates a link toward the bottom of the report that one can click on to create the current selection.  The link only appears if you put a check in the box to the right of ‘Use Option A’.  As mentioned in the blue part above, the current selection will include all the students in the original query because the query is used to build the selection.  You would click on the Build Query link to create the SQL.

Edit sqlReport Student Selection Option B

Option B started with sqlReports 4 and is the recommended approach to take.  It adds a button to the results screen that one clicks on to create the current selection of what is currently on the screen.   If you start out with the entire student body and filter the results down to just one grade, then that one grade will become the student selection.  Option A on the other hand would create the current selection based on the entire student body even if you filter the results. As mentioned in the screenshot above, you will need to include the student DCID field in your SQL Query and then use either class="DCID" or class="DCID DISPLAY" code in the column heading for the coding to work.

If you choose to use Option B only, you do not need to fill in Option A, or vice versa. There are some issues with Option B and Row Grouping, so if you enable Row Grouping for a report, Option B will be disabled.

Back to Top


Edit - Run Access

Edit sqlReport Run Access

Run Access was introduced in sqlReports 3 and is a way to limit who can run the report based on PowerSchool groups.  For example, if you create a report and only want those in the Counselors group to run it, you would choose that group.  Only those in the Counselor group would then see the report listed on the sqlReports main menu page.   Admins in the preferred admin security group, which is the group that creates and edits reports, will see all reports.

Back to Top


Edit - Results Page

The Edit, Results Page tab contains four parts – Use Non-Flash Buttons, Create as a Chart, Page Buttons and Page Options.

sqlReports Edit Results Page - Non-Flash Buttons

The ‘Use Non-Flash Buttons’ was added in sqlReports 5 and allows one to choose to run reports with non-Flash buttons on the output page.  This setting is on a report-by-report basis, so you can choose to run reports with Flash buttons or use the non-Flash buttons.  Reports with the box checked will have a blue H icon on the report name line to signify the report uses HTML5 buttons and not Flash buttons.

sqlReports Edit Results Page - Charts

The ‘Create as a Chart’ was added in 4.3 and when checked, the output is a chart rather than a tabular report.  The Initial Chart allows you to choose a default chart to use with the report.

sqlReports Edit Results Page - Buttons

Every sqlReports report will show or hide certain things by default on the results page.  The ‘Page Buttons’ area allows you to show or hide these default items.  The first five items deal with the buttons that appear by default – Copy allows one to copy the results to the clipboard, CSV and Tab will export the data into csv or tab files, Print will display a printer friendly version of the results, and PDF will save the results to PDF.  If you don’t want users to have access to one or more of the buttons, you can hide them by checking the boxes.

The Show/Hide Columns button allows users to hide columns from view (they are also left off of the results if the user clicks on any of the other buttons) or show any hidden columns.  By default the feature is disabled for a report and you must go here to enable it.  You can use class=”HIDE” on a column header in the Report Header area to hide a column initially. An admin would then need to use the Show/Hide Columns button to view that column.   If you enable Column Filtering or Row Grouping, the Show/Hide Columns button will be ignored because its functionality causes conflicts with the column filtering and row grouping options.

sqlReports Edit Results Page - Some Page Options

The Page Options part of the Edit, Results Page area allows you to control non-button related items.

‘Open in New Window’ will display the results in a new tab or window.

‘Display Debug Info’ will show you the sql with the actual values for system variables or parameters, which will allow you to see if they’re being used properly.   A link titled SQL Query will appear on the results screen, and clicking on it will show the sql.  Starting with 5.1, text will appear that links to the System Log page where you can check for Oracle errors.

‘Hide the Parameter Line’ was added in 4.7.0 and allows you to hide the parameters line if needed.  By default, if no parameters are used, the line will be hidden, but this allows for the option of hiding it even if there are options.

‘Show Gridlines’ was added in 4.7.0.  By default reports will have rows with alternating colors, but this option will allow you to have gridlines on the report as well.

‘Include Row Numbers’ will add a column at the beginning of the report with row numbers.  The numbering is dynamic, so if one sorts by column headings, or filters the results, the row numbers will update.

‘Show the Search Box’ will add a search box to the page which will search all columns.  As information is typed in the box, the results will filter down. Only those results are used when one clicks on any of the buttons to copy, export, or print to pdf.

‘Show the Results Info Line’ will add a line to the bottom giving a count of the number of records displayed out of the total number found.

‘Enable Column Filtering’ will add a dropdown filter to each visible column.  A column can be excluded by adding class=”NOCF” to the column heading in the Report Header area.  The dropdown will include information from the column and as an item is picked it will appear under the filter.  One can click on the item name to remove it from the filter.  Column Filtering requires the Search Box to work, so it the box will appear on the report output even if it hasn’t been checked.  Also, Show/Hide Columns and Column Reordering do not work correctly when Column Filtering is used, so they are disabled on the output even if they have been checked.

‘Enable Column Reordering’ will allows users to move columns around on the page by clicking on the column heading and dragging and dropping it where they want it to appear.  The order is used when one clicks on any of the buttons to copy, export, or print to pdf.  If you enable Column Filtering or Row Grouping, the Column Reordering will be ignored because its functionality causes conflicts with the column filtering and row grouping options.

sqlReports Edit Results Page - Row Grouping

‘Enable Row Grouping’ will group rows together in the output based on a common field.  You set the field to use by adding class=”ROWGRP” to the column heading for the field in the Report Header area.  For example, if you have a report that lists low current grades with course names being one of the fields, you can group the output by course names.  There are two options on how the output will look – 1) you can have the output show everything and when you click on a group name the rows under it will be hidden, or 2) only the group names are shown and if you click on the name then the rows under it are shown.  

‘Show Row Grouping in Descending Order’ will allow you to have the row grouping listed in descending order rather than ascending order.

‘Display Column Used for Row Grouping’ will include the column used for Row Grouping in the report.  If you plan to export the results to file or pdf, be sure to use this option, otherwise the field used for grouping will be left off.

‘Show Row Grouping Counts’ will display the number of rows in the group in the gray grouping row on the screen.  If you have all the rows collapsed, the numbers will let you know how many items are in each group.

sqlReports Edit Results Page - More Options

‘Enable the Fixed Header Row’ will freeze the header row at the top of the screen when one scrolls down the results page to view results past the bottom of the screen.

‘Disable Multiple Row Selection’ will disable the ability of users to highlight certain rows and only have them used when one clicks on any of the buttons to make current selection, copy, export, or print to pdf.

‘Use landscape for PDF’ will let you set the report to be printed in landscape orientation when using the PDF button.  If unchecked, the PDF will be in portrait orientation.

‘No Double Quotes Around Data on CSV Export’ will remove double quotes around exported data when the CSV button is clicked.  Double quotes keeps things together in a column when a comma is possible, such as lastfirst names, so checking this is not recommended unless absolutely necessary.

‘No Header Row on CSV or Tab Export’ will remove the header row on those two types of exports.

Back to Top


Edit - Notes

The Notes area allows one to type in notes related to the report. For example, one could type in when the report was created or imported and the date and by whom, and then whenever a change is made to the report, that information could be added. Starting with 4.5a, the Notes area also shows when a report was created or imported, and the last time it was updated.   Reports prior to 4.5a will have a line mentioning that they were created or imported prior to 4.5a.

Back to Top


Running a Report

To run a report, click on the sqlReports 5 tab and a list of reports will appear:

sqlReports Main Menu

The list will only include reports that the admin has access to and will be listed either under a report group or as part of a list of those not in a group.  If an admin is part of the group allowed to create and edit reports, an edit icon (a pencil icon) will appear to the right of the report title.  When the icon is clicked, the Edit pages will appear for the report. If a report is set to run as a chart, a chart icon will appear to the right of the report title.  Reports with a C icon have been set to display as a chart, and reports with a blue H icon are using the non-Flash buttons.

To run a report, a user clicks on the report title.  If users are not sure what the report does, they should be able to hover their mouse over the report title and what you typed in the Report Description box will appear.   After clicking on the report name, the user is taken to the parameters input page. If the report was built with parameters, the options will appear like so:

sqlReports Run sqlReport

Once the user fills out the parameters and clicks on the submit button, the results page will appear.  If there are no parameters, the user will still see the Name and Description parts and would need to click on the submit button to go to the results page.

Back to Top


The Results Page

Below is an example of the results page using the Flash buttons.  All data in the screen shots is fictional and based on the Apple Grove training database from PowerSource:

sqlReports Flash Buttons Output

If a user entered any parameters, those will show at the top of the report unless the ‘Hide the Parameter Line’ has been checked for the report. Buttons will appear next and clicking on any of the buttons will invoke the following actions for the results on the screen at the time:

  • Make Current Selection – will make the students the current selection of students and take the user to the Group Functions page.
  • Copy – will copy the results to the clipboard and the user could paste the results into another application.
  • CSV – will create a comma-delimited file with a file extension of csv.
  • Tab – will create a tab-delimited file with a file extension of txt.
  • Print – will display the results in the browser window in a printer friendly version.
  • PDF – will print the results to a file with a file extension of pdf.


The Copy, CSV, Tab, and PDF output will include just the column headings and the row information that is currently on the screen.  Print and PDF will also include the report title and any information marked to include after the report title on the Edit, Reports page.

One can sort the results by clicking on a column heading.  The up and down arrows to the right of the column heading can be clicked on to set ascending or descending order. To sort by more than one column, click on a column and then hold down the shift key and click on the other column heading(s) or column arrows.  The search box allows one to filter the results before clicking on any of the buttons.  Row numbering should update as results are filtered.

Below is what it looks like if non-Flash buttons are used:

sqlReports Non-Flash Buttons Output

The buttons will operate the same as the Flash buttons, with one major difference - one must select results before using any of the non-Flash buttons. One can either click on the Select Results button to select everything on the screen or click on individual rows.   One can also use the Shift or Ctrl keys to choose specific rows.  Hovering over any of the buttons should produce a tool-tip popup reminding the user to select data first.

The use of non-Flash buttons allow users to use buttons on devices that do not have Flash installed, but you may notice some performance lag with the new version when using column filtering and/or row numbering.  It’s best to test out the report first to see if using non-Flash buttons will cause any performance issues.

Below is another example, this one with all the buttons enabled, column filtering without the Percent column filtered, and row grouping by course name:

sqlReports Output with Several Options

When column filtering is enabled, dropdowns will appear for the columns on the report except for those that are hidden or have been set for no column filtering.  When row grouping is enabled, the data from the field that is used for row grouping will appear in a gray row, followed by items in that group. Clicking on the gray row will expand or collapse the rows of items within it.

Back to Top


sqlCharts

sqlCharts allows you to create single series or multi-series charts using sqlReports.  You build a report like normal and make sure it returns data, and then you check a box to tell it to run as a chart.  When you run the report, you will first be taken to the report parameters page like normal, and you can fill out any user parameters that have been created.  When you click on the Submit button, you will be taken to the chart parameters and output page.  sqlCharts is available in both the admin portal and the teacher portal of sqlReports.

With sqlCharts you create normal sqlReports reports but with two main requirements:  1) the first column is used for labels in the chart and can contain numbers or letters, and 2) the rest of the columns can only contain numbers and cannot have any blank cells (you may need to use a nvl command in your sql in some cases to make sure a zero appears instead of null data).  Below are additional notes regarding report setup:

  • Edit, Report, Report Title – this will be used as the default Chart Title, but can be changed on the Chart Parameters page
  • Edit, Report, Add After Report Title – this will be used as the default Chart Sub Title, but can be changed on the Chart Parameters page
  • Edit, Report, Report Header – the heading for your first column will be used as the label for the X axis, but can be changed on the Chart Parameters page, the heading for the other columns will be used in the legend if there are multi series
  • Edit, Results Page, Create output as chart – this box must be checked for charts, if it isn’t checked, the regular sqlReports results page will appear
  • Edit, Results Page, Initial chart - the initial chart for each report, the chart chosen will appear at the top of the drop-down on the Chart Parameters page and be the selected chart
  • Edit, Results Page, Page Buttons – the chart page will show a data table at the bottom of the page and you can show or hide the buttons on it
  • Edit, Results Page, Page Options – the following can be used with charts and will act like they do on a non-chart report: Open in New Window, Display Debug Info, Show the Search Box, Enable Column Filtering, and Enable Column Reordering


You can use system variables and parameters in your chart reports just like you do in regular reports.  Chart reports will ignore anything under Edit, Student Selection, since you’re producing a chart rather than a listing of students like in a regular report.  You can use Edit, Run Access like you do a regular report and only allow certain security groups to have access to the chart report.

Once you have the report set up, run it like normal from the sqlReports home page. You will see a C icon to the right of the report title to signify the report output is a chart.


Chart Parameters and Output Page

The chart parameters and output page has three main areas – 1) the Chart Parameters area, 2) the chart output area, and 3) the Data Used To Create Chart area.

sqlCharts Input

Below is a breakdown of the Chart Parameters – you can change any of the settings and click Submit and the chart will update:

  • Chart Type – there are over 25 choices in the menu, some are single series, which will only chart the first two columns of data, and some are multi-series, which will chart all the columns.  If the sqlReport only contains two columns, only the single series chart options will be listed.  A breakdown of charts can be found here.
  • Chart Title – by default the report title will appear, but you can change it. The chart title is also used as the default file name if you save the chart as a jpeg or png file.
  • Chart Sub Title – if you have something chosen in the Add After Report Title in the report setup, it will appear here by default, but you can change it. The sub title will appear under the main title on the chart.
  • X Axis Label – by default will be the column heading of the first column, but you can change it.   With bar charts, the label will appear on the left of the chart, while with other charts it will appear on the bottom. Pie and doughnut charts do not have a x axis, so anything in the box will not show on them.
  • Y Axis Label – by default will be blank, but you can change it.   With bar charts, the label will appear below the chart, while with other charts it will appear on the left of the chart. Pie and doughnut charts do not have a y axis, so anything in the box will not show on them.
  • Starting Y Value – by default will be blank and the starting y value will be zero. A user can change the value, however, any value that is set too high will revert back to zero.
  • Chart Size – default is 600 x 400 but you can change it if you want a larger or smaller chart.
  • Pie / Doughnut Charts Only – allows you to have the chart display either the value in the data column or it’s percent of the overall total
  • Stacked Bar / Column Only – allows you to add the total of the stacks to the chart
  • Background Color – allows you to remove the default color and just have a white background.
  • Border Around Chart – allows you to remove the black border around the chart.


Data Used To Create The Chart

At the bottom of the page of the sqlCharts page you'll see a table displaying the data from the report.  sqlCharts uses the table to create charts and multi-series charts will graph all of the data in the table.  If there are only two columns, multi-series charts will not be available and only single series chart options will be available.

Saving a Chart

Once a chart is being displayed on the page, you can save it by making a screen capture of the chart.  Both Windows and Macs come with a snipping tool built in - Snipping Tool if you use Windows, and Grab if you use a Mac.


Back to Top


Exporting and Importing Reports

The reports are stored in the database in the Gen table and will be a part of your database backups, but you can export a report at any time in case you want a backup copy, you want to import it as a new report with changes, or to have a copy to share with someone.  To export a report, from the main sqlReports page click on the icon to edit the report.  Then scroll to the bottom of the edit page and look for the following:

Export sqlReport link

The export will create a text file called sqlReport.txt by default, but you can rename the file (leave the .txt part) and it’s recommended to do so to avoid overwriting previous exports.

To import a report, click on the ‘Import a new sqlReport’ on the main sqlReports page, which will bring up a page with a window to copy and paste information into:

Import sqlReport

Open up an export text file with a text editor, such as Wordpad, and highlight all the information in it and then copy that information into the import window.  Once you have the information copied, click on the Submit button to import the report.

What needs to be included on an import file for sqlReports 4 and higher is different than for earlier versions because of all the new enhancements. If you try to import an export file made in an earlier version, it won’t import correctly because it will be missing required lines. For example, if the import is missing an <AfterTitle></AfterTitle> line, the import won’t process the parts following it correctly.

If newer versions add more information, those lines will appear at the bottom of the layout. See the Import page for the proper layout of the import file.

Back to Top

 
Back to the main Documentation page.