BI 4.1 sp4: Combine excel data with a webi report

[Former Member]

Hi, I have a scenario as below

We receive a report file in email and a macro is run to format (excel attached) and we have created an initial webi report (attached).  the combined webi works as a planning report

1. how to combine both (can you explain the procedure) in the webi without universe (directly in webi) and if there is no such option please mentioned the steps in universe.

2. also the macro seems to be erroneous, can you tell me if there is a way to use raw excel and combine it with my webi

3. even after the macro runs there are still BLANK ROWS as you see in the attachment, how can I get rid of them and use it

4. most columns are the same in both except a couple, can you tell me how I can address this

5. How do i refresh the incremental data in the webi report as soon as excel file changes with new data. (in my case a macro runs on excel we get everyday)

System: backend: BW and BI 4.1 sp4

Your help would be greatly appreciated

Thanks, Krishna

[amitrathi239 · Accepted solution]

Hi,

1. how to combine both (can you explain the procedure) in the webi without universe (directly in webi) and if there is no such option please mentioned the steps in universe.

Create webi report based on the excel and use excel as a data source in webi rich client.Similar to the report on the universe,create two webi query on top of two excel sheets.

2. also the macro seems to be erroneous, can you tell me if there is a way to use raw excel and combine it with my webi

use raw files in the webi as source and replicate the macro functionality in webi.Use merged Dimension to combine the data from multiple queries in webi.Merged dimension is same as you have used for universe or different source.

3. even after the macro runs there are still BLANK ROWS as you see in the attachment, how can I get rid of them and use it

4. most columns are the same in both except a couple, can you tell me how I can address this

Merge the stock number and plant objects and other  are like measure objects.Drag merged Stock number/plant objects along with measure objects in table.

5. How do i refresh the incremental data in the webi report as soon as excel file changes with new data. (in my case a macro runs on excel we get everyday)

If this report you are only going to refresh and sending then keep it in one location in your system.replace the file every day and open and refresh the report daily.

But you need to make sure File name/Tab/data structure should be the same every time when you are going to replace the excel files.

If there is a requirement multiple users are going to be use the same reports from BI launch Pad then you need to change the file every day at server.in this case location of file should be the same in server when you have built the report.Like in your system if it is D:/Excel then same way you need to put the file on the server at D:/Excel location.

Refer the attached link to get the idea.

BOBJ Tricks: Excel Data Sources for Webi and Xcelsius

Amit

[Former Member · Accepted solution]

Thanks a lot for the clarifications Amit,

Item 1. I already have a webi report which is against a BEx query and now there is excel with same columns and names and we need to combine the data. So its not 2 excel(s). Please advise as this seems to be ta roadblock

Item 3: The excel (after macro run) it gives blank rows and webi takes only excel.xls not excel.csv as source, I am not sure if these blank rows are actually being ignored or causing layout issue

Your help will be appreciated

Thanks, Kris

[amitrathi239 · Accepted solution]

Hi,

Item1: Still you can combine the data from BEx query and excel.You need to make sure when you adding the webi query on excel ,objects type be same as BEx objects.You can only merge objects in webi when objects type is same in both the queries.

Item2:Try to add the excel file before running the macro.BO will not ignore the blanks rows.At report level you need to add filters to exclude blank rows or total rows.

Its' better you start with creating report.You will get the fair idea what is possible and how to do.

Amit

[Former Member · Accepted solution]

Hi Amit i have started putting the report together

1. my existing webi report have 4 queries which are against different BW BEx queries and there is a bunch of measures from all these queries and above mentioned webi output is the result

Now, with my excel I have common stock number and plant (which I merged) but what about the measures how do i merge them?

Do i add the measures from different queries for example, In transit is measure is in existing webi query 2 and excel query, can you suggest me an approach

What measures are to pull into the report or it is based on the requirement

2. please tell me how to get rid of these lines at the beginning of excel, I tried uploading .csv file but webi didn't allow the operation

3. I have a merge dimension on stock number/old material number from 4 queries as below, how do i  merge add stock number from excel to this and I do not have key and text for stock number in excel. please advise or I should merge it with below ones by selecting either all key ones or text ones

Appreciate your kind response,

Thanks, Kris

[amitrathi239 · Accepted solution]

Hi,

