I want my dashboard to be run by default to sysdate()-1??? If you know any solution please tell me.

[Monty]

    I am developing one Dashboard (with SAP DASHBOARD 4.0) using bex query as source. I have used 7-8 queries, each query has cal day as only prompt. I am able  run dashboard for one particular date by default(by checking refresh before load components).

Now problem is: I want my dashboard to be run by default to sysdate()-1??? If you know any solution please tell me. 

[former_member182342 · Accepted solution]

Hi Mahantesh Hiremath,

Check the below link so it will calculate the sysdate-1 functionality in backend.

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/01/24/dynamic-default-values...

Regards,

Anish

[saurabh_sonawane]

Hi

we can directly map to the query prompt output in query browser.

so directly map excel cell to the query prompts in query browser.

use a input text that user can change the date.

or use a label to indicate that dashboard is running upto date ( =text(today()-1,"mm/dd/yyyy" )

and while mapping to query prompt in query browser we have to change the date  format

=datevalue(text(today()-1,"mm/dd/yyyy"))

map the above syntax to the query prompt in query browser 

[Former Member]

Hi Mahantesh,

Are you not able to do today()-1 in excel, i think this should solve your problem

Regards,

Fazith Ali Z

[Monty]

I am using query prompt selector component...There is new feature in sap dashboard 4.0

[Former Member]

Hi Mahantesh,

You can also achieve this through Bex query itself, you can request your BW team to create a customer exit variable to populate calday. Logic will be same sy-datum -1. In this case by default query will always get refreshed by yesterday and nothing needs to be done on dashboard side.

Regards

Shabnam

[former_member182541]

I don't think its not possible to pass current date -1 by using query prompt selector.

The work around will be you can use the combo box here to pass the value and use the query refresh button "When value changes". Create a separate query to get only the dates from the report and map the value to the combo box, then use the destination value to trigger the query. In behavior tab take the current -1 excel function and pass to trigger the query when it invokes.

But Query prompt selector is the powerful selector rather using combo box.

[former_member182541]

Hi Shabnam,

Have you tried in this way, I really want to know how it will work when it comes to Dashboard. We can write a customer exit in BEx to get the default date as sysdate()-1, you can achieve this in BEx Analyzer. When you take this query to dashboard before invoking the dashboard we need to pass the date here. So i don't think it will satisfy the question.

Correct me if i am wrong.

[Former Member]

Hi Suman,

If date is populated through customer exit variable and its not input ready, it will not show prompt popup. Yes I have created many dashboards where it shows data as per bex prompt(default value or customer exit.).

for simple example you can use current calendar month variable present in ur system in any of your query and use same in dashboard. It will show data as per March 2014, input not required in this case.

Regards

Shabnam

[Monty]

Hi Shabnam,

Can you please elaborate with screen shot? I am using query prompt selector (MIS date). This date prompt is default date given by BW team.

Now problem is that date prompt given by them working right way in WEBI, But it is not working  DASHBOARD.  By default it is not going for the current-1 date...??

Thank you

MAHANTESH

[Former Member]

Hi Mahantesh,

As I ve told earlier its a workaround, you can acheive it through Bex query as below.

In my bex query I ve a calmonth customer exit variable for last month.

And I am using same query in dashboard and when I add this query for first time or refresh it, it doesn't ask for calendar month input and by default it refreshes for last month.

Regards

Shabnam

[former_member182541]

So Month is not the prompt in dashboard, whenever you run it will execute for last month ?. I don't see month prompt in that list.

[Former Member]

If its working fine in webi(showing last date by default), then please ask your BW team to check if that variable is mandatory or input ready?

If so then ask them to create a similar variable which is not input ready and mandatory. As shown in the screenshot in this case Bex query itself will take care of prompt value and show you desired output in dashboard without binding it any cell etc in dashboard.

Regards

Shabnam

[Former Member]

Hi Suman,

As Shabnam said, we can do that yesterdays values as default in the Bex query and the same can be used in Dashbord/Design Studio. Ensure that the exit variable should be filled before invoking the query. i.e., before variable screen pop up. I am sure this will work. Ask the BW team, to create an exit variable whci should fill values as SY-DATUM-1. Then this values will populate in your dashboard/Design Studio.

Best Regards

Venkat...

[Monty]

Hi Shabnam,

Thanks for the information....

Still my problem is not solved. Please see this I have 2 Bex queries on this I have made 4 queries(as in fig 1), both queries have same prompt (as in fig 2). By default prompt showing sysdate-1 (as in fig 2).

Once I run this queries with one date say 28-Feb-2014 in my case  ( I have checked refresh before load components.) and mapped all fields in excel.  After clicking on preview my dashboard running on  28-feb but next day also its running on same date.

I need to edit query and select 1 march and then save,publish .....etc

See these screen shots please suggest

Fig 1

Fig2

Thank you

Regards

Mahantesh

[Former Member]

Please ask your BW team to check this variable again as I feel its ready for input. In this case ready for input is not required. Prompt will not appear in that case. It will automatically refresh data for last day and show you in dashboard.

Check without input ready and let me kw if it doesn't work for you.

Regards

Shabnam

[Monty]

Hi Shabnam,

I told  BW team to remove ready for input then no prompt appearing in Dashboard. Actually My requirement is my Dashboard should have prompt as user wants to select some dates and by default Dashboard should open with default(MIS) date.

Reagards

MAHANTESH

[Former Member]

Hi Mahantesh

I checked your case but its not reflecting changed value in case of input ready variable.

Please check below SAP Note also:

1977728 - Dashboard does not reflect the updated Customer Exit Variable default value when prompted

Input Ready prompt doesn't reflect changed value, this is product limitation in both 4.0 and 4.1.

You can only achieve sy-datum -1 through above steps which I suggested earlier. Input ready will not work for dashboard.

Regards

Shabnam

[Monty]

That means there is no solution for my problem......:(.

Thank you very,very,very  much for your valuable time and suggestions..:)

[former_member182541]

Finally the answer is no.

[Former Member]

I have had exactly the same problem.

If you want the Dashboard to use a BW Customer Exit variable then, like Shabnam explained, it needs to be mandatory, not ready for input - this is the only way to refresh Dashboard with the most up to date date.

However, I understand that once your dashboard has run, you want to let the user change the date.

That's what I did to achieve this.

1. I have added all objects in my dashboard to a Canvas Container.

2. I have copied the Container so now I had two identical versions of the dashboard on top of each other.

3. I have recreated all BW queries that I used for the original dashboard and changed the variable from Customer Exit to a different one, a mandatory, ready for input.

4. I have linked objects in Container 2 to the new queries and added a Query Prompt linking it to the available variable. So I have got two dashboards, one linked to BW queries with Customer Exit variable and second linked to BW queries with a different variable.

5. I have created a toggle button "Refresh with new dates". If a user presses the button, value 1 will be inserted into a worksheet. This will hide Container1 and unhide Container 2 (I am using Dynamic Visibility option for that). Query Prompt will also only appear if button is pressed on so that the user can select a new date.

6. The user selects a new date - the value is passed to the BW variable and refreshes the second set of queries in Container2.

7. If the user wants to return to the original view they press the toggle button.

A little complicated, and not greately elegant but works - it opens the dashboard with Container1 visible and refreshes the queries with Customer Exit variables. Container2 remains hidden. When the user presses the toggle button, Container1 hides and Containere2 with the Query prompt unhides.

Hope that makes sense!

[Monty]

This solution I know but my dashboard performance will decrease very badly.

[Former Member]

HI mahantesh,

i am also facing the same issue. Did u get any solution for ur problem?

thanks,

rajwinder

[Monty]

Hi Rajwinder,

I solved this issue.

What is source for your Dashboard? is Bex query or Universe(IDT)?

Solution is little complex to write all steps.

Regards

Mahantesh

[Former Member]

Hi,

I am getting data from BEx query.

Thanks,

Rajwinder

[Monty]

Raj,

Saurbh Ans can help you I think..!!?? Every time user need to enter date ..By default dashboard can't  open with Sysdate-1 . What exactly you want? elaborate plz. ..

Regards

Monty

[former_member182541]

As explained earlier you can use the excel function =today() - 1.

What is the component you have used to trigger the BW queries ?.

If you are using calender component, you check the below image for better understanding.

[Former Member]

Hi Suman,

Which connections you are using to fetch the data?

Regards,

Rohan Ghadi

[former_member182541]

Hi Rohan,

Question was posted by Hiremath, not by me.

[Former Member]

Opps Sorry Suman,

I wanted to ask the same to mahantesh only...

Regards,

Rohan ghadi

[former_member182541]

No need ask sorry, its ok.

[Monty]

I am using bex query as source ....this not possible

[former_member182342]

Hi Mahantesh Hiremath,

Try to pass value from excel to Dashboard prompt.

write the formula in excel

=TODAY()-1

and map that cell to calday prompt value

Regards,

Anish