Tax sheet Report (1 Viewer)

[w8ingforyou]

Hi Everyone.
i have a simple Scenario i have related to taxes
i have multiple taxes based entries

I_ref_number |% |Price |TAX |
I2 |5.50 |62.83 |3.45 |
I2 |14.50 |3097.5|449.13|

i am trying to generate a report in this format
i ref // 5.5% // 14.5% // 0% // price // totaltax
i ref // tax // tax // Tax // price // totaltax


can any one suggest me on how to do this or point me in the direction of similar examples.
i am quite confused of how to pull this off.

kindly help

regards.

 

[NauticalGent]

Good morning...I have having a little bit of a problem trying to determine what you are trying to do. Can you be a little more specific?

 

[w8ingforyou]

Good Morning
There are 3 main points
1. Pkey ; Invoice ID (I Ref no)
2. Pkey: Invoice Details ID.
3. tax % of Invoice Details ID ** 14.5%, 5.5%,0% Etc.,"" (COMBO BOX). (has a seperate table taxtbl)


My Requirement.
i want a Report which is a datasheet. in following pattern
InvoiceID // Taxes (14.5) // Taxes(5.5) // Taxes(0.5) // Taxes(0) // etc // Invoice Total // Tax Amount Total //

Example
InvoiceID // Taxes (14.5) // Taxes(5.5) // Taxes(0.5) // Taxes(0) // etc // Invoice Total // Tax Amount Total //
21//.......................450.00//...........26.00// ................0//..............0//.....................3576.22//...........................476//

 

[plog]

When you want to transpose column values into column headings you do so via a Cross-Tab query (https://support.office.com/en-us/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8).

However, a cross-tab query does not easily lend itself to a Report. That's because a report needs to know what your query column headings are--in a cross-tab query they are dynamically generated. So, if you have no Taxes(0.5) values, you won't have a Taxes(0.5) heading and the report will break because it can't find that field in the query.

So, the way to overcome this is to ensure your cross-tab query has every single Tax value you will want to report on--even if there isn't values to report on. This is done by having a datasource in your query with all the tax values and then using a LEFT JOIN from it to your actual table of data.

 

[w8ingforyou]

it seems like a complex problem to me now but can i export the data into excel and organize them there? because it is quite painful to see my tax agent scratch his head.
kindly help!
regards! :banghead::banghead::banghead::banghead:

 

[plog]

It would be simple to do this in Excel. You would get your data into Excel and then create a pivot table on it (https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576).

You provided no context for your screenshot, so I have no idea what it is for.