Parse the field

I have a table that has a "Problem" field with the following data
Customer Service...Jam...Jam 5...Planned
Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
New Drive
Sale Made
I need to separate this "Problem" field into separate fields the issue1,
issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
) .
What would be the easiest way to do so within the SELECT statement ?here's a quick and dirty way of parsing out the data:::
declare @.myString varchar(2000) --Set to max length of field
declare @.Issue1 varchar(100) --Set to max length of any single entry
set @.myString = 'Customer Service...Jam...Jam 5...Planned '
set @.myString = 'Technical
Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
--set @.myString = 'New Drive'
declare @.Index int -- Holds the next position of delimeter
declare @.delimiter char(1) -- holds delimeter character
set @.delimiter = '.' -- set the delimer value
set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
of delimeter
-- run a loop while we still have delimeters in the string
while @.Index>0
begin
-- get the next value
set @.Issue1 = substring(@.myString,1,@.Index-1)
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
-- parse the latest value from the current string
set @.myString = substring(@.myString,@.Index+3,len(@.myString))
-- get the next instance of the delimeter
set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
end
-- Get the last value from what's left
set @.Issue1 = ltrim(rtrim(@.myString))
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
Copy and paste it into QA and you can see how it works for you
Tony
"agenda9533" wrote:
> I have a table that has a "Problem" field with the following data
> Customer Service...Jam...Jam 5...Planned
> Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> New Drive
> Sale Made
> I need to separate this "Problem" field into separate fields the issue1,
> issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> ) .
> What would be the easiest way to do so within the SELECT statement ?|||This is awesome!
Only thing, Ijust don't know how to run/call this script within RS.
As far as I know RS only recognize T-Sql SELECT statements (in order to
create a dataset).
Am I wrong?
"Logicalman" wrote:
> here's a quick and dirty way of parsing out the data:::
>
> declare @.myString varchar(2000) --Set to max length of field
> declare @.Issue1 varchar(100) --Set to max length of any single entry
> set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> set @.myString = 'Technical
> Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> --set @.myString = 'New Drive'
> declare @.Index int -- Holds the next position of delimeter
> declare @.delimiter char(1) -- holds delimeter character
> set @.delimiter = '.' -- set the delimer value
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> of delimeter
> -- run a loop while we still have delimeters in the string
> while @.Index>0
> begin
> -- get the next value
> set @.Issue1 = substring(@.myString,1,@.Index-1)
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
> -- parse the latest value from the current string
> set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> -- get the next instance of the delimeter
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> end
> -- Get the last value from what's left
> set @.Issue1 = ltrim(rtrim(@.myString))
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
>
> Copy and paste it into QA and you can see how it works for you
> Tony
> "agenda9533" wrote:
> > I have a table that has a "Problem" field with the following data
> >
> > Customer Service...Jam...Jam 5...Planned
> >
> > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > New Drive
> >
> > Sale Made
> >
> > I need to separate this "Problem" field into separate fields the issue1,
> > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > ) .
> >
> > What would be the easiest way to do so within the SELECT statement ?|||My first suggestion would be to place this in a Stored Procedure, and call
the SProc as the dataset for the RS report.
"agenda9533" wrote:
> This is awesome!
> Only thing, Ijust don't know how to run/call this script within RS.
> As far as I know RS only recognize T-Sql SELECT statements (in order to
> create a dataset).
> Am I wrong?
> "Logicalman" wrote:
> > here's a quick and dirty way of parsing out the data:::
> >
> >
> > declare @.myString varchar(2000) --Set to max length of field
> > declare @.Issue1 varchar(100) --Set to max length of any single entry
> >
> > set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> > set @.myString = 'Technical
> > Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> > --set @.myString = 'New Drive'
> >
> > declare @.Index int -- Holds the next position of delimeter
> > declare @.delimiter char(1) -- holds delimeter character
> > set @.delimiter = '.' -- set the delimer value
> >
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> > of delimeter
> > -- run a loop while we still have delimeters in the string
> > while @.Index>0
> > begin
> > -- get the next value
> > set @.Issue1 = substring(@.myString,1,@.Index-1)
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> > -- parse the latest value from the current string
> > set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> > -- get the next instance of the delimeter
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> >
> > end
> > -- Get the last value from what's left
> > set @.Issue1 = ltrim(rtrim(@.myString))
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> >
> >
> > Copy and paste it into QA and you can see how it works for you
> >
> > Tony
> >
> > "agenda9533" wrote:
> >
> > > I have a table that has a "Problem" field with the following data
> > >
> > > Customer Service...Jam...Jam 5...Planned
> > >
> > > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > > New Drive
> > >
> > > Sale Made
> > >
> > > I need to separate this "Problem" field into separate fields the issue1,
> > > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > > ) .
> > >
> > > What would be the easiest way to do so within the SELECT statement ?

