Thank you for your reply vhuynh. I have not been back to check on answers since I have been nose-down trying to solve this issue. I have come up with a similar answer, but without the macro to dynamically create the expressions, as I am not exactly sure what you mean. I thought I would share what I have done so far and see if anyone has a more elegant or more efficient solution:
I create a table (see below) with a column for each customers UDFname and UDFValue for each UDF on a client.
I then select those into a table to load into the QVW and use for the Dimensions/Expressions the UDFValue while the name is the UDFName. Then to show/hide each UDF I take the total possible values into another table and use the following for the Presentation of each UDF field [ =SubStringCount(GetFieldSelections(UDFName,',',Count(Distinct UDFName)),UDF20Name) ]. That way whenever a field is selected to been shown from the ListBox the column in the table will be shown/hidden based on the name the client recognizes rather than the UDFName label.
My only problem now is saving the selections from one load to another when we use QV server to QV Publisher to a display in our page. The clients are not seeing the QVW, rather a translated copy, so we loose the bookmarks whenever the report is re-loaded.
This is from the Load Statement in the report::
SQL
DECLARE @colsName NVARCHAR(2000)
SELECT @colsName = COALESCE(@colsName + ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Value] varchar(50)'+ ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Name] varchar(50)',
'[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Value] varchar(50)'+ ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Name] varchar(50)')
FROM dbo.UDFDefs
ORDER BY UDFDefIds
DECLARE @zsql NVARCHAR(4000)
SET @zsql = '
CREATE TABLE ##UDFMaster (CustID INT, ' + @colsName + '
)
'
EXECUTE(@zsql)
INSERT INTO ##UDFMaster (CustID)
SELECT DISTINCT CustID
FROM UDFCustVal
-- POPULATE UDFValues
DECLARE @c CURSOR
DECLARE @UDFid INT
DECLARE @UDFMasterField NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @c = CURSOR FOR
SELECT UDFDefIds
FROM dbo.UDFDefs
OPEN @c
FETCH NEXT
FROM @c INTO @UDFid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @UDFMasterField = CAST('##UDFMaster'+'.'+'UDF'+CAST(@UDFid AS NVARCHAR(1000))+'Value' AS NVARCHAR(1000))
SET @sql = '
UPDATE
##UDFMaster
SET
'+@UDFMasterField+' = UDFCustVal.UDFValue
FROM
##UDFMaster
INNER JOIN
dbo.UDFCustVal
ON ##UDFMaster.CustID = UDFCustVal.CustID
WHERE
UDFCustVal.UDFDefIds = '+CAST(@UDFId AS NVARCHAR(1000))
--PRINT @##UDFMasterField
--PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM @c INTO @UDFid
END
CLOSE @c
DEALLOCATE @c
-- POPULATE UDFNames
DECLARE @c2 CURSOR
DECLARE @UDFId2 INT
DECLARE @UDFMasterField2 NVARCHAR(1000)
DECLARE @sql2 NVARCHAR(1000)
SET @c2 = CURSOR FOR
SELECT UDFDefIds
FROM dbo.UDFDefs
OPEN @c2
FETCH NEXT
FROM @c2 INTO @UDFid2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @UDFMasterField2 = CAST('##UDFMaster'+'.'+'UDF'+CAST(@UDFid2 AS NVARCHAR(1000))+'Name' AS NVARCHAR(1000))
SET @sql2 = '
UPDATE
##UDFMaster
SET
'+@UDFMasterField2+' = UDFDefs.UDFName
FROM
UDFDefs
WHERE
UDFDefs.UDFDefIds = '+CAST(@UDFId2 AS NVARCHAR(1000))
--PRINT @##UDFMasterField
--PRINT @sql2
EXEC sp_executesql @sql2
FETCH NEXT FROM @c2 INTO @UDFid2
END
CLOSE @c2
DEALLOCATE @c2
SELECT * FROM ##UDFMaster ORDER BY CustID
;
NEWUDFs:
LOAD
CustID,
UDF1Value , UDF1Name ,
UDF2Value , UDF2Name ,
UDF3Value , UDF3Name ,
UDF4Value , UDF4Name ,
UDF5Value , UDF5Name ,
UDF6Value , UDF6Name ,
UDF7Value , UDF7Name ,
UDF8Value , UDF8Name ,
UDF9Value , UDF9Name ,
UDF10Value , UDF10Name ,
UDF11Value , UDF11Name ,
UDF12Value , UDF12Name ,
UDF13Value , UDF13Name ,
UDF14Value , UDF14Name ,
UDF15Value , UDF15Name ,
UDF16Value , UDF16Name ,
UDF17Value , UDF17Name ,
UDF18Value , UDF18Name ,
UDF19Value , UDF19Name ,
UDF20Value , UDF20Name ,
UDF21Value , UDF21Name ,
UDF22Value , UDF22Name ,
UDF23Value , UDF23Name ,
UDF24Value , UDF24Name ,
UDF25Value , UDF25Name ,
UDF26Value , UDF26Name ,
UDF27Value , UDF27Name ,
UDF28Value , UDF28Name
;
SELECT CustID,
UDF1Value , UDF1Name ,UDF2Value , UDF2Name ,
UDF3Value , UDF3Name ,UDF4Value , UDF4Name ,
UDF5Value , UDF5Name ,UDF6Value , UDF6Name ,
UDF7Value , UDF7Name ,UDF8Value , UDF8Name ,
UDF9Value , UDF9Name ,UDF10Value , UDF10Name ,
UDF11Value , UDF11Name ,UDF12Value , UDF12Name ,
UDF13Value , UDF13Name ,UDF14Value , UDF14Name ,
UDF15Value , UDF15Name ,UDF16Value , UDF16Name ,
UDF17Value , UDF17Name ,UDF18Value , UDF18Name ,
UDF19Value , UDF19Name ,UDF20Value , UDF20Name ,
UDF21Value , UDF21Name ,UDF22Value , UDF22Name ,
UDF23Value , UDF23Name ,UDF24Value , UDF24Name ,
UDF25Value , UDF25Name ,UDF26Value , UDF26Name ,
UDF27Value , UDF27Name ,UDF28Value , UDF28Name
FROM ##UDFMaster
;
SQL DROP TABLE ##UDFMaster
;
SQL SELECT UDFDefIds,UDFName FROM UDFDefs
;
