[Solved] Base/Calc confusion

Discuss the spreadsheet application
Post Reply
Tim_D
Posts: 2
Joined: Sat Mar 18, 2017 12:09 am

[Solved] Base/Calc confusion

Post by Tim_D »

Hi,
I am trying to convert from Excel. I have numerous spreadsheets that connect to a mssql server run a query and return the data to the ss. I then run calculations on that data. All pretty vanilla to be honest.

In Calc/Base I have made the connection to the server, built a query and am getting the data into the ss, all good. I simply cannot figure out how to run calculations against that data. Things like, the number of work hours something has been up, net work days taking holidays and weekends into consideration, etc... When I bring the data in from the query, calc simply erases all the fields that contained my calculations. Clearly I am missing something.

My SQL query is very simple, just grabbing selective fields based on time-ranges.

My ss is also simple, just doing date and time math, defining source and dest of defined groups, etc... really nothing magical at all.

Some of the existing excel docs are meant to operate on data that changes hourly so I am at a loss as to where to even start.

MS is NOT the answer for me, but they have made it so easy to stay...like smoking, breaking the habit is hard but it must be done.

Any help out there in terms of tutorials? or even word to the wise sort of things....
Last edited by Tim_D on Mon Mar 20, 2017 3:39 pm, edited 1 time in total.
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Base/Calc confusion

Post by RusselB »

It sounds like you're importing the data into fields that contain the formulas you want, thus making the formulas redundant.
I can only make educated guesses as the details would be in your actual spreadsheet/database
In Calc figuring out the number of hours is a simple subtraction (end time - start time)
Calc has a NETWORKDAYS function that uses 3 parameters. Start date, end date, holidays
I recommend using a small table containing the dates of the holidays, then reference that range for the holidays parameter.
NETWORKDAYS is in the help file brought up when running Calc
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base/Calc confusion

Post by Villeroy »

[Tutorial] Using registered datasources in Calc
Get into the properties of the import ranges and check extra options "insert cells" and "keep formatting". Database links make no sense with these options unchecked. In OpenOffice they are checked by default.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Tim_D
Posts: 2
Joined: Sat Mar 18, 2017 12:09 am

Re: Base/Calc confusion

Post by Tim_D »

FIrst: Thank you both for your replies. They were appreciated.
RussekB - Actually what I am trying to accomplish is to just import the raw data. For example: 10 fields of a 80+ field database sorted on the contents of one of those fields. Thus the number of records can vary at different points of the same day. Anything from 60-160 or more. So let's say a flat spreadsheet of 10 columns by a variable number of rows. I am then starting my calculations in column K (in this example) through column N. I want to be able to refresh the flat ss data without losing the formulae I have created in columns K-N.

which brings me to

Villeroy: I believe checking those options is exactly what I need, although I have not yet had the chance to test it out. Now to figure out how to make my calculations expand and contract rows as the imported data adds and shrinks rows. I'm hoping something in the tutorial will show me what I need to do. In excel it just magically happens (again with the smoking analogy)

At any rate, I feel confident enough that Villeroy provided the answer for me that I will mark this Solved.

Thanks again!
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
Post Reply