cancel
Showing results for 
Search instead for 
Did you mean: 

Specified Group Order

Former Member
0 Kudos

Hi All,

I have a field called brand category.I applied group on this and the order is original order ,but with that original order as in database i need some specified order

Ex:

brand_codecategoryBrand_Category
G-HOMEDebenhams-HOME
G-MENS ACCESSORIESDebenhams-MENS ACCESSORIES
G-HOME CONCESSIONSDebenhams-HOME CONCESSIONS
G-MENS WEARDebenhams-MENS WEAR
G-KIDS WEARDebenhams-KIDS WEAR
G-OTHERSDebenhams-OTHERS
G-LADIES ACC OBDebenhams-LADIES ACC OB
G-OWN BOUGHTDebenhams-OWN BOUGHT
G-LADIESWEAR OBDebenhams-LADIESWEAR OB
G-WOMENS WEARDebenhams-WOMENS WEAR
G-ACCESSORIESDebenhams-ACCESSORIES
G-LINGERIEDebenhams-LINGERIE
LFOTHERSFOOT LOCKER-OTHERS
OCOTHERSCoast-OTHERS
SVOTHERSVictoria's Secret-OTHERS
TWACCESSORIESBrand Warehouse-ACCESSORIES
TWCLOTHINGBrand Warehouse-CLOTHING
TWOTHERSBrand Warehouse-OTHERS
Z0CARTIERDebenhams Cosmetics-CARTIER
Z0RIMMELDebenhams Cosmetics-RIMMEL
Z0CLINIQUEDebenhams Cosmetics-CLINIQUE
Z0VERSACEDebenhams Cosmetics-VERSACE
Z0GIORGIO ARMANIDebenhams Cosmetics-GIORGIO ARMANI
Z0JUICY COUTUREDebenhams Cosmetics-JUICY COUTURE
Z0LOREALDebenhams Cosmetics-LOREAL
Z0BOGARTDebenhams Cosmetics-BOGART
Z0NARCISO RODRIGUEZDebenhams Cosmetics-NARCISO RODRIGUEZ
Z0CHANELDebenhams Cosmetics-CHANEL
Z0SHISEIDODebenhams Cosmetics-SHISEIDO
Z0DAVIDOFFDebenhams Cosmetics-DAVIDOFF
Z0YOHJIDebenhams Cosmetics-YOHJI
Z0GIVENCHYDebenhams Cosmetics-GIVENCHY
Z0KANEBODebenhams Cosmetics-KANEBO
Z0MAKE UP FOR EVERDebenhams Cosmetics-MAKE UP FOR EVER
Z0BOURJOISDebenhams Cosmetics-BOURJOIS
Z0OTHERSDebenhams Cosmetics-OTHERS
Z0CHLOEDebenhams Cosmetics-CHLOE
Z0ST. DUPONTDebenhams Cosmetics-ST. DUPONT
Z0DOLCE & GABBANADebenhams Cosmetics-DOLCE & GABBANA
Z0YSLDebenhams Cosmetics-YSL
Z0GUCCIDebenhams Cosmetics-GUCCI
Z0LA PRAIRIEDebenhams Cosmetics-LA PRAIRIE
Z0MAX FACTORDebenhams Cosmetics-MAX FACTOR
Z0BURBERRYDebenhams Cosmetics-BURBERRY
Z0PACO RABANNEDebenhams Cosmetics-PACO RABANNE
Z0CHOPARDDebenhams Cosmetics-CHOPARD
Z0THIERRY MUGLERDebenhams Cosmetics-THIERRY MUGLER
Z0DUNHILLDebenhams Cosmetics-DUNHILL
Z0GUERLAINDebenhams Cosmetics-GUERLAIN
Z0LALIQUEDebenhams Cosmetics-LALIQUE
Z0MAYBELLINEDebenhams Cosmetics-MAYBELLINE
Z0BVLGARIDebenhams Cosmetics-BVLGARI
Z0PUPADebenhams Cosmetics-PUPA
Z0CHRISTIAN DIORDebenhams Cosmetics-CHRISTIAN DIOR
Z0U MUJERDebenhams Cosmetics-U MUJER
Z0ESCADADebenhams Cosmetics-ESCADA
Z0HELENA RUBINSTEINDebenhams Cosmetics-HELENA RUBINSTEIN
Z0LANCOMEDebenhams Cosmetics-LANCOME
Z0MICALLEFDebenhams Cosmetics-MICALLEF
Z0CAROLINA HERRERADebenhams Cosmetics-CAROLINA HERRERA
Z0RALPH LAURENDebenhams Cosmetics-RALPH LAUREN
Z0CLARINSDebenhams Cosmetics-CLARINS
Z0VAN CLEEF & ARPELSDebenhams Cosmetics-VAN CLEEF & ARPELS
Z0ESCENTRIC MOLECULESDebenhams Cosmetics-ESCENTRIC MOLECULES
Z0HERMESDebenhams Cosmetics-HERMES
Z0LOEWEDebenhams Cosmetics-LOEWE
Z0BOBBI BROWNDebenhams Cosmetics-BOBBI BROWN
Z0MORRISDebenhams Cosmetics-MORRIS

