Customer Questions » Report Making

ID #1222

Entering a database query through the data browser. I need a custom report. Can I create the report by writing a database query directly?

You can write database queries directly through the iCare interface. To do so, do as follows. Also, see section on Using the Data Browser:
  1. Click on the Data Browser icon in the Toolbar.
  2. In the box titled, Write Your Query Here, type or copy and paste your query. See examples below. 
  3. For Select queries click on Accept Query.
  4. For Update, Delete or Insert queries click on Execute Query.
  5. To save the query for future use, enter a name for the query in the New Query box and click on Save Query. (Saved queries become available in the Adhoc Query Name drop-down box.) 
  6. To print or view the result as a report click on Show Report. 
  7. To save this report, click on Memorize. (Follow instructions on memorizing reports.) 

Additional Information and Samples

Sample Queries

Sample 1: Detailed subsidy balances report: (This query will give the account balance for each subsidy and each child. Note that this query is valid only if each child has had only one subsidy account. If children have had transactions under multiple subsidy accounts then a different query will be needed.)

Select ChildLastName, ChildfirstName, SubsidyBalance, subsidy_id, childsiteid, inactive from cr_child where subsidy_id = 'ccc' order by childlastname, childfirstname

Sample 2: New Enrollees for a Specified Period: (This query tells how many children started in a specified period. In the query you have to enter the dates as needed. To get data for a specific date enter same date in both places).

SELECT Count(cr_Child.Child_Id) AS CountOfChildrenEnrolled, cr_Child.ProgramName
FROM cr_Child
WHERE (((cr_Child.StartingDate)>#1/1/2008# And (cr_Child.StartingDate)<#6/1/2009#))
GROUP BY cr_Child.ProgramName;

æ Notes on passing Values in WHERE clauses

In your queries you will typically include some WHERE clauses to pin-point your query output. Below are some syntax rules for the WHERE clauses.

  • Date fields: Date values should be delimited by two ##s (number or pound signs). For example, [StartDate] > #1/1/09#.
  • String fields: String values should be delimited by two ""s (double quote marks). For example, [LastName] = "Smith".
  • Number fields: Numerical values should have no delimiters whether they are integers, real numbers or currency, etc. For example, [Child_ID] = 8032. Another example, [AccountBalance] = 667.5.
  • Yes/No fields: Use 0 for false and 1 for true with no delimiters. For example, Inactive = 1 (meaning all inactives).

æ Notes on Joins and other rules

  • Joins should be specified as 'Inner Join'.
  • If there are more joins than one, then all join clauses excepting one should be enclosed in a set of parentheses.
  • For queries in iCareSQL do not include 'dbo.' in specifying the table names.

æ Other Notes

Here are additional features that you can utilize after running the query.

  • For the Data Browser click on Show Report to run the query as an iCare ad hoc report.
  • Memorize the ad hoc report for future repeated use.
  • Click on the Excel button to take the result of the query to Excel.
  • Format the ad hoc report using the embedded Crystal Reports Designer.
æ Handling Special Fields
  • It is best to enclosed fields with reserved SQL names, such as, Date and Time, in [ ]'s. For example, if the database field is Date, which is also a reserved database variable, use [Date]. 

æ Execute Queries 

To run queries such as Update, Delete and Insert, use the Execute Query button instead of the Accept Query button.

æ Parameter Queries 

The Data Browser accepts parameters in Where clasues. Parameters should be enclosed in square brackets and preceded with the @ character like [@Select a Program Name]. When you run a parameter query an Input Box is opened asking you to enter a value for the parameter.

To run queries such as Update, Delete and Insert, use the Execute Query button instead of the Accept Query button.

æ Using a Like Operator for Wild-card Matches

The wild-card matching character used in the Data Browser is the asterisk (*). 

Example: Select Count(*) from cr_Child where ProgramName like '*todd*'.

This query will return records of all children in the Toddler 1 and Toddler 2, etc. programs.

Last update: 2012-04-13 17:38