Total Values Split By Month

[mrbrad1986]

Hi,

I am building a report in Business Objects 4.3 and cannot seem to get the output I need.

I am running a report that is capturing a number elements and then I wanted a grand total for these elements, split by each payroll and then by each month. The report itself looks for the specific elements needed in a date range of Jan21 - May 21 and I have included the payrolls I need.

The output I wanted is shown below. So for example

Payroll Name Jan 21 Feb 21 March 21 April 21 May 21
Example Payroll 1 300 200 275 320 250
Example Payroll 2 125 100 110 80 100
Example Payroll 3 150 155 140 190 165

I have tried creating variables like
=If [Transaction Date:Payroll]=“31/01/2021” Then [Total Cash Value:Trans]

But I just get error messages. I have also tried
=[Total Cash Value:Trans] Where [Transaction Date:Payroll]=“31/01/2021”

But again I’m getting error messages that I can’t figure out what the issue is. Any help on this would be amazing!

Thanks

[nscheaffer]

Have you tried creating a crosstab?

[mrbrad1986]

Hi,

If I’m honest, I’ve no idea how to do that I’m afraid. Quite basic level knowledge of BI.

Thanks

[nscheaffer]

No worries.

You can transform a standard vertical table with Payroll Name, Transaction Date:Payroll, and Total Cash Value:Trans into crosstab by right-clicking on it and choosing Turn Into > Cross Table. Here is an example with Quarter, State, and Quantity sold from the eFashion universe.

Which gives you this…

Is that what you want?