Monday, July 2, 2012

Advanced Custom Grouping based on Prompt

How do you display prompted elements on a report, and at the same time display all those not selected as a single Element called "Other"



Eg.
Country Prompt Answers: (India, USA)

Report:
Country                                            Sales
India                                               10000
USA                                                10000
Other                                              90000


I saw this question on the MSTR forum.
https://resource.microstrategy.com/Forum/ReplyListPage.aspx?id=21981


The trick here will be to create a custom group based on two “Shortcut to report” qualifications. One report will contain the selected elements, the other will contain the rest.

 1. Create Element Prompt for Country (I called mine “Country Prompt”)








2. Create a new Report (This report will contain the selected Countries)
     a. Drag your prompt onto the Local Filter section
 
 
    b. Add Country and Sales to the template
     c. When you run it, you should see only the selected countries

3. Make a copy the above report (This report will contain the other Countries)
    a. Run the report and select some elements (It will look the same as the one above)
    b. Go to design view and you will see that the filter criteria can now be edited
            i. It should look like “Country in list (India, USA)”
    c. Double click the filter and change “In list” to “Not in List”


    d. Save the report making sure you select the option to have the report prompted

4. Now Create a new Custom Group
     a. Drag your first report onto Element 1
     b. Drag your second report onto Element 2


     c. I renamed my elements: 1 – Selected Countries; 2 – Other
     d. To get the groups the way you want them to display
           i. Menu bar >> Custom Group Options
           ii. Un check the “Enable Hierarchical Display”
           iii. Right click Element 1 >> Show display options >> radio button 2
           iv. Right click Element 2 >> Show display options >> radio button 1

5. Now create another report (That will contain your results)
    a. Drag the Custom Group object onto the rows, drag Sales metric onto Columns
    b. Run Report, answer the prompts c. This should give you what you need

No comments:

Post a Comment