Help Pls - SubReport - Column Question

[Guest]

Hello All,
I have read through all threads dealing with this challenge and am still not
able to find the answer to what I need. I would appreciate any help soonest.

I have a Main Report with many subreports embedded, all run off a date field
selected i.e. 09/22/05-10/19/05 so can get quite lengthy. I have one
subreport that I would like to run in columns, really want it down and across
but understand that it has to be Across then Down to work it appears. Have
set subreport to that. Problem arises when viewing, it reports

Subreport is an output by date of number of calls, by category with a count
i.e.
Sept 22, 2005 8 (Group Heading is Date Call Came In, keep Group together)
Booked 2 (Group Header is Categoires, keep Group together)
Cancelled 1
Left Message 5
Total 8

What I am getting is:

Sept 22, 2005 8 Booked 2
Cancelled 1
Left Message 5
Total 8

Any idea why it is breaking the Category group up across the page instead of
keeping them all together as I want them. I can live with the Across and
Down as long as they will stay together but the way it is they don't make any
sense.

Any help gratefully accepted.

Thanks,
IEJ

 

[Marshall Barton]

I have read through all threads dealing with this challenge and am still not
able to find the answer to what I need. I would appreciate any help soonest.

I have a Main Report with many subreports embedded, all run off a date field
selected i.e. 09/22/05-10/19/05 so can get quite lengthy. I have one
subreport that I would like to run in columns, really want it down and across
but understand that it has to be Across then Down to work it appears. Have
set subreport to that. Problem arises when viewing, it reports

Subreport is an output by date of number of calls, by category with a count
i.e.
Sept 22, 2005 8 (Group Heading is Date Call Came In, keep Group together)
Booked 2 (Group Header is Categoires, keep Group together)
Cancelled 1
Left Message 5
Total 8

What I am getting is:

Sept 22, 2005 8 Booked 2
Cancelled 1
Left Message 5
Total 8

Any idea why it is breaking the Category group up across the page instead of
keeping them all together as I want them. I can live with the Across and
Down as long as they will stay together but the way it is they don't make any
sense.

I don't know, but it almost looks like your detail section
has its New Row or Col property set to Yes.

 

[Guest]

Hello,
No, no New Row or Col. This seems like it should be so simple but something
is wrong somewhere. It is a simple report that is run from a query, first by
date i.e. Sept 22, 2005 and then showing all the telephone categories entered
for that date with a Count in an unbound field. No Page Header or Report
Header anymore, I took them out to try to solve this.

I have the Date in a Date header, and the CategoryDescr in a CategoryDescr
Footer with a Count([CategoryDescr]). Results should be like shown
previously:
Date Header: Date ex. Sept 22, 2005
CategoryDescrFooter ex:
Booked 8
Cancelled 4
Concerned 6
etc. all down one side underneath the date.

Page Setup is set to 2 columns, Across and Down. The minute you view it, it
takes the Categories and splits them across two columns and puts the first
line in line with the date (even though you aren't asking it to split
anything). I have checked everything that I can think of and it still keeps
splitting them up.

Maybe I digress, what I really just want to happen is to have the Date shown
with all the categories for that date underneath. And if the Across and Down
is the way it has to be, I want it to print as it sounds like its should like:

Sept 22, 2005 Sept 23, 2005
everthing that is related to it everything
that is related to it

I would greatly appreciate help with this, it is making me crazy.

Thankyou,
IEJ

 

[Marshall Barton]

I'm still confused. What you're describing as the objective
is Down then Across.

Across then Down prints each section next to each other so
the result you're seeing is to be expected, except the
category should also be in two columns.

Maybe what you need is to place the date and "everything
that is related to it" in a subreport that only has one
narrow column. Then the parent report can use two or more
columns Across then Down.
--
Marsh
MVP [MS Access]

No, no New Row or Col. This seems like it should be so simple but something
is wrong somewhere. It is a simple report that is run from a query, first by
date i.e. Sept 22, 2005 and then showing all the telephone categories entered
for that date with a Count in an unbound field. No Page Header or Report
Header anymore, I took them out to try to solve this.

