This discussion is archived
10 Replies Latest reply: Nov 23, 2012 4:52 AM by 952937 RSS

Reg Report subtotals in the prompt

952937 Newbie
Currently Being Moderated
Hi Experts,

We have one Multi select Product prompt and i have report like (Prod,Year,Sales) when we selecting the single product from the prompt it is showing the report of single product.

Here the requirement is when we selecting single product from the prompt it as to show the report of the product but when we select multiple products (more than one product) then it as to show the subtotals of the products in the report.

Regards,
Rafi
  • 1. Re: Reg Report subtotals in the prompt
    David_T Guru
    Currently Being Moderated
    Rafi.BI wrote:
    Hi Experts,

    We have one Multi select Product prompt and i have report like (Prod,Year,Sales) when we selecting the single product from the prompt it is showing the report of single product.

    Here the requirement is when we selecting single product from the prompt it as to show the report of the product but when we select multiple products (more than one product) then it as to show the subtotals of the products in the report.

    Regards,
    Rafi
    Do this:

    1) Don't enable the "sigma sign" total for the Table (the one at the very top left in Table View).

    2) Do enable the sigma sign on the Products column.
  • 2. Re: Reg Report subtotals in the prompt
    952937 Newbie
    Currently Being Moderated
    Hi David,

    Thanks for your reply.

    As per your post when we selecting single product also it is showing the subtotals but here my requirement is when we select single product it should not show the subtotal when we select more than one product i.e.(two or three or four products) it should the subtotals for the products in the report.

    I think the requirement is clear.

    Regards,
  • 3. Re: Reg Report subtotals in the prompt
    927557 Newbie
    Currently Being Moderated
    HI Rafi,

    check the below link

    http://obiee1000.blogspot.in/2012/08/display-report-subtotal-only-if.html

    hope it is useful.

    Regards,
    Anand
  • 4. Re: Reg Report subtotals in the prompt
    952937 Newbie
    Currently Being Moderated
    Hi User,

    Thanks for the URL here the logic has been implemented in 11G based on presentation variable but i have to do the same in 10G i tryed same logic in 10G but in the Multi select prompt it is not showing the presentation variable.

    Can any one help me how to do the same logic in 10G.

    Regards,
  • 5. Re: Reg Report subtotals in the prompt
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You might have heard selecting a view using prompt, your requirement can be done using this option; the details are:
    Try to create 2 views 1 for single product and other for multiple products and name them 1 and 2 respectively.

    Now in the prompt use one more column(with contraint option) which holds the value for number of prodcuts selected, use 1 for 1 product and 2 for more than 1.
    Once user selects product or products new column would hold the value based on the selected products and report show with required view.

    I didnt tried this in my local there might be hard to get the number value(1 or 2) based on selected products,if possible try to use rpd metric to get value 1 or 2 in the prompt.

    Let me know updates, if you like this approach or helps pls mark ;)
  • 6. Re: Reg Report subtotals in the prompt
    952937 Newbie
    Currently Being Moderated
    Hi Veeravalli,

    Thanks for your time and Sorry for the late respond.

    As per your post it is little bit hard to implement could you please elaborate step by step how to reach the requirement.

    can i have any ideas or suggestions from the gurus for my above requirement.


    Regards,

    Edited by: Rafi.BI on Nov 21, 2012 11:01 PM
  • 7. Re: Reg Report subtotals in the prompt
    Hyder Mehtawala Expert
    Currently Being Moderated
    Hi Rafi,

    For implementing similar logic in 10g which is provided in the given link, you can try the below approach.

    - As per the link, create 2 reports (one with SubTotal and one without it). Also, create an indemediate report.
    - In the intermediate report, include just one column i.e. Product and apply count function on it. i.e. change the fx to count("ProductDim"."ProdName").
    - In the filters of the intermediate report, include 2 filters - ProdName is prompted and Count(ProdName) > 1. So basically, this report will return rows if nothing is selected in the prompt or if there are more than 1 product selected in the prompt. If only single product is selected, this report would return no rows.
    - In both the other reports, just have the ProdName column is prompted.
    - Now place the two reports in different sections on the dashboard and apply guided navigation based on the intermediate report.

    Let me know if this helps.

    Thanks
  • 8. Re: Reg Report subtotals in the prompt
    952937 Newbie
    Currently Being Moderated
    Hi HM,

    I implemented the steps as per your previous post that creating 2 reports(with and without subtotals) and conditional request and in guided navigation for first section i have select the source(Conditional report) and If request returns rows
    and for the second section guided navigation i have select source(Conditional report) and if report returns nothing.

    But in the report when i am select more than one product it is not showing the subtotals of the products of the report.

    Thanks in advance,
  • 9. Re: Reg Report subtotals in the prompt
    Hyder Mehtawala Expert
    Currently Being Moderated
    Hi Rafi,

    In the intermediate report ensure you properly follow the below steps as mentioned in the above post.

    - In the intermediate report, include just one column i.e. Product and apply count function on it. i.e. change the fx to count("ProductDim"."ProdName").
    - In the filters of the intermediate report, include 2 filters - ProdName is prompted and Count(ProdName) > 1. So basically, this report will return rows if nothing is selected in the prompt or if there are more than 1 product selected in the prompt. If only single product is selected, this report would return no rows.

    To test out whether the intermediate report is behaving the right way, add the report in another section of the dashboard. Ideally, when you select only one product this report should not return any rows. If you select more than one product it should return a single row with the selected product count. This way you should be able to debug the issue.

    Thanks
  • 10. Re: Reg Report subtotals in the prompt
    952937 Newbie
    Currently Being Moderated
    Hi HM,

    Thanks for your time.

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points