Skip to Content
0
Former Member
Apr 29, 2012 at 12:17 AM

Single parameter for multiple dimension?

20 Views

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