I have the Date in a Date header, and the CategoryDescr in a CategoryDescr
Footer with a Count([CategoryDescr]). Results should be like shown
previously:
Date Header: Date ex. Sept 22, 2005
CategoryDescrFooter ex:
Booked 8
Cancelled 4
Concerned 6
etc. all down one side underneath the date.

Page Setup is set to 2 columns, Across and Down. The minute you view it, it
takes the Categories and splits them across two columns and puts the first
line in line with the date (even though you aren't asking it to split
anything). I have checked everything that I can think of and it still keeps
splitting them up.

Maybe I digress, what I really just want to happen is to have the Date shown
with all the categories for that date underneath. And if the Across and Down
is the way it has to be, I want it to print as it sounds like its should like:

Sept 22, 2005 Sept 23, 2005
everthing that is related to it everything
that is related to it

I don't know, but it almost looks like your detail section
has its New Row or Col property set to Yes.

 

[Guest]

Good evening,

I am also still very confused. What I was attempting to do was place one of
many subreports on a Main Report in columnar order. In reading all the
threads it appeared that it was not possible to do it in the Down then Across
fashion, that in order to make it print properly, it had to be Across and
Down, so that's what I formatted it to. It would print but the categories
were in two columns. Though workable, it is not what I wanted as the date was
on the left side with the totals and the categories were all out of
alignment. But if you say that this is the way there are supposed to be
(categories in two columns), then how do you get then to be sized to be all
the same size groupings i.e Sept 22, 2005 has 10 Categories, 5 are printed on
the left, 6 on the right. Sept 23, 2005 only has 5 Categories so it scues
the next date.

Re your third para, I went back to the original layout of the report, there
are 9 subreports. I set the page format for the columar report back to 1
column and tried to set the whole parent to 2, still doesn't work.

Is there anywhere that you are aware of that talks about how to overcome
this type of challenge. I can live with the two columns as it is printing
out now but just wanted to know why they are not lining up, such as my
example, the first line of the Category is printing out on the right side of
the line the date is on. Is there any way to make the Categories line up on
the same lines?

I do appreciate you help, I am just really confused as to why I am unable to
find the solution to this challenge. It must be possible as there are alot
of posts related to it, just nothing that ties all the strings together. If
starting over is the option I am happy to do it just don't know what I may
have missed.

Regards and Thank you.
IEJ

I'm still confused. What you're describing as the objective
is Down then Across.

Across then Down prints each section next to each other so
the result you're seeing is to be expected, except the
category should also be in two columns.

Maybe what you need is to place the date and "everything
that is related to it" in a subreport that only has one
narrow column. Then the parent report can use two or more
columns Across then Down.
--
Marsh
MVP [MS Access]

No, no New Row or Col. This seems like it should be so simple but something
is wrong somewhere. It is a simple report that is run from a query, first by
date i.e. Sept 22, 2005 and then showing all the telephone categories entered
for that date with a Count in an unbound field. No Page Header or Report
Header anymore, I took them out to try to solve this.

I have the Date in a Date header, and the CategoryDescr in a CategoryDescr
Footer with a Count([CategoryDescr]). Results should be like shown
previously:
Date Header: Date ex. Sept 22, 2005
CategoryDescrFooter ex:
Booked 8
Cancelled 4
Concerned 6
etc. all down one side underneath the date.

Page Setup is set to 2 columns, Across and Down. The minute you view it, it
takes the Categories and splits them across two columns and puts the first
line in line with the date (even though you aren't asking it to split
anything). I have checked everything that I can think of and it still keeps
splitting them up.

Maybe I digress, what I really just want to happen is to have the Date shown
with all the categories for that date underneath. And if the Across and Down
is the way it has to be, I want it to print as it sounds like its should like:

Sept 22, 2005 Sept 23, 2005
everthing that is related to it everything
that is related to it

