Calculation Syntax

[kennedto]

I have a calculation where this works

IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  'AVALUE'   )

If I attempt to replace the 'AVALUE' string with a parameter map as follows

IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  #$get_cf_combo_val{[Views].[Custom Field].[ICF_VALUE]}#   )

I get a syntax error.   Can someone tell me what proper syntax is for this?

[MFGF]

I have a calculation where this works

IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  'AVALUE'   )

If I attempt to replace the 'AVALUE' string with a parameter map as follows

IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  #$get_cf_combo_val{[Views].[Custom Field].[ICF_VALUE]}#   )

I get a syntax error.   Can someone tell me what proper syntax is for this?

Hi,

Can you explain your requirement here? A macro like this is evaluated and resolved as a query is being formulated - not after it has run. Generally macros are used to assemble item names to retrieve in a query, eg you might have a parameter map that uses a session parameter as the key and returns the ICF_VALUE item name or a different item name, which is then used by the query to retrieve that item.

The example you have here seems to be trying to use the returned contents of ICF_VALUE after the query has run, but this is not going to work since the macro is being evaluated as the query gets composed.

Cheers!

MF.

[kennedto]

I have a table with rows that hold a single value of data, but the data can be of different types.
Supported types are text, integer, datetime, value of combo html box, value of html mutlbox..

It rows are defined as
FLD_TITLE,FLD_TYPE, ICF_VALUE_DATE, ICF_VALUE_INTEGER, ICF_VALUE, ICF_FLD_ID


The FLD_TYPE field can contain the values text,textarea,datetime,integer,combo or multi.
If FLD_TYPE contains text or texarea, the value is stored in the ICF_VALUE_FIELD
if  FLD_TYPE contains integer,   the value is stored in the ICF_VALUE_INTEGER field
if  FLD_TYPE contains datetime the value is stored in the ICF_VALUE_DATE field.
if FLD_TYPE contains combo or multi you have to look into another table to get the value (CUST_FLD_OPT)
The CUST_FLD_OPT table has these fields
CFO_ID, CFO_FLD_ID, CFO_VALUE
Use the ICF_FLD_ID the value of ICF_VALUE field  as keys to get the CFO_VALUE from this table table.


Obviously this is too complicated for a report writer to use.
My goal is to make a table that is simple to use with the following fields
TITLE,VALUE

Report writers should not have to know about the rules for extracting the data.
I have not done any complicated calculation before, so this is my solution.

For the TITLE field I have created a calculation as shown below.
It works fine for text, textarea, integer and datetime.  However, since data needs to be extracted from the CUST_FLD_OPT  table, combo and multi
is where I need assistance.  If you could assist with retrieving values from the CUST_FLD_OPT using the  ICF_FLD_ID and  the value stored in the ICF_VALUE field I would appreciate it.
Need  something like   get_cust_field_opt_val( ICF_FLD_ID,ICF_VALUE) to return CFO_VALUE and assign it to VALUE (see combo below).

CASE [Views].[Custom Field].[FLD_TYPE]
WHEN 'text'        THEN cast([Views].[Custom Field].[ICF_VALUE] ,varchar(100))
WHEN 'textarea' THEN  cast([Views].[Custom Field].[ICF_VALUE] ,varchar(100))
WHEN 'integer'   THEN cast([Views].[Custom Field].[ICF_VALUE_INTEGER] ,varchar(20))
WHEN 'datetime' THEN cast([Views].[Custom Field].[ICF_VALUE_DATE] ,varchar(20))
WHEN 'combo' THEN
IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  '#$get_cf_combo_val{    [Views].[Custom Field].[ICF_VALUE]   }#'   )
WHEN 'multi' THEN 'TTEXT'
ELSE 'NOTSET'
END

[MFGF]

I have a table with rows that hold a single value of data, but the data can be of different types.
Supported types are text, integer, datetime, value of combo html box, value of html mutlbox..

It rows are defined as
FLD_TITLE,FLD_TYPE, ICF_VALUE_DATE, ICF_VALUE_INTEGER, ICF_VALUE, ICF_FLD_ID


