• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel "dashboard/tool" to be deployed to other users : Suggestions ?

L

Lolo

Member
Hello,

I'm an IT professional person, but work since few months directly in business department . So I'm not belonging to our IT department. I am considered as a business user, So I have not acces to the IT development tools.

In my company, Business users (so like me ;) ) have a web app which enable to post some requests to the IT department, and especially evolution requests for all tools used by business users.

The problem is that there is no real efficient dashboard in order to follow user's requests, and it is very difficult to see the requests under their responsibility.

So, I have decided to do it myself with the tools at my disposal : Excel.
I have managed to access to the SQL DB, and I have created an excel workbook (+vba code) which make an ODBC connection and display resut in a sheet, and create an efficient dashboard. Some setting allows me to tune the result (exlude some requests which are on my dashboard by error (because wrong keyed in) for example).

Moreover this dashboard enables me to add some comments for each request, in order to follow them(Do this in the webapp is not possible or it is a mess). So the workbook combines DB data (requests I want to follow) and manual keyed in data (comments, ranking for example)

The problem is that some other business users from other departments have seen this and want it...
But the problem is that it is difficult to deploy, since each users will have its own settings, own data and own personal comments. So I will have several workbook to maintain. One by department I think.

I would be able to dynamically update VBA code of deployed workbook (or by using an addin), but what if for example I basically fix a bug in a formula ? Or add a new column ? I need to re-deploy the fixed excel workbook to the users. So their personal comments will be erased...

Do you have any suggestions in such case ? How do you manage Excel workbook deployment ?
Or It is not possible, and the solution, in my case, is not in Excel, but in a standalone app with a real DB ? Or a combination between access and Excel ?

Thank you for your help, I hope I'm clear...
 
Good day LoLo and welcome to the forum, this link will help in the use of the forum

http://chandoo.org/forum/forums/new-users-please-start-here.14/

As to your question I have had to do something similar with Access and I found the best way to do multi deployment with different OS releases and different office releases was with SharePoint, this way you can set permissions and rights, can you not get your IT department to set it up for you (they may not agree but that’s what they are there for) you then have control of what users can, and more importantly, cannot do.



.
 
Good discussion...

Here is what I suggest:
  • Break your dashboard in to at least 3 components - Data, calculations and Dashboard output. Maintain individual components in separate sheets.
  • Create a template-like version of the dashboard. This would show the output dashboard based on data & calculations and user-defined settings (example settings: querystring, user name, password, currency & date formats, types of charts to show / hide)
  • Deploy
  • When formulas need to change, re-deploy.
  • If too many people are customizing & using the dashboard, you can add a macro to 'import settings from other workbook' feature. This will make the process of bug fixes and new features easy.
Here are 2 example dashboards where some of these techniques are implemented.

1. Project Portfolio Dashboard
2. Dynamic Excel Dashboard

If you want more ideas and examples, visit Excel Dashboard Examples page.
 
bobhc said:
Good day LoLo and welcome to the forum, this link will help in the use of the forum

http://chandoo.org/forum/forums/new-users-please-start-here.14/

As to your question I have had to do something similar with Access and I found the best way to do multi deployment with different OS releases and different office releases was with SharePoint, this way you can set permissions and rights, can you not get your IT department to set it up for you (they may not agree but that’s what they are there for) you then have control of what users can, and more importantly, cannot do.


.
Click to expand...
First, Sorry for my late reply.
And thank you for your answer. Indeed we have
r1c1 said:
Good discussion...

Here is what I suggest:
  • Break your dashboard in to at least 3 components - Data, calculations and Dashboard output. Maintain individual components in separate sheets.
  • Create a template-like version of the dashboard. This would show the output dashboard based on data & calculations and user-defined settings (example settings: querystring, user name, password, currency & date formats, types of charts to show / hide)
  • Deploy
  • When formulas need to change, re-deploy.
  • If too many people are customizing & using the dashboard, you can add a macro to 'import settings from other workbook' feature. This will make the process of bug fixes and new features easy.
Here are 2 example dashboards where some of these techniques are implemented.

1. Project Portfolio Dashboard
2. Dynamic Excel Dashboard

If you want more ideas and examples, visit Excel Dashboard Examples page.
Click to expand...

Hello first thank you for your reply and sorry for my late answer.
Many thanks for your helpful suggestions. I will try to apply your advices. You are an inspiration for me ;)
 