1) You can't merge the measures.Only you need to merge the dimension objects.Drag the measure in the table and you will get the values according to Dimension objects.

2)You can create the webi report on top of CSV also.In the data source select Text and upload the CSV file.

As per you screenshot either select the Excel range at the time of uploading excel file in the webi query.But range you can only select when you are sure every time data would come in the particular range only.In your case better to upload the raw file like where excel file contains first row as Column definitions and from second row only values.

3) first de-merge the existing merged dimension and later select five objects (one from excel and four BEx objects) and merge the objects.

Amit

[Former Member · Accepted solution]

Hi Amit thanks a lot for the response,

1. the issue is we have lots of formulas/variables in the report. ex: v.InTransit measure which has

=If(IsNull([2443_Q001].[In Transit]);0;[2443_Q001].[In Transit])

and it had only 1 query included now with excel has In Transit and this should be included as well in the formula. I'm wondering how i can have both

another ex: v.3mthUsage with

=If(IsNull([Usage (in SAP) - Rolling 3 Months]);0;(([Usage (in SAP) - Rolling 3 Months]/3)*(-1))) which comes from query 3 above and excel has Usage (in SAP) - Rolling 3 Months too.

2. I think because of the size of data the webi or for some other reason webi rich client throws error all the time such as below

to be honest BW OLAP as backend seems to be very painful Amit, I never found this kind of issues with relational datasources (at least not these many) and the we do not have Webi rich client enabled and they are not planning to even do it anytime soon.

also, they do not like universes (if it doesnt work in webi browser i mean with the 2 unnecessary lines in excel sheet mentioned above) I have to suggest them to use IDT

Also, another important point is that in webi browser there is no option to mention cell ranges, we can just say A1:F5 but I may not need E column, is there an option to mention ranges (ex: A1:D5,F1:F5 etc) again, the excel data is incremental and there could be more than 5 rows of data. Any suggestion

I really appreciate your help on this topic,

Thanks again

Kris

[amitrathi239 · Accepted solution]

Hi,

how many records in the excel file?

if excel sheet  data changing frequency like monthly then you can tweak the sheet first and then use.

Like delete the first two records manually,blank columns or total rows.After that use in the report.The only thing in this case every time you have to delete the unwanted rows and then have to use in the report.

Also about your error delete the space and  blank columns and then try to upload.

About your question how to display measure values from query 1 and query 2 or 5 in single column.

Once you will merge the dimension objects create measure var like=[measure1] +[measure2] etc..

See my attached screenshot.I have created this based on the two queries and displayed the quantity from both the queries in the single column.Same way you can do for measures.

Before that check how can you align the data in the excel file before use.

Amit

[Former Member · Accepted solution]

Hi

Appreciate the help Amit I have put the stuff together and it is due for testing, can you clarify below issue

1. why query1 has stock doesnt show LOVs while query 4 shows the LOVs for stock code,

these queries are against different Infoproviders do you think this is the reason, if so can you suggest me a way to resolve this please

and as a result, when I refresh the whole report for stock code I do not see the LOVs

Your responses would be greatly appreciated

Thanks, Kris

[amitrathi239 · Accepted solution]

Hi

run the BEx query in BW RSRT or analyzer and check values are coming or not..might be some issue with this object.

Amit

[Former Member · Accepted solution]

The erroneous query runs okay in RSRT Amit but not in webi and surprisingly same stock code in Q4 runs fine. I did try re-point the datasource (modify - data - change source) but no luck

is there anything you think that should be changed.checked in Webi or Query designer?

i see the error message as:

