Hello, I hope someone can help me with this because it is making me pull my hair out.
I have a string value which I am passing in a drill-through report where the string is this in the source report:
'660001,660002,660010,660014,660015'
I want to use this as a filter in the target report so need to convert that parameter to:
'660001','660002','660010','660014','660015'
Does anyone know how I can do this? At the moment I am getting errors when I try this:
case when ParamDisplayValue('pNumber')='660001,660002,660010,660014,660015' then '660001','660002','660010','660014','660015'
else 'test2'
end
You'll have to use Macro functions, specifically Split() and CSV().
Quote
split
Splits a string or string elements of the array into separate elements.
Syntax
split ( pattern_string, string_expression|array_expression )
Example 1
# csv ( split ( '::', 'ab=c::de=f::gh=i' ) ) #
Result: 'ab=c', 'de=f', 'gh=i'
Example 2
# csv ( split ( '=' , split ( '::', 'ab=c::de=f::gh=i' ) ) ) #
Result: 'ab', 'c', 'de', 'f', 'gh', 'i'
So, you are drilling from a single value in your source report, and trying to pass multiple values to your target report? I think that means that your source report has a grouping level that your target report needs. Please correct my assumptions:
In my example below I show Revenue by Country and include a list of Provinces in a single field. You want to pass States/Provs value as a single string, and transform it into an array of filter clauses for an 'in' statement?
Source Report
Country | States/Provs | Revenue |
Canada | AB, BC, MN, NF, ON, NS, ... | 6M |
United States | CA, FL, NY, NH, NJ, AK, AL, WY, WI, ... | 600M |
Do you not have a viable grouping level to represent those lower values? In my case I'm referring to the Country value.
Can you implement a table to manage those groupings?
CognoidJay is correct, in that you could do some fancy macro stuff, but you start heading into territory that is difficult to maintain. This is the essence behind building a data warehouse, so that your business users don't need to know how to navigate this complexity.
|
Hi, thank you for getting back to me. I have tried your split function suggestion:
# csv ( split ( '=' , split ( '::', '660001::660002::660010::660014::660015' ) ) ) #
However, I'm getting an error. Are you able to help me again here?:
QE-DEF-0261 QFWP - Parsing text: '660001','660002','660010','660014','660015'QE-DEF-0260 Parsing error before or near position: 10 of: "'660001',"QE-DEF-0261 QFWP - Parsing text: '660001','660002','660010','660014','660015'RSV-SRV-0042
Quote from: berks321 on 30 Aug 2016 11:07:26 AM
Hi, thank you for getting back to me. I have tried your split function suggestion:
# csv ( split ( '=' , split ( '::', '660001::660002::660010::660014::660015' ) ) ) #
However, I'm getting an error. Are you able to help me again here?:
QE-DEF-0261 QFWP - Parsing text: '660001','660002','660010','660014','660015'QE-DEF-0260 Parsing error before or near position: 10 of: "'660001',"QE-DEF-0261 QFWP - Parsing text: '660001','660002','660010','660014','660015'RSV-SRV-0042
Don't forget that the end result is a string, and therefore should be in single quotes. You're missing the SQ() function, and also have an extra Split() function. Here's the working code:
# sq(CSV(split ( '::', '660001::660002::660010::660014::660015' ) ) ) #
This is working well now - many thanks for your help.
Quote from: berks321 on 31 Aug 2016 10:55:36 AM
This is working well now - many thanks for your help.
Wonderful! My pleasure.
Jay