Wednesday, March 21, 2012
Dynamically supress drill-down enabled groups ?
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
Friday, March 9, 2012
Dynamically configuring Fuzzy Grouping
Hello,
I have been struggling with this for quite awhile so any help would be appreciated.
I need to know if there is away to populate the fuzzy grouping control dynamically. I know you programmatically design a package and customize it in C# but for our purposes we would like to control the SSIS package via database settings. When the settings change the package would then act different. Its a simple a package consisting of an Input - fuzzy grouping - conditional split - output. The connections are setup dynamically using parameters, expressions and a script task. Is there anyway I could do a similar thing for Fuzzy Grouping?
No, the FG component needs to know its columns up-front. Either you'll have to use a script component, or code the package from scratch.|||Thanks for getting back to me.
Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.
|||
Brat wrote:
Thanks for getting back to me.
Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.
No, none of the above. You'd have to perform the fuzzy grouping inside a script component. It is likely quite a bit of work.|||
One more question...
The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?
Thanks
|||
Brat wrote:
One more question...
The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?
Thanks
No. But see if these help you... (From the Transact-SQL forum)
http://forums.microsoft.com/MSDN/Search/Search.aspx?words=fuzzy&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=85|||thanks|||
I have tried with out success to modify the FG component using the Script Task. It seems that a package cannot modify itself at runtime (http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx). I haven't tested this theory yet, but as a work around I created a package that contains the source - FG - destination components and then created another package that contains a Script Task. The idea would be that the script task would reference and load the first package. I can do this like so:
Code Snippet
Dim application AsNew Application()
Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"
Dim pkg As Package = application.LoadPackage(pkgName, Nothing)
but how do I get a handle on the FG component from within the first package? Please help, this is getting extremely frustrating, thanks
|||Once the package is loaded, you need to iterate navigate from the package to the data flow that contains the FG. A package object implements the IDTSSequence interface, which has an Executables collection. Executables can contain other Executables (a sequence container, for example), so you may have to traverse multiple levels to get to your data flow.|||
Thank you for your response!
I have managed to get into the Data Flow Task by doing the following:
Code Snippet
PublicSub Main()
Dim application AsNew Application()
Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"
Dim pkg As Package = application.LoadPackage(pkgName, Nothing)
Dim pgkExecs As Executables = pkg.Executables
Dim exec As Executable
ForEach exec In pgkExecs
Dim th As TaskHost = CType(exec, TaskHost)
Dim i AsInteger = 0
Dim s AsString
While i < th.Properties.Count
s = th.Properties(i).Name
i = i + 1
EndWhile
Next
Dts.TaskResult = Dts.Results.Success
EndSub
The question I now wanted to know if you could help me with is this:
The th.Properties(0).Name is "[Dynamic Fuzzy Grouping].[MaxMemoryUsage]" which is my FG Control. Any idea how to access this control and now change the properties? I have tried several types of casts and I cant seem to get it right, any ideas would be appreciated. Thanks!
|||
You're close. The TaskHost object is the wrapper around the actual dataflow object. The properties you are seeing are the properties of the data flow's inner objects (but only the ones exposed). To get to the actual pipeline, you need to take one more step.
Code Snippet
Dim pipe as MainPipe = CType(taskHost.InnerObject, MainPipe)
Then you can use the ComponentMetaDataCollection property to access the components in the pipeline, which should include your Fuzzy Grouping.
|||Thank you! Thank you! Thank you!
I finally got it working! I see my problem was also that I was working with the control flow and not the data flow script task, once I changed over I was able to cast my innerobject to MainPipe, thank you so much for your help, I have been battling with this for two weeks now and even though the code itself is quite simple I couldn't seem to find any help about what to do, so thank you so much!
Dynamically Change the Grouping
I am trying to change the Grouping on the report based on which field a user
select. Basically I want to change the way report looks when the user select
a particular field as Group By option. Can any one help me with is.
For Example:
A Report has ClientName, Address, State, City. On the Report, I set Grouping
Property to "ClientName". I want to pass in a parameter that will change
the Grouping Expression to "State" and show the result.
I really need help on this. Can you provide me with an example?
Thank you
Darshan1) Create a new Parameter and call it GroupBy.
2) Since you only have two values to group by, just hard code your
selectable values into this parameter. i.e. ClientName and State
3) Edit your grouping. Instead of using =Fields!ClientName.Value for your
hardcoded group value, refer to the newly created parameter.
To do so, in the group property screen select <Expression...>. Next select
the newly created parameter on the left box, and insert it into the right
box. Or manually type the new parameter in the expression box. i.e.
=Parameters!GroupBy.Value
Now it you have dynamic grouping. Hope that helps - Holler if I was unclear.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Ok, ignore my first instructions. /shakes off the rust...
Here's how you do it.
1) Create a Parameter call it GroupBy
2) Add two new static values
3) Set the first Label = "ClientName", set its Value to 0
4) Set the second Label = "State", set its Value to 1
Now in your group by expression you have to build a conditional expression:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName,Fields!State.Value)
which translates to If GroupBy equals zero, then group by ClientName field,
else group by State field.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||had a typo in my sample expression, it should be:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName.Value,Fields!State.Value)
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Thank you for the example. I have another issue on the same topic:
I have multiple fields then hard coding the value may not be the solution.
Is there any other way to achieve the grouping.
Basically, the user can pick any one field from the list that they can group
by. Can I code a vb function on the report that would allow me to pass the
value.
Example
Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
as String
lsDir = "Fields!" + GroupByStr +".Value"
GroupByClause = lsDir
End Function
Let me know.
Again thank you for all the help in advance.
"RS_CZAR" <ichijoe@.hotmail.com> wrote in message
news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> had a typo in my sample expression, it should be:
> =iif(Parameters!GroupBy.Value => 0,Fields!ClientName.Value,Fields!State.Value)
>
> "Darshan" wrote:
>> Hi!
>> I am trying to change the Grouping on the report based on which field a
>> user
>> select. Basically I want to change the way report looks when the user
>> select
>> a particular field as Group By option. Can any one help me with is.
>> For Example:
>> A Report has ClientName, Address, State, City. On the Report, I set
>> Grouping
>> Property to "ClientName". I want to pass in a parameter that will change
>> the Grouping Expression to "State" and show the result.
>> I really need help on this. Can you provide me with an example?
>> Thank you
>> Darshan
>>|||Try using the Fields() collection passing in the indexer of your fieldname as
is shown here:
http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx
Let me know if you get this to work...I am using SSRS 2005 and cannot get it
to work if I don't specify a grouping...
"Darshan" wrote:
> Thank you for the example. I have another issue on the same topic:
> I have multiple fields then hard coding the value may not be the solution.
> Is there any other way to achieve the grouping.
> Basically, the user can pick any one field from the list that they can group
> by. Can I code a vb function on the report that would allow me to pass the
> value.
> Example
>
> Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
> as String
>
> lsDir = "Fields!" + GroupByStr +".Value"
>
> GroupByClause = lsDir
> End Function
> Let me know.
>
> Again thank you for all the help in advance.
> "RS_CZAR" <ichijoe@.hotmail.com> wrote in message
> news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> > had a typo in my sample expression, it should be:
> >
> > =iif(Parameters!GroupBy.Value => > 0,Fields!ClientName.Value,Fields!State.Value)
> >
> >
> > "Darshan" wrote:
> >
> >> Hi!
> >>
> >> I am trying to change the Grouping on the report based on which field a
> >> user
> >> select. Basically I want to change the way report looks when the user
> >> select
> >> a particular field as Group By option. Can any one help me with is.
> >>
> >> For Example:
> >>
> >> A Report has ClientName, Address, State, City. On the Report, I set
> >> Grouping
> >> Property to "ClientName". I want to pass in a parameter that will change
> >> the Grouping Expression to "State" and show the result.
> >>
> >> I really need help on this. Can you provide me with an example?
> >>
> >> Thank you
> >> Darshan
> >>
> >>
> >>
>
>
Dynamically change report grouping
differently without us having to create them a new report with their
required group options.
Is there a way perhaps to hard code maybe 1 or 2 group levels in a reports
and then turn them off or turn them on dynamically depending on how the user
wanted to view the results.
Any help is appreciated.We use a 'Group By' parameter that the stored proc uses to do the
grouping. The results come back in the same format each time of
course, they're just grouped differently. This can sometimes lead to
some icky VB.NET conditionals inlined into the RDL, but in general it
has worked very well.|||Alternatively, group on an expression that is based on the value of a
parameter.
In our case, the client's requirements needed 6 reports but this
technique allowed us to give them one.
The stored proc must return a result set at a grouping level just above
the two alternative groupings.
BrianK
www.bolign.com
Friday, February 24, 2012
Dynamic Summing
Cat, New and Current). I want to sum a field on that level, but only if the
grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
whichever works. So something like this, but this doesn't work:
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))I think it should be something like this. (You don't need the extra sum in
your formula)
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Fields!CurrMthSales.Value,0))
"Chris Patten" wrote:
> I have a 4th level grouping that has just 3 different values to group on (No
> Cat, New and Current). I want to sum a field on that level, but only if the
> grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
> whichever works. So something like this, but this doesn't work:
> =SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))
>