Is it possible to sort by 3 fields?

[vshapiro]

I creates parameter Sort and then formula Sort where I have something like

if {?Sort} = "Project#" then {V_PACS_PROJECT_STATUS.PROJECT_NUMBER}
else
if {?Sort} = "Open Date" then totext({PACS_PROJECT.OPEN_DATE},"yyyy-mm-dd&quot
else
if {?Sort} = "Provider Name" then {PACS_PROJECT.PROVIDER_NAME}
else
if {?Sort} = "Provider#" then {PACS_PROJECT.PROVIDER_NUMBER}
else
if {?Sort} = "Project Type" then {FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION}
else
{FACTS_DIM_FIELD_OFFICE.FIELD_OFFICE_DESCRIPTION}

I need to add a sort something like
if {?Sort} = "OfficeSort" then ({FACTS_DIM_FIELD_OFFICE.FIELD_OFFICE_DESCRIPTION},
{FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION},
{PACS_PROJECT.PROVIDER_NAME})
Is it possible to do??

 

[bdreed35]

You would need to concatenate the three fields together so that it looks at them as one value.

if {?Sort} = "OfficeSort" then
({FACTS_DIM_FIELD_OFFICE.FIELD_OFFICE_DESCRIPTION} + {FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION} +
{PACS_PROJECT.PROVIDER_NAME})

just integrate the above into your sort formula and then create a group based upon it.

~Brian

 

[rhinok]

Are you only going to have multiple sorts based on {?Sort} = 'OfficeSort'?

If so, you can't do what you want with one formula, but you can do it with three. You'll need to create @Sort1, @Sort2 and @Sort3 formulas. Your formula for @Sort is already written, but should be modified as @Sort1:

//@Sort1 - depends on ?Sort
//?Sort - Forces users to select from pick list
//Uncheck 'Allow Editing of Default Values...'
Switch
(
{?Sort} = "Project#",{V_PACS_PROJECT_STATUS.PROJECT_NUMBER},
{?Sort} = "Open Date",ToText({PACS_PROJECT.OPEN_DATE},"yyyy-mm-dd&quot,
{?Sort} = "Provider Name",{PACS_PROJECT.PROVIDER_NAME},
{?Sort} = "Provider#",{PACS_PROJECT.PROVIDER_NUMBER},
{?Sort} = "Project Type",{FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION},
{?Sort} = "OfficeSort",{FACTS_DIM_FIELD_OFFICE.FIELD_OFFICE_DESCRIPTION}
)

Next, for @Sort2 and @Sort3, write something like this:

//@Sort2
//Returns the second sort field if ?Sort = "OfficeSort"
//Returns the default value for all other Primary sort choices
Switch
(
{?Sort} = "OfficeSort",{FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION},
{?Sort} = "Project#",{V_PACS_PROJECT_STATUS.PROJECT_NUMBER},
{?Sort} = "Open Date",ToText({PACS_PROJECT.OPEN_DATE},"yyyy-mm-dd&quot,
{?Sort} = "Provider Name",{PACS_PROJECT.PROVIDER_NAME},
{?Sort} = "Provider#",{PACS_PROJECT.PROVIDER_NUMBER},
{?Sort} = "Project Type",{FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION},
)

and

//@Sort3
//Returns the third sort field if ?Sort = "OfficeSort"
//Returns the default value for all other Primary sort choices
Switch
(
{?Sort} = "OfficeSort",{PACS_PROJECT.PROVIDER_NAME},
{?Sort} = "Project#",{V_PACS_PROJECT_STATUS.PROJECT_NUMBER},
{?Sort} = "Open Date",ToText({PACS_PROJECT.OPEN_DATE},"yyyy-mm-dd&quot,
{?Sort} = "Provider Name",{PACS_PROJECT.PROVIDER_NAME},
{?Sort} = "Provider#",{PACS_PROJECT.PROVIDER_NUMBER},
{?Sort} = "Project Type",{FACTS_DIM_PROJECT_TYPE_CODE.PROJECT_TYPE_DESCRIPTION},
)

Next, select @Sort1, @Sort2 and @Sort3 as the values in your Sort Order. If {?Sort} = "Office Sort" then the report will be sorted by all three orders. If another value is selected ("Project#, for example) then all three formulas will return the same value to sort by, which means that you'll only end up with a single sort.

If you need multiple sorts, depending on the other ?Sort entries, then you can either modify the formulas to account for the combinations or use ?Sort1, ?Sort2 and Sort3 parameters.