Report Refresh

[Former Member]

Dear Experts,

We are facing the issue with the formulae in the excel report.

Below are the details.

1. We have two reports sharing the row axis. Report 1 and Reprot 2. Both of them are input enabled and are dynamic.

2. Report 1 has formula refrencing the cells of Report 2. (Formula is applied in the coulumn through the formatting sheet)

3. Upon refresh of the work book, Report 1 is getting refreshed first and then Report 2.

4. I believe because report2 is getting refreshed after report 1, cells with formula in the report 1 are ending an error message as # Ref.

So is there any way to make Report 2 refresh first and Report 1 later.

For getting the reports working momentarily, as a work around I am doing the refresh two times. Which is turning the '# Ref' error to appropriate formula on second refresh.

Though in the above example I have given only 2 reports, we have around 8 other reports in the same workbook performing different functionalities. So making the workbook refresh two times is causing a lot of performance issues.

Please suggest how to go about this refresh issue.

Thanks,

Shiva

[Former Member]

Hi Shiva,

Write a macro code of refresh worksheet data using EPMfunction"=EpmreportID" cell reference.

I am not sure,may be it works.

Thanks,

pavan

[Former Member]

Hi Pavan,

I believe you are suggesting to refresh a particulat sheet by using EPMREPORTID function.

But can you suggest how can we use this report number in macro for refresh.

Is there any way to pass this report number and write a macro like EPM.refreshreport001 similar to (EPM.refreshactivesheet).

Thanks,

Shiva

[Former Member]

Hi Shiva,

Try build report 2 first (as the default report) then build report 1.

Andy

[Former Member]

Hi Andy,

Thanks for replying.

As per the business requirement, we need columns of report 1 built first and then report 2.

Since the reports are sharing the row axis, which ever is built first is getting refreshed and then second one.

Please suggest.

Thanks,

Shiva

[Astha_M]

Hi,

Try using EPMCOPYRANGE to apply the formula.

Regards,

Astha

[Former Member]

Hi Astha,

Forumulae are getting applied correctly in the report 1 using format range, so I dont think copy range is of any advantage.

The problems is that when the report 2 refreshes,  the formulae are going # REF (the formulae are referring to cells of Report 2)

Thanks,

Shiva

[former_member186338]

Have you set the option: Keep formulas static that reference report cells?

Vadim

[Former Member]

Hi Shiva,

Also try use EPMRetrieveData in your report 1 to get all the report 2 data in report 1.

Andy

[Former Member]

Hi Vadim,

The sheet is dynamic and there are some static excel formulas at the bottom of the report. If I set that option, the staic formulas are not working.

Thanks,

Shiva

[Former Member]

Hi Andy,

The Report2 is also dynamic which can contain 10 columns sometimes and 20 columns other time based on user selection.

So I cannot exactly pass the parameters to EPMRetrieve data.

Also, If I used Retrieve data, I think the sheet will slow down further which is already low on performance.

Thanks,

Shiva

[former_member186338]

Please, show example))))

[former_member210696]

Hi Shiva,

If you have a refresh button enabled for these reports, this can be handled using macro. Something like below -

Sub Refresh()

ActiveSheet.Range("A1").Select
EPMobject.RefreshActiveReport

  ActiveSheet.Range("K1").Select
EPMobject.RefreshActiveReport

  End Sub

Where A1 is one of the cells in report 1 and K1 is one of the cells in report 2. This way you can make the reports refresh one after another as per your requirements.

Regards,

Ashish

[Former Member]

Hi Ashish,

Thanks for responding.

I believe, because two reports are sharing the row axis, upon doing above suggested approach, the refresh is still the same old way.

Report 1 is refreshing first and then report 2.

(The columns of report 1 are built at the start and after that the columns of report 2 are built because of the business requirement)

Please advise.

Thanks,

Shiva