The FLD_TYPE field can contain the values text,textarea,datetime,integer,combo or multi.
If FLD_TYPE contains text or texarea, the value is stored in the ICF_VALUE_FIELD
if  FLD_TYPE contains integer,   the value is stored in the ICF_VALUE_INTEGER field
if  FLD_TYPE contains datetime the value is stored in the ICF_VALUE_DATE field.
if FLD_TYPE contains combo or multi you have to look into another table to get the value (CUST_FLD_OPT)
The CUST_FLD_OPT table has these fields
CFO_ID, CFO_FLD_ID, CFO_VALUE
Use the ICF_FLD_ID the value of ICF_VALUE field  as keys to get the CFO_VALUE from this table table.


Obviously this is too complicated for a report writer to use.
My goal is to make a table that is simple to use with the following fields
TITLE,VALUE

Report writers should not have to know about the rules for extracting the data.
I have not done any complicated calculation before, so this is my solution.

For the TITLE field I have created a calculation as shown below.
It works fine for text, textarea, integer and datetime.  However, since data needs to be extracted from the CUST_FLD_OPT  table, combo and multi
is where I need assistance.  If you could assist with retrieving values from the CUST_FLD_OPT using the  ICF_FLD_ID and  the value stored in the ICF_VALUE field I would appreciate it.
Need  something like   get_cust_field_opt_val( ICF_FLD_ID,ICF_VALUE) to return CFO_VALUE and assign it to VALUE (see combo below).

CASE [Views].[Custom Field].[FLD_TYPE]
WHEN 'text'        THEN cast([Views].[Custom Field].[ICF_VALUE] ,varchar(100))
WHEN 'textarea' THEN  cast([Views].[Custom Field].[ICF_VALUE] ,varchar(100))
WHEN 'integer'   THEN cast([Views].[Custom Field].[ICF_VALUE_INTEGER] ,varchar(20))
WHEN 'datetime' THEN cast([Views].[Custom Field].[ICF_VALUE_DATE] ,varchar(20))
WHEN 'combo' THEN
IF ( [Views].[Custom Field].[ICF_VALUE] = '-1'  ) THEN
    ( 'MINUSONE' )
ELSE
    (  '#$get_cf_combo_val{    [Views].[Custom Field].[ICF_VALUE]   }#'   )
WHEN 'multi' THEN 'TTEXT'
ELSE 'NOTSET'
END

Hi,

As I mentioned previously, you won't be able to use a macro (and therefore a parameter map) to do this, since you need to use a value returned by the query (which obviously means the query must already have been built and executed). Macros are usually evaluated during creation of the query.

It looks to me like you need to have both your main table and the CUST_FLD_OPT table as query subjects in your model. Create a relationship between them joining ICF_FLD_ID and ICF_VALUE to CFO_ID and CFO_FLD_ID, and set the cardinality as 1..1 at the main table end and 0..1 at the CUST_FLD_OPT end.

You will then be able to reference the CFO_VALUE item from CUST_FLD_OPT in your expression.

Cheers!

MF.

[kennedto]

Call the main table ISSUE_CUSTOM_FIELD.
I am not sure if this relationship
ISSUE_CUSTOM_FIELD. ICF_VALUE   <-->  CUST_FLD_OPT.CFO_ID

is valid for a join because for records where FLD_TYPE != 'combo' or multi'
ICF_VALUE is empty or has a string value in it that is not an index into the  CUST_FLD_OPT table.

I get an SQL 401 error (type incompatibility) when I try that.. 

If I remove that relationship, the join will work, but it is of course not correct. 

[MFGF]

Call the main table ISSUE_CUSTOM_FIELD.
I am not sure if this relationship
ISSUE_CUSTOM_FIELD. ICF_VALUE   <-->  CUST_FLD_OPT.CFO_ID

is valid for a join because for records where FLD_TYPE != 'combo' or multi'
ICF_VALUE is empty or has a string value in it that is not an index into the  CUST_FLD_OPT table.

I get an SQL 401 error (type incompatibility) when I try that.. 

If I remove that relationship, the join will work, but it is of course not correct.

What are the data types of the four columns involved in the relationship? Do they match in the underlying tables?

MF.

[kennedto]

Questionable relationship
===============================================
ISSUE_CUSTOM_FIELD. ICF_VALUE  = LONGVARCHAR           
CUST_FLD_OPT.CFO_ID                       = INTEGER


Seems to work
==================================================
ISSUE_CUSTOM_FIELD. ICF_FLD_ID  =  INTEGER
CUST_FLD_OPT.CFO_FLD_ID               = INTEGER