java.util.concurrent.ExecutionException: com.businessobjects.sdk.core.server.CommunicationException$UnexpectedServerException: The number of requested members exceeds 10000. Do a search by element instead.

  at java.util.concurrent.FutureTask.report(Unknown Source)

  at java.util.concurrent.FutureTask.get(Unknown Source)

  at javax.swing.SwingWorker.get(Unknown Source)

  at com.sap.webi.qp.lovprompt.LOVTable.endFetch(LOVTable.java:318)

  at com.sap.webi.qp.lovprompt.LOVTable.propertyChange(LOVTable.java:499)

  at java.beans.PropertyChangeSupport.fire(Unknown Source)

  at java.beans.PropertyChangeSupport.firePropertyChange(Unknown Source)

  at javax.swing.SwingWorker$SwingWorkerPropertyChangeSupport.firePropertyChange(Unknown Source)

  at javax.swing.SwingWorker$SwingWorkerPropertyChangeSupport$1.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.run(Unknown Source)

  at sun.swing.AccumulativeRunnable.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.actionPerformed(Unknown Source)

  at javax.swing.Timer.fireActionPerformed(Unknown Source)

  at javax.swing.Timer$DoPostEvent.run(Unknown Source)

  at java.awt.event.InvocationEvent.dispatch(Unknown Source)

  at java.awt.EventQueue.dispatchEventImpl(Unknown Source)

  at java.awt.EventQueue.access$500(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)

  at java.awt.EventQueue.dispatchEvent(Unknown Source)

  at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

  at java.awt.WaitDispatchSupport$2.run(Unknown Source)

  at java.awt.WaitDispatchSupport$4.run(Unknown Source)

  at java.awt.WaitDispatchSupport$4.run(Unknown Source)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.awt.WaitDispatchSupport.enter(Unknown Source)

  at java.awt.Dialog.show(Unknown Source)

  at com.jidesoft.dialog.StandardDialog.show(Unknown Source)

  at java.awt.Component.show(Unknown Source)

  at java.awt.Component.setVisible(Unknown Source)

  at java.awt.Window.setVisible(Unknown Source)

  at java.awt.Dialog.setVisible(Unknown Source)

  at com.sap.webi.toolkit.ui.dialog.GenericDialog.setVisible(GenericDialog.java:128)

  at com.sap.webi.ui.context.managers.DataManager.resolvePrompts(DataManager.java:1946)

  at com.sap.webi.ui.context.managers.DataManager.resolveParameters(DataManager.java:1723)

  at com.sap.webi.ui.tasks.workflows.ResolveParametersUITask.doneProcess(ResolveParametersUITask.java:108)

  at com.sap.webi.toolkit.ui.tasks.WebITask$PrivateWorker.done(WebITask.java:378)

  at javax.swing.SwingWorker$5.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.run(Unknown Source)

  at sun.swing.AccumulativeRunnable.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.actionPerformed(Unknown Source)

  at javax.swing.Timer.fireActionPerformed(Unknown Source)

  at javax.swing.Timer$DoPostEvent.run(Unknown Source)

  at java.awt.event.InvocationEvent.dispatch(Unknown Source)

  at java.awt.EventQueue.dispatchEventImpl(Unknown Source)

  at java.awt.EventQueue.access$500(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)

  at java.awt.EventQueue.dispatchEvent(Unknown Source)

  at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

  at java.awt.WaitDispatchSupport$2.run(Unknown Source)

  at java.awt.WaitDispatchSupport$4.run(Unknown Source)

  at java.awt.WaitDispatchSupport$4.run(Unknown Source)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.awt.WaitDispatchSupport.enter(Unknown Source)

  at java.awt.Dialog.show(Unknown Source)

  at com.jidesoft.dialog.StandardDialog.show(Unknown Source)

  at java.awt.Component.show(Unknown Source)

  at java.awt.Component.setVisible(Unknown Source)

  at java.awt.Window.setVisible(Unknown Source)

  at java.awt.Dialog.setVisible(Unknown Source)

  at com.sap.webi.toolkit.ui.dialog.GenericDialog.setVisible(GenericDialog.java:128)

  at com.sap.webi.ui.workbench.datamanager.qpframe.QPFrame.showModal(QPFrame.java:456)

  at com.sap.webi.ui.workbench.datamanager.qpframe.QPFrame.showQueryPanel(QPFrame.java:406)

  at com.sap.webi.ui.context.managers.DataManager.showQueryPanel(DataManager.java:3001)

  at com.sap.webi.ui.context.managers.DataManager.editDataProvider(DataManager.java:588)

  at com.sap.webi.ui.tasks.workflows.EditDataProviderUITask.doneProcess(EditDataProviderUITask.java:90)

  at com.sap.webi.toolkit.ui.tasks.WebITask$PrivateWorker.done(WebITask.java:378)

  at javax.swing.SwingWorker$5.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.run(Unknown Source)

  at sun.swing.AccumulativeRunnable.run(Unknown Source)

  at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.actionPerformed(Unknown Source)

  at javax.swing.Timer.fireActionPerformed(Unknown Source)

  at javax.swing.Timer$DoPostEvent.run(Unknown Source)

  at java.awt.event.InvocationEvent.dispatch(Unknown Source)

  at java.awt.EventQueue.dispatchEventImpl(Unknown Source)

  at java.awt.EventQueue.access$500(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.awt.EventQueue$3.run(Unknown Source)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)

  at java.awt.EventQueue.dispatchEvent(Unknown Source)

  at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

  at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

  at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

  at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

  at java.awt.EventDispatchThread.run(Unknown Source)

