Dynamically supress drill-down enabled groups ?

Hi,
We are trying to find if it is possible to customize grouping with
drill-down in a way as explained below. Any help is appreciated. Thanks.
The structure of the data is such that a group can contain subgroups which
in turn contain detail rows. However, in some cases
a group does not have subgroups (actually has one subgroup which represents
group itself). In our table-based reports this is defined as :
Col 1 Col2 Col3 ...
---
Group | | | (group by
Fields!group)
---
Subgroup | | | (group by
Fields!subgroup)
---
Detail | | |
---
Oupt of this report is as follows:
Figure 1 :
--
+ Group 1
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
+ Group 2
+ SubGroup 1
Detail 1
+ Subgroup 2
Detail 1
Detail 2
...
Both group and subgroup feature toggle item that hides or shows their
children rows. However, as explained above sometimes a group may not have
subgroups. In that case query returns one subgroup which has the same name as
the main group.
In example below Group1 only has one subgroup. That subgroup represents
actually the main group itself. Thus it is redundant.
Figure 2:
--
+ Group 1
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
In the case as shown above we would like to have something like:
Figure 3
--
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
Note that subgroup of Group1 has been supressed in the example above. The
details that were in subgroup of Group 1 were moved up the hierarchy and now
are shown as part of the main group. We can find out if the subgroups need
to be supressed by using CountDistinct for example. However we do not know of
way to supress the subgroups like in example above once we have the
information that it should be supressed. Again, please note that we would
like to use drilldown feature and toggle-item functionality with this.The current feature set of the matrix region doesn't include the
functionality you are asking for. Instead, I will try to massage the data at
the data store to give me the hierarchy I need.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Vedad" <Vedad@.discussions.microsoft.com> wrote in message
news:8E2B91AE-854E-40C0-9964-41127BBF08A4@.microsoft.com...
> Hi,
> We are trying to find if it is possible to customize grouping with
> drill-down in a way as explained below. Any help is appreciated. Thanks.
> The structure of the data is such that a group can contain subgroups which
> in turn contain detail rows. However, in some cases
> a group does not have subgroups (actually has one subgroup which
represents
> group itself). In our table-based reports this is defined as :
> Col 1 Col2 Col3 ...
> ---
> Group | | | (group by
> Fields!group)
> ---
> Subgroup | | | (group by
> Fields!subgroup)
> ---
> Detail | | |
> ---
>
> Oupt of this report is as follows:
>
> Figure 1 :
> --
> + Group 1
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> + Group 2
> + SubGroup 1
> Detail 1
>
> + Subgroup 2
> Detail 1
> Detail 2
> ...
> Both group and subgroup feature toggle item that hides or shows their
> children rows. However, as explained above sometimes a group may not have
> subgroups. In that case query returns one subgroup which has the same name
as
> the main group.
> In example below Group1 only has one subgroup. That subgroup represents
> actually the main group itself. Thus it is redundant.
>
> Figure 2:
> --
> + Group 1
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> In the case as shown above we would like to have something like:
> Figure 3
> --
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> Note that subgroup of Group1 has been supressed in the example above. The
> details that were in subgroup of Group 1 were moved up the hierarchy and
now
> are shown as part of the main group. We can find out if the subgroups
need
> to be supressed by using CountDistinct for example. However we do not know
of
> way to supress the subgroups like in example above once we have the
> information that it should be supressed. Again, please note that we would
> like to use drilldown feature and toggle-item functionality with this.sql

Dynamically determining the SQL statement?

Hello all,

I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.

Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.

With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).

Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).

BTW...currently using SSRS 2005 + ASP.NET 2.0.

Thanks for your help!!

-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>

Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...

I guess the *best* way to set the command text and datasource from the front end is the use parameters?

Are there any other possible solutions?

Thanks!

-Brian