Grouping on Multiple Fields based on Parameter

[rose4567]

Hello all,

I am trying to group my report by either
Department
or
Department AND Location.

Group Header 1: = Department (crosstab here)
Group Header 2: = Location (crosstab here)

I have a parameter that asks the user to choose the level of grouping. "Either Department" or "Department & Location."
Where Department is selected, I need ONLY Group Header 1 to display with crosstab.
Where Department and Location selected, I need BOTH Group 1 and Group 2 to display in tree hierarchy because each section contains a crosstab with different sets of data.

I have supression set in the Section Expert on the Group 2 (Location grouping level) "Supress (No drill-down)" and the formula for suppression is:
{?grouping} = False
LOGIC: False is set to Department, so this SHOULD suppress all locations.

The group hierarchy tree, however, STILL displays when I select group by department only. It displays the locations. If you click on them you only get Department data, but is there a way to suppress Group 2 entirely?

I thought by checking "Suppress (No drill-down)" that I would only get group 1 results and group 2 would not display at all, but that does not seem to be the case.

Thank you all in advance for your help!

 

[rose4567]

Here is what all the sections look like with Supress No Drill-Down turned OFF so you can see how each section is broken out:

Here is the detail behind section expert to show suppression:

Here (with Supress No Drill-down turned ON) the result I get when I select "Department" only:

 

[lbass]

Try inserting your group #1 on Department, and group on a formula for group #2:

if {?group} = "Department" then
"" else
{table.location}

Use a suppression formula of:

{?group} = "Department" on the group #2 header

...assuming that {?group} is set up with options "Department" and "Department and Location".

-LB

 

[rose4567]

This works!

However, for some reason now I can't get the pages to break between Group 1, Group 2 and Footer. It all runs together despite applying page breaks in the section expert.

Also, when user chooses Dept & Location I want the department level to diplay once (at top level) then just the locations. Perhaps it's a page break issue, but the Department displays above each location.

I tried suppressing using:
{?Location} = True and
RecordNumber >1
but that only works for the first page.

 

[lbass]

I don't know what {?location} refers to. Go to the group expert and make sure that department is your group #1, {@myformula} is group #2. I can't tell where you mean to have page breaks either. Ordinarily, you would add them after the group#1 footer and after the group#2 footer.

Also check to make sure your group names are in the appropriate group sections.

-LB

 

[rose4567]

Thanks for the input, LB!

I got the page breaks working, but the last piece of the puzzle is some conditional suppression on group header 1.

When multiple levels of grouping are applied, Group 1 displays on every page above Group 2.
Department (Group 1) does display once, on it's own page, before all the locations (Group 2). But then for every location, Group 1 still prints at the top of the page.
(SEE IMAGE)

Is there a way to have Group 1 show up once at the top of each group, then suppress Group 1 from printing above every record in Group 2?

I tried suppressing with:
{?Location} = True and
PageNumber >1
but of course that doesn't work where there are multiple pages.