Using age ranges as parameters in crosstabs

[janet_donbavand]

I would like to be able to change the columns in my crosstab depending on the age ranges specified in my parameters.

One time I might select parameters Under 30, 30-40, 41-50, 51-60, Over 60, then on another occasion I would like to choose Under 25, 25-35, 36-45, etc. Is there a way to do this. I have set up the parameters but am unsure how I can relate this to my crosstab columns.

Thanks in advance.

[abhilash_kumar · Accepted solution]

Hi Janet,

How is the parameter set up? Is it a Range prompt or a multi value string prompt?

E,g: Do you type in '30-40' as one of the values?

-Abhilash

[janet_donbavand · Accepted solution]

I am setting up parameters as enter first age in range1, enter end age in range 1, enter first age in range 2, enter end age in range 2, etc. So multiple parameters.

[janet_donbavand · Accepted solution]

I guess what I'm really trying to work out is whether I can use parameters to create specified groups.

[abhilash_kumar · Accepted solution]

Why do you have multiple range prompts?

How many of these do you have actually have?

-Abhilash

[janet_donbavand · Accepted solution]

The reason is so that we would like to produce crosstabs where the ranges we choose in the parameters are reflected as the column groupings in the crosstab. So that we could on one occasion choose under 30, 31-45, 45- 60, 60+ and another time say Under 30, 31 - 50, 50 - 70, 70+ etc. Really to give flexibility to the crosstab rather than just one set of specified groupings.

[abhilash_kumar · Accepted solution]

Could you post a screenshot of the full prompt screen please?

-Abhilash

[janet_donbavand · Accepted solution]

[abhilash_kumar · Accepted solution]

Hi Janet,

Here's what you need to do:

1) Create a formula with this code:

//Replace {?Range_Prompt} with the prompt name you have in the report

local numbervar i;

local stringvar s;

for i := 1 to ubound({?Range_Prompt}) do

(

    If Maximum({?Range_Prompt}[i]) = 0 AND

     {Customer.Customer ID} >= Maximum({?Range_Prompt}[i]) then

    (

        s := 'Over ' & ToText(Minimum({?Range_Prompt}[i]),'#');

        Exit For;

    )

    Else If {Customer.Customer ID} IN [Minimum({?Range_Prompt}[i]) TO Maximum({?Range_Prompt}[i])] then

    (

        s := ToText(Minimum({?Range_Prompt}[i]),'#') & ' - ' & ToText(Maximum({?Range_Prompt}[i]),'#');

        Exit For;

    );

);

s;

2) Remove the existing field under Columns and add this formula field instead

Hope this helps.

-Abhilash

[janet_donbavand · Accepted solution]

Many thanks Abhilash - I think that does the trick - you are a star!