When an institution begins its reporting efforts – let’s say after recently changing databases or reporting tools – one of the first steps usually taken is to replicate what they had before. Often, this means creating some type of file and distributing it to users.

While this step can be necessary and has the benefit of being easy for both the developer and the user, it falls short of the potential offered by reporting solutions such as Evisions Argos. As you move away from just producing files, new opportunities appear. This includes creating dynamic and interactive tools that can serve the varying needs of multiple users. One feature of Argos that can quickly take your dashboards and reporting to another level is a SQL variable.

What is a SQL variable?

As the name implies, a SQL variable* is simply a variable comprised of a SQL query. Within Argos, form objects such as list and multi-column boxes query and hold information. SQL variables do the same thing but do so in the background. The variable can query and hold one piece of information or many pieces for multiple records. This makes it possible to present information on a dashboard as formatted text instead of limiting the display to a box. Another benefit to using SQL variables, discussed in more detail below, is the ability to gather information that supports the user experience without the need to display it.

Examples of SQL variables at work

Oakland University uses SQL variables to reduce the need for user input and to bring focus to the information presented in the Argos datablock. Here are some examples of how they are used. (All of the examples are from a datablock created for academic advisers. The goal was to have one datablock serve many different advising and student support areas. This demanded flexibility but resulted in more complexity. SQL variables made achieving this goal possible.)

Preset parameters 

One of the variables that we use in many datablocks simply returns the current term code [see ‘sql_CurrentTerm’ in the illustration below]. For our student data work, almost everything is driven by this term code. The variable returns the current term code, which is then displayed in an edit box on the datablock’s form. This allows the user to change the term if needed.

A second variable we use looks at the term in the edit box and then pulls in additional data for that term, such as start date, end date, and aid year. This supporting information is used to control parts of the main data query. For instance, the term dates are used to limit advising note information to only those that were entered for the student during that particular term.

Focus the user experience

When you run a datablock, it knows who you are. It’s because your Argos account information is held in the User systems variables. Leveraging this, a SQL variable can pull in supporting information from Banner. In our advising datablock example, the variable returns the user’s name, PIDM and advising code. This information is then used to limit the list of students displayed to just those that are part of the adviser’s caseload [see ‘sql_GetUserInfo’ in the illustration above]. Many advisers can run the dashboard, yet each will only see the results relevant to them.

Similar to this tailoring of the user experience, we can use SQL variables to limit what information is displayed. Continuing with our advising datablock example, advisers can select an individual student to drill down into a detailed student profile. Some of the information we display on the student profile comes from the financial aid module of Banner and should only be accessible to those users with the proper credentials. To handle this, we use a variable to ask a ‘Yes/No’ question from the Oracle data dictionary as to whether or not the user running the datablock has the role required to see this information. If the answer is “No,” the navigation button and other form elements related to the privileged information is not visible to the user. If “Yes,” the information and related form objects display.

Managing your SQL variables

Once you have a collection of SQL variables that you want to use in your datablocks, there are other features in Argos that can help you manage them. The Library of Objects* is a great place to store your SQL variables for easy access. Keeping track of where you have used the variables can be done using the Object Contents* feature. Tracking variables in Object Contents allows you to adhere to a standard naming convention for your variables (which can be helped by storing standardized versions of them in the Library of Objects).

Conclusion

Building a datablock to be flexible and responsive to the individual user adds complexity to its design, but allows for one datablock to serve many different types of users. As you grow your reporting from one-off, file-based reports to include responsive dashboards, keep in mind that Argos has features that can take your design to a new level. SQL variables are one such easy-to-use feature. It can grow in complexity to meet the demands for flexibility in both your datablocks and in your overall reporting needs.

 

*These links take you to the Evisions Support site. If you’d like to view these knowledge articles and do not already have a log-in, please email CommunityRegistration@evisions.com to have an account created.