Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
CustomSummary Report and UNbilled costs only |
Author |
Message |
|
Posted : 2008-06-01 07:00:22 |
My QB report is a Custom Summary Report with Display Columns by Total only and Display rows by Customer with Filter of Billing Status = Unbilled. This gives me what is unbilled costs by customer but with totals only. I am trying to create the equivalent report with QODBC, but Billing Status is not an available parameter and ItemLineBillableStatus is not an available column in this report. Suggestions? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-02 08:28:26 |
You need to use a CustomTxnDetail report as it has a BillingStatus filter.
sp_report CustomTxnDetail show Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, BillingStatus parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly', OpenBalanceAsOf = 'Today' where BillingStatus= 'Unbilled'
See: How do I run a CustomTxnDetail Report? for more. |
|
|
|
|
Posted : 2008-06-04 09:27:52 |
I am only interested in total balances for each client. Despite the parameter of SummarizeRowsBy='TotalOnly', I still get many transactions for each customer. The CustomSummary report is exactly what I need, but I just need to filter out billed jobs. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-04 10:19:36 |
Try working with this:
sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, BillingStatus, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'Customer', OpenBalanceAsOf = 'Today', ReportDetailLevelFilter = 'SummaryOnly' where BillingStatus= 'Unbilled' |
|
|
|
|
Posted : 2008-06-05 08:19:52 |
That query only produces 1 record but I know it should produce closer to 800 records. Here is the VBDemo screenshot.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-05 08:45:30 |
Detail level refers to the detail lines in a transaction (for example, the line items on an invoice or the expense account detail on a check). When you create a transaction report, you can use this filter to show or suppress the detail lines for each transaction. All - Include the detail lines for each transaction. For an invoice, for example, the report shows the amount of each line item as well as the invoice total. SummaryOnly - Exclude detail lines. The report shows only the total amount of each transaction. AllExceptSummary - Include the detail lines for each transaction without the transaction totals.
so this ins't going to help you. The problem with the CustomTxnDetail report script that I gave you is that the Total lines are being blocked by the BillingStatus= 'Unbilled' filter. This works a little better:
sp_report CustomTxnDetail show Blank, RowData, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, BillingStatus, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'Customer', OpenBalanceAsOf = 'Today' where BillingStatus= 'Unbilled' or Blank is not null |
|
|
|
|