bobhc said:
Good day LoLo and welcome to the forum, this link will help in the use of the forum

http://chandoo.org/forum/forums/new-users-please-start-here.14/

As to your question I have had to do something similar with Access and I found the best way to do multi deployment with different OS releases and different office releases was with SharePoint, this way you can set permissions and rights, can you not get your IT department to set it up for you (they may not agree but that’s what they are there for) you then have control of what users can, and more importantly, cannot do.


.
Click to expand...
Oops my first answer was truncated... Sorry.
So Indeed we have a sharepoint server. With it I would easily centralize the deployed workbook, but in case of modification, I will have to do it in each deployed workbook. However, combined with the suggestions of chandoo, I have an idea on how I could do it. Thank you !
 
Hello,

1/ DASHBOARD CONCEPTION

About dashboard conception, in some cases where the final output is near of the data output and calculations are easy (I mean : I want to display data output, with just additional formula columns, and some other little action (color, format, ...), I don't really split the layers (DATA,CALCULATION,OUTPUT) as advised by chandoo.

I finally use a different approach for simple cases. I have only 2 sheets:
- I have a template sheet, where there is the formulas column
- I have a data sheet, where there is the data. The format and confditional format are direclty done in this sheet.

The refresh process is the following:
- 1/ I have a customized ribbon with a refresh data button --> SQL connection via ADO : refresh a data table in the data sheet.
- 2/ Then (all in vba) I copy the formulas in the first line of the data table, at the right place, then formula are automatically applied to all lines of the data table, then I replace the formulas result by the value (pasteAs Value). If necessary I group/the calculation columns
- 3/ If necessary I protect the sheet

This works fine for me, for simple cases., and very easy for users.

2/ DEPLOYMENT

Secondly, I am still working about deployment, and there is 2 options :
- Easy case : the excel dashboard file is sent to authorized users. they have a copy, locally on their computer.
The official version of the dashboard is on the network (or sharepoint server), with a version number and an xml config file which contains the users who can use the report. At the opening of the local copy, I check the version no and if the user is authorized to use the dashboard.
- If there is a new version, I simply remove the old version by the new one (I rename the active one, copy the new one locally, and close the active one (I warn the user before of course). At the opening of the new version, the old xls file will be removed)
- If the user is not allowed to use it, or if there is no network, the data refresh action are disabled. But the user can continue to use the dashboard, but he will be limited on the possible action.

- Complex case : If the excel file contains some manual data filled in by the user, I cannot simply remove and replace it.In this case, I need to create a specific update code for this dashboard, to take into account its particularities. Otherwise, it is the same as the easy case.

NB :I have customized ribbon where in addition of the data refresh button, there is a button to copy the active excel dashboard as a simple excel file (+ option to send it by mail). I mean, I remove VBA code, buttons, customized ribbon, even formulas if necessary. With this, the user clearly knows that the current excel file is not to be shared. If he want to share the content, he has to create a basic copy, and send it.
NB2 : Since my code has a password (it is weak of course), and alos obfuscated, and string are encrypted, security is ok according to me.

I hope I was clear. Let me know your opinion ;)
 
Globally not really savvy scale 3 ? (depends on what is the scale 10 :) ).

About deployment, the problem is that my company has a big IT security department and they are very critic when it is about Excel. Since I'm an It user, but since 7 months directly in a business department, I want to be able to explain them that I try to develop an excel program like in any other technology (well coding, documentation, minimum of security, management of the deployment, well all things that are not done by a basic (business) excel user...), and prove them it is a viable option.
 
Hi, Lolo!

My humble point of view:

If IT security guys are actually strict and serious, there's no argument that might convince them that any Excel application is secure, so I'm bad news. There're 2 different things:
a) IT guys accept Excel (or other non-secure software platforms/tools) developments, either reluctantly or saying "ok, but..."
b) IT guys get convinced that Excel (or...) developments are secure, saying "Ok!"

Maybe I can resume it with this code:
Code: 
Option Explicit

Sub GuillotineForITGuys()
    If a) Then
        do_it_but_just_this_time_ (next_time_come_and_ask)
    ElseIf b) Then
        tell_the_IT_security_manager_the_famous_Donald_Trump 's_phrase
    End If
End Sub

You also might want to give a look at this link:
http://chandoo.org/forum/threads/wh...re-advanced-excel-knowledge.13895/#post-82305

Regards!
 
You must log in or register to reply here.
Back
Top