WEBI on Infocube

[Former Member]

Dear All,

Kindly guide me on the following.

My requirement is i need to create a webi report with ageing days, like 0-30 31-60, 61-90....and so on.

I have created a relational database connection using IDT in SAP BO 4.0 using a CUBE and have got all the data.

In this data i have comp code, customer, debit, credit and doc date and posting date.

1. I need to create a date object(prompt or any variable), which should accept the input date from the user and also need to create varaibles like     0-30, 31-60.61-90......till 180.

2. Based on input date, i need to calculate the amounts and put into the variables 0-30...31-60...61-90.....till180.

Kindly assist me on the following:

1, How and where(IDT level or Webi Report level)  to create a prompt for user input date, i created a prompt and also dimension object on date  but when i published the .blx file to repository, its giving error while running the webi report.

2. How to calculate the ageing days, can anyone recommend any formula suggestions.

Thanks,

A Sustainer

[sateesh_kumar1 · Accepted solution]

Hi ,

Daysbetween,

=DaysBetween([GR Date];lastexecutiondate()) In ([Dimension:Sales Document])

0 - 30 Days

=Sum([Pending PO Qty MT] Where ([DaysBetween]Between(0;30)))

31-60 Days

=Sum([Pending PO Qty MT] Where ([DaysBetween]Between(31;60)))

and so on ..

You can have date prompt at webi report itself.

Note : it is best practice to use BEx Query based reporting , we can easily create Variables and ageing buckets in BEx and move the processing time to Database side .So, reporting will be fast.

[Former Member · Accepted solution]

Dear Sathish,

I have now created a Bex query with ageing slabs and its working perfectly.

I have few concerns, which are as follows:

1. my bex query is based on Key Date format( ex: 31.12.2014).

2. Target is Business Object Dashboard and in Dashboard my design is as follows:

Month(Filter) Year(Filter) Comp Code(Filter) and few other.

My Concern is, when i input the Month (Ex: 12) year (Ex: 2014) from Dashboard, but there is no date in my dashboard requirement.

It means from front end (Dashboard) i am passing 12.2014 (month and year) where as for a query execution date format(31.12.2014) is required.

So i am not able to understand how to achieve this and what should be my approach.

OR

Otherwise, is it possible, instead of key date as a filter in Bex query, I will create 2 other prompts, with Input as Month and Year, Which in turn submits or fills Key Date (through exit) and all my calculations will again work perfectly in Bex, so that when user selects month and year in front end(dashboard), this will become the input to Bex query and in turn when these 2 variables goes to exit, it will become Key date.

OR

it it possible in dashboard, when user selects Month and Year, can i write a formula in Excel and converts the Month and Year into a date format and submit it to the Bex Query.

Kindly let me know the  best approach.

Regards,

A Sustainer

[sateesh_kumar1 · Accepted solution]

Hi ,

It is good to have month ,year filter created on BEx and utilise it in dashboard.

Create a variable on 0CALMONTH2 , read last date of the month from that .

pass this value to calendar exit in the report with less than equal operator.

[Former Member · Accepted solution]

Hi,

I am facing 1 issue which is as follows:

Requirement changed little bit,

I need to pass date month and year from dashboard.

Ex: I have prompt key date in bex and same by default becoming as prompt in Dashboard, but the format is ex: 31.12.2014, but in dashboard i have 3 filters, date, month and year.

How to pass these 3 inputs(date, month and year) as 1 date to the bex query.

Thanks,

A Sustainer

[sateesh_kumar1 · Accepted solution]

HI,

use concatenation(cells) in Excel and give that cell as prompt value in the dashboard.

Ex : =CONCATENATE(D14,".",E14,".",F14)

you need to format the string as per your req , means the prompt value that will passed to backend may be in different format "YYYYMMDD" ,depends on the locale set at server.

in that case change your concatenate formula.

[Former Member · Accepted solution]

Thanks i did the same thing, but there was a error that is coming, as in the destination cells during runtime it will be empty and when user selects the date month and year then only the values gets populated, but before user selects anything from the filter, the cell in which we have written the formula, will be like // only...( as my date format is / not .).

Thats why when running the preview, its showing error as string //.

so i am writing a formula with if cell1 not empty and cell2 not empty and cell3 not empty then populate the final cell.

Also majorly, could you also tell me once the i get the date into my cell, how do i map it to the Bex prompt. Kindly provide the steps.

[sateesh_kumar1 · Accepted solution]

Initially you should have a date by default (currentday/yesterday) or have optional prompt in the BEx.

Which connection type you are using to create dashboard?

[Former Member · Accepted solution]

HI,

From Bex Query i am passing current date and the connection type is Direct Bex(OLAP Connection).

Further, i am getting error after passing date as constant(for test) i am getting following error.

Request processing failed. (XLS 000009)

Request processing failed. (XLS 000009) For input string: "16/06/2014"

i was passing date as constant 16/06/2014 (for testing), but while running its giving error.

[sateesh_kumar1 · Accepted solution]

Hi,

Pass date value with datevalue() function in EXCEL.

select prompt , assign destination to some cell , say A5

use =datevalue([A5]) ,pass this value.

[Former Member · Accepted solution]

Dear Sathish,

Thanks a lot.

In addition for your reply i have written the formula as follows:

3 variable in 3 filters (date, month and year)

=if(AND(FILTER1<>"",FITLER2<>"",FILTER3<>""),DATE_VALUE(CONCATENATE(FITLER1,'/",FITLER2,"/",FILTER3,"/")),"").

I have written the value like this because, i was not using DATE_VALUE

=if(AND(FILTER1<>"",FITLER2<>"",FILTER3<>""),CONCATENATE(FITLER1,'/",FITLER2,"/",FILTER3,"/"),""),   and then i was using =date_value(above cell no).

Hence I wrote the first formula in bold, i hope this reply would certainly help other as well.

Thanks,

A Sustainer

[Former Member · Accepted solution]

Hi Sathish,

I am facing a issue with another filter having text (ex: i have comp code - 1000, but in filter it should come as text...ex: TATACOMPCODEINDIA), i have done this mapping from one of the prompt as company code which i am getting from Bex. and i mapped it to the combobox(fitler) and values are coming as required.

Issue is when i am selecting comp.code from filter( text ), i am mapping it to a cell with insertion type as LABEL and also checked with insertion type VALUE, but still the  value which i am selecting from Comp Code Filter : ex : TATACOMPCODEINDIA, this text is giving problem, as follows:

Request processing failed. (XLS 000009)

Request processing failed. (XLS 000009) [] &lt;com.businessobjects.semanticlayer.qt.QTException: Query script generation failed. See your BusinessObjects administrator. Couldn't fill Variable 'VAR_20120815060056' with answer 'TATACOMPCODEINDIA' (WIS 00013)&gt;

Can you let me know the solution.

Thanks,

A Sustainer

[sateesh_kumar1 · Accepted solution]

Hi Sustainer,

Try by passing key of the object as prompt value.

[Former Member · Accepted solution]

Hi Sathish,

I was using combo box.

Now i removed combo box and directly dragged and dropped the prompt on the canvas.

Its working fine. now....thanks...