QODBC working but will not retrieve dates prior to Sept. using Crystal Reports |
Author |
Message |
|
Posted : 2007-09-04 08:14:52 |
This is a very weird problem. I finally have the driver working in unattended mode. The crystal report calls for data to be pulled for the "last 7 days". As of the date of this writing this would include Aug 07 and Sep 07 . . That doesn't work. But any data which has been entered ONLY for Sept 07 comes up! How can that be? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-04 08:53:44 |
|
|
|
|
Posted : 2007-09-04 14:01:23 |
The syntax is the crystal commands and its programmed into the report under the Select Expert as the following formula:
{Command.Date} in Last7Days
This has worked in the past with the 2006 driver - but no longer works under the 2007. Or at least nothing has changed in the report . . . |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-04 16:21:56 |
Check that you actually have transactions for the Last7Days. |
|
|
|
|
Posted : 2007-09-04 22:30:19 |
I do have transaction for the last 7 days. For some reason it will only read transaction as of begining of Sept. and not end of Aug (tail end of the 7 days). I tried removing the criteria from the selection page and adding the criteria to the command part (sp_report) in the database part. Now I get some dates, but again - not all. How can that be? I thought we have an all or nothing solution? Show I reoptimize or something? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-05 09:07:17 |
Again, you will need to either show a screen dump of what you are trying to do or the SQL statement being executed by Crystal Reports. As far as the Last7Days function is concerned, the function was found to work correctly with QODBC as shown here:-
This report wizard executed the following SQL Statement:
SELECT "InvoiceLine"."CustomerRefFullName", "InvoiceLine"."TxnDate", "InvoiceLine"."RefNumber", "InvoiceLine"."InvoiceLineItemRefFullName", "InvoiceLine"."InvoiceLineDesc", "InvoiceLine"."InvoiceLineQuantity", "InvoiceLine"."InvoiceLineAmount" FROM "InvoiceLine" "InvoiceLine" WHERE ("InvoiceLine"."TxnDate">={d '2007-08-29'} AND "InvoiceLine"."TxnDate"<={d '2007-09-04'})
and 5 records were returned, which also matched with the 5 records returned using VB Demo:
For the record, I didn't have any transactions on the 29th, 30th or the 31st of August:
|
|
|
|
|
Posted : 2007-09-05 12:18:41 |
In my SQL query I have the following:
sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'Last7Days', AccountFilterType = 'Bank'
This doesn't work.
In addition - what I REALLY want and don't know if possible is instead of Last7Days by transaction date the last 7 days in which the database was modified. This would allow me to include, for example, a transaction put in yesterday but dated laste MONTH! |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-05 13:32:46 |
The correct SQL Syntax is:
sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'
The valid values for the QODBC DateMacro parameter are:
|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter |ThisQuarterToDate|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate |LastMonth|LastMonthToDate|LastQuarter|LastQuarterToDate|LastYear|LastYearToDate |NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear| |
|
|
|
|
Posted : 2007-09-05 14:08:11 |
Things are starting to work as I am slowly figuring this out . . . thanks for your help.
In addition: Is there any way I can use the DateMacro for the date the database was modified (regardless of the date which the user entered for the transaction)? For example: Transaction entered: Sept 4, 2007 ---- but the user tried to write back validate a check and entered Feb 2, 2007 thinking that no one will catch this.....(The date macro will only do transaction for the last week . . . )
Therefore what I want is that regardless of the date that is entered for the check, I want the Lastweektodate to go according to when the transaction database was modified.... |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-05 17:45:18 |
You're far better off looking at the transactions themselves within the appropriate QODBC table for exceptions. For example, for manual checks, I can do:
SELECT * FROM Check order by TimeModified desc
which will show the latest modified check to the oldest modified date.
The TxnDate, TimeCreated and the last time the transaction was modifed can be anything! To see the last 50 modified entries do:
SELECT Top 50 * FROM Check order by TimeModified desc |
|
|
|
|
Posted : 2007-09-06 05:40:31 |
Since I am beginner at this two further "newibie" questions:
1. Since I am using Crystal to create my reports I am not sure how I can implement your suggestion in the sp_report statement that I have to provide to Crystal. How will I put the suggestion to use the the "SELECT Top 50 * FROM Check order by TimeModified" desc in the sp_report statement I need to give Crystal when I click to Add Command and am presented with the statement: sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'
2. How can I filter by TWO account types: Bank AND Credit Card instead of just BANK? What would be the AccountFilterType command to use? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-06 08:23:42 |
For:
sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'
The available AccountFilterTypes that you can use are:
|AccountsPayable|AccountsReceivable|AllowedFor1099|APAndSalesTax|APOrCreditCard|ARAndAP|Asset |BalanceSheet|Bank|BankAndARAndAPAndUF|BankAndUF|CostOfSales|CreditCard|CurrentAsset |CurrentAssetAndExpense|CurrentLiability|Equity|EquityAndIncomeAndExpense|ExpenseAndOtherExpense |FixedAsset|IncomeAndExpense|IncomeAndOtherIncome|Liability|LiabilityAndEquity|LongTermLiability |NonPosting|OrdinaryExpense|OrdinaryIncome|OrdinaryIncomeAndCOGS|OrdinaryIncomeAndExpense |OtherAsset|OtherCurrentAsset|OtherCurrentLiability|OtherExpense|OtherIncome|OtherIncomeOrExpense|
You can choose from any one of the above parameters. See: What do the new stored procedures SP_REPORTS and SP_PARAMETERS do? for more information.
You can run any SQL Statement you want in Crystal Reports in the Add Command pane:
|
|
|
|
|
Posted : 2007-09-06 08:50:52 |
Apologies for all these issues but here is another one:
In the Enterprise version there is NO VBDemo tool to check out the SQL statements to see the type of records they pool. Right now I have to use Crystal. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-06 09:09:34 |
|
|
|
|
Posted : 2007-09-06 09:27:05 |
Will I need another license? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-06 09:31:23 |
No, you can install the full version of QODBC over your Enterprise Edition. Intuit has chosen to restrict certain functions in what's delivered with QuickBooks Enterprise, but the full version of QODBC will work in read-only mode with USA editions of QuickBooks Enterprise 2006 and 2007 (or later) without a CD Key. |
|
|
|