Assistance with macro function promptmany expression

[borednpcuser]

Hello Cognoise forum members,

Reaching out about an issue regarding, macro function, promptmany. Using IBM Cognos Analytics version 11.1.7.

There is a data item that uses the expression definition ?Select_Period?
 
Looking to substitute this with the macro function, promptman.
To allow multiple inputs being selected from the Period Prompt from our end users.

Query:
[Year]
[Period]

Input for: [Period]
07, 08
 
Expected Output:
[Year] [Period]
2024   07
2024   08

Ran into an issue where the macro function won't accept multiple inputs.
But this accepts single inputs, with no issue.

Each time enter a secondary input, would reach this error:
XQE-V5-0017 V5 syntax error found for data item 'Period' of query 'period test', invalid token ";" found after "('07'".
RSV-SRV-0042
Trace back:

[Period] = (#promptmany('Select_Period', '', '')#)

Any ideas how to refine this macro function expression to allow for multiple inputs?

[MFGF]

Hello Cognoise forum members,

Reaching out about an issue regarding, macro function, promptmany. Using IBM Cognos Analytics version 11.1.7.

There is a data item that uses the expression definition ?Select_Period?
 
Looking to substitute this with the macro function, promptman.
To allow multiple inputs being selected from the Period Prompt from our end users.

Query:
[Year]
[Period]

Input for: [Period]
07, 08
 
Expected Output:
[Year] [Period]
2024   07
2024   08

Ran into an issue where the macro function won't accept multiple inputs.
But this accepts single inputs, with no issue.

Each time enter a secondary input, would reach this error:
XQE-V5-0017 V5 syntax error found for data item 'Period' of query 'period test', invalid token ";" found after "('07'".
RSV-SRV-0042
Trace back:

[Period] = (#promptmany('Select_Period', '', '')#)

Any ideas how to refine this macro function expression to allow for multiple inputs?

Hi,

Your data item can only contain a single value per row of data, which is where I think things are going awry here. Is there a reason you have the expression in a data item rather than using it in a filter?
You could, for example, add a filter with the expression

[Period] in (?Select_Period?)

Would that work for you?

Cheers!

MF.

[borednpcuser]

Is there a reason you have the expression in a data item rather than using it in a filter?

Additional information, from my knowledge the report was developed by a consultant, nearly a decade ago.

After upgrading to Analytics, we noticed this report taking way to long to run. Discovered with our other reports, by changing the reports package to its analytics package counterpart. Reports run faster. This meant each time changing to its analytics report package counterpart, had to locate each data item that was used in the reports. Since analytics package counterpart, had changed the path for each data item.

Applied this report package change to this report, and while in this process of hunting down each data item, discovered the period prompts selection style had changed from multiple selection to single selection and now the calculation totals were as you best expressed it, going awry.

Original report primarily uses three queries.

Main: Union'd by Query1 & Query2
[Year]
[Period]
[Period Debit]
[Period Credit]

Query1: Activity
[Year] Expression Definition = Package.Transction_Year
[Period] Expression Definition = Package.Transction_Period
[Period Debit] Expression Definition =
case
when [Period] in ?Select_Period?
then Package.Transction_Debit
end

[Period Credit] Expression Definition =
case
when [Period] in ?Select_Period?
then Package.Transction_Credit
end

Query2: Activity Placeholder
[Year] Expression Definition = Package.Transction_Year
[Period] Expression Definition = ?Select_Period?
[Period Debit] Expression Definition = 0
[Period Credit] Expression Definition = 0

None of these queries had used the [Period] in ?Select_Period? in its Detail Filter pane.

Your data item can only contain a single value per row of data, which is where I think things are going awry here.

[Period] in (?Select_Period?)

Would that work for you?

After some digging, lead me to [Period] used in Query2.
Setting [Period] Expression Definition to Package.Transction_Period; and adding [Period] in ?Select_Period? in its Detail Filter pane. Doing this still resulted in the report producing indifferent calculation value totals and a single selection for the Period prompt.

Ultimately this has lead me to the macro function promptmany to use in place of ?Select_Period? in the Expression Definition.
It does revert the Period prompt back to multiple selection, but it currently only accepts single input/selection, such as ex: 07.
If an input/select more than one value at a time (07, 08), an error would occur. Not quite familar with using promptmany.

[Period] = (#promptmany('Select_Period', '', '')#)

Any ideas how to refine the function to accept multiple period values? or perhaps an alternative approach that does not involve macro functions?

[dougp]

After upgrading to Analytics, we noticed this report taking way to long to run. Discovered with our other reports, by changing the reports package to its analytics package counterpart. Reports run faster. This meant each time changing to its analytics report package counterpart, had to locate each data item that was used in the reports. Since analytics package counterpart, had changed the path for each data item.

What does that mean?

Upgrading to Analytics = Upgrading to Cognos Analytics 11.x?  From what?

What is an "analytics package counterpart"?  A package is a package.  Packages are not modified during the upgrade process.  Packages continue to work the same across Cognos versions.

[borednpcuser]

Hi dougp,

Upgrading to Analytics = Upgrading to Cognos Analytics 11.x?  From what?

We recently upgraded from 10.2.2 to 11.1.7

What is an "analytics package counterpart"?  A package is a package.

In 10.2.2 were using SPS Analytics packages. When upgraded to 11.1.7, Finance Enterprise packages became available.

Packages are not modified during the upgrade process.  Packages continue to work the same across Cognos versions.

Yes, can confirm SPS Analytics packages were not modified and continue to work in 11.1.7. But we noticed some of our reports using SPS Analytic packages, ran slower. Found, by changing the report package to their Finance Enterprise package counterpart, ex: SPS Budget -> Finance Enterprise Budget. Report ran faster. Drawback, sometimes data items path were not always the same between the two packages; so had to hunt them down. This particular report, ran exceptionally slow, and used a different package, not SPS. Only changes made after changing report package, was just correcting the path(s) for any existing data item that was already in the report.

Discovered the Period prompt had switched from check box group to radio button group.
Which lead me to believe the culprit is the expression being used in the data item, ?Select_Period?
Tried using the expression in the Detail Filter, but that ultimately changed some of the totals in the Main query.

Searched for an alternative solution, came across promptmany macro function.
But my current setup for promptmany, keeps coming up with an error, when more than one input is entered in.
XQE-V5-0017 V5 syntax error found for data item 'Period' of query 'period test', invalid token ";" found after "('07'".

[dougp]

Regarding checkbox vs. radio button:  That's a difference between

Code Select Expand

IN and

Code Select Expand

=.  It definitely changes what the expression is expecting from the parameter.

I have been using IBM Cognos Business Intelligence and IBM Cognos Analytics for about 10 years.  I have never seen SPS Analytics packages or Finance Enterprise packages.  Are you sure you are using IBM Cognos Analytics and not something else - maybe IBM Finance Analytics?

[cognostechie]

= is for single select only and in is for multi-select.

When you write a SQL, you would write where [Period] = '07'  for selecting only one value. For selecting multiple values, you would write where [Period] in ('07,'08')

So, for the promptmany macro the expression would be:

[Period] in ( #promptmany('Select_Period','int')# )      - if your Period datatype is integer
[Period] in ( #promptmany('Select_Period','string')# )   - if your Period datatype is character

 

[cognostechie]

By the way, the macro should only be used when you are hand-writing a SQL and using that SQL to provide data to the report query. The macro should be used only inside the SQL. If you are creating a filter in the repot query then a filter should be used such as [Period] in ?Select Period?