Reference Custom Code from Query? Or better option?

[MrsMope987]

I'm using SSRS 05 to report on a progress database via ODBC connection through MS Access. There is a field in the dataset I'm using that is formatted like this (1;1;1;1;1;1;1;1). Each value is a month worth of sales, I need to split this field for viewing in a matrix control. I've got a small VBA function I wrote in MSAccess that works fine but I don't know how to use this same function in SSRS. I've added the function to the custom code in SSRS but I'm not able to reference it in the query; I should I don't know how to reference it in the query. I've tried to reference it from within the matrix control but continue to get errors:

Function:

Public Function getElem(str as String, delim As String, N As Integer)

Dim myArr as Array
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = Trim(myArr(N - 1))
Return Cdbl(getElem)
End If
End Function

[red]matrix cell contents (trying to pull July's data)[/red]

=Code.getElem(First(Fields!peramt.Value, "dsIncomeStmt"),";",7)

 

[jymm]

have you thought about breaking things up in the sql?

/* I know you are getting it ODBC, but this will get you there */
Declare @Sales nvarchar(1000)
set @Sales = '1;2;3;4'

select Replace(@Sales, ';', '.')

Select 
PARSENAME(replace(@sales,';','.'),4) as MonthSales1
,PARSENAME(replace(@sales,';','.'),3) as MonthSales2
,PARSENAME(replace(@sales,';','.'),2) as MonthSales3
,PARSENAME(replace(@sales,';','.'),1) as MonthSales4

Parsename works backwords from my thinking, but that could just be me... so I am replacing the ; with a . in order to make parsename work...

this help or hinder?

 

[MrsMope987]

I'll try it. thanks

 

[MrsMope987]

Does the PARSENAME have a limit of 4 fields though? I have 13 I need to split out (each month and one for end of the year adjustments).

 

[jymm]

I have never tried more than 4 - I actually use it for other reasons (getting fully quilified domain parts and I use only 3 or 5) - but I do not see why not - use my example and just hard code in a test to see if it works. Holler if this is not working.

 

[jymm]

ooops - stand corrected - 4 IS the limit - let me look

 

[jymm]

you could do some fancy stuff - this might get you another option

Declare @Sales nvarchar(1000)
set @Sales = '1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16'

declare @pos int
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@Sales),1) <> ';'
 set @Sales = @Sales  + ';'

set @pos =  patindex('%;%' , @Sales)
while @pos <> 0 
begin
 set @piece = left(@Sales, @pos - 1)
 
 -- You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @Sales = stuff(@Sales, 1, @pos, '')
 set @pos =  patindex('%;%' , @Sales)
end

 

[MrsMope987]

Dumb question but...

once I create this function, how do I reference it from my query to get the result set back split?

 

[jymm]

try going here to look at a similar need for you

http://www.madprops.org/blog/T-SQL-to-Split-a-varchar-into-Words/

 

[MrsMope987]

Thanks, that website did have the function that I needed. But I still don't know how to get the results returned. I assumed it would be simple like:

SELECT G.CoNo, G.GLDivNo, SplitWords(G.peramt) as 'Something' 
FROM PROGRESS..PUB.GLSA as G

But when I check the syntax I get an error

Msg 195, Level 15, State 10, Line 1
'SplitWords' is not a recognized built-in function name.

I'm betting this is because my function is stored in my ReportServer database and I'm attempting to pull data from an ODBC connection. If this is true, how do I get a SQL Function inside of an MS Access database then reference it through SQL. OR how do I reference an Access Function written in VBA from SQL?

Thanks,
REO (MrsMope)

 

[jymm]

at the very top of that web site there is a 'CREATE FUNCTION SplitWords(@text varchar(8000))' --- did you run this to create it? When you ran that you were connected to a database (I will wager the Master db)

try dbo.SplitWords (not just splitwords)--- or potentially master.dbo.splitwords (since I am betting that is where it was created). THis fully qualifies where the function is.

 

[MrsMope987]

Yes I did run the create statment and I can see the function in my object explorer. If I run the following

SELECT * FROM splitWords('9;8;7;8;9;7;8;8;99;9;8;7')

I get records returning as they should but when I use

SELECT G.CoNo, G.GLDivNo, master.dbo.SplitWords(G.peramt) as 'Something' 
FROM PROGRESS..PUB.GLSA as G

[red]Msg 4121, Level 16, State 1, Line 1
Cannot find either column "master" or the user-defined function or aggregate "master.dbo.SplitWords", or the name is ambiguous.[/red]

So that's why I'm wondering how do I get a SQL Function inside of an MS Access database then reference it through SQL. OR how do I reference an Access Function written in VBA from SQL?

 

[MrsMope987]

I still haven't got this working, anyone with a suggestion?

 

[MrsMope987]

Anybody got any suggestions for me? I still can't get this working.

 

[amysdude]

I belive the function in the code-behind needs to be a Public Shared Function.

Public Shared Function getElem(str as String, delim As String, N As Integer)
Dim myArr as Array
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
getElem = Trim(myArr(N - 1))
Return Cdbl(getElem)
End If
End Function