Viewing Report data in Microsoft Excel

Article author
Lucy Hynes
  • Updated

What is the purpose of the article?


To demonstrate how Meddbase users can export report data into Microsoft Excel.  This article will explain the step by step process using the example report 'Appointments by Date'.

How do you export report data?


To do this:

1. Find the report in which you wish to pull data into Microsoft Excel

1.1 In Meddbase, go to Admin > Report management

1.2 Select the report from the Reports menu

1.3 Copy the report URL

1b_-_Report_file_adminstration_-_Copy_the_report_URL

2. Paste the example URL as below onto your notepad:

MicrosoftTeams-image__5_.png

3. Open a new Microsoft Excel document

4. Name Sheet 1 in the Workbook parameters

5. Go to

5.1 Cell A1 and input the value username

5.3 Cell A2 and input parameter 1 i.e. date_from

The corresponding details need to be added in the cells in column B.

6. Go to

6.1 Cell B1 to input the username value

6.2 Cell B2 to input the input parameter value

2a_-_Excel_spreadsheet_showing_column_in_sheet_1_with_values_in_cells_A1_to_B2

You will need to repeat the above steps for each parameter in the query.

7. Add sheet 2 and name this data

8. Select Data in the ribbon at the top of Microsoft Excel

9. Select Get data > Legacy Wizards > From Web (legacy)

1b_-_Excel_-_Select_from_Web_legacy_in_Data_menu.png

10. In the New Web Query dialogue that appears

10.1 Paste the example URL saved on Notepad

10.2 Select Go

3_-_New_Web_Query

You will now see sample data.

11. Select the arrow next to this and then select the Import button at the bottom-right of the New Web Query dialog

An Import Data dialogue is presented asking where you want to put the data. From here

12.1 Ensure that cell A1 should be highlighted in the worksheet

12.2 Ensure the Existing worksheet radio button is selected in the Import Data dialog

12.3 Then select OK

3b1_-_Import_data_dialog_-_where_do_you_want_to_put_the_data.png

An Enter Parameter Value dialogue is presented asking you for your Username

13. In the dialogue

13.1 Input the Username value manually or reference the values you've added in the parameters sheet.

13.2 Tick 'Use this value/reference for future refreshes' if you do not want to enter the Username repeatedly

13.3 Click on OK

3c_-_Enter_parameter_value_dialog_-_username_with_parameter_value.png

Another Enter Parameter Value dialogue is presented again asking you for your Password

14. In the dialogue

14.1 Input the Password value

14.2 Click on OK

We would not recommend recording the password as a parameter in the spreadsheet. We would recommend you input the password value each time the data is obtained.

If there are parameters in the report such as the 'Date from' for appointments, these are presented in further Enter Parameter Value dialogs.

15 In any further dialogs that are presented:

15.1 Input the required value or select the required parameter

3d_-_further_parameter_dialogs_in_Excel.png

15.2 Click on OK

Once this is completed the report query will be run and you will now see the data in Microsoft Excel.

5_-_Results_in_excel.png

 

Did you know?


If you don't have access to Legacy Wizards in Microsoft Excel, you can activate this as follows

1. Go to File in the Ribbon the top left of the screen

2. Select Options

3. In the Excel Options dialog that appears go to Data

4. Tick option From Web (Legacy) in the Show legacy data inport wizards

6_-_Excel_application_Options_-_Selecting_Show_Excel_options.png

 

Review date


This article was last updated on 3rd of May 2022 in the context of Meddbase version 1.250.0.38261.