i need like this


Debenhams-ACCESSORIES

Debenhams-LINGERIE

Debenhams-HOME

and then same order as in database

.................

Debenhams Cosmetics-JUICY COUTURE

Debenhams Cosmetics-LOEWE

Debenhams Cosmetics-BOGART

............

and then same as in database

Accepted Solutions (0)

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Francis,

Both Prathamesh and Samuel have suggested the right approaches. Here is another way to do this:

1) Create two array variables. Each array variable will hold custom values. In this case, the formula can look like this:

stringvar array grp1 := ["Debenhams-ACCESSORIES","Debenhams-LINGERIE","Debenhams-HOME"];

stringvar array grp2 := ["Debenhams Cosmetics-JUICY COUTURE", "Debenhams Cosmetics-LOEWE", "Debenhams Cosmetics-BOGART"];

""

So, array variable grp1 will hold names in the order that you wish to be shown at the beginning. Similarly array variable grp2 contains values in the order that you want them to appear towards the end.

2) Create a formula that will put the Brand Names in separate groups:

if {Brand_Category} in ["Debenhams-ACCESSORIES","Debenhams-LINGERIE","Debenhams-HOME"] then

"A"

else if {Brand_Category} in ["Debenhams Cosmetics-JUICY COUTURE", "Debenhams Cosmetics-LOEWE", "Debenhams Cosmetics-BOGART"] then

"C"

else

"B"

3) Insert a group on the above formula from the Group Expert. You should see three Groups - A, B and C on the report

4) Insert a 2nd group on the Brand Category field. While in the same window,select the 2nd group and click the Options button > Choose "Original Order" as the Sort Order

5) The report now shows Brand Categories lined up as per the database order. This also re-organizes the records under Group A and C in the database order, however you want these records to be shown in the same order as it appears in the array variable, right?

6) Create another formula that displays the values in the array and place this on the Group Header 2:

WhilePrintingRecords;

stringvar array grp1;

stringvar array grp2;

numbervar x;

if GroupName ({@Name_of_the_formula_created_in_step2}) = "A" then

(

    x := x + 1;

    grp1[x];

)

else if GroupName ({@Name_of_the_formula_created_in_step2}) = "C" then

(

    x := x + 1;

    grp2[x];

)

else

{Brand_Category}

7) One last formula to reset a variable. Place this formula on the Group Header 1:

WhilePrintingRecords;

numbervar x := 0;

😎 You can then go ahead and delete the "Group #2 Name" text object from Group Header 2 and just have the display formula created in Step 6 show the Group Name. You will also need to Suppress Group Header 1, Group Footer 1 and Group Footer 2

