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!
Friday, February 17, 2012
Dynamic SQL statement
help appreciated.
Declare @.sqlStmt nvarchar(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 240
Set @.Range = 36000
--EXEC --('insert Into ' + @.TempVS +
SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '
EXEC @.sqlstmt
I get an error :
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'Select Distinct patNo, DateDrawn,
Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '.
Thanks for the help,
Stephen K. MiyasatoDon't call Exec. Use sp_execsql instead. Look it up in books online for
examples, but you're about 90% of the way to implementing it.
Regards
Colin Dawson
www.cjdawson.com
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>|||Thanks for the help.
Stephen K. Miyasato
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:oH47g.65228$wl.53809@.text.news.blueyonder.co.uk...
> Don't call Exec. Use sp_execsql instead. Look it up in books online for
> examples, but you're about 90% of the way to implementing it.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
>|||Just adding to Colin's comment.
don't use nvarchar unless u are sure about unicode character. use varchar.
and
for executing dynamic sql
use
EXEC(@.sqlstmt)
again. This is just for the syntax.
Use sp_executesql. Its better in code reuse and does the same job from a
developers standpoint.
--
"Stephen K. Miyasato" wrote:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
You need parentheses after EXEC to execute dynamic SQL. Now you are
trying to execute a stored procedure of which the name is in @.sqlstmt.
Again, I recommend you to read my article
http://www.sommarskog.se/dynamic_sql.html. That will give you the
answer to this question, it will also sort out some problems that
you have run into yet. And you may even find out that you don't need
dynamic SQL at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx