accessing your data using reports, grids and queries
TRANSCRIPT
![Page 1: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/1.jpg)
Accessing Your Data
Using Reports, Grids and Queries
![Page 2: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/2.jpg)
Why is it important to know how to access your data?
To get the information you need
![Page 3: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/3.jpg)
How Data is Processed
Enter Data
Software validates and formats the data
Software puts data in correct DB, Tables, Records and Fields
Output
![Page 4: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/4.jpg)
How do we get the output?Use Prewritten Reports
Use Display Grids
Use Prewritten User Queries
Database Queries (MS Query and Excel)
![Page 5: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/5.jpg)
How Data is storedDatabase – File Cabinet for Data – NFBSData, GLData, APData and CTData
.
![Page 6: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/6.jpg)
How Data is stored
Tables – Drawers in the File Cabinet – tblAgency, tblDonor, tblProduct
![Page 7: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/7.jpg)
How Data is storedRecords – Folders in the Drawers – all of the
information for a particular Agency.
![Page 8: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/8.jpg)
How Data is storedFields – Pieces of Information in the Folder –
AgencyRef, AgencyName, ContactName, Email, etc.
![Page 9: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/9.jpg)
Queries & Reports
A way to look at the contents of several drawers
at once.
… or the content of several tables
at once.
![Page 10: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/10.jpg)
Using “Report Form”Report Groups/Menus
Required Selections
Optional Selections
Saving selection sets for reuse
“My Reports”
Exporting Reports
![Page 11: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/11.jpg)
Report FactsThere 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.
![Page 12: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/12.jpg)
Report Groups/Menus
![Page 13: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/13.jpg)
Report Menu
![Page 14: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/14.jpg)
Required Selections
Must make these selections in order for report to run.
![Page 15: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/15.jpg)
Optional SelectionsSelections for extra filtering
Table Name – “Friendly” name for table field is
stored in.
Field – “Friendly” name for the field.
Field Selections
![Page 16: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/16.jpg)
Optional Selections
Window at bottom – Displays what has been
selected.
![Page 17: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/17.jpg)
How to set an Optional SelectionSelect the field you want to filter on.
Double-Click
![Page 18: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/18.jpg)
Adding Optional Selections
Set the Operator
![Page 19: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/19.jpg)
Adding Optional Selections
1) Choose the field
value
Text fields include a dropdown.
![Page 20: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/20.jpg)
Adding Optional Selections
Date fields include a calendar.
![Page 21: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/21.jpg)
Adding Optional Selections
True/False (BOOLEAN) fields include a checkbox.
![Page 22: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/22.jpg)
Adding Optional Selections
2) Click the Add button
![Page 23: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/23.jpg)
Changing Optional Selections
Reset will clear ALL Optional
Selections for the report.
![Page 24: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/24.jpg)
Changing Optional Selections for 1 field
Double-Click on option to
change
![Page 25: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/25.jpg)
Changing Optional Selections
Highlight the selection to remove and press Delete on the
keyboard.
![Page 26: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/26.jpg)
Changing Optional Selections
![Page 27: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/27.jpg)
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.
![Page 28: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/28.jpg)
Saving Optional Selections
1) Give selection set a name.
2) Click Preview to save
This report only
![Page 29: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/29.jpg)
Saving Optional SelectionsAny
report with this
table
1) Give the option set a name
2) Click Save and Close to
save
![Page 30: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/30.jpg)
Reusing saved selections for same report
Saved option will appear for
this report.
Click the Use button to retrieve
the selection.
![Page 31: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/31.jpg)
Reusing saved selections
Selection is
retrieved
![Page 32: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/32.jpg)
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.
![Page 33: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/33.jpg)
Reusing saved selections
Selection is
retrieved
![Page 34: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/34.jpg)
My Reports
![Page 35: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/35.jpg)
My Reports
![Page 36: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/36.jpg)
My Reports
To remove a report from this
menu
![Page 37: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/37.jpg)
Exporting Reports
![Page 38: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/38.jpg)
Exporting Reports
![Page 39: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/39.jpg)
Exporting ReportsRecommend Data-Only Exports for these reports
![Page 40: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/40.jpg)
Exporting to Excel
![Page 41: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/41.jpg)
![Page 42: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/42.jpg)
Removing blank lines in Excel spreadsheetSelect one row. Edit –Go to – Click on “Special”
![Page 43: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/43.jpg)
![Page 44: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/44.jpg)
![Page 45: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/45.jpg)
![Page 46: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/46.jpg)
Using Grid Filters
![Page 47: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/47.jpg)
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
![Page 48: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/48.jpg)
Grid FiltersPantry – Group code = “Pantry”
![Page 49: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/49.jpg)
Grid Filters
Open Saturday morning – Open Saturday AM = True
![Page 50: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/50.jpg)
Grid FiltersBurlington or Camden County - County = “Burlington” or “Camden”
![Page 51: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/51.jpg)
Custom Grid Filters
![Page 52: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/52.jpg)
Copy Grid to ExcelRight-Mouse-Click Menu:
![Page 53: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/53.jpg)
![Page 54: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/54.jpg)
Clearing FiltersBlue columns indicate a filter set on field
Clear Filters will remove ALL filters
![Page 55: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/55.jpg)
Grid returns back to normal
![Page 56: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/56.jpg)
Queries
User Queries – Prewritten queries within Primarius that you can edit from a grid.
Database Queries – Queries designed by you within Excel
![Page 57: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/57.jpg)
User Queries
Donor, Agency, Product, Grant
Receipts, Receipt Details, Order, Order Details
Agency Membership Fee (Agency Menu)
Product MenuInventory AdjustmentsPhysical Inventory
Prewritten templates that you can edit to get the data you need
![Page 58: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/58.jpg)
Accessing User Query
![Page 59: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/59.jpg)
On grid, Right-Click Edit Columns
Initially get all records and all fields
![Page 60: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/60.jpg)
Selecting Fields to Display
Check the check boxes for the fields you want to show.
Edit User Query – Select Fields
Edit existing query
![Page 61: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/61.jpg)
Edit User Query – Set Filters
Only allows FROM and TO
![Page 62: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/62.jpg)
Edit User Query – Set Advanced Filters
![Page 63: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/63.jpg)
User QueryCreate a new Query from an existing query
![Page 64: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/64.jpg)
User Queries
![Page 65: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/65.jpg)
User QueriesAdvanced 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
![Page 66: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/66.jpg)
User QueriesQuery will now show under User Query, Query Type menu
![Page 67: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/67.jpg)
Copy grid to ExcelOn the grid, use right-click, Copy Grid Data to Excel to export data
![Page 68: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/68.jpg)
![Page 69: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/69.jpg)
Database QueriesConnecting to Data from Excel
![Page 70: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/70.jpg)
Database QueriesConnecting to Data from
ExcelCreate a new Data Source
Enter your Server Name
Give your data source a name that you can use to reference the data
![Page 71: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/71.jpg)
Database Queries
Click Options and select the correct database to
be used.
Ex. NFBSData = PrimariusGLData = General LedgerCTData = Donor Vantage
Choose the correct database
![Page 72: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/72.jpg)
Database Queries
![Page 73: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/73.jpg)
Database QueriesUsing Query Wizard
Select fields
Set filters on data fields
![Page 74: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/74.jpg)
Database QueriesUsing Query Wizard
Sort the data
Select output option
![Page 75: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/75.jpg)
Database Queries
![Page 76: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/76.jpg)
Database Queries
Without Query Wizard
![Page 77: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/77.jpg)
Database Queries
Select tables to include
Recommended :Use tables beginning
with “Uqry”
![Page 78: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/78.jpg)
Database Queries
Link tables with fields
![Page 79: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/79.jpg)
Database QueriesAdd the fields you want to show
![Page 80: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/80.jpg)
Database QueriesAdd criteria to set filter on the data.
The example above will give you only Authorized shopper Contacts
![Page 81: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/81.jpg)
Database Queries
![Page 82: Accessing Your Data Using Reports, Grids and Queries](https://reader038.vdocuments.us/reader038/viewer/2022102818/56649c735503460f949249b6/html5/thumbnails/82.jpg)
End of Accessing Your Data