Formatting data horizontally in a report

[roperj]

Alright, I have a somewhat specific problem with formatting data in a report that seems like it should be a simple fix, but most of ways I have looked up to get it the way I need it have proven too difficult.

I have a report that I'm using as a brochure that lists prices. There is the base price, and then it applies a discount rate depending on how many are purchased. This is a calculated field (code below). Currently, it displays the prices in a list going down, but I would like it to have the prices extending out to the right. I read about doing a cross tab query, but couldn't get that to work with what I had, but I could have been doing it wrong. Here is a picture of the design view:

So, as you can see, I have it sorted by MemDescription as the header, based on the type of plant. Next, it separates them by their type, and then for each of the size in a type, it lists the prices, which can be anywhere from 1-3 different prices depending on the amount of discount rates available.

This is how it outputs:

What I would like to do is have the price fields lay out horizontally from a starting point, but I am having a lot of trouble doing that. I can go into more detail about calculated fields and the query if need be, but hopefully there are ways to do this on the report itself.

 

[stopher]

You can do this as follows:

• Create a sub-report and put the fields you want to appear horizontally in the sub-report
• In the Page Setup of the sub-report, select the columns tab and decide how many columns you want. Also choose the column layout
• add the sub-report to the main report.

hth
Chris

 

[roperj]

Okay, I see how this would definitely work. I need a little bit more help though. How should I set up the sorting and grouping between the main report and the subreport. My first attempt is creating a subreport thats way way to big, I'm also not sure what I should put as far as where to connect the subreport and the main report

 

[stopher]

You need to size your subreport to be small. It only needs to be big enough to hold the fields you want to repeat horizontally.

The joining field I assume is strUnitMeasure? This needs to be in your subreport (invisible if you like). You then can join the main and sub report on this field.

Post your database if you get stuck.

Chris

 

[roperj]

In which of the groupings should the subreport go into?

 

[stopher]

You can pop the subreport in the detail section. Take a look at the attached example.

You can play around with the formatting to get it just how you want it e.g. get rid of the border.

hth
Chris

 

[roperj]

All of the sub reports I have been making haven't been filtering the data correctly. Most show way too much information, and don't cut it down to just the sale price and discount level for the plant at that size. Should I use the same query as the original report, or a new one?


Also, from what I can tell, the example you posted doesn't seem to have horizontally formatted data.

 

[stopher]

You need to view the report in print preview to see the horizontal effect. The other views won't show it.

 

[stopher]

Sorry, I didn't answer your other question.

The sub-report can have an independant data source. The report and sub-report are synched by the master/child properties in the the sub-report properties window.

I suggest googling sub-reports if you're stuck.

Or post your database here.

Chris

 

[roperj]

Alright, so I exported the view the report is based off of into a new table, and based the report off of this view. It s large table that has all of the data, and then just the report. THis is basically how it is currently formatted in my database now. I'm definitely following you on using a subreport and changing the orientation to get the data to print horizontally, I am just stuck on how to group the subreport so that the data groups correctly. Thanks again.



EDIT: the mdb is 5mb, too big to upload on the site, can I email it to you?

 

[stopher]

Run a Compact and Repair on your database first. That will reduce the size.

Then zip it.

Otherwise email it to me at
howeyguard-accessforum@yahoo.co.uk

 

[roperj]

I've emailed you the db

 

[stopher]

here's your database with a new report.

First I reset your page layout back to normal i.e. full page width and single column. The reason is because we want the multiple columns only at the detail level. To do this we have to create a separate report and use it as a subreport in our main report.

So I created a new sub report and put the price fields in it. I made it very small so that 4 report widths of the sub report would fit across a page of the main report. I set the columns to 4. If you run the sub report (in print preview) you will see the 4 columns of two fields. It will show all records but that doesn’t matter because the master/child attributes will deal with that.

I added the sub report to the main report in the third level (not the detail level). I set the master/child attributes. Note how I had to set these to ensure I picked up the right prices for the right combination. I had to do it this way because your data is not normalised.

I adjusted the width of the subreport object in the main form to fill the width of the main form.

That’s it.

 

[gemma-the-husky]

that's a neat work-round, chris

roperj - chris has achieved what you want by using a mult-column report.

trying to do this by compiling a single list of maybe comma separated values eg 12, 14, 20 - would be much harder. It needs programming to select the relevant rows, and read through them, assembling a combined string.

 

[roperj]

It worked! That you so much, I have spent a stupid amount of time on that one simple facet of the report. It was complicated because of the differing layers of information that are sorted, and the calculated field that comes from the view.