What's Wrong With This Expression?

[t0mato]

Hi,

Here is my input: IF ([Step Date] '>=2020-08-04 -7' ) THEN   (count ([Email Address])

Defined: If the step date is greater than or equal to 7 days in the past, then count the email address.

To provide context, I have a long list of email addresses and step dates associated with them for the year. I want to get a count of only the email addresses with step dates that are at least 7 days old.

Am I missing something with this expression? Please help!

[dougp]

the syntax

I think there are multiple problems:

• No comparison operator
• You may not want '>=2020-08-04 -7' to be a string literal.
• count() may not work in the THEN part of the expression.  You may need to count the email addresses in an upstream query, then sum them in this one.

This concept may get you closer:
if ([Step Date] >= dateadd(day, -7, 2020-08-04) then (count([Email Address])
or
if ([Step Date] >= dateadd(day, -7, 2020-08-04) then ([Email Address]) and make the detail aggregation function Total.

(That's pseudocode, so don't just copy/paste it and expect it to work.)

You may also want to use ELSE to return 0 rather than null.

[t0mato]

Thanks for the help! I tried your suggestions and when validating, still receiving an error similar to the ones I've been receiving. I wish I could copy and paste the errors but cognos does not seem to allow that.

Here's what I tried:

IF ([Step Date] >= dateadd(day, -7, 2020-08-04 )
THEN (count ([Email Address] )
ELSE (0)

I will try your other suggestion of counting the email addresses in a separate query then summing them in this one and report back.

[t0mato]

*Update:

Still unable to get this to work. Any help would be appreciated!

[MFGF]

*Update:

Still unable to get this to work. Any help would be appreciated!

Hi,

The dateadd() function is not an inbuilt Cognos function. It may or may not exist as a function within your data source, but we don't know if it does because we don't know what data source you are connecting to.

A safer and more portable option is to use inbuilt Cognos functions such as _add_days() instead, eg
_add_days(2020-08-04, -7)

or even better, rather than hard-coding today's date in the expression, use current_date instead
IF ([Step Date] >= _add_days(current_date, -7))
THEN (count ([Email Address] )
ELSE (0)

If you see errors from your expression, there's nothing stopping you from copying and pasting the error message from your browser into a message. This is a good idea if you need help troubleshooting.

Cheers!

MF.

[dougp]

MFGF:
Of course.  Even if the OP is using SQL Server, that syntax is not valid for dateadd().  (It's missing {}, which is clear just by typing into the expression editor and looking at the Tips box.)  I was trying to communicate a concept and hoping the OP would read some docs.  I've seen a lot of people asking us to do their homework lately.  It seems that help from people like you and me is making it so easy to "do IT" that people aren't even trying anymore.

[t0mato]

Hi there and thanks so much for the help.

I've tried this and again receive an error when validating. For some reason, I'm unable to copy the error codes. I'm using Google Chrome and will see if I can copy it in IE instead. There is no right click option available and ctrl+c does nothing.

I'll paste the error code in a moment should IE allow me to copy it.

Hi,

The dateadd() function is not an inbuilt Cognos function. It may or may not exist as a function within your data source, but we don't know if it does because we don't know what data source you are connecting to.

A safer and more portable option is to use inbuilt Cognos functions such as _add_days() instead, eg
_add_days(2020-08-04, -7)

or even better, rather than hard-coding today's date in the expression, use current_date instead
IF ([Step Date] >= _add_days(current_date, -7))
THEN (count ([Email Address] )
ELSE (0)

If you see errors from your expression, there's nothing stopping you from copying and pasting the error message from your browser into a message. This is a good idea if you need help troubleshooting.

Cheers!

MF.

[t0mato]

Sorry sir, I am very new to IBM Cognos Analytics and have just been promoted into a role that uses this and given no formal training. Can you recommend a good source for learning more about writing expressions?

MFGF:
Of course.  Even if the OP is using SQL Server, that syntax is not valid for dateadd().  (It's missing {}, which is clear just by typing into the expression editor and looking at the Tips box.)  I was trying to communicate a concept and hoping the OP would read some docs.  I've seen a lot of people asking us to do their homework lately.  It seems that help from people like you and me is making it so easy to "do IT" that people aren't even trying anymore.

[t0mato]

*Update: I have no clue how to copy the error. I can copy the text in Tips but not Errors - right click simply does nothing. I will try to get some screenshots here.

Hi,

The dateadd() function is not an inbuilt Cognos function. It may or may not exist as a function within your data source, but we don't know if it does because we don't know what data source you are connecting to.

A safer and more portable option is to use inbuilt Cognos functions such as _add_days() instead, eg
_add_days(2020-08-04, -7)

or even better, rather than hard-coding today's date in the expression, use current_date instead
IF ([Step Date] >= _add_days(current_date, -7))
THEN (count ([Email Address] )
ELSE (0)

If you see errors from your expression, there's nothing stopping you from copying and pasting the error message from your browser into a message. This is a good idea if you need help troubleshooting.

Cheers!

MF.

[t0mato]

I've written the error code here since copy/paste is not available for me:

Error 1
QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 87 of: "IF ([Step Date] >=_add_days(current_date,-7)) THEN (count ([Email Address]) Else"
QE-DEF-0261 QFWP - Parsing text: IF ([Step Date] >=_add_days(current_date,-7)) THEN (count ([Email Address]) ELSE (0)

Error 2
QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 87 of: "IF ([Step_Date]>=_add_days(current_date,-7)) THEN (count ([Email Address]) ELSE"
QE-DEF-0261 QFWP - Parsing text: IF ([Step_Date]>=_add_days(current_date,-7)) THEN (count([Email Address]) ELSE(0)

[dougp]

Sorry sir, I am very new to IBM Cognos Analytics and have just been promoted into a role that uses this and given no formal training. Can you recommend a good source for learning more about writing expressions?

I don't know what your role is.  Mine involves all things Cognos (reports, dashboards, models/packages, agents, scheduling, scripting, installing, configuring, monitoring, report troubleshooting, server troubleshooting, database troubleshooting, database partitioning), so my perspective may be a little skewed.

I'd start here:
https://www.w3schools.com/sql/default.asp
https://www.w3schools.com/html/default.asp
https://www.w3schools.com/css/default.asp
https://www.w3schools.com/js/default.asp
https://www.w3schools.com/xml/default.asp
https://requirejs.org/
https://developer.mozilla.org/en-US/docs/Web
Plus any training you can find specifically about Cognos.  For basic report development concepts -- data item expressions, joins, unions, data containers, master-detail relationships, etc. -- you can find training from Cognos 10 forward all over YouTube.

You may want to start with SQL.  Without a solid understanding of database query concepts, you'll probably remain lost in Cognos.

Error 1 is missing a closing parenthesis before ELSE.  Error 2 looks exactly the same (aside from some spacing and capitalization).  I can't tell you what your query item names are.

[t0mato]

Thanks a lot for your help. I've changed my approach to this question. I now have a query calculation that calculates the days in step. How can I get a total of the days greater than or equal to 7?

My approach is:

IF ([Days in Step] >= 7) THEN   (count ([Email Address]) ELSE      (0)

This should look at the days in step (which is a whole number) and if that number is greater than 7, count the email address associated with it so I can get a total number of days greater than 7, but I am receiving an error.

Does this help make it easier?

I don't know what your role is.  Mine involves all things Cognos (reports, dashboards, models/packages, agents, scheduling, scripting, installing, configuring, monitoring, report troubleshooting, server troubleshooting, database troubleshooting, database partitioning), so my perspective may be a little skewed.

I'd start here:
https://www.w3schools.com/sql/default.asp
https://www.w3schools.com/html/default.asp
https://www.w3schools.com/css/default.asp
https://www.w3schools.com/js/default.asp
https://www.w3schools.com/xml/default.asp
https://requirejs.org/
https://developer.mozilla.org/en-US/docs/Web
Plus any training you can find specifically about Cognos.  For basic report development concepts -- data item expressions, joins, unions, data containers, master-detail relationships, etc. -- you can find training from Cognos 10 forward all over YouTube.

You may want to start with SQL.  Without a solid understanding of database query concepts, you'll probably remain lost in Cognos.

Error 1 is missing a closing parenthesis before ELSE.  Error 2 looks exactly the same (aside from some spacing and capitalization).  I can't tell you what your query item names are.

[bus_pass_man]

You might be missing a closing bracket at the end of the THEN

IF
(
     [Days in Step] >= 7
)
THEN   
(
    count ([Email Address])
)
ELSE      (0)

[t0mato]

Thanks a lot as I think this works. When validating the code, no errors are found. When trying to run the report however, I get the following error:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2020-08-04-18:21:55.600-#4904

You might be missing a closing bracket at the end of the THEN

IF
(
     [Days in Step] >= 7
)
THEN   
(
    count ([Email Address])
)
ELSE      (0)

[t0mato]

It appears the two end quotes after Email Address give no errors when validating but when running the report, it fails and throws the error. After additional testing, I can confirm the issue is due soley to this approach. Any ideas?

You might be missing a closing bracket at the end of the THEN

IF
(
     [Days in Step] >= 7
)
THEN   
(
    count ([Email Address])
)
ELSE      (0)

[bus_pass_man]

err what end quotes?

What exactly is your expression?

[t0mato]

Sorry, I meant to say closing parentheses. Without the second closing parentheses (bolded in the expression below), the validation throws an error. With it there, no errors occur when validating but when running the report, the error previously posted is returned and the report fails to run.

IF
(
     [Days in Step] >= 7
)
THEN   
(
    count ([Email Address])
)
ELSE      (0)

err what end quotes?

What exactly is your expression?

[MFGF]

Sorry, I meant to say closing parentheses. Without the second closing parentheses (bolded in the expression below), the validation throws an error. With it there, no errors occur when validating but when running the report, the error previously posted is returned and the report fails to run.

IF
(
     [Days in Step] >= 7
)
THEN   
(
    count ([Email Address])
)
ELSE      (0)

Hi,

Ok - so we now have no parsing errors in the expression itself. Let's turn to what you are trying to achieve to see if we can get to the bottom of what you need.

It looks like you might be trying to get a total of how many rows of data from within the last seven days have email addresses - am I right? If so, consider this - the expression is being validated for each consolidated row returned from your data source.
An important word here is consolidated. Cognos Analytics will automatically group and summarize detail rows from your data source. So, for example, if you have ten rows in your underlying data source with the same [Days in Step] date and the same [Email Address] value, and you bring in just these two items in your report, the report query will consolidate those ten rows into a single grouped row in your report data set.
So at this point we have some questions. If this is the case in this instance, what are you trying to count? The number of underlying rows from the data source (10) or the number of consolidated rows (1)?
Query calculations can either be performed before the group/summarize consolidation happens, or after. What confuses some authors is the fact that there isn't a property or radio button to allow you to specify which you want. So what drives the before/after aggregation timing of your calculation? The secret is in the choice of from where you bring in your items in the expression.

If you bring in items directly from the package tree, you will see the items have a three-part name in the expression, eg [namespace].[query subject].[query item]
If you bring in items from the existing query in your report, you will see the items have a one-part name in the expression, eg [query item]

If your calculation uses three-part names for the items, the timing will be before consolidation. If it uses one-part names, the timing will be after consolidation.

I'm assuming that on each row there will only ever be up to one email address? The important question is - will there always be an email address on a row or could some rows have null email address values? If they are all populated, you could perhaps just return a 1 for each row?

IF ([Step Date] >= _add_days(current_date, -7))
THEN (1)
ELSE (0)

This would obviously be done after consolidation, since [Step Date] has a one-part name in the expression. To perform this before consolidation, you would need to bring in Step Date directly from the package, and the expression would look similar to:

IF ([Your package or namespace].[Your query subject].[Step Date] >= _add_days(current_date, -7))
THEN (1)
ELSE (0)

We don't know which of these you are looking for so I have included both of them. You could then just allow the report to sum this item to get your counts?

I'm working in the dark here, since I don't know your exact requirements and I don't know your data. I'm hoping these thoughts will guide you in the right direction though - hopefully you can take these concepts and work out what you need to do to get the answer you are looking for - usually things are not as simple as the expressions above assume. You may have null or empty email address values, for example? There may be other things going on in the report that I'm not aware of that would make a big difference to how to approach this. Sadly my trusty old clairvoyance cap doesn't work well these days. All it will tell me is that you might have a name beginning with the letter A.. :)

Good luck!

MF.

[t0mato]

I really appreciate all of your help with this guys.Llet me try to explain a little better regarding what i'm trying to achieve. I would provide images but embedding images in forums like this can be tricky and I tried yesterday with no success.

Imagine this report:

Column A - Consists of different business divisions. Imagine US states, Illinois, Arkansas, etc.
Column B - Candidate Email Address associated with a particular division.
Column C - Days in Step (this is the number of days the candidate has been in one of our recruiting steps e.g. Application Received, Under Review, Interviewing, Hired, etc.)

This is a large report with thousands of lines of data.

I've grouped the report in Cognos by business division (column A), so for example it will start with Arkansas, list all of the candidate email addresses associated with that division below and their Days in Step in column C.

My goal is to create a query to look at all of the data in Days in Step and get me a count of how many of them are greater than 7.

The overall goal is simply to be able to quickly view, by division, how many candidates have Days in Step greater than 7. We want all the other Days in Step visible, but the main concern is how many of them are greater than 7.

My issue is writing the query to achieve this. So we want to count only the email addresses (i think we can alternatively just count the Days in Step that are greater than 7 as well) with Days in Step values greater than 7.

I can do this in Excel if I include the Step Date data item in the report (which is the date in which the candidate was placed in a particular recruiting step) by using the following formula: =COUNTIF(Table1[Step Date],">="&TODAY()-7)

I believe this returns a total count of all Step Dates that are larger than 7. If i can get something like this to work in Cognos, that will suffice.

Hi,

Ok - so we now have no parsing errors in the expression itself. Let's turn to what you are trying to achieve to see if we can get to the bottom of what you need.

It looks like you might be trying to get a total of how many rows of data from within the last seven days have email addresses - am I right? If so, consider this - the expression is being validated for each consolidated row returned from your data source.
An important word here is consolidated. Cognos Analytics will automatically group and summarize detail rows from your data source. So, for example, if you have ten rows in your underlying data source with the same [Days in Step] date and the same [Email Address] value, and you bring in just these two items in your report, the report query will consolidate those ten rows into a single grouped row in your report data set.
So at this point we have some questions. If this is the case in this instance, what are you trying to count? The number of underlying rows from the data source (10) or the number of consolidated rows (1)?
Query calculations can either be performed before the group/summarize consolidation happens, or after. What confuses some authors is the fact that there isn't a property or radio button to allow you to specify which you want. So what drives the before/after aggregation timing of your calculation? The secret is in the choice of from where you bring in your items in the expression.

If you bring in items directly from the package tree, you will see the items have a three-part name in the expression, eg [namespace].[query subject].[query item]
If you bring in items from the existing query in your report, you will see the items have a one-part name in the expression, eg [query item]

If your calculation uses three-part names for the items, the timing will be before consolidation. If it uses one-part names, the timing will be after consolidation.

I'm assuming that on each row there will only ever be up to one email address? The important question is - will there always be an email address on a row or could some rows have null email address values? If they are all populated, you could perhaps just return a 1 for each row?

IF ([Step Date] >= _add_days(current_date, -7))
THEN (1)
ELSE (0)

This would obviously be done after consolidation, since [Step Date] has a one-part name in the expression. To perform this before consolidation, you would need to bring in Step Date directly from the package, and the expression would look similar to:

IF ([Your package or namespace].[Your query subject].[Step Date] >= _add_days(current_date, -7))
THEN (1)
ELSE (0)

We don't know which of these you are looking for so I have included both of them. You could then just allow the report to sum this item to get your counts?

I'm working in the dark here, since I don't know your exact requirements and I don't know your data. I'm hoping these thoughts will guide you in the right direction though - hopefully you can take these concepts and work out what you need to do to get the answer you are looking for - usually things are not as simple as the expressions above assume. You may have null or empty email address values, for example? There may be other things going on in the report that I'm not aware of that would make a big difference to how to approach this. Sadly my trusty old clairvoyance cap doesn't work well these days. All it will tell me is that you might have a name beginning with the letter A.. :)

Good luck!

MF.

[MFGF]

I really appreciate all of your help with this guys.Llet me try to explain a little better regarding what i'm trying to achieve. I would provide images but embedding images in forums like this can be tricky and I tried yesterday with no success.

Imagine this report:

Column A - Consists of different business divisions. Imagine US states, Illinois, Arkansas, etc.
Column B - Candidate Email Address associated with a particular division.
Column C - Days in Step (this is the number of days the candidate has been in one of our recruiting steps e.g. Application Received, Under Review, Interviewing, Hired, etc.)

This is a large report with thousands of lines of data.

I've grouped the report in Cognos by business division (column A), so for example it will start with Arkansas, list all of the candidate email addresses associated with that division below and their Days in Step in column C.

My goal is to create a query to look at all of the data in Days in Step and get me a count of how many of them are greater than 7.

The overall goal is simply to be able to quickly view, by division, how many candidates have Days in Step greater than 7. We want all the other Days in Step visible, but the main concern is how many of them are greater than 7.

My issue is writing the query to achieve this. So we want to count only the email addresses (i think we can alternatively just count the Days in Step that are greater than 7 as well) with Days in Step values greater than 7.

I can do this in Excel if I include the Step Date data item in the report (which is the date in which the candidate was placed in a particular recruiting step) by using the following formula: =COUNTIF(Table1[Step Date],">="&TODAY()-7)

I believe this returns a total count of all Step Dates that are larger than 7. If i can get something like this to work in Cognos, that will suffice.

Hi,

Ok, so Days in Step is a number not a date?

In that case, adding a calculation

IF ([Days in Step] > 7)
THEN (1)
ELSE (0)

will return a 1 for rows with a Days in Step value greater than 7, and a 0 for rows with a Days in Step value of 7 or less. You can then just total this in the footer for each grouped division? That sounds to me like it fits your requirement?

Cheers!

MF.

[t0mato]

This indeed sounds like what i'm looking for. However, I've tried it and the report fails to run. So I create the conditional expression, add it to my table, and try to run it. Here is the error when running as CSV:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2020-08-05-13:25:22.597-#5366

Hi,

Ok, so Days in Step is a number not a date?

In that case, adding a calculation

IF ([Days in Step] > 7)
THEN (1)
ELSE (0)

will return a 1 for rows with a Days in Step value greater than 7, and a 0 for rows with a Days in Step value of 7 or less. You can then just total this in the footer for each grouped division? That sounds to me like it fits your requirement?

Cheers!

MF.

[t0mato]

It appears the error I get when running the report happens only when I add the expression

IF ([Days in Step] > 7)
THEN (1)
ELSE (0)

to the report page. So if I create the query expression, it validates with no errors, and if I don't add it to the report page, I can run the report. This is useless though because it isn't displayed in the report. When I add it to the report page, it fails to run and the error is given.

[MFGF]

This indeed sounds like what i'm looking for. However, I've tried it and the report fails to run. So I create the conditional expression, add it to my table, and try to run it. Here is the error when running as CSV:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2020-08-05-13:25:22.597-#5366

Hi,

Sadly that error isn't telling us anything of use. The CAF (Cognos Application Firewall) is preventing your user ID from seeing error messages. Either you'll need to get your Administrator to change this, or to at least tell you what is being logged.

In the meantime, what summary and detail aggregation is set for this calculated item if you look at the Properties pane for this item?

It appears the error I get when running the report happens only when I add the expression

IF ([Days in Step] > 7)
THEN (1)
ELSE (0)

to the report page. So if I create the query expression, it validates with no errors, and if I don't add it to the report page, I can run the report. This is useless though because it isn't displayed in the report. When I add it to the report page, it fails to run and the error is given.

I would expect that. If you define a calculation in your query but don't use it in the layout, Cognos is intelligent enough to realize that it doesn't need to execute the calculation to render your report - ie it tries to behave in the most efficient manner it can.

I have a sneaky feeling that your error might be to do with the defined aggregation type for the item. I'm guessing though - the error message would tell us more. However, we can look at the aggregation properties and make sure they are set correctly.

Cheers!

MF.

[t0mato]

I'm not entirely sure how to find the summary and detail aggregation for this calculated item. However, when added to the report table, I clicked the item and selected Data Format. From there, I can see that the format type is set to Default. I've changed it to Number.

If this isn't the correct place, would you mind pointing me in the right direction?

Thanks again for the help! We're getting closer and closer.

Hi,

Sadly that error isn't telling us anything of use. The CAF (Cognos Application Firewall) is preventing your user ID from seeing error messages. Either you'll need to get your Administrator to change this, or to at least tell you what is being logged.

In the meantime, what summary and detail aggregation is set for this calculated item if you look at the Properties pane for this item?

I would expect that. If you define a calculation in your query but don't use it in the layout, Cognos is intelligent enough to realize that it doesn't need to execute the calculation to render your report - ie it tries to behave in the most efficient manner it can.

I have a sneaky feeling that your error might be to do with the defined aggregation type for the item. I'm guessing though - the error message would tell us more. However, we can look at the aggregation properties and make sure they are set correctly.

Cheers!

MF.

[MFGF]

I'm not entirely sure how to find the summary and detail aggregation for this calculated item. However, when added to the report table, I clicked the item and selected Data Format. From there, I can see that the format type is set to Default. I've changed it to Number.

If this isn't the correct place, would you mind pointing me in the right direction?

Thanks again for the help! We're getting closer and closer.

Hi,

Once you have dropped the calculated item into your list, select the list column for the item, and open the Properties pane on the right (it is toggled using the Properties button on the app bar). You should see one property called Detail Aggregation, and another called Summary Aggregation. Can you tell us how both of these are set currently?

MF.