Dynamic Subtotal (1 Viewer)

[tmyers]

I believe I have touched on this before, but am curious if someone can possibly provide an example.

On my report, I want to have "dynamic" subtotals. I have messed with grouping, but that is an all or none deal. An example, say I have 3 groups. I want a subtotal for group 1 and 3 but not 2. How would I accomplish that? Would I have to use subreports?

 

[Ranman256]

groups show everything in the query.
make another group BATCH (or other name)in the query that combines your groups 1 & 3., but not 2
batch, group, field1, ...
ODD, 1, ...
ODD, 3, ...
even, 2, ....

then sub total that.

 

[tmyers]

How could I do that one the fly however? Give the user the ability to select which groups to show a subtotal for?

 

[arnelgp]

you can also use IIF() function.
on a textbox, add this formula:

=Sum(IIF([groupField]="Group1" Or [groupField] = "Group2", [FieldToSum], Null))

 

[MajP]

How could I do that one the fly however?

Can you provide a screenshot of an actual db? I have never tried, but my first guess would be something like, have a form with a multiselect listbox filled dynamically. The use then selects the desired groups. This builds a sting that is passed into the report through openargs. In the on paint event determine what groups get sub totals painted. This seems doable, but really convoluted. An example would help.

 

[tmyers]

Can you provide a screenshot of an actual db? I have never tried, but my first guess would be something like, have a form with a multiselect listbox filled dynamically. The use then selects the desired groups. This builds a sting that is passed into the report through openargs. In the on paint event determine what groups get sub totals painted. This seems doable, but really convoluted. An example would help.

Im not too sure what to actually screen shot. I had initially tried setting this up using grouping, so I have a field [Breakout] that is just numerical. In a nutshell I could turn on subtotals then make the group footer have an on click event to hide them if they aren't needed. The problem I foresee is like I stated above with a situation where a person may need subtotals on only a select set of groups, and not all of them.

 

[MajP]

Im not too sure what to actually screen shot.

can you show the report in design view and regular view and show what you might want to hide or show? Even better would be some stripped down database with this report if possible. I have never tried something like this, so I would have to give it a try to figure out the approach to doing it.

When a report is made it paints each record and each section. You can use this event to do things like hide something based on value. You can insert extra lines in a report, hide records or repeat records. So that is my guess of how I would do it, but I would have to see something to try to demo it.

 

[MajP]

This kind of sort of works. You can click on a sub total if the report is in report view. It will allow you to hide or unhide the subtotal. I left the Group name in the footer for demo purposes, but you could "hide" that too. Unfortunately you cannot really hide it, so I make it appear to be hidden. This will leave the space where the footer is.

In print preview you can really hide it, however, there is no way to interact with the report. You would need a form prior to opening the report to tell it what categories to hide. This is because the print event for the footer has a cancel so if you passed in to the report the categories to hide you could actually cancel the print of the footer for that category.

I scrambled the cost and other data in case that is proprietary.

 

[tmyers]

You went with the similar method I had attempted at one point. Click the section to set its visibility to no. You did it better however in that it doesn't hide everything lol.

I will mess with this and see what I can come up with.
Thanks for your help!

 

[MajP]

You went with the similar method I had attempted at one point. Click the section to set its visibility to no. You did it better however in that it doesn't hide everything lol.

No. this is not even close to what you attempted. What you tried had no chance of working. One you cannot set the visibility of a report section once painted, and two even if you could it would hide each section.

The concept is actually pretty complicated.

1. when you click on a selection to hide it adds it to a collection or removes it from a collection.

The report constantly repaints as you scroll or move around the report. It repaints row by row section by section.
2. In the repaint event of the footer I read the current category and search the collection to determine if that category is in the collection. If in the collection it gets "hidden" if not it gets "shown".
3. You cannot set the visibility or change size of controls, so the only property available is the color of the controls. If the category is in the collection it sets the font to white (making it appear hidden) if not it sets the font to black

If you want to do this in print preview you can do better, but you would need a way to identify what section to hide prior to opening the report. In the print preview you cannot set visibility but you can choose not to "print" a record or section.

 

[tmyers]

I just played with the report and made the incorrect assumption. Looking at the code now, you weren't kidding. It is nothing like what I had initially attempted and is pretty dang complex. I wouldn't have been able to get to that on my own.

I do have a form that preempts the opening of the report that I can try and build what you suggested into in regards to predefining what categories to hdie.

 

[MajP]

I do have a form that preempts the opening of the report that I can try and build what you suggested into in regards to predefining what categories to hide

On the form you probably need a multiselect listbox to choose the categories you want to hide. Then prior to opening you loop the selections and build a string someething like
1;3;19 (things to hide)

Pass that in openArgs

Now in the report you can build a function that reads the string to determine if the current category is in the string. Then you can cancel the Print of that footer.

Look at this example of how to loop a ms listbox to create a string.

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

If you provide the form I can help with the code. It would actually be much simpler than this version, but you can have both code in the report. It can be dynamic like this for report view and done by a leading form for print view.

 

[tmyers]

If you still have the one I initially sent, the form "ClarificationForm" is the one that preempts the opening of the report. That particular form is what I used to populate a sub-report within that report.

 

[MajP]

Quick demo with a hardwired example. My thought is that they may not know what they want to hide until seeing the report. In that case you could put the listbox on the report and show it in reportview. Then they can see and select what to hide instead of the click event in the footer. Then from there open it in print preview and also hide it.