Custom Grouping Problem

I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:

Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select

If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function

Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function

Does anyone have any ideas on why the grouping would not be working with the custom function?

Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.

Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function

Now is ...

Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function

Similar changes in Function OrganisationGroup()

Custom Grouping Problem

I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:

Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select

If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function

Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function

Does anyone have any ideas on why the grouping would not be working with the custom function?

Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.

Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function

Now is ...

Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function

Similar changes in Function OrganisationGroup()

Custom Data Provider Extension : Request for the permission of typ

I have developed my Custom Data Provider extension by using the ample
provided by Micorosft Reporting Service.
In my extension I am using another thirdpary dll
UltEik.dll
to fetch informtaion from Ultimus Soaftware.
My report is working fine in Designer but when I am deploying it to
ReportServer then I am continuously getting this error
Request for the permission of type
System.Security.Permissions.SecurityPermission, mscorlib, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed
I have added UltEIK.dll into GAC but but still failing.
I have added the following code group
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="FSICodeGroup"
Description="Code group for my FSI data processing extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="E:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\Microsoft.Samples.ReportingServices.FsiDataExtension.dll"/>
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="E:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\Microsoft.Samples.ReportingServices.UltEik.dll"/>
I have copied the UltEik.dll in bin directory of ReportServer too but no
gain :(
but still getting the same error.
Please help me. It is very urent!
Thanks in advance,
Namwar />If you have installed UltEIK.dll into the GAC, the membership condition
should read either:
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$Gac$/UltEik.dll" />
or (if the filename is actually
Microsoft.Samples.ReportingServices.UltEik.dll):
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$Gac$/Microsoft.Samples.ReportingServices.UltEik.dll" />
"Namwar" wrote:
> I have developed my Custom Data Provider extension by using the ample
> provided by Micorosft Reporting Service.
> In my extension I am using another thirdpary dll
> UltEik.dll
> to fetch informtaion from Ultimus Soaftware.
> My report is working fine in Designer but when I am deploying it to
> ReportServer then I am continuously getting this error
> Request for the permission of type
> System.Security.Permissions.SecurityPermission, mscorlib, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed
> I have added UltEIK.dll into GAC but but still failing.
> I have added the following code group
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="FSICodeGroup"
> Description="Code group for my FSI data processing extension">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="E:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin\Microsoft.Samples.ReportingServices.FsiDataExtension.dll"/>
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="E:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin\Microsoft.Samples.ReportingServices.UltEik.dll"/>
> I have copied the UltEik.dll in bin directory of ReportServer too but no
> gain :(
> but still getting the same error.
>
> Please help me. It is very urent!
> Thanks in advance,
> Namwar />

Custom Connection Manager or SSIS Provided one?

Hi,

I have been working with a client on a legacy application migration to SSIS and I found SSIS to be very helpful, as this is my first project using integration services I found myself in a situation where multiple solutions can work but I cannot assess the risk of using one or the other and the effort required for the implementation.

The situation is as following, we cannot touch the input files that come from the other legacy systems so I need to get information that is packaged following the next multipart schema:

&Header with context information from the source
&BatchHeader with context information from the batch (including format N)
- Data in format N
&BatchHeader with context information from the batch (including format K)
+Data in format K
&BatchHeader with context information from the batch (including format P)
=Data in format P

Etc... Needless to say that they can come in whatever order and they need different processing depending on the format. I have been tempted to use the techniq shown in "Handling Different Row Types In The Same File" from http://www.sqlis.com/default.aspx?54.

However, there are multiple issues that I have to resolve to do that: First is how to feed the pipeline with the Data in format N and then reparse them in an easy way cause the data that comes from the other system is very wide (aprox 120 columns), so using a parsing script is by no means a viable solution (I have multiple formats to handle). So I do not see a way to treat the data without using a Custom Connection Manager. Here comes my first question: Is there anyway someone can use the already provided managers to handle an scenario like this one?

Next comes the separation, which one is the wisest thing to do: Write again that to disk in as easier format or pass along that information to the next task in memory? The volume of data is in the order of hundreds of megabytes (100 to 200), maybe half a Gigabyte in the extreme cases. How about uploading that to SQL Server and work from there?

BTW, the production servers will have 6 to 8 Gb of memory and 4 to 8 processors (more or less).

Thanks in advance.

Federico

Hi, I am not sure I understand your questions.

>>>> First is how to feed the pipeline with the Data in format N and then reparse them

The concept show in the article loads the whole row as 1 column to get the data into the pipeline and then you make decissions and parse from there. The article shows using script components to parse but you could also just use a derived column transform and the expression syntax, it all depends on the complexity of splitting your file. My guess is the trick in your case is identification of the rows. Is there something that can identify each row?

File header (multiple rows?)
K_BatchHeader (multiple rows?)
K_rows
P_BatchHeader (Multiple Rows?)
P_rows
...
...

Or is the only way you know K rows for sure is they follow the KBatchHEader and precede some other header?
Could be a multi step approach is best. One to "Split" parse to the source files to individual file formats (k,p,N) and another to read those files and do the actual data processing. This way, the 2nd process can likely use the built in parsing features.

|||

What I can suggest is an approach.

Creating a custom connection manager won't do any good here because connection managers are agnostic to the Dataflow task. What you need is a custom source adapter...

Or, in certain cases you can use the flat file adapter to read in the file row by row and use a script transform to do the parsing.

The best thing to do is to create a custom source adapter. You'll have much more control and the solution will ultimately be more robust.

K

|||although a custom source component will do the trick, a script component may suffice.|||

Thanks Craig, you understood correctly :)

In fact the solution you propose is what I am going to implement because I could be able to get a sample of the format (what I had until this point was the functional extraction of the legacy Cobol programs) and the rows format is not even normalized. So the problem now reduces to Normalize the file format (where I can split it with a custom task) and then do the Staging on the database and further processing.

So in fact I am moving toward your solution ;)

Again, thanks for the help.

Greetings
Federico Andrs Lois

|||

Yeah I used the wrong name, I meant a source adapter :)

As I wrote to Craig, I am moving toward a prenormalization and splitting in a task just because I found the data wasnt as clean as I though (regarding the format) so it will be required a normalization step before that.

Thanks too for the response.

Greetings
Federico Andrs Lois