Transcript Accessing Your Data

Accessing Your Data
Using Reports, Grids and Queries
Why is it important to know how to
access your data?
To get the information you need
How Data is Processed
Software validates and formats the data
Enter Data
Output
Software puts data in correct DB, Tables, Records and Fields
How do we get the output?
Use Prewritten Reports
Use Display Grids
Use Prewritten User Queries
Database Queries (MS Query and Excel)
How Data is stored
Database – File Cabinet for Data – NFBSData, GLData,
APData and CTData
.
How Data is stored
Tables – Drawers in the File Cabinet – tblAgency,
tblDonor, tblProduct
How Data is stored
Records – Folders in the Drawers – all of the
information for a particular Agency.
How Data is stored
Fields – Pieces of Information in the Folder –
AgencyRef, AgencyName, ContactName, Email, etc.
Queries & Reports
A way to look at
the contents of
several drawers
at once.
… or the content
of several tables
at once.
Using “Report Form”
Report Groups/Menus
Required Selections
Optional Selections
Saving selection sets for reuse
“My Reports”
Exporting Reports
Report Facts
There are over 370 prewritten “standard”
reports included in the Primarius system.
Each report is assigned a Group that best
fits the menu to attach it to.
Each food bank has its own set of Custom
reports (Number varies by food bank).
All reports are written in Crystal Reports
2008. Only CR 2008 will run from
Primarius Report Interface.
Report Groups/Menus
Report Menu
Required Selections
Must make these selections in order for report to run.
Optional Selections
Selections for extra filtering
Table Name –
“Friendly” name
for table field is
stored in.
Field –
“Friendly”
name for
the field.
Field
Selections
Optional Selections
Window at bottom –
Displays what has been
selected.
How to set an Optional Selection
Select the field you want to filter on.
Double-Click
Adding Optional Selections
Set the
Operator
Adding Optional Selections
1) Choose
the field
value
Text fields include a dropdown.
Adding Optional Selections
Date fields include a calendar.
Adding Optional Selections
True/False (BOOLEAN) fields include a checkbox.
Adding Optional Selections
2) Click
the Add
button
Changing Optional Selections
Reset will
clear ALL
Optional
Selections for
the report.
Changing Optional Selections for 1 field
DoubleClick on
option to
change
Changing Optional Selections
Highlight the selection to remove
and press Delete on the
keyboard.
Changing Optional Selections
Saving Optional Selections
- Save selections to be reused for one specific
report.
- Filters can be from multiple tables.
- Save selections to be reused for any report.
- Filters from one table only.
- Can be used with any report containing the same
table.
Saving Optional Selections
This report only
1) Give selection
set a name.
2) Click
Preview to save
Saving Optional Selections
2) Click
Save and
Close to
save
Any
report
with this
table
1) Give the
option set
a name
Reusing saved selections for same report
Saved option
will appear for
this report.
Click the Use
button to retrieve
the selection.
Reusing saved selections
Selection
is
retrieved
Reusing saved selections for same table
Saved option
will appear for
any report with
same table
Click the Use This
button to retrieve
the selection.
Reusing saved selections
Selection
is
retrieved
My Reports
My Reports
My Reports
To remove
a report
from this
menu
Exporting Reports
Exporting Reports
Exporting Reports
Recommend Data-Only Exports for these reports
Exporting to Excel
Removing blank lines in Excel spreadsheet
Select one row. Edit –Go to – Click on “Special”
Using Grid Filters
Using Grid Filters
Example - All Pantries within Burlington or Camden County
that are opened on Saturday morning.
Pantry – Group code = “Pantry”
Burlington or Camden County - County = “Burlington” or
“Camden”
Open Saturday morning – Open Saturday AM = True
Grid Filters
Pantry – Group code = “Pantry”
Grid Filters
Open Saturday morning – Open Saturday AM = True
Grid Filters
Burlington or Camden County - County = “Burlington” or
“Camden”
Custom Grid Filters
Copy Grid to Excel
Right-Mouse-Click Menu:
Clearing Filters
Blue columns indicate a filter set on field
Clear Filters will
remove ALL filters
Grid returns back to normal
Queries
User Queries – Prewritten queries
within Primarius that you can edit from a
grid.
Database Queries – Queries
designed by you within Excel
User Queries
Prewritten templates that you can edit to get the data you need
Donor, Agency, Product, Grant
Receipts, Receipt Details, Order, Order Details
Product Menu
Inventory Adjustments
Physical Inventory
Agency Membership Fee (Agency Menu)
Accessing User Query
Initially get all records and all fields
On grid, Right-Click Edit Columns
Edit User Query – Select Fields
Edit existing query
Selecting Fields to Display
Check the check boxes for the
fields you want to show.
Edit User Query – Set Filters
Only allows FROM
and TO
Edit User Query – Set Advanced Filters
User Query
Create a new Query from an existing query
User Queries
User Queries
Advanced Selections
Add a New Condition
Select Column Name (Field)
Select an Operator (Is:)
Select Data
If adding more than one
condition, select Open, And
or Or and Close Options
Press Save
User Queries
Query will now show under User Query, Query Type menu
Copy grid to Excel
On the grid, use right-click, Copy Grid Data to Excel to export data
Database Queries
Connecting to Data from Excel
Database Queries
Connecting to Data from Excel
Create a new Data Source
Give your data source a name that you can use to reference the data
Enter your Server Name
Database Queries
Click Options and select
the correct database to
be used.
Choose the correct
database
Ex. NFBSData = Primarius
GLData = General Ledger
CTData = Donor Vantage
Database Queries
Database Queries
Using Query Wizard
Select fields
Set filters on data fields
Database Queries
Using Query Wizard
Sort the data
Select output option
Database Queries
Database Queries
Without Query Wizard
Database Queries
Select tables to
include
Recommended :Use
tables beginning
with “Uqry”
Database Queries
Link tables with
fields
Database Queries
Add the fields you want to show
Database Queries
Add criteria to set filter on the data.
The example above will give you only Authorized shopper Contacts
Database Queries
End of Accessing Your Data