Skip navigation

Smart Reporting-- Calculated Fields-- Allow Pre-Defined to take in Calculated Fields -- Dates & Text manipulations often require multiple calls and are not supported in 'Simple' or 'Freehand SQL'

score 60
You have not voted. Active


If Pre-Defined's could call other calculated fields, you could get around most of Smart Reporting's limitations in Calculated Fields, with the exception of the window function gap.


Below is documentation of why I think this is the preferred solution for both the limitations of Dates & Text in Smart Reporting:


Date Functions:

Let's say I have a trending report, where all I want to do is show the volume of incidents over a few dates.  I want to show on the row level something along the lines of

  "01/01/2020-01/08/2020" #

  "01/08/2020-01/15/2020" #


Let's discuss my options in Calculated Fields:

Pre-Defined&Simple Fields:

I mention these together for date, because you can only really use the pre-defined on the bottom layer (referencing view fields or fixed values), and thats not enough to get the final product.

The solution I use is:

Predefined Field  Datediff(second, now, [datefield])

Simple Calculated Field  Datediff / 7/86400   --- this will return the integer of weeks ago.

If we wanted the actual dates, my best shot would probably be displaying weeks ago and min(datfield) and max(datefield), which could be quite bad for small volume queues.


If i could instead run cast(cast(Dateadd (week, -weeks_ago, current_date) as date) as varchar) +'-'+ cast(cast(Dateadd (week, -weeks_ago-1, current_date) as date) as varchar),  i would be able to display exactly the format above.  That is only possible if pre-defined's can be used in the upper layers of calculated fields.


Free-Hand SQL Fields:

Good luck with Typing & getting the tool to compile code correctly in the Freehand SQL option.  I have had no end of trouble with either of those issues.  My experience leads me to think Freehand SQL calculated fields are reliable as long as they are a single function call.  Also, at least within my company, no one knows how to use the Freehand SQL.  It requires a rareified level of technical knowledge (SQL/db knowledge) and patience with the tool (the people who can do this are usually able to direct query the database).



Text Functions:

Let's say I have a simple parse.  I have a templated incident for a product that isn't being read by a scanner (not a real incident, but the template is what matters).  The notes could be in the form:

"Product ID:hajhsdkfhljkasdfhasdjklfh



Has this happened before?:[Yes/No]

Are there any details you would like to add?:sfdhasjklsfhsdjklfhs"

And I want _any_ of that information pulled into a column.  Maybe I want to filter on Yes/No  or a price point  (Number x Price).  Let's discuss my options in Calculated Fields:


Simple Calculated Field:

No support outside of LIKE -- which will not be efficient for most of these purposes and can be misleading.  Let's say you set up CASE WHEN Notes LIKE '%Yes%'  THEN 1 ELSE 0 END. This could be messed up by the user-included information.  It also wouldn't help with parsing numerics, like the price.


Pre-Defined Field:

The relevant fxns for this example are:






Essentially, these functions are good, but they can't be nested.  I can have Substring([viewfield/fixedtxt], [viewfield/fixedtxt], [viewfield/fixedtxt]) or Charindex([viewfield/fixedtxt], [viewfield/fixed#], [viewfield/fixed#]) .  Ideally I would want something like Substring(viewfield, Charindex(fixedtxt, viewfield), ...)

Unless I make specific fields in the View, nothing can be done.  So if I add a field in the view for a specific Charindex, I can do this.  However, I would need to do this every time i wanted to do a simple parse.  Furthermore, not every organization allows every user to do this.


Free-Hand SQL Fields:

Free-Hand SQL feels like it is referenced as a stopgap for alot of the other failings. It is not.


Let's say you try to use it. You're desperate and you know it.  You're on the last train to flavortown, you're low on coal and you already lost the caboose.  You want to provide a simple parse to your employer for reports that would be quick and easy in any other tool, including SSMS or any direct query tool.  All you want to do is use similar code with the same calls you would in that direct query.


Your train derails at level of support for tables.  None of this runs for fields in non-mandatory tables.  Luckily, you only need to parse information from one table (this is not always the case), so you set your one table in the view with fields to parse as mandatory. You've passed the easiest hurdle.


Now you have to write a reliable structure of DBFN's in the tool.  And this is when you realize you can't get to flavortown.  Let's say you are comfortable enough in sql/this dbfn call that you write it up correctly.  It won't compile the DBFN('substring', `table`.`fieldname` , DBFN('Charindex' , 'searchtext',`table`.`fieldname`)-1, DBFN('Charindex' , 'searchtext2',`table`.`fieldname`)-n)

where n = length of searchtext2  fails.  You test around and find that -1 and -n make it fail to compile.  You work around this: run it with -dbfn('len','searchtext2') instead of -n, or completely scrap the -1 and -n adjustments.


This resulting calculation, in my experience, will parse correctly some of the time, but never all of the time.  It required some sql expertise, which not all users have, was a little clunky to code out and still failed.





If Pre-Defined's could call other calculated fields, you could get around most of Smart Reporting's limitations in Calculated Fields, with the exception of the window function gap.


Vote history