cancel
Showing results for 
Search instead for 
Did you mean: 

XL Reporter - Advanced Report Builder

Former Member
0 Kudos

Again I have problems to get the results I need in an evaluation using XL Reporter.

This is the code of a row selection with row expansion by customers:


BPA( CardType = "C" And rondot = "Y" ) 
TTY( Code = "ARCreditMemo","ARInvoice","JournalEntry" ) 
FIG( ContraAct = "8600","8601","8602","8640","8648" )  
Group By BPA.CardCode

rondot = "Y" means that only customers marked by the user defined field "U_rondot" are evaluated.

Additionally I have column selections by periode (one column for each month of the year).

I access the sum for each customer per month with function

ixGet("Amount")

.

The expected behaviour is:

Show me for each customer and month the sum of all net amounts booked to account 8600 or 8601 etc.

The actual result is almost as expected but the gross amounts are evaluated, not the net amounts!

The code shown above looks for the specified Contra Account (ContraAct), so the customer code is in ShortName. In this lines in the journal actually the gross amount for the specified customer is booked.

I also tested it with the following code where I changed ContraAct to +ShortName:

FIG( ContraAct = "8600","8601","8602","8640","8648" )

Now the lines in the journal show the net amount booked to the specified account and the customer is shown in ContraAct. But the results of the XL Reporter are now all 0,00! It seems that XL Reporter needs the customer code in ShortName to filter out the customer specified with +BPA( CardType = "C" And rondot = "Y" ) +.

How can I write the request to get only the specified customers and the net amounts booked to the specified accounts?

Can somebody help?

Thank you!

Frank Romei

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

You could try a formula ixGet(Amount)-ixGet(vatsum) - I'm not sure of the correct name for the tax amount field. I have used this successfully in some marketing document based reporting

Former Member
0 Kudos

Hi Julie,

I don't think that this will work because the lines in the SAP JDT1 table doesn't hold the vat amount data, so there is nothing available to subtract.

You did this with marketing reports, as you have told, where this data is stored in the DB tables.

But nevertheless I'll try it out. If it works you earn 100 points!

Thank you!

Frank Romeni

Former Member
0 Kudos

If you run query on JDT1 table, you will know the reason. Because the net amount may not be posted in the account you selected.

I suggest you run query first to see which accounts have the amounts you try to get.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

you are right! But I already know why not net but gross amounts are evaluated with my first query - that was not the question!

The lines in JDT1 specified with this selection are those with ContraAct = 8600,8601, ... and ShortName = "user codes as specified in BPA(...)".

These lines hold the gross amount.

The lines in JDT1 holding the net amount booked to accounts 8600, 8601, ...(this is what we need) are those with ShortName = 8600, 8601, ... and ContraAct = "user codes as specified in BPA(...)".

So I tried to change the code for the selection as follows:


FIG( ShortName = "8600","8601","8602","8640","8648" )

And this doesn't work, because it appears that XLR matches the BPA(...) specification against ShortName. Of course, in this case there are no customer codes matching the account codes in ShortName and no values are found.

It is so simple to solve this request with a sql-statement in SAP - but we need it as part of a large XL Report.

Any idea?

Thank you!

Frank Romeni

Former Member
0 Kudos

XLR uses its own language to link tales and run SQL. We have no way to copy SQL directly into XLR. I may have got more problems than you with it. If all efforts fail, I would use ODBC and Database Query in Excel directly. That is easy since I can copy SQL query to it. In your case, this is part of large XL Report. I am afraid you may have to give up XLR for your special need.

Former Member
0 Kudos

Hi Gordon,

I think you are right that it is not solvable with XLR.

But I can't use the database query from Excel directly because the overall report, including the parts that work, has additional column selections which expand to the month of a year.

I don't think that I can mount direct queries from Excel into the overall structure.

Currently to many parts of my large report the user has to call separate SQL-Queries from within SAP to correct manually the results evaluated by XLR. It's realy an awkward situation explaining this to our customer.

OK, I hope nobody asks me in the future to do a job with XLR in the future again. I suppose I would refuse it.

Thank you for help!

Frank Romeni

Former Member
0 Kudos

Even though so many people do not like XLR, it is still a great tool for simple report. The decision to make a complicated report by this tool need to be very careful. That is all. To refuse to use it might be too extreme to me. Using some soft words like: Could you consider Crystal Report in order to get complex report would be more realistic.

You still have option to use direct query from a new sheet and by VLOOKUP function to get the data to the original sheet.

Former Member
0 Kudos

Your post looks confusing. First of all, the FIG has same value for two, how come it has different results?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon!

Sorry! It's my mistake!

The second formula should be

FIG( ShortName = "8600","8601","8602","8640","8648")

Frank Romeni