Put Details section fields horizontally?

[Former Member]

Good Day!

I want to display my last two fields next to each other. Currently is looks like this in my report:

Business Name | Month(YYYYMM) | Amount

Company A | 200806 | 500

Company A | 200807 | 700

Company A | 200808 | 200

Company B | 200806 | 800

Company B | 200807 | 1000

Company B | 200808 | 250

I want to display it like this in my report:

Business Name | 200806 | 200807 | 200808

Company A | 500 | 700 | 200

Company B | 800 | 1000 | 250

[Former Member · Accepted solution]

Have you considered a cross tab?

Fuskie

Who supposes you could also hard code the display with formulas if the number of rows and columns is fixed...

[Former Member]

Hi,

Try this..

Have sub-reports in the detail section adjacent to the company name..

Pass two parameters to the sub-report, one the company name and the other the date..

So if you have three dates there will be three sub-reports..

Please note there will be as many sub-reports as many date values.. As a developer I will not recomend this.. This is because the report will be very slow and will be hard to maintain in future..

This is one way to achive your target..

eg:

Business Name | 200806 | 200807 | 200808

Company A | 500 | 700 | 200

Company B | 800 | 1000 | 250

Whatever shown as Italics will be subreports..

[Former Member]

I have done case statements in mySQL db to display the fields. Rather do it on db side than on the reporting side.

Thanks for the guidance.

[Former Member]

Hey Shaun,

You can also try "Format with Multiple Columns".

Go to Details in the Section Expert.

You can make a formula for the each month:

Maybe you can make one formula with a Select statement

and group on it, like:

@Dates

Select Month(Table.Date)

Case 1 : 1

Case 2 : 2

etc.

Default

...of a series of "if" statements if you need the date to show up, even when there's no data.

Otherwise, you can just group on Month(Table.Date)

The idea is that you can have all the formula boxes line up like the columns in a

spreadsheet or db.

Hope this helps,

The Panda

[Former Member]

Hi Shaun,

Insert a geoup on company field and create a formula like this

@Evaluate1:

whileprintingrecords;

stringvar txt1;

txt1:=txt1"|"totext(date field);

see the example

txt1:=txt1"|"totext(200806);

Also create anothe formula like

@Evaluate2:

whileprintingrecords;

stringvar txt2;

txt2:=txt2totext(amount field)"|";

now place these two formulas in details section and suppress the details section

Also create one more formula like

@Initialize:

whileprintingrecords;

stringvar txt2:="";

plcase this fromula in group header and suppress the group header.

Now place the formula @Evaluate1,@Evaluate2 in group footer that shows the out put in horizontal way.

Regards,

Raghavendra

[Former Member]

Hi Shaun,

To get the required format create a crosstab report.

On column select Year Field, on rows select Busniess Name and on summary field select Amount.

This will resolve the issue.

Regards,

Sastry

[Former Member]

Thanks for the help so far and getting closer to resolving the issue .

The cross tab is working BUT the formatting does not do what I really need. I have this as my result:

200808|200809

Company A 200 | 500

200702|200806

Company B 250 | 800

I want to have one heading with the years and all the companies underneath each other like my first example. So if one company does not have a value for one year it must have a 0 in it.

[Former Member]

You could transform the dataset either by (1) modifying the underlying SQL statement that fetches the records and doing recursive left joins to get the data to line up or (2) using manual running totals to hold and place the data exactly where you need it to be on the report - thereby creating a dynamic report layout (search on 'manual running totals' on this forum or Google for details).

I'll tell you that either option will be error-prone - because you have to consider every possible scenario that could arise: how years of data are you going to have; from the sample data set, you almost have to join the table to itself thrice to get the data to line up. What if you end up with 10 months' worth of data? Same comment goes for the manual running total option.

A simpler alternative that may work - albeit may not so elegantly - is to use a cross-tab instead. One thing you'll be assured of is that it will scale gracefully to handle any span of data.