Home

Extracting the Data

1. 2 A PAGE 11 U 19 n PAGE 1 U 6 n PAGE 11 U 11 Owner Salary Owner Bonuses amp Mgmt Fees GM Salary GM Commissions GM Bonuses ba Directors Fees BED Clerical amp Gen Maint Salaries nf PAGE 2140415 B6205 Salaries Accounting 66210 Salaries Clerical 66215 Salaries Maintainance 66225 Bonuses Administration a 66300 Sales Department Wages nf PAGE 21 D 13 n PAGE AT FFTS HAT PAGE 2 0915 n PAGE 210315 HH PP GE 20 7 nit j Wages Sales Management nf PAGE 2 0 13n PAGE 214413 Commissions Sales Management Bonuses Sales Management E Wages Sales People PAGE SDE FAT PAGE 2 hr 7 Commissions Sales People Salaries Sales Clerical Expand els Add Mew Item Remove Item Print cy Previ Calc Save Cancel Collapse Add Sub Item Remove Branch review sl The top section of this form displays the same information on the template list and cannot be changed on this form This form contains a tree or outline capable list where you specify an item or row name and the cell where the value can be found in the Excel workbook The cell reference is formatted just like a cell reference if you were in Excel which is Worksheet name Cell If the worksheet name contains a space then enclose it with single quotes The Cell reference is row first then column like A1 for Column A Row 1 For absolute cell referencing you will want to use the symbol i e A 1 You s
2. TM CLEVERQ TOUR BUSINESS E Extractors Manual Copyright 2008 2012 Better Days Enterprises LLC 3213 West Main Street 119 Rapid City SD 57702 2314 Revision February 4 2012 Version 1 917 CleverQ Extractors Manual Table of Contents AOCUC HOME SR de a M ee ts 4 QUICK BOOKS Exacto usa ea 5 SCU 0 E E a TE 5 OPIO SE ee eee ee 7 Usine TOD TEI K a E dt a mn tante 7 a O A ON 7 koad Records With 7 Cros a ween Meare SR eee ees 7 Ovemide Data Rare did des 8 Report Data erin Om SSSR dai Rte 8 RQ PS TO AD LEE A AA EA 10 MantalE xtc 10 ATOMICE TCU Oa E Ath 13 be EAN Be O E A ETA 13 Viewin Extracted Dita 14 Database EXACCION aore E E A 16 DO FUE ances E E E AA E T E E E E E A AE n 16 PiL SOUCO eaa a a E eras ta catalan aN herent Aon 17 NUCTOSO ACCESS acacia titine 17 ODA Setigdnnsladawe a 17 REPO N aie ns 18 DA EN DRE sere tee ecto dE rite te on a DO D LE 18 Ped Mippin Se ne nn ne RE 18 Date RAS ia nin ne den nee ea Diese TE 18 RATES MA D Diy DEP u a A A eeahea donnie ves 19 FSCO OSCO RI Sp ii ia 20 Keeping PHONE T 21 AUtomatic Eoad ss 21 Cene D EE A 21 Edit Database Sourc e EON ne tee a orne 22 Load Data from Databases PORN id 23 Microsott EXC FE ACTOR in 24 DC HUTT UP Ah QUIN 24 Detimiistne Extract da 29 Adding and Editing DETMIIONS ASA 30 E UU a EEE 31 FV S EOD Ye CUMIN OS dido 92 E PT o alain tala 33 D S PS ta ata pios 33 COPA rat 33 A e UE EI 34 PAPAS a nn ios 35 Data Packase Creator D
3. Inventory Valuation Summary _ Inventory Valuation Summary Filter _ Open Sales Orders by Item open Sales Orders by Nem By Rep 0 144031 44 623095 48 1532 4 _ Open Sales Orders by Item Filter 0 0 0 _ Open Sales Orders by Rep _ Open Sales Orders by Rep Filter 0 _ Payroll Summary _ Payroll Summary by Employee _ Profit amp Loss _ Profit amp Loss Filter _ Sales by Customer Summary 0 0 0 _ Sales by Customer Summary Filter _ Sales by Item by Rep Summary _ Sales By Item By Rep Summary Filter _ Sales by Item Summary _ Sales By Item Summary Filter 0 0 0 _ Sales by Rep Summary _ Sales By Rep Summary Filter _ Seasonal Trend FP _Preview Export_ __ Pin _ Done The form can be resized by dragging edges and more or less data can be shown Columns can be resized by moving the mouse over to the border of the column and dragging the edge Page 49 CleverQ Extractors Manual Sorting Sorting 824318 100914 2 100914 2 Total Accounts A dimension field is a row or column field and they are all arranged in ascending or descending order The sort order is determined by an up or down arrow Filtering Any field except the data field can have a filter You saw the filter for Report name when you first opened the form Clicking on the top right portion of a field will show the filter icon and then you can filter by that field value PA ee Report Mame D
4. ODBC stands for Open DataBase Connectivity This can include SQL Server MySQL Oracle InterBase Sybase and others There are even ODBC drivers for QuickBooks so you can extract data independently from the predefined QuickBooks CleverQ interface This will give you unlimited access to all the QuickBooks data Setup Using the main menu and selecting Setup Data Sources Database or selecting Database in the Extractor Definitions block on the Navigator will bring up the Load Data from Databases form as shown in the figure below Coad Dara Pon Danasa o 7 E CC y E __ O o 1 E AS A Y ES EEE D AAA M Stocks StockData Close Monthly Pros EDIT Data load complete M Stocks StockData Close Daily pros EDIT Data load complete Clear Selections Select All Add New Get Data sor E On this form you will see a list of individual extraction definitions On this list there is a selection box which enables the individual definition when the actual extraction occurs A description of the extraction 1s shown on the first line for each definition This description is a combination of the report name data type column type and filters The second line will show the status of the extraction The buttons to the right include a button to view the extracted data and an Edit button to edit the individual definition To add a new definition press the Add New button This will display a blank form as shown in the nex
5. that is the only thing that can be in the reference you cannot include a formula The Calc button will not work for a range reference Working with items involves using the various buttons located on the bottom of the form as shown in the following figure 7 Add item Delete Expand to Promote Demote at current current item show all f level only items DA i Y e Expand Add New Item Remove Item Collapse Collapse t Add ec Remove Branch tree and F show only Move Move Additem Delete current level 1 Up Down under item and all tems current item dependents Be sure to Save your template before you close this form by pressing the Save button Extracting Data With the templates defined and the workbook or folder defined you can now extract the data This is done by pressing the Extract Data Now button on the main menu Viewing Extracted Data To view the extracted data press the View Extracted Data button The View Data form will appear This form is almost identical to the one within CleverQ to look at report data Select your filters and press the Refresh button Page 40 CleverQ Extractors Manual View ota ALE Select Filters for Viewing Report Names bept Data Type gt Refresh Close Record 14 4 I gt gt Viewing the Log A log of activity associated with the DPXE can be viewed by pressing the View Log button on the main menu The log will display the dates and times of all
6. 11 23 3 318 706 100 News News News 76 ze o 0 0 unch oo0 10 49 465 265 480 765 03 O 34 931 680 News 683 6 98 7os 6 78 19 453 267 016 222 10 24 433 4 603 940 804 1 59 4 4 674 570 100 News 19 21 1915 1937 1913 44127878 006 0 31 24 37 17 6 106 961 274 902 1 67 11 5 5 568 000 000 News 17 03 638 823 996 1 27 News 227 42 0 7 77 7es 7 3 2 163 974 035 4 50 8 82 5 35 7313171367 1 93 O 985 602 60 News 21 42 1 44 1 46 142 19880 0 02 1 39 3 2 134 37 313 992 0 29 O 26277460 News 3001 30 19 30 29 296 1 239 963 0 19 0 61 4047 2611 5 838 864819 3 31 91 194 596 400 News 3134 3128 315 3124 4174570 0 06 0 19 31 98 2585 54 650 055 833 273 18 2 1 744 058 000 News t718l 17 23 17 38 1717 26 239 187 0 05 News 2436 2435 2454 2413 35873311 0 01 3 News News 3412 3507 3522 3394 10117 625 0 96 News 0 19 News News News News 1 49 26 277 460 Another unique aspect of the row name range occurs when you export data from QuickBooks into an Excel worksheet See the next figure Note that the row names take up multiple columns in Excel If you define a row name range with multiple columns the text from all the columns will be concatenated into one row name Page 26 CleverQ Extractors Manual Mt A AAA ANA EA AA EA 486108 486108 486108 486108 466108 4 861 08 6 208 25 208 25 208 25 208 25 208 25 2
7. CleverG Dev Test qbd Page 46 CleverQ Extractors Manual Extracting the Data Extract Now on the Navigator The Navigator has an icon labeled Extract Now Clicking on this icon will extract data from all selected data sources Depending on the amount of extraction this could take a long time Extract Transform Load ETL Schedule Extraction Also on the Navigator form you can click on the Schedule Extraction icon which will create a Windows Scheduled Task which will cause the data extraction to occur at a particular time This is useful to schedule in the evening when the extraction takes a long time In addition to extracting from all selected data sources including databases the formulas are populated in the data cache and the data cache is recalculated Using the Data The data extracted can be used as a source for reports or as a source for parameters As a source for parameters they will be referenced by indicators and ultimately gauges Page 47 CleverQ Extractors Manual Laie Leary Met Income Parameter Tested Mame Met Income Mote To orere aufo naming change te name after selecting fhe repor row ano couma Total revenue in an accounting period less all ES expenses during the same period If income taxes El Key Parameter sre not deducted it is called operating profit for Loss as the case may bej Also called earnings parameter can be mapped to a specific item on a repo
8. Dates although they do not have to be across columns and can consist of a single cell or a range of cells In the previous figures the date ranges are shown with a tan background Another range is referred to as a Column Type and is shown with a light green background in the figures This is optional You might also want to define what is called a Row Number range This 1s a set of numerical values that generally 1s used to maintain the same order of data in the extraction This is optional and CleverQ can auto number as necessary If you use Filters in CleverQ you can also define filter ranges as you will see described further on These can be used in place of row name ranges or column type ranges Filter ranges can also be single cells For example you have multiple companies and you have Page 27 CleverQ Extractors Manual the company name located in a single cell of the worksheet You can assign a range name to that single cell and then assign that range name to the Company filter in the extraction definition Page 28 CleverQ Extractors Manual Defining the Extractions Using the main menu and selecting Setup Data Sources Excel or selecting Excel in the Extractor Definitions block on the Navigator will bring up the Load Data from Excel form as shown in the figure below Since you can have an unlimited number of extractions coming from the same or different Excel workbooks the form li
9. HE CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract QuickBooks Not Used Single Company LC Multiple Companies LC Multiple Companies with Totals Choose your mode first Record 0 of 0 _ MIE C Users Public Documents CleverQ CleverQ Data Sample qbd You can setup the extractor is one of four modes e No Extractions QuickBooks Not Used e Single Company e Multiple Companies e Multiple Companies with totals across all companies You most likely will use the Single Company mode and when you select this you will be able to select your QuickBooks Company file Page 5 CleverQ Extractors Manual EN CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract ACKER Single Company File Single Company C Users Public Documents Intuit Quick Books Sample Company Files Quick Books Enterprise Solutions QB C Multiple Companies 10 0 sample_service based business qbw C Multiple Companies with Totals Open QuickBooks with your Company File Browse to your company file 44 1144 4 Record 0 of 0 mi a AR C Users Public Documents CleverQ CleverQ DataSample qbd Ds For multiple companies you can choose any number of companies The key to making this work is to have your first Filter in CleverQ set to Company This is where the Company name will b
10. List as shown in the next figure Page 37 CleverQ Extractors Manual ENCH RE M fol Report Name Dept Name Column Type Data Type Date Cell Balance sheet Master D Pret Loss Master SSS El E Monthly gt PAGE TES Pamese SOS Monthly PAGE 1UE87 Protes SOS Monthly PAGE T1E47 EE Add new templates to Co Delete Edit the bottom of the list cid Record IA 4 3 r be of 5 It will initially be blank you and can just start creating new templates by typing on the bottom of the list Each template consists of e Report Name will be used to define the CleverQ parameters e Dept Name Optional e Column Type Optional if data has multiple column types you will need to create a template for each type e Data Type Required e Date Cell This will point to a cell in the workbook that contains the date If the cell does not return a date the data extraction will not proceed Templates can be copied deleted and edited You can also import templates from other DPXE files from the main menu Doing so will delete any templates existing within the current DPXE file so import first NOTE Templates can be defined and not used If the Data Type column is blank the template will not be used during the data extraction Editing a template will bring up the form as shown in the next figure Page 38 CleverQ Extractors Manual ES friii Peyulsts Monthly Cel n PAGE 1 1 U 62
11. Mappings tab you will see listed the filters specified on the CleverQ preferences form For those filters enabled you can use the dropdown list to select a range from the worksheet or enter text that is not in the list and this will be used as the value for all the records extracted Edit Excel Source D CleverQDocumentation Excel Samples Quickbocks Export 1 xls M Balance Sheet Range Mappings Filter Mappings History Settings Status Open Data Source GetData View Data Extracted Page 31 CleverQ Extractors Manual History Settings The History Setting tab gives you options for keeping historical data Edit Excel Source D CleverQ Documentation Excel Samples Quickbocks Export 1 xls M Balance Sheet Range Mappings Filter Mappings History Settings Status Me ome Open Data Source Get Data View Data Extracted Normally whenever you load the data from a worksheet range all the previous data from that source will be deleted You can keep this data so it doesn t get deleted by checking off the option Keep History This is useful if you want to maintain the data for plotting or comparing values The Last Data Loaded at field will tell you the data and time when the last load was performed If you only want to keep history for a specific period of time enter a value next to the caption Keep History for This can be as little as 1 minute or as long as hundreds of days You can setup the sof
12. Microsoft Excel Help system to understand how to name a range of cells One method to name a range is described below To name a range of cells in Excel 1 Select the range of adjacent cells 2 Click the Name box on the left end of the formula bar 3 Type the name for the cells 4 Press Enter File Edit wiew Insert Format Tools Data Window Help Deh ee Gar d Bamm g 2 2 31 MU E 4 100 Arial 10 B 7 U Z w sc t A Data ll fe 0456 A E PM CS C H 1 3 5 8 456 67 540 12 553 E 3z 032 9 509 z 307 83 103 74 504 22 022 98 781 51 518 Were he 2 402 77 098 49 4434 a 10 You can have as many named ranges within one worksheet or workbook as you want Page 24 CleverQ Extractors Manual There are different types of named ranges required for importing to CleverQ Again it helps to understand how data is stored within CleverQ Please review the section on Data Sources in the Customization Manual to understand these concepts At a minimum CleverQ requires a range of cells that contain numeric data This will be referred to as the Data Value range As shown in the next figure the light blue areas is where the data values are so these would be the Data Value range 1 2 3 4 5 E B 5 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 25 27 2 29 30 31 32 5310 33 63010 34 35 6
13. change the source You must delete and re enter the source access DataType Monthly y C Program Files Dealership Software Pata CQ_DS_Queries mdb eQ Sales IDS Unit COGS Field Mappings Date Range Filter Mappings History Settings Status Select Enter Mapped Field Company OoOo Location Department New Used New Used Product Type BodyType Manufacturer Manufacturer Brand Employee Delete Copy Open Data Source Get Data View Data Extracted There is a subtle understanding that 1s necessary here If you setup an extraction as shown in the previous diagram three filters are selected This means that only records with all three fields will be extracted and monthly totals will only exist when all three Page 19 CleverQ Extractors Manual filters are specified In other words if you need a total for in this case Product Type only it will not be available in the extracted data You will need to add a new extraction only specifying the Product Type as a filter If you wanted all combinations of filters extracted you will need many definitions So it is necessary to know what combinations of filters you will be using in your dashboards scorecards and reports and make sure you have specified all the extraction definitions you need If you bring in combinations you will not use you will be making the time to extract and calculate longer than necessary in addition t
14. data extractions as well as any errors that occurred during the extraction You should view this after each extraction to make sure there were no problems Automating the Extraction When the DPXE file is opened and there is no user activity for the first 30 seconds data extraction will automatically occur and when completed the program will close This mode should be used in conjunction with a Windows Scheduled Task to automate the extraction Adding a Source to the Data Load When you are in the CleverQ software you can load the data entered with the Data Package Excel Extractor two ways 1 Using the main menu and selecting Setup Data Sources External Page 41 CleverQ Extractors Manual File View Edit Navigator Preferences QuickBooks Parameters Excel Indicators Database Gauges Data Package Dashboards Security Object Relationships Export Import Objects 2 Selecting Data Package from the dropdown list in the Extractor Definitions on Navigator will bring up the Load Data from External Data File form as shown in the figure below In either case you will see the following form displayed NA rey Mejia pila BHE External Data File Report Name EE keep History For Mins Update Interval O Mins Last Update F Do not use External File Mame as part of Report Mame Status Clear Selections Select All Get External Data OK With the form containi
15. remaining fields please review the section on Data Sources in the Customization Manual Date Range On the Date Range tab you can leave the entire tab blank and then all the data from the source will be extracted Or you can choose various options for the monthly or daily data types For the other types use the specific data ranges set on the right side of the form Page 18 CleverQ Extractors Manual Erie ips Goles Data Source Type Source Query Table jew Report Marne For Monthly Data Type Extract the last months or Calculate Quarterly Totals Enabled To avoid orphaned data once a data source is defined you cannot change the source You must delete and re enter the source onec Data Type Monthly MySQL on KS4 i tbl_calendar ki Use this date range start Date PO E End Date lO Calculate Annual Totals For Daily Data Type Extract the last days or Calculate Weekly Totals NOTE Tokals are not calculated when getting data using the Get Data button on this Form Delete Copy Open Data Source Get Data View Data Extracted Filter Mappings If you have setup your QBD file with filters See the Preference form you can map fields in your extractions to the filters Erie Wipes DUES Data Source Type Source Query Table iew Report Name M Enabled To avoid orphaned data once a data source is defined you cannot
16. you extract budget data Y ou can optionally Remove all Detail Records and Keep Totals Only With this selected only row names coming from QuickBooks starting with the word Total are extracted Each report has the option to load all rows and all columns When these options are selected you will get a complete extraction and possibly many rows and columns that are all zeroes This is necessary when there exists data for parent and child items and consistency is required for row names and filters When the All Rows option is selected rows with all zeroes will not be extracted if the Load records with zeros option is not selected When the all rows and all columns options are not set the Page 9 CleverQ Extractors Manual extraction will only include rows and or columns that contain non zero values Some reports ignore the all rows and all columns options Any zero value entries regardless of the all rows and all columns options are not extracted unless this option Load records with zeros is selected The extractor provides for you to extract only one report at a time which is helpful in troubleshooting This is done by the button on this tab called Extract Data Now If you want to purge the data first press the Purge this Data Now button If QuickBooks is opened to this company file you can actually see the report s that are being extracted in QuickBooks if you check the Show Report in
17. 08 25 200 00 200 00 200 00 200 00 200 00 200 00 148092 4148092 148092 148092 149092 1 480 92 11827092 118 270 92 11827092 118 270 92 11827092 118 270 92 12 1 912 61 1 912 61 1 912 64 1912 64 1 912 61 1912 61 150 537 23 150 597 23 150 537 23 15053723 15053723 150 537 23 3565984 3565964 3565994 3565994 3565984 35 659 94 69114821 691 148 21 691 148 21 69114821 69114821 591 148 21 727 008 05 727 008 05 727 008 05 72700805 72700805 727 008 05 73207738 72207738 73207736 73207738 73207736 73207738 2499535 3499535 34 995 35 3499535 3499535 34 995 35 1616776 1616776 46 167 76 1616776 1616776 16 167 76 961235 981235 981235 961235 961235 9612 35 1059994 1059994 1059994 1059994 1059994 10 599 94 982 83 982 83 982 83 982 83 982 83 982 53 7039257 7039257 7039257 7039257 7039257 7039257 70 392 57 7039257 7039257 7039257 7039257 70 392 57 123 577 76 12457776 124 577 78 12457776 12457776 124 577 76 44583500 44583500 44583500 44583500 44583500 445 835 00 161 949 00 161 949 00 161 949 00 16194900 16194900 161 949 00 607 784 00 607 784 00 60772400 60778400 60778400 607 784 00 40351200 40351200 40351200 403 512 00 40351200 40351200 7339200 73 392 00 73 392 00 7339200 7339200 73 392 00 72990700 72990700 72990700 72990700 72990700 729 907 00 53562500 535 625 00 53562500 535 625 00 53562500 53562500 11 742 436 00 1 742 436 00 1 742 436 00 1 742 436 00 4 742 436 00 4 742 436 00 There are other range names that can be defined Dates are referred to Column
18. 24174 80 24174 80 24174 80 24174 80 23522 32 23354 08 23354 08 233 O14 13100 Pre pai 6750 5400 4050 2700 1350 11672 10024 34 8376 68 6729 02 5081 36 3433 70 9886 04 82 15 13400 Retain 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 35 E 16 12000 Undep 16252 08 18252 08 18252 08 184 jo Total Other Cur 36301 54 35367 94 34017 94 32008 24 30658 24 40980 24 39332 58 37684 92 36037 26 49989 20 50173 30 56625 64 551 as Total Current A 219667 58 219605 63 237171 88 245883 36 263501 71 285713 62 290405 81 340583 51 356077 58 384727 06 384510 76 396036 00 4192 E20 17000 Accum 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 150548 48 150 G21 15200 Building 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 6 82 15300 Constr 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 8 23 15000 Furnitu 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 Eiza 16900 tand 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 1 Eiza 15100 Vehide 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 1578 G29 Total 15100 v 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 1578
19. 30 08 New Jerse 36 6 3008 Rhode Island 37 Data can be presented in an Excel worksheet in various formats as shown in the previous figure and the figure is not showing the only formats that could exist We need to extract the appropriate row and column headers surrounding the data Generally there 1s always a row name as shown in the yellow cells in the figure This 1s referred to as the Row Name range and you will need to name that group of cells separate from the data value range Note that the row name range does not have to be adjacent to the data value range as shown in the next figure Page 25 CleverQ Extractors Manual A B f SEE E F G H J K L M N o P Q 10 7 2010 7 4 o tase Ciel mil Low voumel Cnange 2 Change 52 vk High S2 Wt Lou Market Cap _EPS PrE Ratio Shares ou Close News 10940 52 10944 72 1097416 10918 57 113 647 413 42 0 04 11258 01 9601 26 so News News 1558 160 87 160 68 154 6 4581 711 5 07 315 161 78 88 4 69 772 257 022 242 66 5 447 825 000 News 60 81 7ol 69 34 5974 14 233 696 919 13 13 71 93 3675 11 367 791 252 1 26 55 5 186 939 500 News 32 34 3206 3241 3196 5 690 227 028 0 87 38 27 26 84 43 924 770 327 26 12 3 1 358 218 000 News 24 48 24 59 2478 2425 604006 011 0 45 28 37 1906 32 121 553 799 1 9 12 9 1 312 155 000 News 633 01 638 23 539 95 529 94 2194696 522 0 97 629 51 43363 169 873 541 473 23
20. 36 CleverQ Extractors Manual On this main menu the text box Customized for is optional You must enter a company name and it must match exactly the company name in the CleverQ QBD file that you will be loading the data into You have a choice of working with a single Excel workbook file or a folder with al files You will either enter the single file or the folder There is a browse button to allow you to point to the file or folder CleverQ Data Format It is important to understand how data is stored both in the DPXE and CleverQ A group of related data gets a name and this is called the Report Name It is called a report because normally the data comes from a report Within a report you can specify a department 1f the data 1s associated with a department Data must also be associated with a date and a period of time This is called the Data Type You can choose Annually Quarterly Monthly Weekly Daily and Latest Dates You always enter the last date within the period With the Report Name Dept Name and Data Type you have enough to enter a table of data The table is organized into rows items and columns column types The row name describes what the data value is for The optional column type is usually used to describe the type of data For example Qty Hours Amount Percent Templates By pressing the Edit Templates button on the main menu you will be presented with a Template
21. 73 82 157873 82 157873 82 1578 630 Total Fixed Assets 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 913577 34 9135 E 32 18700 Securit 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 233 Total Other Ass 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 234 TOTAL ASSETS 1181120 83 1181058 88 1198625 13 1207336 61 1224954 96 1247166 87 1251859 06 1302036 76 1317530 83 1346180 31 1345964 01 1313053 34 13362 G39 20000 Accoun 7485 5435 5714 5000 5000 9050 11733 75 10979 87 9744 9050 9050 13100 116 240 Total Accounts 7485 5435 5714 5000 5000 9050 11733 75 10979 87 9744 9050 9050 13100 116 02 20600 Caloi 4050 98 4050 98 4050 98 4050 98 4050 98 4050 98 4050 98 1710 98 6390 98 6390 98 6390 98 5860 98 50 20500 Quick8 1600 1600 200 3000 3000 3000 3000 3000 1050 7050 7050 10 44 Total Credit Cards 4050 98 2450 98 5650 98 4250 98 7050 98 7050 98 7050 98 4710 98 9390 98 5340 98 13440 98 12910 98 113 05 eno ew a kl You can filter sort rearrange expand and collapse group print and export the data Refer to the section later in this document titled Viewing Extracted Data Using the PivotGrid Page 15 CleverQ Extractors Manual Database Extractor The CleverQ Database Extractor is a powerful interface that allows you to extract data from Microsoft Access or any ODBC compliant database
22. Data Extracted Page 22 CleverQ Extractors Manual Load Data from Databases Form Using the Get Data button on the Load Data from Databases form will only extract the data for the sources that have been selected If calculation of totals is selected on the Date Range tab they will be calculated when extracting data from this form Se 2 C f EE _ _ a E 1 E nn pene o A A E A pesca A E A A g Y AA CO E A A 2 YY Clear Selections Select all Add New Get Data ok Page 23 CleverQ Extractors Manual Microsoft Excel Extractor NOTE Starting with version 1 908 of CleverQ the Microsoft Excel Extractor has been redesigned and previous extraction definitions will not work You will need to edit your workbooks and definitions CleverQ can connect to one or more Microsoft Excel workbook files and pull data from one or more named ranges within the workbooks The data ranges are blocks of data with rows and columns There must be row names and optionally column names as well as some date associated with the data If your requirements are to pull data from individual cells and you want to map the cells to specific names then refer to the Data Package Excel Extractor for an alternative method of loading Excel data Setting up a Source The Excel Workbook requires you name sets of cells using the Named Range feature of Excel This allows CleverQ to find the correct set of cells Refer to the
23. PC ati tailandia taal 35 Data Package EXCEL Extractor ID EXE tata ls 35 Sene Up a SO UIC AR lid 35 CleverQ Extractors Manual The Data Package Excel Extractor DPXE File 35 Creating a New DP XE eiii iaa 36 Starmedia o 36 Clover O Data OEM A 37 Template Se ed ne E ein 37 Eranio D AGA iii TR EN aneeneate 40 View me Extracta DATA AR SR A na dl 40 VISIO AO OR nt ea a t 41 Automating the Extractions ress el cn AR 41 Addins Source to the Data OAC sn nn hide 41 Kecpine HiS 6 ah reer eevee RS ti ia nn 44 Automate LOLA A AAA AE 44 OBD AN LS JU a 44 Extracine D NL A A daa 47 Exact Now Othe Nay MO dd aria cos 47 ASIGNAR a rene E E E 47 Usmo the Data das dad 4 Viewing Extracted Data Using the PivotGrid ss 49 O A Ne ee ete A cae haa de a al Ae 50 Elton desu 50 SP E e sesielauseonauh re 32 Expanding and Collapsing Groups a ea eee 53 A Re Try Tren Cntr se en Oem Mees eee te ete ee er mee one rer ee ee tec ee nate 54 EXPORT a TR A ne 54 Copyright 2008 2012 CleverQ Patent Pending Designed for use with QuickBooks QuickBooks is a trade mark and service mark of Intuit Inc registered in the United States and other countries Microsoft Excel and Microsoft Access are trademarks and service marks of Microsoft registered in the United States and other countries CleverQ is a trade mark and service mark of CleverQ and Better Days Enterprises LLC CleverQ Extractors Manual Introduction T
24. Page 52 CleverQ Extractors Manual Expanding and Collapsing Groups wele ax Ajas menasta Data Report Name Data Type _ Company _ Customer name vencorname Jem Aco Data Value J Elizabeth N Blueprints Floor Plans Cabinet Pulls Carpet Doorknobs Std Framing Installati Lk Doorknobs 3 IO Drywall Installation Labor Light Pine Lk Doorknobs Removal Total Cabinets Total Hardware Total Lumber Total Parts Total Wood Door Appliance E u Mason Click to expand or collapse Employee Name Column Date Column Type q _____ e BETA Avg COGS Qty Amount May Betyee tat Det we ox Employee Name Column Date Column Type Data Yalue Blueprints Floor Plans Cabinet Pulls Installation Lk Doorknobs Drywall Light Pine Lk Doorknobs Total Cabinets Duct Work Elizabeth N Mason Total no sales rep 5080 14794 2276 66 1179 20 12648 9062 1267 32 22695 22 4225 30314 918 88 912 72 14099 83 2572 322 1301 10632 9576 1927 24 5709 38941 34 1552 04 790 3995 4 238 90 712 06 13720 1663 0 19417 40 4802 6936 a Cave an CleverQ Extractors Manual Printing Press the Print button and the PivotGrid will be sent to your default printer To select a printer press the Preview Export button instead Exporting Press the Preview Export button to view the PivotGrid as it would print From here yo
25. QB checkbox After the extraction you can view the data just for this definition by pressing the View Extracted Data button More about that later Extracting Manual Extraction After all the report data definitions are set and you are ready to really extract the data go to the Extract tab EN CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract Be sure to include enough data to support the gauges and indicators you have specified Excluding data you do not need will improve the overall performance of the system Caution Clever relies on Quick Books reporting accuracy Although inactive items activity are included in QuickBooks Balance Sheet and Profit amp Loss reports DD ee Eu pe in the Sales by tem Summary if the items had activity within the report date range but exclude all inactive items from the Inventory Valuation Summary Accurate Annual Tumover Retum on Asset Days Inventory and other indicators require child items remain in Active Status while loading QuickBooks data Get QuickBooks Data Now Show Extracted Data OR Create Scheduled Task View Log for Last Extraction C Users Public Documents CleverQ CleverQ DataSample qgbd First 1f you want to clear out all extracted data press the Purge All QuickBooks Data button You will normally not do this but occasionally it is necessary to start with all new extracted data You wi
26. UTA Payable Vo gel SITY WEES 24050 State Withholding TOTAL ASSETS 24060 SUTA Payable 20000 Accounts Payable 24070 State Disability Payable Total Accounts Payable 24080 workers Compensation 20600 Caloil Credit Card 24100 Emp Health Ins Payable 20500 QuickBooks Credit Card 25500 pF ee ARBOL diet a BSD rt S e Sales Tax Payable Page 50 CleverQ Extractors Manual In addition to filtering each column you can create complex filters with Prefilters Right mouse click on an empty space and choose Show Prefilter Ce View baracted Data Sele Data Value Employee Mame Column Dake Column Type M Mason Total no sales rep 5050 ES Plans 14794 Plumb Fixtrs 2276 66 1179 20 You will then get the PivotGrid Prefilter form where you can create complex filter expressions PivotGrid Prefilter Or Page 51 CleverQ Extractors Manual Grouping In the following example for the Sales by Item by Rep Summary report the Employee Name field was dragged into the column header area to facilitate grouping by Employee CEroree ane Decimos comm tine gt Elizabeth N Mason SET Blueprints i Floor Plans Cabinet Pulls Carpet Doorknobs Std Interior Lk Doorknobs Rough Trim Drywall Interior Labor Light Pine Lk Doorknobs Removal Total Cabinets Total Hardware Total Lumber Total Parts Total Wood Door Appliance gt gt w
27. al format required within a worksheet You map each cell to a data element using a template e There can be multiple reports contained within each worksheet where a template defines each report e Multiple identically formatted workbooks can be located within a single folder and the software will process all workbooks in the folder and keep track of which workbooks have been processed Setting up a Source The Data Package Excel Extractor DPXE File When the software was installed a blank DPXE file was put in the folder C Documents and Settings All Users Documents CleverQ or C Users Public Documents CleverQ The file name is CleverQ DPXE mdb The file extension of mdb tells the system that the file is a Microsoft Access database You will need to have Microsoft Access installed on your computer to use the DPXE If you use the DPXE on the same machine that you installed CleverQ software then you should be all set If you use the DPXE on another machine you will need to have Microsoft Access on that machine The file 1s self contained and includes the data that you will enter the templates and the software to enter the data Page 35 CleverQ Extractors Manual Creating a New DPXE File Do not use the original file that was installed with the software Make a copy and rename the file to something that is appropriate for the type of data it will contain For example if you are going to enter data for a parti
28. ample files in QuickBooks Report Data Definitions On the Report Data Definitions tab you will see a tree view of all the reports and data definitions available HE CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract Company 4 Financial E Budgets l Sales E Payroll El Inventory G Vendors amp Payables E Customers 4 Receivables Time Tracking Click here to expand all definitions Individual groups can be expanded or collapsed by clicking here C Users Public Documents evera ever Data Sample gbd When you click on a specific definition Profit amp Loss shown in the next figure the right side of the form will fill in with the options for that report Page 8 CleverQ Extractors Manual CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract El Company 4 Financial i Profit amp Loss Balance Sheet E Budgets Profit amp Loss Budget Overview Sales i Sales by tem Summary Sales by Rep Summary Sales by tem by Rep Summary i Sales by Customer Summary x Open Sales Orders by tem Open Sales Orders by Rep Open Sales Orders by ltem by Rep l Payroll e Payroll Summary E Inventory Inventory Valuation Summary i Inventory Stock Status by Item Profit Loss or Pri 1 curent yr only o Account For Prior 2 years yr only Calculate Qu
29. arterly Totals Class lt No Filter gt r PF Calculate Annual Totals Customer For Future 0 years Employee en FT Daily Data For Last lo days Job T Calculate Weekly Totals Sales Rep Vendor o Vendors amp Payables a rd I Remove all Detail Records and Keep Totals Only I Show Repot GE All Rows Al Columns ew Furge this Extract this Extracted Data Now Data Now Data Done o Customers 4 Receivables A R Aging Summary l Time Tracking Time by Name Clear Settings for this Report C Documents and Settings Al Users Documents CleverQCleverQ DataSample gbd Filters and other controls on this form will be enabled depending on the options and specific report type You can assign filters or not Data is either extracted by month by day or for the current date depending on the report Y ou can choose to include any number of prior years of data including the current year You can also calculate quarterly or annual totals based on the monthly data For daily data you can choose the last x days and optionally calculate weekly data When an extraction occurs and you have chosen not to purge all data only the data for the data range specified will be cleared before the extraction This gives you the ability to build up the data without extracting all the data all the time In other words once you have extracted the prior years just extract the current year Future years are enabled when
30. ata Value LE las El 56 B7 Ele 1E 10 iE 12 J Row Mumber Ar ye neem F1 312 1034 1203 120 a4 ee lt Q A AREA aaa a Data Type Company Customer Mame Vendor Marie Employee Mame Item Account Column Date Column Type p sono Row Mame a EI EEE Show AIT 10100 Checking 10100 Checking 10400 Petty Cash 10300 Savings 10300 Savings 10400 Petty Cash Total Checking Savings 11000 Accounts Receivable 11000 Accounts Receivable POD Eee post ea unos 12100 Inventory Asset Total Accounts Receivable 12000 Employee Adyances 12500 Employee Advances 13100 Pre paid Insurance 12100 Inventory Asset 13400 Retainage Receivable 13100 Pre paid Insurance 15000 Furniture and Equipment y 15100 Vehicles Other l e 15200 Buildings and Improvements 15300 Construction Equipment Total Other Current Assets 155 Wilks Blvd Total Current Assets 16900 Land 17000 Accumulated Depreciation 17000 Accumulated Depreciation 15200 Buildings and Improvements 18700 Security Deposits 15300 Construction Equipment 20000 Accounts Payable 20500 QuickBooks Credit Card 15000 Furniture and Equipment 20600 Caloil Credit Card 16900 Land 23000 Loan Vehicles 15100 Vehicles Other 23100 Loan vehicles Total 15100 Vehicles 23200 Loan Vehicles Total Fixed Assets 24010 Federal withholding 18700 Security Deposits 24020 FICA Payable 24040 F
31. cular company then use the company name for the filename For a department you would use the department name It does not matter what you use for the name but ultimately the filename may be used as part of the report name in the CleverQ software so it should be meaningful When renaming the file make sure to maintain the same file extension of mdb Use the Windows menus or keys to copy the file You can locate the file anywhere you want to as long as the CleverQ software will have access to it when it gets loaded Starting the DPXE After copying and renaming the blank master file simply double click on the file and Microsoft Access should open and load the file After seeing the splash screen for a few seconds you will see the main menu DATA PACKAGE EXCEL EXTRACTOR Version 1 01 CLEVERQ Customized for Edit Templates Import Templates Company Marne Input File Option C Single Excel Workbook Folder with Multiple Files all files must have same format Excel Workbook File Extract Data Now Purge Data View Extracted Data View Log Note IF this program is opened and there is no user activity For the first 30 seconds data extraction will automatically occur and when completed the program will close This mode should be used in conjunction with a Windows Scheduled Task to automate the extraction IF you open Excel From this program some Functionality may be disabled until you close Excel Page
32. e following QuickBooks Application with No Certificate enhancing the QuickBooks user interface Certificate Information This application does not have a certi cannot verity the developer s identity Search for information about this application at the QuickBooks Solutions Marketplace ate QuickBooks Do you want to allow this application to read and modify this company file No Yes O time E Allow this application to access personal data such as Socal Security Numbers and customer credit card information Tell me more After the extraction is complete you will see a message similar to the following depending on the reports you have chosen Y Extraction Started Profit amp Loss Processed Profit amp Loss Filter Extraction Complete Page 12 CleverQ Extractors Manual Automatic Extraction You can automate the extraction of QuickBooks data so it can occur in the evening after the days transactions are complete It is also good to do this since the extraction can take some time This is done from the Create Scheduled Task button on the Extract tab NOTE This ts a different process than scheduling the extractions from the CleverQ Navigator You may need to do both depending on the data to be extracted By pressing this button you will see a message similar to the following Create Scheduled Task E de Use the Windows Task Scheduler to schedule the
33. e stored to match up with the data HE Clever QuickBooks Extractor Setup QuickBooks Setup Options Repor Data Definitions Extract L QuickBooks Not Used awe Gan ie O Single Company C Users Public Documents ntuit QuickBooks Sample Company Files QuickBooks Enterprise Solutions PA Multiple Companies 10 0 sample_service based business gbw CL Multiple Companies with Totals Multiple Company Files E C users PubliclDocumentsintuitlQuickBooks Sample Company Enter name of company name to be stored in Filter Click the to add a new company i aa al Record A C Users Public Documents Clever CeverQ Data Sample gbd Check off to enable one or more companies Browse to select company file Page 6 CleverQ Extractors Manual Options After you have selected your mode and company file s select the Options tab CleverQ QuickBooks Extractor Setup QuickBooks Setup Options Report Data Definitions Extract Using Job Tracking PF Ovemide Date Range Options and use these dates I Using Classes Monthly Data Start Date Monday August 09 2010 Daily Data Start Date Monday August 09 2010 Load records with zeros End Current Date Monday August 09 2010 y Each report has the option to load all rows and all columns When these options are selected you will get a complete extraction and possibly many ro
34. f you only want to keep history for a specific period of time enter a value next to the caption Keep History for This can be as little as 1 minute or as long as hundreds of days Automatic Loading Y ou can setup the software to automatically load periodically by entering an Update Interval This can be a value in terms of minutes hours or days When the current time and the Last Data Load at value exceed the update interval the data 1s loaded in the background This can provide a real time update of data and gauges on a dashboard Don t forget to set your preference for Auto Update Dashboard Getting Data There are 4 places where you can extract database data e Edit Database Source form e Load Data from Databases form e Extract Now on the Navigator see later section e Schedule Extraction see later section Page 21 CleverQ Extractors Manual Edit Database Source Form Using the Get Data button on the Edit Database Source form will only extract the data for the specific data source If calculation of totals is selected on the Date Range tab they will not be calculated when extracting data from this form This option is good for testing your extractions since you can view the data source get the data and then view the extracted data all from this form Edit Database Source Pst A Field Mappings Date Range Filter Mappings History Settings Status Open Data Source GetData View
35. following file C Users Public Documents CleverQ CleverQDataSample_RDE bat This filename has been copied to the clipboard Automatic extraction is done using the Windows Task Scheduler CleverQ has automatically created a batch job file as listed in the message box and pasted the name of the file to the clipboard for creating a scheduled task using Windows Since there are so many different versions of Windows now and each has its own interface the details of how to do this is not covered here You can refer to numerous online references about Scheduled Tasks For Windows XP Users press the Start button All Programs Accessories System Tools and Scheduled Tasks If you are using the Report Manager you may want to extract this data first and then schedule the report processor to run after all the extraction are completed Viewing the Log Any activity associated with the extraction process is done in the background and status is stored in a log To view the log press the View Log for Last Extraction button You will see this form where all activity is logged including errors Page 13 CleverQ Extractors Manual HE View Log 6 28 2010 2 47 32 PM Extraction Started mm 6 28 2010 2 48 55PM Profit amp Loss Processed m 6 28 2010 2 49 07PM Profit amp Loss Filter m 6 28 2010 2 49 07PM Extraction Complete Record 1 of 4 e J Refresh Show Complete Log Purge Log Close This form also provides f
36. form To load the data press the Get External Data button on the bottom of the form You will be notified when the data is loaded and there will be a status message for each report under the MDB file name Check these to see that there were no errors However you leave this form in terms of the selected reports to load they will be remembered after it is closed When you do a complete reload from the Main Menu Page 43 CleverQ Extractors Manual using File Load Data from Sources the selections on this form will be used to determine what data will get loaded Keeping History Normally whenever you load the data from a report all the previous data from that report will be deleted You can keep this data so it doesn t get deleted by checking off the option Keep History This is useful if you want to maintain the data for plotting or comparing values The Last Data Loaded at field will tell you the data and time when the last load was performed If you only want to keep history for a specific period of time enter a value next to the caption Keep History for This can be as little as 1 minute or as long as hundreds of days Automatic Loading You can setup the software to automatically load periodically by entering an Update Interval This can be a value in terms of minutes hours or days When the current time and the Last Data Load at value exceed the update interval the data is loaded
37. his document describes the Extractors to be used with the CleverQ software which includes QuickBooks Microsoft Access ODBC Databases Microsoft Excel Data Package Creator It helps to understand how data is stored within CleverQ Please review the section on Data Sources in the Customization Manual to understand the concepts before reading further in this manual The Navigator provides a friendly way to work with the Extractors to see the flow of data as well as choose the functions within the program CleverQ Navigator eS Extract Transform Load ETL Definitions QuickBooks Database Excel Data Package QBD Merge Data Warehouse 49 records QBD File C Wsers Public Documents CleverQ DevTest qbd 11 5 MB 0 out of 91 records Copyright 2008 2012 CleverQ The Extract Transform Load ETL block on the Navigator has a drop down list of the extractors available which include QuickBooks Page 4 CleverQ Extractors Manual Database Excel Data Package QBD Merge QuickBooks Extractor Setup When you first startup the QuickBooks Extractor you will notice a new window appearing outside of the CleverQ window This occurs because the QuickBooks Extractor runs as a separate program from CleverQ so you can be doing one thing in CleverQ and something else in the extractor including extracting data The program Opens up connected to your CleverQ QBD file and on the Setup tab
38. hould refer to the Microsoft Excel documentation for more information about cell references You might also want to enclose the entire worksheet cell reference within parenthesis and preceeded by the letter n This converts an empty cell to a zero value Ifa cell is empty and you do not want to joad it into CleverQ then the n is not needed Your cell reference can also include a formula or calculation Itis evaluated just like Excel would evaluate it This way the template could setup one item which could be the sum of several cells You can include any of the arithmetic operations as well as the Excel built in functions If you are working with one workbook then you can also select an item 1f it refers to a single cell and press the Calc button on the bottom of the form and the value returned from the workbook will be displayed This is a handy feature to make sure you have entered the cell reference correctly Page 39 CleverQ Extractors Manual The Item Row Name is text and is usually manually entered but can also reference text within the workbook You can include a cell reference just like the value cell reference Both the Item Row Name and the Cell Reference can also include the name of a range within the workbook This will allow one item in the template to extract many rows in the extracted data They must be ranges with one dimension either vertically or horizontally and both must be the same size If you reference a range
39. in the background This can provide a real time update of data and gauges on a dashboard Don t forget to set your preference for Auto Update Dashboard QBD Merge The QBD Merge extraction is only available for Multiple Company licenses After choosing this extraction type you will see the following form ad CleverQ QBD Merge Extractor Setup Setup Extract QBD Files Select Source Company Name Use for Filter 1 Data Source File Press to add a QBD File M4 4 4 Record ofO be MH F X C Users Public Documents CleverQ Dev Test gbd Page 44 CleverQ Extractors Manual Then Press to select the file QBD Files Select Source Company Name Use for Filter 1 Data Source File Company 1 D lCleverQCleverQ Test Data DevTest1 qbd D lCleverQCleverQ Test Data DevTest2 qbd Rename Company Select which Name if desired QBD file s to extract M4 44064 Record 2o0f2 bh H wf X C Users Public Documents CleverQ Dev Test gbd When you are ready to extract go to the extract tab and choose from the various features available Page 45 CleverQ Extractors Manual ad CleverQ QBD Merge Extractor Setup Merging GED files requires that all Filter settings and other preferences be identical to the main GBD file Filter in the GED files being extracted will be ignored and replaced by the company name on the Setup tab of this farm Create Company Totals C Users Public Documents
40. is can be done from this form by pressing the Get Data button The status of the extraction will be displayed on the Status tab Once the data is loaded you can view the data by pressing the View Data Extracted button This could be used to verify you have loaded the data properly The features of the View Extracted Data form are described in a section at the end of this manual Mew bBaraicted Data Report Mame Data Type Data Value Column Date Column Type 10 6 2010 270560 50212 33824 16544 29228 148128 332412 395124 298016 206072 308392 988 9608 197772 2 Freview Esport Print Done Page 34 CleverQ Extractors Manual Data Package A Data Package is an external set of programs that are available with CleverQ for manually creating data or for extracting data from Excel is a different way than the Excel Extractor The programs are referred to as e Data Package Creator e Data Package Excel Extractor Data Package Creator DPC Refer to the separate user manual called Data Package Creator Manual for details of using this program Data Package Excel Extractor DPXE The CleverQ Data Package Excel Extractor is an alternative way to get Excel data into the CleverQ software Here are the features and limitations of this interface e Each Excel Workbook usually contains data for the same date e Each Workbook can contain multiple worksheets e There is no speci
41. ll be prompted with this message Page 10 CleverQ Extractors Manual Confirm Purge Bo you want to purge all data No will only purge QuickBooks data w coe Press Yes to purge all data not just QuickBooks data Press No to just purge QuickBooks data or press Cancel to not purge anything Pressing the Get QuickBooks Data Now will start an extraction process and you will see a progress bar window CleverQ QB RDE Extracting ii Extracting Depending on options you have set the number of reports the number of companies and the amount of data the extraction could take a few minutes to a few hours You should be able to use your computer for other things during the extraction You can also view the progress by clicking on the View Log for Last Extraction button and pressing the Refresh button NOTE Make sure you open QuickBooks the first time you do an extraction or you will probably see this message MB Extraction Started ERROR StartSession This application is not allowed to log into this QuickBooks company data file automatically The QuickBooks administrator can grant permission for an automatic login through the Integrated Application preferences Extraction Complete Page 11 CleverQ Extractors Manual This occurs if you have not enabled CleverQ through QuickBooks Integrated Applications The first time this occurs you will see a window similar to th
42. may be missing some report data you need to create parameters so use this option with caution Also note that the CleverQ calculation engine will fill in data gaps with zeros in report data or in other words getting data missing inside the report date range will return zero While getting data missing outside the report date range will return null Nulls will usually result in a warning or error message Page 7 CleverQ Extractors Manual Each report has the option to load all rows and all columns When these options are selected you will get a complete extraction and possibly many rows and columns that are all zeroes This is necessary when there exists data for parent and child items and consistency is required for row names and filters When the All Rows option is selected rows with all zeroes will not be extracted if the Load records with zeros option is not selected When the all rows and all columns options are not set the extraction will only include rows and or columns that contain non zero values Some reports ignore the all rows and all columns options Any zero value entries regardless of the all rows and all columns options are not extracted unless this option Load records with zeros is selected Override Data Range In some cases you may want to extract data for a specific data range and overwrite the options for each report This is not a frequently used option but comes in handy when working with the s
43. ng one or more entries 1t will look something like this Page 42 CleverQ Extractors Manual Cod Date tion Eotenial Data mile Ela lx BE oe k eo Clear Selections Select All Get External Data El Use the button to browse to the file location of the data file mdb Once the MDB file has been selected you can choose the report name using the dropdown list under Report Name Behind the scenes CleverQ will open the MDB file and get a list of the report names and populate the drop down list To delete a selected report name from the list press the button on the appropriate line After the data 1s loaded you can view the data by pressing the button This could be used to verify you have loaded the data properly Remember the Report Name is the name of the MDB followed by a dash followed by the report name unless you choose the option Do not use External File Name as part of Report Name in which case the Report Name will only be used After optionally selecting the Date Range Row Name and or Column Type press the Refresh button Y ou use the same form Load Data from External Data File to set up and execute the loading process On the left side of the form are checkboxes Check off those reports you want to load You can select all of them by pressing the Select All button on the bottom of the form You can de select all of them by pressing the Clear Selections button on the bottom of the
44. o making your QBD file larger than necessary Here is a helpful analysis if you want to bring in all combinations For filter you will need 2 extraction definitions one with the filter and one without the filter For 2 filers you will need 4 extraction definitions one with no filters one with only filterl one with only filter 2 and one with both filters For 3 filters you will need 8 extractions No No No No Yes a For 4 filters you will need 16 separate extractions And for more filters the number of extractions can be calculated using the formula Number of Filters Number of Extractions History Settings On the History Setting tab you can set options for doing real time extractions Page 20 CleverQ Extractors Manual Edit Database Source ACCESS x Monthly C Program Files Dealership Software DataiCQ_DS Queries mdb CO Sales r DS Unit COGS Field Mappings Date Range Filter Mappings History Settings Status ms y ome 1 9 2010 12 47 20 PM Open Data Source GetData View Data Extracted Keeping History Normally whenever you load the data from a query all the previous data from that query will be deleted You can keep this data so it doesn t get deleted by checking off the option Keep History This is useful if you want to maintain the data for plotting or comparing values The Last Data Loaded at field will tell you the data and time when the last load was performed I
45. omer Summary Filter V Sales by Item by Rep Summary Sales By Item By Rep Summary Filter V Sales by Item Summary Y Sales By Item Summary Filter e a v Sales By Rep Summary Filter V Seasonal Trend FP V Seasonal Trend PL The form can be resized to you can see more data 1f you want The data 1s shown in a pivot table and there are many options for looking at this data EN View Extracted Data newton A ers Ce Jia Row Number RowName 64 10100 Checking 5025182 65348 96 57450 59 68688 73 71157 08 59807 69 55115 44 70365 80 55191 61 65713 29 58673 32 64684 78 576 Ss 10400 Petty 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 Ge 10300 Savings 31200 31200 31293 42 31293 42 31293 42 31322 72 31322 72 31322 72 31408 64 31408 64 31408 64 31481 03 314 a7 Total Checking 82451 82 97548 96 89744 01 100982 15 10345050 92130 41 87438 16 102688 52 87600 25 98121 93 91081 96 97165 81 901 100914 22 86688 73 113409 93 112892 97 129392 97 152602 97 163635 07 200210 07 232440 07 236615 93 243255 50 242244 55 2739 G10 Total Accounts 100914 22 86688 73 113409 93 112892 97 129392 97 152602 97 163635 07 200210 07 232440 07 236615 93 243255 50 242244 55 2739 az 12800 Empoy 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 013 12100 Invent 24418 10 24834 50 24834 50 24174 80 24174 80
46. or viewing the complete CleverQ log not just for QuickBooks extractions You can purge the log if you choose Pressing the Refresh button will update the form every few seconds so you can see the progress of activity especially 1f you are doing a long extraction Viewing Extracted Data You can view extracted data for a specific report on the Report Data Definitions tab byh pressing the View Extracted Data button or for any report by pressing the Show Extracted Data button on the Extract tab The form will open with the Report Name filter dropdown open Uncheck Show All and then select the report you want to see Page 14 CleverQ Extractors Manual iew Extracted Data v A P Aging Summary v A P Aging Summary Filter v A R Aging Summary Z A R Aging Summary Filter V Dept Allocation V Dept User Allocation Formula Parameter 0 Z Inventory Stock Status by Item 71227 23 Y Inventory Stock Status by Item Filter Inventory Valuation Summary Y Inventory Valuation Summary Filter V Open Sales Orders by Item Open Sales Orders by Item By Rep 0 144031 44 335552 98 1532 4 V Open Sales Orders by Item Filter 0 0 0 V Open Sales Orders by Rep v Open Sales Orders by Rep Filter 0 V Payroll Summary V Payroll Summary by Employee V Profit amp Loss Profit amp Loss Filter v Sales by Customer Summary 0 0 0 Y Sales by Cust
47. ple need to pick one on the rest of the form Edit Borel Sunes D CleverQ Documentation Excel Samples Quickbocks Export 1 xls M Balance Sheet Range Mappings Filter Mappings History Settings Status RowhWames T Open Data Source Get Data View Data Extracted Once you have selected a workbook file you can actually open it up in Excel very easily by clicking on the Open Data Source button You can actually edit the workbook at this time but any new named ranges will not show up on this dropdown lists If you do add any named ranges close the Edit Excel Source form and reopen it The next item you need to add is the Report Name you want to assign to the Extracted Data You can have several extractions defined with the same name and all the data will be treated by CleverQ as the same data source This is common for multiple companies different dates etc Page 30 CleverQ Extractors Manual You then need to select a data type This refers to whether the data is monthly weekly daily quarterly annual or current Mappings There are four 4 tabs showing various items The most important is the Range Mappings tab From the dropdown lists choose the Row Name Data Value Column Date Row Number and Column Type ranges Some of these can be left blank Except for the Data Value entry you can also enter text that is not in the list and this will be used as the value for all the records extracted On the Filter
48. rt data T Null to zero p pp P part net earnings or net profit source or a Formula based on other parameters F No Filters Map Parameter Report Mame Profit amp Loss Row Mame Met Income Column Type Calculate Parameter Formula A Mote For Formulas When a parameter Formula is calculated normally the results are calculated For each time period in a date range and stored to be used by other parameters or indicators To only have a Formula calculated once with the specified date range preceed the Formula with Ratio E Add New Parameter Delete Parameter Where Used Calculate Page 48 CleverQ Extractors Manual Viewing Extracted Data Using the PivotGrid Some extractors provide a way to look at the extracted data using a powerful PivotGrid In particular the QuickBooks extractor provides this interface When the View Extracted Data form opens you will be prompted to select a report Uncheck Show All and check off the report you are interested in EN View Extracted Data AE or tes omen om nt Jer Je Je a _ A P Aging Summary te Column Type _ A P Aging Summary Filter 6 30 2008 CI AR Agina Summary New Construction overhead Remodeling ofrotamet Amou C A R Aging Summary Filter 1 Balance Sheet re j _ Dept Allocation 7 Dept User Allocation 0 r _ Formula Parameter 0 C Inventory Stock Status by Item C Inventory Stock Status by Item Filter C
49. sts each of the extraction definitions On this form you can see a description of the extraction as well as the last status of the extraction i Balance Sheet Daily Pros EDIT Data Data load complete 0000 Data load complete 0000 J Examples 1 Current Prol EDIT Data Data load complete 0000 Data load complete 0000 M Examples 2 Monthly fes EDIT Data Data load complete 0000 Data load complete 0000 M Examples 3 Monthly fes EDIT Data Data load complete 0000 Data load complete M Examples 4 Monthly Ea EDIT Data Data load complete 00 Data load complete 00 V Examples 4 Transpose Monthly EN EDIT Data Data load complete 00 Data load complete 00 W Profit amp Loss Monthly Pros EDIT Data Data load complete 00 Data load complete 00 W Stocks Test Filter Current StockNames Pros EDIT Data Data load complete 000 Data load complete 000 M Stocks Test Normal Current pros EDIT Data Data load complete 0000 Data load complete 0000 Clear Selections Select all Add New rn Get Data PRE There are checkboxes next to each definition to select which extractions will occur when you press the Get Data button on the bottom of the form This 1s the manual method to get multiple extractions You can also manually do one extraction at a time which is available on another form that will be described later The extractions can also be programmed to occur at a certain time That is covered in another section of
50. t figure Page 16 CleverQ Extractors Manual Edit Database Source GE Field Mappings Date Range Filter Mappings History Settings Status Open Data Source GetData View Data Extracted Data Sources The Data Source Type dropdown box will give you a choice of two options Access or ODBC Microsoft Access If you choose to connect to a Microsoft Access database press the button to browse to the MDB or MDE file This file cannot be password protected You can then use the dropdown box labeled Query Table View to choose either a table or query in the database Once you do that the dropdown for the various field mappings will automatically have the field names for that table or query You can even press the Open Data Source button to look at the raw data CleverQ will open up a new instance of Microsoft Access and automatically display the table or query in read only mode ODBC If you choose to connect to an ODBC source the ODBC source must have already been established through Windows You will generally find the program to setup ODBC connections under the Administrative Tools in Windows The ODBC sources listed as System DSN s are available in the dropdown list of sources NOTE If you are running on a x64 machine you must use the 32 bit ODBC administrator that can be found at windir S ysWOW 64 odbcad32 exe You can then use the dropdown box labeled Query Table View to choose either a q
51. the user manuals You can individually check of the selection box or use the Clear Selections or Select All buttons However you leave this form in terms of the selected definition they will be remembered after it is closed When you do a complete reload from the Main Menu using File Load Data from Sources the selections on this form will be used to determine what data will get loaded On option that is common to all extractions is to Load Data with Zeros This option is selected by checking off the box on the bottom of the form When unchecked any Excel data cells that are blank or zero will not be loaded into CleverQ Page 29 CleverQ Extractors Manual For each extraction you can view the data extracted by pressing the button To edit the extraction press the button EDIT next to the definition Adding and Editing Definitions To add a new extraction definition press the Add New button on the bottom of the form The Edit Excel Source form is shown in the next figure When adding a new source first enter the filename of the Excel Workbook There 1s a button to the right of the field where you put the Excel Workbook file You can click this to get a file select dialog and browse your disk for the file you want to use You can also just type in the name The software will actually open the Excel Workbook in the background and gather the names of the named ranges in the workbook You sim
52. tware to automatically load periodically by entering an Update Interval This can be a value in terms of minutes hours or days When the current time and the Last Data Load at value exceed the update interval the data 1s loaded in the background This can provide a real time update of data and gauges on a dashboard Don t forget to set your preference for Auto Update Dashboard When data from an Excel worksheet is loaded the worksheet is recalculated so the most current data is loaded This includes updating any web queries that are stored on the worksheet Page 32 CleverQ Extractors Manual Status And finally there is the Status tab This simply shows the results of the last extraction including any error messages Edit Excel Source Ds CleverDocumentation Excel Samples Quickbocks Export 1 xls M Balance Sheet Range Mappings Filter Mappings History Settings Status Data load complete Open Data Source Get Data View Data Extracted Deleting To delete a selected definition press the Delete button on bottom of the form You will be prompted to delete any data with the same report name prior to completing the deletion Copying To make it easier to create definitions from existing ones there 1s a Copy button Press this button and a copy of the current definition will be made Page 33 CleverQ Extractors Manual Testing It is recommended when you setup an extraction to testit Th
53. u can choose a printer and print or export the PivotGrid to a variety of formats including PDF HTML MHT RTF TXT CSV XLS XLXS BMP EMF GIF JPEG PNG TIFF and WMF You can also directly email from this form LS Previet GE BEA EX alele alg a tE naaa m Wal gt o F amp A larl OL nilo ea rr i ir a a re RE i la lo la fal ha lo i i a E i PEGEUDEEEES ED Etta peugiiiser chive COHERE A OEA LEN cabsbbblys gra ALLEE ARROBA MHH ESE ba Page 1 of 10 Zoom Factor 38 Page 54
54. uery table view in the database Once you do that the dropdown for the various field mappings will automatically have the field names for that table or query You can even Page 17 CleverQ Extractors Manual press the Open Data Source button to look at the raw data CleverQ will open up a query and automatically display the data in read only mode Report Name After you select your Access or ODBC data source you can enter a report name or leave it blank and CleverQ will automatically assign a report name based on the source Multiple definitions can have the same report name Data Type You must select a datatype This basically tells CleverQ whether the data to be extracted will be stored in the data cache as monthly daily annual quarterly weekly or current data This affects how the data maybe totaled in the extraction as well and how the data will be made available to the CleverQ ACE and VP Field Mappings You must at a minimum assign a field to the Row Name and Data Value mappings Essentially if you were to input a simple list of items these would be the only items you need The Row Name must be mapped to a string field and the Data Value must be mapped to a numeric field If you leave the Column Data blank the current data will be used You can also enter a fixed value rather than a field for the Row Name by enclosing a string is single quotes This may be useful if you are going to use the filter fields For the
55. ws and columns that are all zeroes This is necessary when there exists data for parent and child items and consistency is required for row names and filters When the Al Rows option is selected rows with all zeroes will not be extracted if the Load records with zeros option is not selected When the all rows and all columns options are not set the extraction will only include rows and or columns that contain non zero values Some reports ignore the all rows and all columns options Any zero value entries regardless of the all rows and all columns options are not extracted unless this option Load records with zeros is selected C Documents and Settings Al Users Documents Clever evera Data Sample qbd On this tab you can set the following options Using Job Tracking Job Tracking within QuickBooks provides the ability to segregate data by job Use this option if you are using Job Tracking in QuickBooks This basically allows you to set a job filter for certain reports and your extracted data can be utilized by job Using Classes If you have setup Classes in QuickBooks and want to segregate data by classes check off this option You will be allowed to setup a filter for Classes for various reports Load Records with Zeros If you choose to not load the records with zeros the amount of data loaded will be smaller and this will improve the performance of the system Without the records you

Extracting the Data

Contents

Download Pdf Manuals

Related Search

Related Contents