Hide empty rows on drill down report

Is there any way to hide the expand button (the plus sign) for rows that have
no containing rows on a drill-down report? What is happening now is that the
plus signs are populated for all rows and only a small subset of them
actually have data in the to drill into.Jason,
You can go into the toggle item's Properties and under the Visibility
Tab, set the "Initial appearance of the toggle image for this report
item:" to Expression and use a variation of the following:
=IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
This makes it so that if my next rows Item_Name field is blank, the
toggle will be automatically set the the - symbol so that the user
knows there is nothing past this point.
Hope that helps. If I didn't make myself understandable, let me know
and I can try again.|||That's pretty good. Is there any way to make it so that the "-" symbol cannot
be expanded? It looks weird to have a "-" symbol change to a "+" symbol and
then expand with an empty line under it.
"Mal" wrote:
> Jason,
> You can go into the toggle item's Properties and under the Visibility
> Tab, set the "Initial appearance of the toggle image for this report
> item:" to Expression and use a variation of the following:
> =IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
> for example:
> =IIf(Fields!Item_Name.Value = "",true,false)
> This makes it so that if my next rows Item_Name field is blank, the
> toggle will be automatically set the the - symbol so that the user
> knows there is nothing past this point.
> Hope that helps. If I didn't make myself understandable, let me know
> and I can try again.
>|||Jason,
To remove the additional row, you will need to set the Hidden value, of
the Visibility properties for the row, to the same as what is in the
toggle item's Initial Appearance value.
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
And here is an example of a 6 level report, that may or may not have
data at all levels:
=IIf(Fields!lev1_Name.Value="",IIf(Fields!lev2_Name.Value="",IIf(Fields!lev3_Name.Value="",IIf(Fields!lev4_Name.Value="",IIf(Fields!lev5_Name.Value
= "",IIf(Fields!lev6_Name.Value ="",true,false),false),false),false),false),false)
This would be inserted into the Hidden property of Group 1 in said
report. And would need to be placed in that property value for each of
the following groups, removing the previous row from the equation each
time. So for next row, the expression would start at lev2_Name, and the
Group 3 row would start at lev3_name, etc.
Does that help?|||Jason,
You will need to place an Expression into the Hidden field for the
Row's Visibility Properties. If you are using the above Expression on
the Initial Apperance, the Expression for the Hidden property would be
the same.
So, if it is a 3 level report and level 2 is a toggle for level 3, the
Expression would go in the toggle item on row 2 and in the Hidden
Property for Row3.

Hidden Subreport Executing Queries

I'm currently developing a report in which the customer has requested a
drill-down type of interface that starts at a regional level to a
district level to a territory level and finally to the individual
customers within the territory. The drill-down interface was easy to
create by using Groups in conjunction with the ToggleItem and
Visibility.Hidden properties at the different Grouping levels.
The problem I have run into is that there are around 1 Million
customers across the country and the report was attempting to retrieve
all of the records at all levels at once. This turns out to be
unworkable from a performance standpoint.
To try to work around this I proposed using drill-through at the
territory level to open a new report with that territories customer
list. The customer was unwilling to accept that approach since they
wanted to keep the flexibility of navigating through the tree without
the disruption of opening a separate report.
Next I tried to work around the problem by implementing a subreport at
the customer level thinking that the subreport would only execute once
the user drilled down into the territory level. Following is a general
overview of this solution:
+ North East
- South East
- Georgia
- Atlanta
- subreport displays here with territory customer list
+ Savannah
+ Athens
+ Florida
+ Kentucky
- Midwest
+ Kansas
+ Iowa
+ Minnesota
+ West
By using SQL Profiler I found that the report was actually executing
the query for each individual territory's corresponding subreport
even though those levels were hidden. This was essentially the same
scenario that I had with my initial attempt.
To try to work around this I created a parameter within the subreport
called "show_subreport" and if that parameter is set to 1 the dataset
will return the customer list and if it is set to 0 the dataset will
not return any rows. By manually setting the parameter to 0 through
the parameter mapping in the parent report this worked well. However,
I have not been able to figure out how to dynamically change that
parameter by using an expression. I have been trying to set the
parameter expression to something like:
= IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
However, I have not been able to get to that property or any other
property which would be relevant to the "show_subreport" parameter.
This seems like it would be a very common type of report request so I
assume there is a way to do this. Any help would be appreciated.I've got the same problem.
--
/Mattias
"fred_mumble@.yahoo.com" wrote:
> I'm currently developing a report in which the customer has requested a
> drill-down type of interface that starts at a regional level to a
> district level to a territory level and finally to the individual
> customers within the territory. The drill-down interface was easy to
> create by using Groups in conjunction with the ToggleItem and
> Visibility.Hidden properties at the different Grouping levels.
> The problem I have run into is that there are around 1 Million
> customers across the country and the report was attempting to retrieve
> all of the records at all levels at once. This turns out to be
> unworkable from a performance standpoint.
> To try to work around this I proposed using drill-through at the
> territory level to open a new report with that territories customer
> list. The customer was unwilling to accept that approach since they
> wanted to keep the flexibility of navigating through the tree without
> the disruption of opening a separate report.
> Next I tried to work around the problem by implementing a subreport at
> the customer level thinking that the subreport would only execute once
> the user drilled down into the territory level. Following is a general
> overview of this solution:
> + North East
> - South East
> - Georgia
> - Atlanta
> - subreport displays here with territory customer list
> + Savannah
> + Athens
> + Florida
> + Kentucky
> - Midwest
> + Kansas
> + Iowa
> + Minnesota
> + West
> By using SQL Profiler I found that the report was actually executing
> the query for each individual territory's corresponding subreport
> even though those levels were hidden. This was essentially the same
> scenario that I had with my initial attempt.
> To try to work around this I created a parameter within the subreport
> called "show_subreport" and if that parameter is set to 1 the dataset
> will return the customer list and if it is set to 0 the dataset will
> not return any rows. By manually setting the parameter to 0 through
> the parameter mapping in the parent report this worked well. However,
> I have not been able to figure out how to dynamically change that
> parameter by using an expression. I have been trying to set the
> parameter expression to something like:
> = IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
> However, I have not been able to get to that property or any other
> property which would be relevant to the "show_subreport" parameter.
> This seems like it would be a very common type of report request so I
> assume there is a way to do this. Any help would be appreciated.
>sql