Software Quality Assurance & Testing Stack Exchange is a question and answer site for software quality control experts, automation engineers, and software testers. It only takes a minute to sign up.
Sign up to join this community
This is the first time I encountered such a problem.
There is an Excel table with a macro that selects data from the database using SQL query according to specific criteria. Data is grouped in different ways, then number of items is counted based on certain rules.
How it can be tested? Write my own queries to the database - I will check the database is working correctly :))
To motivate a point, I will describe two scenarios from a math class.
First, think back to when you took math tests in school. Sometimes your teacher asked you to check your work. One way to check your work is to calculate everything twice, but of course if you miscalculated because of a misunderstanding, you will probably get the same, wrong answer twice. Another way to check your answer is to calculate in a different way, or even better, to run the calculation backwards. So for example if you just calculated C = A + B, you check C by verifying that A = C - B.
Second, consider how a math teacher comes up with a test problem whose answer is a nice, round number. They have a choice: they can start with the nice round number and work backwards to determine the initial conditions, or do they can tinker with the initial conditions and recalculate until the initial conditions result in a nice round number.
Your choice is similar to the math teacher's: either (1) populate your database in such a way that you know what the report should produce, (2) use your own queries and your own calculations to determine what the spreadsheet should contain.
With either choice, you need to understand all the relevant requirements, but with this second choice, you also need to implement your own, bug-free queries and your own, bug-free formulas. Essentially, the second choice requires re-implementing the spreadsheet. If your results do not match what the spreadsheet produces, you must decide which of you is wrong. If you are not absolutely sure about your queries or your calculations, it may be tempting to peek at how the spreadsheet does it and amend your work accordingly until the results match. But of course if you borrow queries or formulas from the spreadsheet in order to get your results to match the spreadsheets, you have transitioned from testing the spreadsheet to reproducing the spreadsheet.
(This is a common phenomenon, especially when the requirements are vague or not written down at all. I knew a tester who chose the "reimplement" approach to test something that imported data from an OLTP database into an OLAP database. The developer's requirement was simply "implement an OLAP database that you can use to implement this set of reports". You could test the reports, but it wasn't clear what it meant to test "import" software because there were no clear requirements. Since the requirements were unclear, the tester looked at the developer's queries and then wrote his own queries that calculated the same thing. Of course if the developer's queries had bugs, the tester's probably would too.)
I suggest you consider the initial work and maintenance cost (if any) of both options. The answer may depend on the complexity of the spreadsheet and available of resources (e.g. availability of additional "test databases").
