Get Server instances as listbox for filter

[#1]

Hi

I have a simple click view with one table creating a Data Dictionary.

[DATA_DICTIONARY]:

LOAD ServerName AS [Server Name],

     DatabaseName AS [Database Name],

     SchemaName AS [Schema Name],

     ObjectName AS [Object Name],

     ObjectType AS [Object Type]

FROM Someplace

I want to take the Server Name field and parse it so that I get the last portion of the Server Name which is its Instance into another field. I want to create a list box so when you click a instance type the server name field only shows those types of servers.

Example of Server Names

Server1\DEV

Server1\QA

Server1\PROD

DEV, QA, AND PROD will be in another ListBox where if clicked the Server Names list box only shows the servers with that instance type.

Thanks

Al

[sunny_talwar · Accepted solution]

May be like this:

[DATA_DICTIONARY]:

LOAD ServerName AS [Server Name],,

    SubField(ServerName, '/', 2) as ListBoxField,

    DatabaseName AS [Database Name],

    SchemaName AS [Schema Name],

    ObjectName AS [Object Name],

    ObjectType AS [Object Type]

FROM Someplace

And now use ListBoxField as for your selection purposes. Note: ListBoxField is just a placeholder and name your field according to your requirement

[#3]

Hi Sunny,

Thanks for Answering my question so late, its almost 12am here in the us. Anyway I had to tweek it a bit for it to work.

I needed the DEV or QA or PROD so I had to change the number to a -1 because there is only 1 "\" in the names. and - gets the flip side of a positive 1.

here is what worked.

[DATA_DICTIONARY]:

LOAD ServerName AS [Server Name],

  SubField(ServerName, '\', -1) as ListBoxField,

     DatabaseName AS [Database Name],

     SchemaName AS [Schema Name],

     ObjectName AS [Object Name],

     ObjectType AS [Object Type]

FROM

[sunny_talwar]

If you only ever going to have just one '/', then 2 should work as well. But if you can have multiple '/' and you want the last part after /, then -1 is the way to go.

Best,

Sunny