How to create a prompt, Year-Month format, in analysis while not having a "Month" dimension?

Question

Summary

The goal is to enable end users to look at an analysis by year-month periods (ex. YYYY-MM, 2020-01), but "Month" dimension is not available within the subject area.

Content

Hello Oracle Community,

Do you know in analysis whether there is a method to create a Year-Month format (YYYY-MM, ex.2020-01) prompt without the availability of a Year-Month dimension within the subject area?  Condition displayed in enclosed screenshot.

The goal is to enable end users to view an analysis in different year-month periods by selecting them on a drop-down prompt.  In the subject area, the only date type dimension available is "Account Established Date", that comes in DD/MM/YYYY format.  Dropping this dimension in prompt section only limit selection to each event date, but not at a upper tier of "Year-Month".

I do not have access to edit RPD.  Time dimensions from other subject areas could not be "lend" to this subject area because there are no other subject areas that could be successfully connected to this subject area with the  "Show Total value for all measures on unrelated dimensions." option under "Advanced SQL Clauses".

Thank you.

Version

Oracle Business Intelligence 11.1.1.9.0

Jonathan Chan gmg · Answer

Hello Dimple,

Thank you for the detail explanation.

Will look to the alternative syntax for more references.

Stay healthy and safe.

Dimple-Nagesh-Support-Oracle · Answer

Hi Jonathan,

Good Morning!!

The formulae COUNT("Customer Account Details"."Customer Account Number" BY "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_") is getting the count of Number of Customers (Customer Account Number) based on BU's (HZ_CUST_ACCOUNTS_CUSTACC12_)

The BY clause is used to perform the aggregation at specified level.

And when you remove the HZ_CUST_ACCOUNTS_CUSTACC12_ from the formuale, it will provide the count of Number of customers based on HZ_CUST_ACCOUNTS_CUSTACC18_

You may get more details on Alternative Syntax: https://docs.oracle.com/middleware/bi12214/biee/BIESQ/toc.htm#GUID-E4D7E4A8-FB76-422C-B517-1E1C1360F0D0

Jonathan Chan gmg · Answer

Hello Dimple,

Apologize for my delayed response.

No, with regards to the followup question posted on 31-Mar.

Now I want to find out if there is a way to set the selected columns so they are in sync with filter-prompt results, without having to write formulas involving the "by" command.

For example, to create a column that shows the number of new customer account opened per brand per sales person, the following formula ensues:

COUNT("Customer Account Details"."Customer Account Number" BY "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_")

Dimension index:

1) ...Account Number = Customer Account Number

2) ...ACCOUNTS_CUSTACC12 = Brand code

3) ...ACCOUNTS_CUSTACC18_ = Sales person code

I wonder is there a way in which "...ACCOUNTS_CUSTACC12" (Brand code) does not need to be included in the formula, and account number count will automatically be calculated when filter-prompt is filter to a particular brand?  Currently, the column will sum the total amount of new customer account across all brands if command "...by...ACCOUNTS_CUSTACC12" is not included, despite filter-prompt is set to show the result of one single brand only.

Thank you.