(e-mail address removed) wrote:
I have read through all threads dealing with this challenge and am still not
able to find the answer to what I need. I would appreciate any help soonest.

I have a Main Report with many subreports embedded, all run off a date field
selected i.e. 09/22/05-10/19/05 so can get quite lengthy. I have one
subreport that I would like to run in columns, really want it down and across
but understand that it has to be Across then Down to work it appears. Have
set subreport to that. Problem arises when viewing, it reports

Subreport is an output by date of number of calls, by category with a count
i.e.
Sept 22, 2005 8 (Group Heading is Date Call Came In, keep Group together)
Booked 2 (Group Header is Categoires, keep Group together)
Cancelled 1
Left Message 5
Total 8

What I am getting is:

Sept 22, 2005 8 Booked 2
Cancelled 1
Left Message 5
Total 8

Any idea why it is breaking the Category group up across the page instead of
keeping them all together as I want them. I can live with the Across and
Down as long as they will stay together but the way it is they don't make any
sense.


I don't know, but it almost looks like your detail section
has its New Row or Col property set to Yes.

 

[Marshall Barton]

I am also still very confused. What I was attempting to do was place one of
many subreports on a Main Report in columnar order. In reading all the
threads it appeared that it was not possible to do it in the Down then Across
fashion, that in order to make it print properly, it had to be Across and
Down, so that's what I formatted it to. It would print but the categories
were in two columns. Though workable, it is not what I wanted as the date was
on the left side with the totals and the categories were all out of
alignment. But if you say that this is the way there are supposed to be
(categories in two columns), then how do you get then to be sized to be all
the same size groupings i.e Sept 22, 2005 has 10 Categories, 5 are printed on
the left, 6 on the right. Sept 23, 2005 only has 5 Categories so it scues
the next date.

Re your third para, I went back to the original layout of the report, there
are 9 subreports. I set the page format for the columar report back to 1
column and tried to set the whole parent to 2, still doesn't work.

Is there anywhere that you are aware of that talks about how to overcome
this type of challenge. I can live with the two columns as it is printing
out now but just wanted to know why they are not lining up, such as my
example, the first line of the Category is printing out on the right side of
the line the date is on. Is there any way to make the Categories line up on
the same lines?

I do appreciate you help, I am just really confused as to why I am unable to
find the solution to this challenge. It must be possible as there are alot
of posts related to it, just nothing that ties all the strings together. If
starting over is the option I am happy to do it just don't know what I may
have missed.

I guess we're both confused. I still don't understand the
goal here. How about we back up and get the basics
straight.

Post the relevant fields in the Main report's record source
table/query. If the record source is a query, please post a
Copy/Paste of its SQL.

Same for the subreport.

Try to explain what data fields you want where in the
report. I see your example output, but I don't think I
followed where the data was coming from. I also think part
of my confusion is that I can't figure out the difference
between the output you are getting and the output you want
toi get. The compromises you've made in the snaking may be
getting in the way, so let's leave the down/across issues
out of the discussion for now and concentrate on getting a
clear picture of the ultimate objective. If compomises have
to be made, we'll get to them later.

 

[Guest]

Good morning, okay I will try to elaborate a bit more:
Main Report - unbound, no record source; is tied by a Start Date and End
Date field to a Print Report Dialogue Box where staff enter the date range
they want to run the report for. Each subreport is linked back to the same
Start Date and End Date.

Report is a Statistical Analysis of work completed by this specific period,
shown in graph format as well as text format. It is analysing No.of Calls
recorded coming in -- to no. of installations completed. The first page also
has cumulative year-to-date totals and graphs for comparative purposes as
well.

The Call Log Summary has a graph of no. of calls per day. Underneath that
is a breakdown report of all those calls, by category, by date. This is used
by the Marketing staff to see how what the breakdowns are. As the report had
get quite lengthy if it is run for a month period, I wanted to take this
report and save space by putting it into columns.

