Dynamic report

[Guest]

Hello -

I have a summary report that needs to be done dynamically, due to the fact
that the users are able to add and delete categories in a field. In this
field, Category, there are usually 8 or 9 category types; each of which
produces a sum for each category type on a report. An example would be
Category Fruit, apples 10, oranges 3, limes 4, etc. People with
administrative authorization are able to delete the category apples and add
category bananas, etc. These category types could be as many as 15.

The summary report I currently have has labels for each current “fruit,â€
along with a textbox that shows the sum.

Because of the fact that these will change, I somehow have to be able to
dynamically add these things to the report as opposed to using static labels.
What’s the best way to go about this? Is there a link somewhere that
addresses this type of situation?

Any help will be greatly appreciated!

 

[Duane Hookom]

It isn't clear why you don't use text boxes to display the category and
quantity. Where are your totals and how are they calculated? Doesn't your
report's record source look something like:

SELECT Category, Count(*) as NumOf
FROM tblYourTable
GROUP BY Category;

 

[Doctor M$]

Hi S.

You can create a table:

(NameCategory, NumberOfFruit)

This table is the datasource of the report so you can dinamically load the
name of the category (by choosing the first field in the table) and then the
number of the fruits.

I hope that I understand rightly (I'm italian)

Doc.

 

[Guest]

Hi Duane -

Thanks for your reply. I tried to make the example I gave simple, but the
real-life situation query the report is based on is:

SELECT tblCalls.Office, tblCalls.Agent, tblCalls.CallerName, tblCalls.Phone,
tblCalls.CallDate, tblCalls.LoanType, tblCalls.NumbApps, tblCalls.Comment,
tblCalls.Resolved, tblCallType.CallType, tblCalls.CallTypeID,
tblDeclaration.Declaration, tblCalls.DeclarationID
FROM tblDeclaration INNER JOIN (tblCallType INNER JOIN tblCalls ON
tblCallType.CallTypeID=tblCalls.CallTypeID) ON
tblDeclaration.DeclarationID=tblCalls.DeclarationID
WHERE (((tblCalls.CallDate) Between Forms!frmSummaryAndDetail.dtpFromDate
And Forms!frmSummaryAndDetail.dtpToDate))
ORDER BY tblCalls.CallDate;

They want all of these fields in the Detail report and then a Summary page
at the end that totals the different CallTypes by group and Declarations by
group. Also needed is LoanType (totals by group) and resolved (totals by
group).

The only way I could figure out to get all of this in one report was to do
the summary report using explicit labels and "hard code" the textboxes in.
It works great, except now that they have the ability to add and delete
items, those labels and ID numbers are going to be changing all of the time.

Any help will be greatly appreciated!

 

[Guest]

Hi Doctor M$ -

Thanks very much for responding. Actually, the real-life situation is more
complicated.

Please see my response to Duane above.

Any help will be appreciated!

 

[Duane Hookom]

You still didn't tell us much about your "hard code" text boxes. I was
hoping you would share the control sources.

I think I would create a subreport based on a totals query for each of the
fields you want to total by. Then add these subreports to the Report Footer
section of your Main Report.

 

[Guest]

Duane -

The way I "hard coded" it was I put all of the categories into the report
footer and for each item in the category in the footer, I put the actual item
name in a label. I had an adjacent textbox that had the Sum
=Sum(IIf([DeclarationID]=6,1,0))
for example. The "6" here would be the item and it was assigned "1" so that
Sum would work. It was a contrived method, obviously, but it worked, as long
as the items didn't change.

Don't know why my brain wasn't thinking in terms of subreports. Thank you
for solving my problem.