Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Tuesday 31 May 2011

Passing multiple values in a single Cognos Report Studio value prompt

I found a way to pass multiple value combinations through in a single Cognos Report Studio static choices prompt.  Here I describe the generic approach using just two values passed in each available choice.

In a value prompt (P_Prompt) static DisplayValue and UseValue choices are defined as:

DisplayValue   UseValue
Choice 1:          ~1[Prompt value 1],~2[Prompt value 2]
Choice 2:          ~1[Prompt value 2],~2[Prompt value 3]

The UseValues comprise two Member Unique Name (MUN) tokens separated by a comma and identified with a leading ~1 or ~2 (which are unique character combinations).

The key to the approach is the macro expression #join(',',substitute('~[n]','',grep('~[n]',array(split(',',prompt('P_Prompt','token'))))))# which returns the [n]th token identified in the [n] value choice made.

Here’s a description of how the macro works from the inside out.  The UseValue chosen in response to the prompt is returned as a token. The split macro creates multiple tokens from the single token returned using a comma as the delimiter.  The multiple tokens are placed in an array using the array macro.  The grep macro identifies the single array element containing the ~[n] search item specified and the substitute macro removes the ~[n] identifier from the token.  The join macro converts the array item (now only one remaining) back into a string for use as a MUN.

If I choose the DisplayValue “Choice 1” when running a report, the UseValue “~1[Prompt value 1],~2[Prompt value 2]” will be available to the underlying report.  The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 1] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report.

Similarly, if I choose the DisplayValue “Choice 2” when running a report, the UseValue “~1[Prompt value 2],~2[Prompt value 3]” will be available to the underlying report.  The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 3] within the report.

Maybe others have found another way to achieve the same result.  If so, I’d be interested in how you’ve done it.

7 comments:

  1. Hi I'm receiving error message, QE-DEF-0396 The search_pattern '~[n]' argument to macro function 'grep' is not valid. Please let me know where i'm doing wrong

    ReplyDelete
    Replies
    1. vijay,

      From the error message it looks like you have entered '~[n]' in the macro. The intent is that you replace anything between square brackets with the required values. For example you might have:

      In a value prompt (P_Prompt) static DisplayValue and UseValue choices defined as:
      DisplayValue UseValue
      Choice 1: ~1A,~2B
      Choice 2: ~1B,~2C

      If you select choice 2 when running the report then #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return B within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return C within the report.

      Does this make it any clearer?

      Delete
  2. Thanks for the explanation.

    ReplyDelete
  3. Can this be used with a promptmany rather than a prompt? I need to parse a field into three parts to be used for three different filters (from a single prompt). The user can pick multiple values. I'm using code like this...... [field from database] in (#join(',',substitute('~1','',grep('~1',array(split(',',promptmany('P_Prompt','token'))))))# ).

    I get a java.language.null.pointer error. Any ideas on what I am doing wrong. It's Cognos 10.2.

    ReplyDelete
    Replies
    1. I'm not working with Report Studio at the moment so I'm not able to test anything out. I think the token returned by the promptmany function will be key to solving this.

      If I understand it correctly, the sort of thing you envisage is that (using my earlier example) the user could choose both choices 1 and 2 in which case you'd have ~1 bringing back A and B and ~2 bringing back B and C. You'd probably have additional choices defined too. Is that correct?

      In my example the token returned would look something like ~1A,~2B. If you can you show me what a two choice promptmany token will look like I might be able to figure out what to do. Can you post back an example token?

      Delete
    2. Perhaps the separator needs to be changed from "," to ";". See http://www.cognoise.com/index.php?topic=22355.0

      Regards,
      Ian

      Delete
  4. This comment has been removed by a blog administrator.

    ReplyDelete