Skip to Content
author's profile photo Former Member
Former Member

Single parameter for multiple dimension?

Hi All,

I'm asked to provide a WEBI report to display the sales revenue, deliveries, etc. based on fiscal year and the sales org. As we are not using SAP BW, so I created a Z table to store all the order info like sales order number, customer, sales person , order date, sales amount, latest deliver date, delivery amount, etc. with primary key ordernumber. But I found I got a problem to define the query in WEBI, our users want to see FY2012's revenue and FY2012's delivery in the same report in two columns, here the fiscal year is a parameter.

If I define parameter orderFY = 2012 to filter order date, the sales revenue is correct but delivery data is far lower than expected data as it will restrict delivery data only for sales order in 2012, but many delvs are based on orders from previous year which are not selected. So I try to use the statement ( orderFY = @prompt('FY','orderFY',mono,,,) or ( delvFY = @prompt('FY','orderFY',mono,,,)), but then revenue data for delv from 2011 will be added to revenue of 2012, which is incorrect too.

I also tried to create a new query in WEBI report with the same dimension as the first query and combine them together, but then there will have two parameters, user must choose both orderFY=2012 and delvFY=2012, considering we have different dimensions and measures like org data and invoiceFY, it will duplicate many unnessary parameters. If I define them with the same parameter name it seems can work, but I found if I do so, I can never open the query screen, I guess it's because of the name conflict, so it's not the correct way.

Basically my requirement is to filter multiple database columns from one parameter in WEBI and sum results to the correct dimensions, As this is a common requirement, so I'm sure there have solutions. But I'm new to this, so would you please share your solutions? I've been stuck for several days, Thank you very much.

Best Regards,

Jeff

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 01, 2012 at 07:38 AM

    Hi Jeff,

    This can be achieved by forming 2 queries and combining them using Union or minus based on requirement.

    Create separate query for Sales Revenue alone and separate query for delivery and use combination of union or intersection or minus based on situation to get the result.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30, 2012 at 12:04 PM

    Hi Jeff,

    Yes,it is possible through the CascadingPrompt in the Universe Level..

    It ispossible through OracleDB. But if ur using in SAP BW universe i have no idea in it..

    Regards,

    Ravi

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.