OK, I found the appropriate UserDefinedFieldId by browsing the thousands of records in the UserDefinedData table and looking for a group of records that had the right kind of FieldValue (gladly something distince - radio titles - in this case) and was able to correctly cast the columns of my table & properly display all the records.

It was written that, when using the flattened UDT, "Any conditional filter will require referencing the appropriate fieldvalue by UserDefinedRowID and UserDefinedFieldID similar to the actual columns reported in the Select."

Any clause I use in "Conditions" or "Date Range", etc. results in an "Invalid column name..." error. SGSV can't reference the original column names, I understand; but how can I make it properly reference those columns using a "UserDefinedRowID and UserDefinedFieldID" method as written above.

(A simple example I have been using to keep my error potential low.)
There is one record in my UDT with the FieldValue of 'Gettysburg' (with a UserDefinedFieldId of 226 - my UDT's [Title] column). I want SGSV to display only that row by filtering for 'Gettysburg'.

Any help would be appreciated, Thanks.

Continuing on ... how to use SGSV to select just specific records?

First thing is we are going to work with the explicit T-SQL generated by the GetUDTData. Assuming we are working with module 123, then to get the resulting T-SQL run the following in HOST/SQL

exec dbo.GetUDTData 123, 1

The "1" tells GetUDTData to report the T-SQL instead of executing it. Save the resulting "Column1" data and place it into the primary select of SGSV. You should get the same display as running stored procedure in the Primary Select (with "0" telling the procedure to execute the T-SQL and return data )

So using your example where you want to filter on FieldValue 226. Follow these steps:

1) Add a Filter Selection where the Parm Name is "Title;RowID". This will indicate to SGSV that you want to display the "Title" and maintain the associated RowID with each title when displaying the dropdown.

2)Set the Prompt to the appropriate text like "Select Title"

3)Set the Filter Select to

select FieldValue as Title, UserDefinedRowID as RowID from UserDefinedData where UserDefinedFieldID = 226

This select will be used to fill the dropdown box with Titles and associate each title to a rowid.

4) Set the Clause to

UserDefinedRowID = [parm:RowID]

This Clause will be inserted in the primary select with the selected Title's associated row value.

5) Lastly check "Required" and "Allow All"

So what happens: When SGSV starts it will Allow All rows to be displayed from the primary select - no filter clause is added. Then once you select a value, the filter clause is added and the number of rows is restricted.

Hope this helps you
Paul.

As you have discovered, the data stored in a UserDefinedTable is "different" than most other table based data records.

The data is available but requires understanding just what you are after and how to select the data. The primary selection item is the UserDefinedRowID. So every one of the filters you want, must return one more RowIDs. While there are many ways to obtain the appropriate RowIDs, the methods are beyond the support offered here. However I will provide one way to obtain each of the 4 type of data you want to filter on. These methods can work with other modules and are not specifically limited to SGSV (you may need to adjust them to work due to specifics of the modules).

I will start with case two first.

2) Assuming you have selected the date range filter, you will have two parameters availables [Date:to] and [Date:from] so the resulting filter select would be

UserDefinedRowID in (select UserDefinedRowID from UserDefinedData where UserDefinedFieldID = 123 and Cast(UserDefinedValue as Datatime) between '[Date:From]' and '[Date:To]')

The above is a standard T-SQL where clause - the parameters are SGVS's but once substituted results in a complete T-SQL where clause. Something like

UserDefinedRowID in (select UserDefinedRowID from UserDefinedData where UserDefinedFieldID = 123 and Cast(UserDefinedValue as Datatime) between '10-Sep-2010' and '15-Sep-2010')

The clause use the IN keyword - I suggest you review this keyword and it's use as it will be used in the remaining examples. Also the value in the UserDefineData must be convert/cast to the appropriate data type so the comparison is handled properly.

1) Case one is a bit harder as I don't know where you would be storing the data - If it is in another UserDefinedTable, then you will need to understand the data structure and T-SQL explicitly to be able to make this work. I would suggest a specialized store procedure would work best.

3) SGSV allows the user to define Values to be selected. Created a Filter Selection with a name of HowOften and defined the Filter Select as "Values:Weekly,Daily". Please refer to the User Guides on how to do this. It is very similar to the example give in the early post. Here again the IN clause is used.

UserDefinedRowID in (Select UserDefinedRowID from UserDefinedData where UserDefinedFieldID = 124 and Cast(UserDefinedValue as nVarchar(max)) = '[parm:HowOften]')

4) Selecting based on a text value supplied by the User is similar to 3). How the Text Search Filter is used and the substitution parameter is [Search:text], so the filter clause changes to

UserDefinedRowID in (Select UserDefinedRowID from UserDefinedData where UserDefinedFieldID = 124 and Cast(UserDefinedValue as nVarchar(max)) = '[Search:Text]')

The filter clauses outline here are inserted in to the primary select to form a T-SQL select. The completed T-SQL after the selection is made will work in the Report module or even the Host/SQL page. So ultimately, this data selection is a T-SQL issue not one of SGSV.

You should be able to perform these selects with the free version of SGSV.

Hope this point you in the right direction
Paul.

Glad they did.

As for the last points .. pagination, hiding columns, and ww relate dates - these issues require the current version of SGSV.

- pagination has been corrected in the V5 series of products

- Column display processing has been greatly enhanced and allows you hide or change the order of display (very handy when using a stored procedure that you can't or don't want to change.

- ww relative dates -- is not currently support but I added to next release V5.10

Paul.