9 Replies Latest reply: Mar 20, 2013 2:54 PM by A Sheppard RSS

    Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

      Recently I got a assignment from the owner of my company to make our standard reports include what we call UDFs (User-Defined Fields) so that each client (which resides in a different db) can have any or all of their UDFs included in a existing report without it being different from client to client or db to db.  Also each client has a variable number of UDFs as they are unlimited.

       

      The tables involved are:

       

      CustomerValues-

      UDFID, CustomerID, UDFValue

       

      Definitions-

      UDFID, Name, FieldType

       

      Examples:

      Client A (db A)

       

      CustomerValues-

      UDFID, CustomerID, UDFValue

      11 , 123456 , Apples

       

      Definitions-

      UDFID, Name, FieldType

      11 , Fruits , STR

       

      Client B (db B)

       

      CustomerValues-

      UDFID, CustomerID, UDFValue

      11 , 333444 , 112233

       

      Definitions-

      UDFID, Name, FieldType

      11 , Account , INT

       

      I have researched many ways to try to accomplish making both datasets show up on a report.  All of which require some hard-coded values; which

      then renders the unversality null and void.

       

      The last approach I tried was to have the fields put into a view where the outcome was:

      CustomerID,UDF1,UDF1Name,UDF2,UDF2Name,UDF3,UDF3Name

       

      However I ran into the lack of ability to dynamically name the dimensions/expressions.

       

      The other requirement is that this report run as fast as possible, given that one clients UDF combinations result in a record set of over 6 million rows.  They have 57 different UDFs and 300,000 clients; so the product of all 57 UDFs per each customer is a staggeringly large number of returns.

       

      My DBA has said that we must find an answer to this problem, and is open to changing how we structure the UDF tables.

       

      I would appreciate any ideas that lead to being able to have the same report (with zero differences) apply to two DBs using the UDFs from each client in the same way.

       

      My boss has also authorized me to seek out any QV Consultants for hire who may be able to resolve this problem.

        • Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

          There are two issues to solve here, loading the data and displaying the data.

           

          For loading the data, you can load the definitions table then loop through each individual row in the defination table to load the values from the CustomerValues table for each type. By looping, you can load the data from the correct column. You can name the data column UDF<UDFID> (e.g., UDF11 for Fruits in Customer A). If the id values can be large, you will have to create a new field with the loop value to associate the id to the loop value

           

          To display the data, you will create a table with as many expressions as the customer with the most UDF fields. Each UDF expression UDF<id> and the label will be =Only(UDFID=<id> Name) (or use loop value instead of id). If you have many values (more than you want to create manaually) you will need to create a macro to dynamically add the expression.

           

          -Van

            • Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

              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 UDF[x]Value while the name is the UDF[x]Name.  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 UDF[x]Name 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

              ;

                • Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

                  Will you be loading multiple customers, each with different udf fields, into the same qvw file? If not then you don't really need the UDFName fields since the values will be the same. Each copy of the qvw file will need a to load the the definition table specific to the customer.

                   

                  If you need to load multiple customers into one qvw, then you will need to load the definition table with the customer ids.

                   

                  One question I forgot to ask, since the qvw will be used for different customers (whether its one copy or multiple copies) the only thing you can do with the UDF fields is to report on them since there's no way to know what calculations make sense for a particular UDF field.

                   

                  I've attached an example qvw that may help.