Caused by: com.businessobjects.sdk.core.server.CommunicationException$UnexpectedServerException: The number of requested members exceeds 10000. Do a search by element instead.

  at com.businessobjects.sdk.core.exception.ExceptionBuilder.make(ExceptionBuilder.java:152)

  at com.businessobjects.sdk.core.exception.ExceptionBuilder.make(ExceptionBuilder.java:109)

  at com.businessobjects.sdk.core.server.common.CommonRequestHandler.afterProcessing(CommonRequestHandler.java:127)

  at com.businessobjects.sdk.core.server.internal.AbstractServer.processIt(AbstractServer.java:178)

  at com.businessobjects.sdk.core.server.internal.AbstractServer.process(AbstractServer.java:133)

  at com.businessobjects.sdk.core.server.internal.InstanceServer.process(InstanceServer.java:94)

  at com.sap.sl.sdk.parameter.service.processor.AbstractParameterProcessor.provideAnswersForContextOrNonContextParameters(AbstractParameterProcessor.java:154)

  at com.sap.sl.sdk.parameter.service.processor.AbstractParameterProcessor.provideAnswers(AbstractParameterProcessor.java:115)

  at com.sap.sl.sdk.parameter.service.ParameterServiceImpl.provideAnswers(ParameterServiceImpl.java:66)

  at com.sap.webi.client.toolkit.prompts.LOVHelper.provideAnswers(LOVHelper.java:210)

  at com.sap.webi.client.toolkit.prompts.LOVHelper.update(LOVHelper.java:510)

  at com.sap.webi.qp.lovprompt.RefreshLovWorker.doInBackground(RefreshLovWorker.java:46)

  at com.sap.webi.qp.lovprompt.RefreshLovWorker.doInBackground(RefreshLovWorker.java:13)

  at javax.swing.SwingWorker$1.call(Unknown Source)

  at java.util.concurrent.FutureTask.run(Unknown Source)

  at javax.swing.SwingWorker.run(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

  at java.lang.Thread.run(Unknown Source)

Caused by: com.businessobjects.sdk.core.server.ServerException: The number of requested members exceeds 10000. Do a search by element instead.

  at com.businessobjects.sdk.core.server.common.CommonRequestHandler.newServerException(CommonRequestHandler.java:260)

  at com.businessobjects.sdk.core.server.common.CommonRequestHandler.createAllServerExceptions(CommonRequestHandler.java:238)

  at com.businessobjects.sdk.core.server.common.CommonRequestHandler.afterProcessing(CommonRequestHandler.java:121)

  ... 16 more

Any suggestion please

Thanks,

[amitrathi239 · Accepted solution]

HI

as per your error lov's list is exceeding 10000.

instead of lov refresh search the stock code by name.

like *abc*

see if it works.

Also check in BW how many records in stock code.

Amit

[Former Member · Accepted solution]

You were right Amit it accepted when I entered the code manually.

How to limit the size of LOV in BEx query, I remember doing it for relational DB and Oracle backend from CMC (LOV size increase). I hope this works for BEx too

Many thanks,

Kris

[amitrathi239 · Accepted solution]

Hi

it will work but same time large lov's list will take time to load. it will impact the performance. and even i don't think so it helps users.it's difficult to go through by large list and select the values..

better option is to explain users to search the values instead of refresh.

Amit

[Former Member · Accepted solution]

That is true, I will tell them to enter manually.

Thanks Amit

[Former Member · Accepted solution]

Also Amit can you assist me with another issue at below link

as it is another issue I posted another discussion

http://scn.sap.com/message/16270589#16270589

Please suggest a solution

Thanks, Kris