Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Mark Roark]

?Hello All,
I need help once more.
We are wanting to utilize the Excel Report Dashboard with our data.
The way it is now, the data used is sample company data.
Please help me configure the Excel Report security and help with setup for the reports to pull our live data to be used to display the dashboard with real time data.
Thank you in advance.

——————————
Mark Roark
Infrastructure Supervisor
Global Products, Inc.
——————————

[Steve Erbach]

,

That’s an interesting problem. I’ve looked at the Dashboards a few times? but haven’t devoted the time to correcting them. I wonder that they’re there at all, to be frank.

Take the Financial Dashboard. If you navigate in Windows File Explorer to the folder that contains the Dashboard (…ExcelSystemReportsTWOFinancial, for example) and simply open it in Excel, I see this message right away:

“We found a problem with some content in ‘TWO Financial Dashboard.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.”

Clicking “No” gets you nowhere since Excel won’t open the workbook. Clicking “Yes” sets Excel to working on repairing the workbook. After a moment it opens with this message:

The XML file shown just repeats the information in the dialog box.

OK, so the workbook is opened. There are five Data Connections:

Clicking to “see where the selected connections are used” gives me this for the AccountTransactions connection:

When I click on “Properties…” I see the Connection Properties dialog box (last refreshed in April 2014); so I click on the “Definition” tab to get this:

Since I’m looking at the TWO version of the Financial dashboard, I can see that the “Initial Catalog” in the “Connection string” points to the proper database on the proper server, SQLPSRV03 in our case.

But it’s the “Command text:” that’s screwy:

select
[Journal Entry],
[TRX Date],
[Account Number],
[Account Description],
[Credit Amount],
[Debit Amount],
[Account Category Number],
[Account Index],
[Originating Credit Amount],
[Originating Debit Amount],
[Originating Document Number],
[Originating Master ID],
[Originating Master Name],
[Originating Posted Date],
[Originating TRX Source],
[Account Index For Drillback],
[Journal Entry For Drillback],
[Posting Type]
from [WOW].dbo.seeARDaysOutstandingKPI
@TimeUnit=Period

This is an invalid query:

1. It’s looking at the WOW database (our company GP database) instead of TWO.
2. There is no seeARDaysOutstandingKPI View. There is a Stored Procedure by that name… but you don’t query the results of a stored procedure this way.
3. That last line implies a SQL variable assignment… but there are no variables in a View.
4. Why would a “Days Outstanding KPI” query be the source for the AccountTransactions connection?

So I’m stymied by the first Data Connection.

(As an aside, the GP AccountTransactions View is referenced by name in the list of Workbook Connections. If that View is actually going to be used, there’d better be some filtering in the Dashboard. It can easily return more rows than Excel can handle (1,048,576).)

This Dashboard needs some major work to get it up-to-snuff.

, , or  might be able to offer some insight into this issue.

Sincerely,

?????????

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: twitter.com/serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve.
(hat tip: Lou Spevack)
——————————
——————————————-

[Mark Polino]

As I understand it, the dashboards were created as more of a proof of concept of what could be done and to give folks a starting point. I’ve gotten them all to run, but you have to read the instructions. It’s fun to have a free dashboard, and they were useful for showing off the refreshable Excel reports, but the dashboards suffer from the same risk of data overload the refreshable reports do. Namely, they return everything. There’s no way to limit the data being returned. 

The individual at MS who created them is still there, but he’s not doing much GP work anymore so I don’t expect to see these get update.

Mark

——————————
Mark Polino
Director of Client Services
Fastpath
Altamonte Springs FL
——————————
——————————————-

[Rob Wagner]

Here is some information about Excel Reports that you might be able to use when configuring the dashboards.

These are intended to be fully functional out of the box and connected to your live company database via the Reporting Tools Setup window. These examples are provided for customers to build their own rendition of dashboards for the following modules. Financial, Sales, Purchasing and Inventory.

In your test company, go Tools >> Setup >> System >> Reporting Tools Setup. Deploy or redeploy them locally as depicted below.
Any client with access to this window can deploy and redeploy as needed. Why would you want to redeploy?
If you open an Excel Report at the deployed location, and saved it the incorrect state… The next user will see a broken report.
If you save a report without compatibility mode, schema changes can break the report.
There are features to the core reports in the dashboard for later Dynamics GP releases and you likely want to use the latest code for your own purposes.

We don’t recommend deploying reports for the sample company and then changing all the data connections to be directed to your live company.
The deployment takes care of that part of the configuration. You will want to deploy locally for your live company and make changes to that report, then copy it to the network location after changing it to best fit your needs. Remember to save as a different Excel document and keep the original in a safe location.

Let us know if you have any questions about Excel Reporting with Dynamics GP.

Thanks,

——————————
Rob Wagner
Implementation Specialists (IS)
Hawley MN
——————————
——————————————-

[Mark Roark]

Robert,
How are you?
I apologize that this was a long time ago when I posted for assistance but we need some help with our “Test Financial Dashboard” if you are willing.
We are trying to modify the “Test Financial Dashboard” report with live data but we are in our infancy with this process and was hoping you or someone will to be able to help. We received other responses but we don’t understand what is being told to us.
The connection properties look like they are pointing to the correct database but the data is not correct.

How do we correct the connections so we are pulling the correct data? 

Do you have any resources that we can use to learn more about this and how to make this happen?

Thank you for any help you can give.

——————————
Mark Roark
Infrastructure Supervisor
Global Products, Inc.
——————————
——————————————-

[Jo deRuiter]

Hi

If you are using the Out of the Box Excel Refreshables and have not altered the TEST Financial Dashboard, then you should go to the Excel Refreshables that deployed for your Live Company Database. 

If you did not deploy them use the steps above to deploy them for Live.

Otherwise,

In Excel go to Data>Queries and Connections, double click on one of the connections or hit Edit on it.?
Go to the “Definition” tab and change “Catalog=TEST” to Catalog=”your live GP database ID“

This is assuming that the TEST db and the Live company are on the same SQL Server.

——————————

——————————
——————————————-

[Mark Roark]

Thank you. Our live database is already in the connections data. 
We are receiving Security user requests when we try to open the reports. 
Can you give me guidance on how to setup security for these Excel reports?

——————————
Mark Roark
Infrastructure Supervisor
Global Products, Inc.
——————————
——————————————-