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?
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 ?
Comparison_Problem.twbx 43.0 KB
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")
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.
Thanks, Eric! For being here & offering your help to others.