Passing Date Prompt from DAshboard to Bex Queries

[michael_jennings]

Dear All,
I am trying to pass a date as variables to a qBex connection in SAP Dashboard. I use text input boxes to plug the dates into my selected spreadsheet cells, unfortunately the query runs and I get no data.
 
I have tried to use this format :
 
01012016
20160101
 
01/01/2016
2016/01/01
 
01.01.2016
2016.01.01
 
01 01 2016
2016 01 01
 
Which kind of date format I have to pass to the qBex in order to retrieve the data ?
 
Best Regards,
MJ

[former_member182541 · Accepted solution]

SAP mostly accepts YYYYMMDD format.

1. Take the date list of values from BEx and pass it into prompt through combo box and take a snapshot of the data and check. (as a test)

2. Create date input variable in BEx level it self, so it solves all this confusion from BEx Level itself.

[michael_jennings · Accepted solution]

Dear Suman,

          using the calendar object it works because it converts the date in numeric format, for instance 01/01/2021 become 43934.

Now I'm going to use a range date applied on a qBex prompt declared as interval date, also If I use calendar component doesn't work fine.

My range is composed as C1 = A1 & " - " & B1 where

A1 = 43934

B1 = 43934

So the cell C1 is equal to 43934 - 43934

I'm referring also on this topic :

Do you  have any idea ?

Best Regards,

MJ

[former_member182541 · Accepted solution]

What is your connection type?

If the date prompt is at interval, then you might be getting two prompts for start & end. Use two calendar components for from and to this might help you.

Or you need to take date, month & year then you need to concatenate both the start & end date then you need pass it in the prompt.

[michael_jennings · Accepted solution]

Dear Suman,

     below the information requested to me :

Q1) What is your connection type?

R1) The connection are built using Query Browser

Q2) If the date prompt is at interval, then you might be getting two prompts for start & end. Use two calendar components for from and to this might help you.

R2) I'm using two calendar component

Q3) you need to take date, month & year then you need to concatenate both the start & end date then you need pass it in the prompt.

R3) On Bw the date are in this format : yyyymmdd, on Query Bex Date Prompt when I put the interval the are in this format : "01/01/2015 - 01/01/2016"

My situation is :

Two calendar component (start - end)

The first calendar component has the destination on A1

The second calendar component has the destination on B1

on cell C1 I have A1 & " - " & B1 and the result is : 42005 - 42370

And no data are retrieved.

P.S.

"SAP Dashboard 14.1.4.1327"

Best Regards,

MJ

[former_member182541 · Accepted solution]

Sorry for the delay in response, i was testing your scenario locally with my bex query by creating a date prompt with intervals as from - to.  I created two calendar components and retrieved the dates,but as you said its coming as "42005 - 42370". Convert those number as day,month & year using formula for both start & end date and concatenate those dates in trigger cell.

Its working for me, use A1 as trigger cell, concatenated dates should be in B1 then use a push button to submit from B1 to A1 then trigger the query. It will work for sure.

[michael_jennings · Accepted solution]

Dear Suman,

Could you tel me your SAP Dashboard version and SAP BW version ?

I have tried using C1 configured like this : C1 ="42522 - 42522" , but

unfortunately doesn't work. Could you kindly try to test

C1 ="42522 - 42522" on your enviroment ?   

I have also tried to put the Date object in filter panel, and I have

only the "Equal to" and "InList" operator,  If I use Surname object I have the

operator "beetween"...

N.B.

I have found this KBA, but my SAP Dashboard version is higher than the version reported below

https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F...

Best Regards,

MJ

[former_member182541 · Accepted solution]

I am attaching the dashboard what i tested along with this reply in .txt format. Download the file and rename it to .xlf and see the logic but i don't know how it helps in terms of design but by the design you can get the idea.

calendar sample.txt.zip

[michael_jennings · Accepted solution]

Dear Sumam,

        I solved the issue applying all filters on query panel and not on qBex as reported by

        the NOTE 1947226.

        Thanks about your support.

        Unfortunaltely I have another issue now reported on topic:

        If you have any idea please report to me

Best Regards,

MJ

[former_member182541 · Accepted solution]

Found a note on this, hope you would have tried it.

2065552 - Got error "Could not connect to Microsoft Excel. xxxxxxx" when open specific Dashboard rep...

Symptom

• Cannot open specific Dashboard report
• Error message: "Could not connect to Microsoft Excel. To prevent further data corruption, the program must close."

Environment

• Dashboard 4.0 SP04
• Excel 2007/2010

Reproducing the Issue

1. Open Dashboard Designer.
2. Open specific Dashboard report.
   => Got error above.

CauseThe dashboard's excel spreadsheet content is damaged due to unknown reason.

ResolutionTo further narrow down where’s the problem, suggest use this way to trouble shoot:

1. Backup the .xlf file.
2. Rename the file to .zip.
3. Open the .zip file, and copy 'xldoc' onto desktop.
4. Rename xldoc to xldoc.xls.
5. Open xldoc.xls with Excel.
   =>If the .xls cannot be open , it should be a excel worksheet issue. Otherwise ,this is should be a Dashboard component issue.

Basically the way Xcelsius works is that it keeps all the "Excel spreadsheet" stuff saved in this 'XLDOC' document (
which is a real, honest full-blown Excel spreadsheet), and the component data in the .xml file.
What happened in this case is that the XLDOC file is corrupt.  If Excel can't open the file, then neither can Xcelsius
(because Xcelsius just asks Excel to open it for him).  If you can recover the data from the XLDOC file, then you can rec
over the data from your dashboard, and if you can't, then your dashboard is lost.

So the solution is attempt to recover this .xls file.  Either by googling for Microsoft tools which help with dat
a recovery or, trying to open it using Google spreadsheet or some other tool which will have a more robust data recovery mechanism than MS Office.

In addition, if you have a backup file, you can also replace the xldoc.xls file to recover the Dashboard file.

Keywords

unable to open dashboard, xcelsius, excel, 4.0, 4.1