This will now show up as a combination of both Specified Order and Original Order grouping.

Let me know how this goes.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

i tried what you said but i am getting an error ,PFA

Former Member
0 Kudos

Hi Abhilash,

Can you please clarify above error.

And now my report connected to one database,but we have another database having diff brand categories there also we have to display the specified order.

In live there are different databases.Then for every database they have some brand categories.Suppose they have 10 different databases then how we can apply this specified order for all those values.If tomorrow another new database came what we have to do.Please suggest.

Thanks,

Francis

abhilash_kumar
Active Contributor
0 Kudos

Remove this formula from the report. Do you see the three groups A, B and C?

Also, the number of values in the Group A and the number of Brands you put in the grp1 variable in the 1st group should match.

When you create a specified order, you assign some static values to a set or names. You either make these values dynamic by finding out which names need to go in which group and then create the array.

If there are multiple databases then this might not work since "Debenhams-ACCESSORIES" might not be available in some other table. In such cases you would need to find out which values belong to let's say group A. For e.g: all values beginning with Debenhams go into this group etc.

-Abhilash

Former Member
0 Kudos

I think, best approach would be to use sub reports. This will not only simplify dealing with multiple databases but also avoid dependence on hard-coding the group-name values.

-Prathamesh

abhilash_kumar
Active Contributor
0 Kudos

You still have the same issue with the Subreports. You will still need to separate Brand Names manually since one particular brand might not be available in a particular table on another database.

There has to be some 'method' to identify which values or what type of values will 'always' have a specified order while the rest will be in Original Order.

If we have that then we can either create a generic formula and use my approach or use the Subreport path.

I believe Francis has quite a few options here and it is he who needs to identify which values have a particular sequence. You can't expect randon values to follow an order and also expect them to work for all databases.

-Abhilash

Former Member
0 Kudos

Hi Francis,

If this is understood correctly, you need a specified order and original order grouping in combination.

In that case, it is better to segregate the required brand-categories in 2 separate sub reports OR a single main report with a sub report.

Having 2 separate sub reports is much preferable since it would be easier to handle the passing of parameter values and other calculations between the main report and the 2 sub's.

Please let the forum  know if you need more information.

Thanks,

Prathamesh

Former Member
0 Kudos

I am unsure if subreports will help or just complicate the problem.

For me, it is all about how you wish to display the data once the new grouping is made.

Using a formula such as the one below can acheive the order you want but you may need to insert another group of the original brand category.

To avoid this, and to give you a top level group per brand category, you can concatenate the original 'useful' brand name to the numbers (using '001' + {Sheet1_.Brand_Category} instead of just '001' for example). This gives you a better first level grouping for a helpful group tree.

Using the formula below, and grouping on this instead of brand category as you currently are, will mean you need to work out where the non-Debenhams or Debenhams Cosmetics entries go.

Let me know if you need any more info on this.

IF
    {Sheet1_.Brand_Category} = 'Debenhams-ACCESSORIES'
THEN
    '001'
ELSE

IF
    {Sheet1_.Brand_Category} = 'Debenhams-LINGERIE'
THEN
    '002'
ELSE

IF
    {Sheet1_.Brand_Category} = 'Debenhams-HOME'
THEN
    '003'
ELSE

IF
    {Sheet1_.Brand_Category} startswith 'Debenhams-'
THEN
    '004'
ELSE

IF
    {Sheet1_.Brand_Category} = 'Debenhams Cosmetics-JUICY COUTURE'
THEN
    '005'
ELSE

IF
    {Sheet1_.Brand_Category} = 'Debenhams Cosmetics-LOEWE'
THEN
    '006'
ELSE

IF
    {Sheet1_.Brand_Category} = 'Debenhams Cosmetics-BOGART'
THEN
    '007'
ELSE

IF
    {Sheet1_.Brand_Category} startswith 'Debenhams Cosmetics-'
THEN
    '008'
ELSE
    '!ZZZ'