Hello,

New to QODBC (trialing the product).  End objective is to export P&L balance by month by class to an OLAP reporting tool (TM1).  Appreciate I will need to run the report for each month period.   Ideally wanting to export a trial balance by class, but looks as though this is not possible (based on the posts and inability to output by class.)

End output wanting:

Account #, Name, and column for each class with net debit/credit balance and grand total on the right.

Used sp_report, but no natural sign on the report.  Revenue accounts are +ve, as are expenses. 

Here is what I have

sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateFrom = {d'2005-07-01'}, DateTo ={d'2006-06-30'}, SummarizeColumnsBy = 'Class', DisplayReport = 'No' where RowType='DataRow'

We have some 36 different classes (or departments) to report on.

Any help would be appreciated. 

Also, is it possible to have the output class name  above the amount on each column, rather than on  every row in the first 40 columns....  (ie column 3 has "Amount_3_Title" and then the name on every transaction row

Thanks

Thankyou, yes I have already looked at this (and added as part of my post).

ie: 

sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateFrom = {d'2005-07-01'}, DateTo ={d'2006-06-30'}, SummarizeColumnsBy = 'Class', DisplayReport = 'No' where RowType='DataRow'

I am not after the total net income, but rather the spend by each account line, by class.  Also have the sign issue.

Any help would be appreciated.

Thomas

I am not to sure what you want in regards to signage. I already said how to get the column names in my other posts, but hear goes, my sample data from home doesn't have classes, but you should be able to get the idea. For this report:

I can do:
sp_report ProfitAndLossByClass show Amount_Title, Label,
Amount_1 as Unclassified, Amount_2 as TOTAL
parameters DateFrom = {d'2005-07-01'}, DateTo ={d'2008-06-30'},
SummarizeColumnsBy = 'Class', DisplayReport = 'No' where RowType='DataRow'

You need to read the Amount_1_Title, Amount_2_Title and so on values, then re-run the report with the values inserted "as name" for each column.  To do the sign of the type of account (income and expense), I need to break the report into income and expense reports like this:

For Income Accounts:
sp_report ProfitAndLossByClass show Amount_Title, Label,
Amount_1 as Unclassified, Amount_2 as TOTAL
parameters DateFrom = {d'2005-07-01'}, DateTo ={d'2008-06-30'},
SummarizeColumnsBy = 'Class', DisplayReport = 'No',
AccountFilterType = 'OrdinaryIncome'
where RowType='DataRow'

For Cost of Goods (COGS) and Expense Accounts:
sp_report ProfitAndLossByClass show Amount_Title, Label,
(Amount_1 * -1) as Unclassified, (Amount_2 * -1) as TOTAL
parameters DateFrom = {d'2005-07-01'}, DateTo ={d'2008-06-30'},
SummarizeColumnsBy = 'Class', DisplayReport = 'No'
where RowType='DataRow' and
(AccountType ='Expense' or AccountType='CostOfGoodsSold')

This could be made simpler by adding the AccountType column and preforming the x -1 calculation based on the type of account.

Thankyou Tom, that did help.  No issues with the amount_1 titles etc.

I can get around the sign issue in my OLAP cube if I can display an identifier (like AccountType) that I can use to determing the natural sign of the account.   Added AccountType to the show part of the query, but only receive blank data.  Same goes for AccountNumber, AccountName, and NestedText1.

I do have correct data on the report.   The label has the a/c number and then the name.

What am I missing here?  sintax posted below:

sp_report ProfitAndLossByClass show Text, Label, AccountNumber,  AccountName, AccountType, NestedText1,  Amount parameters DateFrom = {d'2006-07-01'}, DateTo ={d'2006-10-30'}, SummarizeColumnsBy = 'Class', DisplayReport = 'No'

Thanks again for your help.

Thomas

This works for me:-

sp_report ProfitAndLossByClass show Text, Label, AccountNumber,  AccountName,
AccountType, NestedText1,  Amount
parameters DateFrom = {d'2006-07-01'}, DateTo ={d'2008-10-30'},
SummarizeColumnsBy = 'Class', DisplayReport = 'No'