View source for Excel Client:Data Input/Planning with the CoPlanner Excel Client

==Data acquisition in formula mode==

The CoPlanner Excel Client allows you to use Microsoft Excel as a front-end for entering or updating data and saving it in the database of the CoPlanner server. 
Use the [[Excel Client|Analyzer]] to define the desired data and format them as needed. Then transfer the data to Microsoft Excel ([[Excel_Client:Transferring_a_Report_into_Excel|see Transferring a Report into Excel]]). Right-click on the inserted report to display the Microsoft Excel context menu with the extensions added by the CoPlanner Excel Client. Select “[[Excel_Client:Transferring_a_Report_into_Excel#Extended_context_menu|Convert to formulas]]”. A message box will appear. Click “Yes” in the message box to start the conversion. 

All value fields will now contain the CoPlanner Excel Client read formula ([[Excel_Client:CellRead|CellRead]]). To write the data into the database, you must activate the option “Write into database”. To activate this option, please open the [[Excel_Client:Options_dialogue|Options dialogue]] and check the check box next to this option. Then confirm your settings by clicking “Apply”. 
After you have done this, when a value is entered in a cell containing a read formula, the value will be applied in accordance with the filter settings when the write-protection and security settings make this possible.

[[Image:EC Data Input.jpg|thumb|center|Excel Client, Data Input/Planning with the CoPlanner Excel Client]]

Once the value has been successfully entered into the database, it will appear in the cell that was updated. Otherwise the cell will contain the string “N/P” for “Not Possible” (see [[Excel_Client:Constants_and_Return_Values|Constants and Return Values]]). If “Automatic updating” has been activated (see [[Excel_Client:Options_dialogue|Options]]), the cell will contain the value that was previously saved in the database.

{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;You can open the entire dimension if you press and hold the Alt key while double-clicking on the dimension element. 
|}


{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hinweis'''</span> &nbsp;The function "Data inputting to database" can only be activated by the PowerUser.
|}

==Select multiple cells when entering data==

When entering data in the CoPlanner Excel Client, you can select multiple cells for data entry.

[[Image:EC select multiple cells01.jpg|thumb|center|Excel Client, Select multiple cells when entering data]]

When you enter a value in the active cell, the same value will be entered in the selected cells. 

[[Image:EC select multiple cells02.jpg|thumb|center|Excel Client, Select multiple cells when entering data]]


==Entering data with Microsoft Excel references and formulas==

The CoPlanner Excel Client gives you the option of using Microsoft Excel cell references and formulas when entering data. 
 
[[Image:EC Entering data with Microsoft Excel references and formulas01.jpg|thumb|center|Excel Client, Entering data with Microsoft Excel references and formulas]]

After creating the formula or cell reference, press the Enter key to enter the value in CoPlanner.
 
[[Image:EC Entering data with Microsoft Excel references and formulas02.jpg|thumb|center|Excel Client, Entering data with Microsoft Excel references and formulas]]

A cell reference can refer to the following elements in one or more formulas: 
:*Data in a cell on the worksheet. 
:*Data in different regions of a worksheet. 
:*Data in cells on other worksheets in the same workbook. 

==Aids for entering data in CoPlanner value fields==
===Automatic completion===

CoPlanner provides the following shortcuts for entering values:
To enter 100,000 EUR, you can type “100t”. By analogy, “m” can be used as a short cut for millions.

 
[[Image:EC Automatic completion.jpg|thumb|center|Excel Client, Automatic completion]]

===Customising values===

When you press F2 in a CoPlanner value field, a data input dialogue opens.

[[Image:EC Customising values.jpg|thumb|center|Excel Client, Customising values]]

You can use this dialogue to change values without having to enter the entire contents all over again. 

{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;When entering text, the program automatically checks to ensure that the length of the text string you enter is not greater than the length of the text field in the CoPlanner database.
|}

This dialogue is available both in Formula Mode and in Dynamic Mode.

==Detail record view==

The CoPlanner Excel Client offers you the option of viewing a specific cell from all detail records from a matrix. 

Highlight the cell for which you want to view the detail records and select the command “Show detail rows” from the context menu.
 
[[Image:EC Context menu Show detail rows.jpg|thumb|center|Excel Client, Context menu: Show detail rows]]

The Detail Record View Assistant appears. This is a dialogue that consists of two steps. In Step One, you define the measures you wish to display.
 
[[Image:EC Show detail rows Measures .jpg|thumb|center|Excel Client, Show detail rows: Measures ]]

At this point you can click “Finish” to exit the dialogue. The program will create a workbook containing the detail records of the selected cell. 

In Step Two of the Detail Record View Assistant, you have the option of applying a style sheet to the table containing the detail records.
 
[[Image:EC Show detail rows Style.jpg|thumb|center|Excel Client, Show detail rows: Style]]

After clicking on “Finish”, a list of the detail records together with all available filters will be displayed.
 
[[Image:EC List of detail records.jpg|thumb|center|Excel Client, List of detail records]]


{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;The detail record view works in both Formula Mode and Dynamic Mode. 
|}


==Fix Values==

This function allows you to change individual values without changing the value of the total. The remaining values making up the total will be altered proportionately. 
The context menu in the data area contains the “Cell” command with a flyout menu showing “Fix value over ‘...’”
 
[[Image:EC Fix value01.jpg|thumb|center|Excel Client, Kontextmenü, Fix value over ‘...’]]

Whenever you want to fix values, the dimensions of the line or column axis will be available. 
In this example, the column “Absolute sales” contains the value of 1,000.- for five products
 
[[Image:EC Fix value02.jpg|thumb|center|Excel Client, Fix values over ‘Products’]]

The sum element for “Road Bianchi” contains the value 5,000.-. 
Select the command “Fix values over ‘Products’” from the context menu and then enter the value of 3,000.- in the element “928 SL IASP Super Record 11sp Double”.
 
[[Image:EC Fix value03.jpg|thumb|center|Excel Client, Fix values over ‘Products’; total remains the same]]

After recalculation, the sum element “Road Bianchi” still displays the value of 5,000.-, while the new value of 3,000.- appears in the element “928 SL IASP Super Record 11sp Double”. The difference has been deducted in equal amounts from the other elements.

==Data transfer==

Data transfer is available if this function has been activated in the input mask (see the section on the Data Assistant in the Framework Manual).
 
[[Image:EC Data transfer.jpg|thumb|center|Excel Client, Data transfer]]

Data transfer can be accessed via the context menu in the data area by selecting the “Cell” menu item. 


==Data Distribution==

Data distribution is available if this function has been activated in the input mask (see the section on the Data Assistant in the Framework Manual). 

Data distribution can be accessed via the context menu in the data area by selecting the “Cell” menu item. 

[[Image:EC Data Distribution.jpg|thumb|center|Excel Client, Data Distribution ]]

This function distributes the total value among the elements lying directly below it. 
 
[[Image:EC Data distribution across the Products dimension.jpg|thumb|center|Excel Client, Data distribution across the “Products” dimension]]


__NOEDITSECTION__
[[category:Excel_Client|Data Input]][[category:Excel_Client Tutorial|Data Input]]
[[de:Excel_Client:Datenerfassung/Planung_mit_dem_CoPlanner_Excel_Client]]