It is a query built on a table that records calls and a table that records
categories. The fields from tblCalls are Date, format is: dddd", "mmmm d",
"yyyy hh:nn ampm; and CategoryID, format: Number, Long Integer. CategoryID
is linked to tblCallCategories to field CateoryDescr, format is Text. Report
is built on this query sorted by Date in a Date Header and and the counting
the Category Descriptions for that date in a Category Description Header.
Result is:

Date: Sept 22, 2005
Category Count
Booked 10
Cancelled 11
Rescheduled 15
etc.

This subreport had been placed in the Detail section of the Main Report,
along with all the other subreports. The Main Report is set to 1 column,
same as detail.

I hope I have covered all the items I should have here.

Regards,

IEJ

 

[Marshall Barton]

Good morning, okay I will try to elaborate a bit more:
Main Report - unbound, no record source; is tied by a Start Date and End
Date field to a Print Report Dialogue Box where staff enter the date range
they want to run the report for. Each subreport is linked back to the same
Start Date and End Date.

Report is a Statistical Analysis of work completed by this specific period,
shown in graph format as well as text format. It is analysing No.of Calls
recorded coming in -- to no. of installations completed. The first page also
has cumulative year-to-date totals and graphs for comparative purposes as
well.

The Call Log Summary has a graph of no. of calls per day. Underneath that
is a breakdown report of all those calls, by category, by date. This is used
by the Marketing staff to see how what the breakdowns are. As the report had
get quite lengthy if it is run for a month period, I wanted to take this
report and save space by putting it into columns.

It is a query built on a table that records calls and a table that records
categories. The fields from tblCalls are Date, format is: dddd", "mmmm d",
"yyyy hh:nn ampm; and CategoryID, format: Number, Long Integer. CategoryID
is linked to tblCallCategories to field CateoryDescr, format is Text. Report
is built on this query sorted by Date in a Date Header and and the counting
the Category Descriptions for that date in a Category Description Header.
Result is:

Date: Sept 22, 2005
Category Count
Booked 10
Cancelled 11
Rescheduled 15
etc.

This subreport had been placed in the Detail section of the Main Report,
along with all the other subreports. The Main Report is set to 1 column,
same as detail.

That helps a lot. Unfortunately, I can't come up with a
straightforward way to get an acceptable result using a
subreport.

The two thoughts I do have is to use a function to build the
stats list as a single string. The subreport would then
have only the date in the subreport's record source and the
stats would be gathered by the function. This way the
entire date block of data would be a single detail and
Across then Down would be the way you want it.

The other thought is to do away with the subreport and put
it all in the main report. I don't know how you are
managing the main report and all the subreports, but I
suspect there is a way to do this.

 

[Guest]

Good evening,

Thankyou again for responding. Would you be able to give me an example. I
think if you explain how to do either method I probably will be able to do it
if I see an example of sorts.

Thankyou,
IEJ

 

[Marshall Barton]

I don't know enough about your main report to speculate how
you would do it all in the main report. However, since you
said it was unbound, I'm pretty sure there is a way. Try
moving the other subreports to the report header and/or
footer section and using the detail section (and probably a
group header/footer) instead of the problem subreport.

The other approach that keeps the subreport but removes the
stats records from the subreport's record source and relies
on a function that takes the date (and other fields???) to
retrieve the stats and construct a string. Here's some
vague air code for the function:

Function BuildStats(dtCallDate As Date) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strStats As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(" SELECT * FROM CatStatsQuery " _
& "WHERE DateField=" _
& Format(dtCallDate,"\#m\/d\/yyyy\#"))
Do Until rs.EOF
strStats = strStats & vbCrLf & rs!CatCount & _
" " & rs!CatDescr
Loop
BuildStats = Mid(strStats, 3)
End Function

The CatStatsQuery would be where you calculate the total
stats by category and date much like the old subreport's
record source query did.

The subreport can then use just two text boxes in its
details section, one bound to the date field and a second
one under the first with the expression:
=BuildStats(DateField)

Since the subreport no lomger has any grouping to get in the
way, you can now use the Across then Down columns (maybe 3
or 4 columns).