All data that is displayed in a report must first come from a query. A query is simply a SQL statement that the data provider uses to grab the data from the database. Teaching you SQL is beyond the scope of the SQLView Pro documentation but there are many good resources to guide you along. One I found recently that provides an interactive online experience is
http://www.sqlzoo.net/ Query Tokens
Query tokens are special keywords surrounded with square brackets that are substituted at query time with dynamic data. There are various forms of query tokens depending on the context of the data.
databaseOwner and objectQualifier ¶
There are two DNN specific tokens available which DNN module developers and power users are sure to recognize. The two tokens I'm referring to are the databaseOwner and objectQualifier tokens. These two pieces of information are specific to your DNN installation and are necessary if you want to create truly portable queries across DNN installations. In the majority of cases the databaseOwner equates to
dbo and the objectQualifer is an empty string but not always. For this reason it's important that when referring to intrinsic DNN tables or tables related to 3rd party DNN modules you include both of these tokens before table, view and stored procedure names.
The format of these tokens is the same as in the standard DNN sql page:
{databaseOwner} and
{objectQualifier}or you can use the shorted forms,
{dO} and
{oQ}
Example
If you were going to select all users from the DNN database for a query you would use
SELECT * FROM {databaseOwner}{objectQualifier}Users or the shorted form
SELECT * FROM {dO}{oQ}Users Parameter Tokens
If you are using parameters to drive your report, you'll undoubtedly want to include the corresponding parameter token for that parameter into your query to filter your data or there is no point in having the parameter in the first place. To do this you'll need a special parameter token.
The format of the parameter token is as follows:
[PARAMETER:{parameter name}] where {parameter name} is the name of your parameter
Example
If I have a parameter named
Department then I would use
[PARAMETER:Department] in my query.
Querystring Tokens
Querystring tokens are useful if you wish to drive your report based on the value of one or more query strings of the current page.
The format of the querystring token is as follows:
[QUERYSTRING:{querystring name}] where {querystring name} is the query string key
Example
If I have a query string key named
ShowAll then I would use
[QUERYSTRING:ShowAll] in my query.
Let's assume the page url is
http://www.something.com/default.aspx?ShowAll=1 and my query is
SELECT * FROM Employees WHERE State = 'NY' OR [QUERYSTRING:ShowAll].
The resulting query will be
SELECT * FROM Employees WHERE State = 'NY' OR 1
WARNING
It is recommended that every time you use querystring parameters or any parameters that you cannot fully authenticate the data for, you create a stored procedure and pass the query string parameters into the stored procedure in order to combat against sql inject attacks