Exporting data from Rosemark can be done in a number of ways. The most common method is via the Shifts on Day window. To navigate there begin by going to the Office tab in the main Rosemark window and select Shifts on Day.

The system will prompt you to select a day, choose today's date to get started.

You can use the filters at the top of the Shifts on Day window to adjust the data that is being exported. 

Once you have the correct information selected go to File and select “Export shifts to spreadsheet”. 

Once you have selected the export option you can further include and exclude fields as needed. Fields beyond the scope of just the shift are available as well including client, caregiver, and customer fields. 

Once you have made your final selections click the Export button in the bottom right and you will be prompted to save the export file. You can rename or save the file however you see fit.

Once you have your data export from Rosemark open the file in Excel. To create a pivot table begin by selecting all of the data, the easiest way to do this is clicking the Select All button on the top left of the spreadsheet. The cells should change from white to grey indicating they have been selected.

With the data selected click the Insert tab in Excel. Then click the first option on the left, Pivot Table.

This will cause a new window to appear, here you can select if you want the Pivot Table added to an existing sheet or on a new sheet. We normally advise adding the Pivot Table to a new worksheet to make it easier to manage and format. 

The Pivot Table sheet will have a distinct layout. In the image below in the Red highlighted area you can see where the data will fill in once it has been added to the table. The orange box along the box will allow you to move between sheets in the same Excel document. The exported data will remain on its own tab while the new tab will be your pivot table. Along the right side of the window there are two components, the yellow box shows the fields that were exported, and the green box shows different ways to format the data. 

Pivot Table Parts

• Filters - These can be added to adjust the data within the pivot table. Common filters for Rosemark users include filtering by service type, client or caregiver status (active, inactive, discontinued), or by date.
• Rows - This is where most data is sorted. 
• Columns - This is a rarely used section, but is most often used when looking at data over a long period of time.
• Values - This box can be used to calculate different values (sum, average, minimum and maximum values).

 

Most Pivot Tables are made by adding the relevant data from Rosemark to the Rows an Values box. As you check a box in the exported fields it will be added to the Pivot table. You can rearrange the data by dragging and dropping the fields in whatever order is needed. 

Once the required data is in the Pivot table you will likely need to format it to make it more readable. To format the report begin by going to the Design tab in Excel. Below you'll find a more detailed breakdown of the options.

Subtotals

Subtotals can be broken down on a number of levels. For most reports we suggest the Do Not Show Subtotals option.

 

 

Grand Totals

Grand Totals look at overall numbers. For most reports we suggest the Off for Rows and Columns option.

 

 

Report Layout

By default the Pivot Table will be made in Compact Form. We normally suggest you change this to the Tabular Form.

Item Labels - To Repeat or Not Repeat

By default Excel does not repeat item labels. If at any point you are going to upload the report (for example to a payroll company) or will need to copy and paste the pivot table to another worksheet or Excel document you will likely want to repeat all item labels.

 

 

 

Once the data has been formatted it becomes more readable. There are additional formatting options, such as Conditional Formatting, that can be used to further format the data. For questions about additional formatting options please reach out to the Customer Success team.