5 Replies Latest reply on Mar 19, 2014 11:50 AM by Keith Helfrich

    Insufficient parameters to format string

    Keith Helfrich

      Dear Community,

       

      I receive the error "insufficient parameters to format string" when dragging a calculated field onto the view which makes use of pass-thru SQLCan someone perhaps point me in the right direction ? 


      I've not been able to find any useful documentation about what causes the error or how to make corrections.


      Thanks!

      Keith Helfrich | LinkedIn

      Keith Helfrich (KeithHelfrich) | Twitter

        • 1. Re: Insufficient parameters to format string
          Eric Munisteri

          Keith, it could be a couple of things.  Best way to answer this would be to see how you wrote and how you are using the calculated field.  Is it possible for you to upload a packaged workbook?

          • 2. Re: Re: Insufficient parameters to format string
            Keith Helfrich

            Hi Eric,

             

            Thank you for offering to take a look.  I've simplified the problem such that the attached workbook contains hypothetical

            demographics for a list of Bocce Ball Teams.


            In short, the error occurs when dragging either of these two pass-thru calculations into the view:

            • _PROBLEM RAWSQL 1 :: Compare_Age_0_17
            • _PROBLEM RAWSQL 2 :: Compare_Age_21_24

             

            In greater context & detail:

             

            The goal of the workbook is a dashboard which allows filtering down from a list of ALL Teams to only a subset of teams, based on their demographic composition when compared to the demographics of the entire Bocce Ball population.

             

            The underlying summary data is organized with one record per team, and one column for the percent of that team's composition in each of the demographic bins.  For example: [Age Range 0 - 17], [Age Range 18 - 20], etc.


            The very first record, with TEAM_ID = 0, is a summary record which describes the demographic composition of the entire population (all teams combined).


            So, in this of context, my approach is to create a RAWSQL pass-thru for each bin which is returns TRUE when this team's % composition is greater than entire population's composition, and FALSE when otherwise.

             

            Since the entire population ("ALL TEAMS") record would not normally appear in the worksheet, as a first step my pass-thru calculation wants to retrieve this value from the underlying data (this is where the error message occurs).  A second step would be to then perform the logical TRUE/FALSE comparison.

             

            As an aside, I'm not yet convinced that this summarized data model is the best way to accomplish the desired filtering & comparison.  An alternative detail data model is also included, which includes similar demographic data at the Person_ID level.  In that case, the RAWSQL would look something like this

            • RAWSQLAGG_INT('select count(*) from [Alternate_Data_Model -- Detail$] where [Gender] = %1',[Gender])/RAWSQLAGG_INT('select count(*) from [Alternate_Data_Model -- Detail$]')

             

            This approach allows the five demographic attributes to be treated as dimensions (instead of 22 separate measures), which is nice, but when the number of records increases up into the many-millions I'm a bit concerned about performance of counts & comparisons using RAWSQL.  I also had some other problems with the detail data model because I couldn't figure out how to build filters on the bin values of each attribute.  And that's what landed me at this summary data model & the RAWSQL which is currently having issues.

             

            Thoughts ?  Tips ? Bocce Ball this Weekend ?

             

            Thanks!!

            Keith Helfrich  | LinkedIn

            Keith Helfrich (KeithHelfrich) | Twitter

            • 3. Re: Re: Re: Insufficient parameters to format string
              Keith Helfrich

              OK!  That was fast!  I've just found the problem in my pass-thru SQL, it was easy, actually.  There were two substitutions being called for & only one substitution was being provided.  So the syntax was wrong (but not caught by the syntax checker).

               

              So this means my real question here is to find the best underlying data model.  Maybe I should open a new forum post with a new title & category.  Any thoughts in this regard will be super helpful & welcome!

               

              For the record, the correct pass-thru syntax is:

              • RAWSQL_REAL('select [Age_0_17] from [Demographics_Comparison$] where [Segment_Name] = %1',"ALL_SEGMENTS")


              Thanks!

              Keith Helfrich  | LinkedIn

              Keith Helfrich | Twitter

              • 4. Re: Re: Insufficient parameters to format string
                Eric Munisteri

                The first thing I saw was an incorrect query...but you found that.  Nice job.  Because this problem has been solved, I would open a new post with the details on your new issue.  You will get more attention that way.

                • 5. Re: Re: Insufficient parameters to format string
                  Keith Helfrich

                  Thanks, Eric!  For being here & offering your help to others.