Paremeter question

I have a report parameter: Customers
That parameter is used in main data set to pull data for the specified customer.
In the report header Iâ'd like to show that customerâ's name.
But because parameter has a customer code and not a name I dont know
the name of the customer that a user selects.
What is a solution here?Use the cutsomer code to query the customers name from the database, and
display that name in the header.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:A4AFE9B4-8598-42C0-B60B-873A37904B5F@.microsoft.com...
> I have a report parameter: Customers
> That parameter is used in main data set to pull data for the specified
customer.
> In the report header I'd like to show that customer's name.
> But because parameter has a customer code and not a name I dont know
> the name of the customer that a user selects.
> What is a solution here?
>

Parameters and MDX

Is there a customer Data Providor for Reporting Services that handles
Parameteized MDX statments
I.E.
For instance the below MDX Statement would have
<%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters passed
into the data providor
WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER ID].MEMBERS},
<%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},{[MAIN]}))'
MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
CLASS]})'
MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
<%BOOKINGS%> <%STD%>]'
MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
<%STD%>]'
MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
<%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
<%STD%>])',FORMAT='0.00%'
SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
<%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
$],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
{[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)The OleDB provider for AS 2000 does not support parameterized MDX queries.
This MSDN article explains how to achieve parameterized MDX in RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
In addition, you may want to download this sample:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
BTW: RS 2005 Beta 2 contains graphical and text-based query designers for
MDX and DMX. They also support single-value parameters at this point.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:e36nP4EjEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a customer Data Providor for Reporting Services that handles
> Parameteized MDX statments
> I.E.
> For instance the below MDX Statement would have
> <%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters
passed
> into the data providor
> WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},
> <%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
> MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
> ID].MEMBERS},{[MAIN]}))'
> MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
> CLASS]})'
> MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
> <%BOOKINGS%> <%STD%>]'
> MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
> <%STD%>]'
> MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
> <%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>])',FORMAT='0.00%'
> SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
> YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
> $],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
> {[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
> FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)
>

Parameters - Selection from a long list

Hello

I want to have a report query parameter which has many thousands of possible values (i.e. Customer Names). I need to provide the ability to allow a user to search for the customer they want and then have that customer's detail appear in the parameter.

Ideally I would like something like the ability to have a "Find" button next to the parameter that would kick off a "Find" report and then pass the value selected back to the original parameter.

The parameter in question is one of a numvber (6) on the actual report so have discounted the option of creating a "Find Customer" report as step 1 and then passing the selection forward to the actual report (Step 2) i.e. drill through.

Any thoughts/suggestions would be much appreciated as I know I will have many of these scenarios in the coming months as I will be using numerous parameters with large selection lists.

Thanks in advance

Eliot

No user is going to want a maseeev ddlist to scroll through.

Has anyone ever tried using code to do an ajax/google suggest type thing?

If so please post!

I think you are going to have to use cascading parameters

So free entry text param 1 allows the user to enter some of the name

param 2 is driven by this and does some sort of fuzzy matching to return a list of possible candidates LIKE 'param1' + '%' (soundex also might help)

|||

Thanks for your reply.

Using Cascading parameters will work in some cases but not in this particular situation as I need to provide multiple ways to search for a customer. i.e. Surname, Customer Number, Postcode etc.

However, some of my scenarios could use cascading parameters.

Any other thoughts out there?