Loading...
Hi, If I have a table like this:


Date Deposit/Withdraw
---------------------------------
01/01 +5,000.00
01/12 -500.00
02/14 +1200.00
03/15 -300.00


Then how to build a report based on the table above with a column which displays the balance automatically?


Date Deposit/Withdraw Balance
--------------------------------------------------
01/01 +5,000.00 +5,000.00
01/12 -500.00 +4,500.00
02/14 +1200.00 +5,700.00
03/15 -300.00 -5,400.00


Thanks!

Matheus

Replies(6)

Hi Matheus,

Thanks for using Zoho DB & Reports.

Sorry for the delay. It is possible to create a column containing cumulative sum of another column. It can be achieved in Zoho DB & Reports by using Query table.

Steps to create a new Query Table View:

1. Login to your Zoho DB account. Open the datasheet view(the view where all the records of the table are displayed) of the table which contains the columns 'Date','Deposit/Withdraw'.
2. Click on the 'New Query Table' menuitem from the 'New' menu available in the toolbar.
3. Copy and paste the below query in the text area after removing the existing sample query:

SELECT a.Date_Column 'Date Column', a."Deposit/Withdraw" 'Deposit/Withdraw', sum(b."Deposit/Withdraw") 'Balance' FROM "Table_Name" a, "Table_Name" b WHERE a.Date_Column >= b.Date_Column GROUP BY a.Date_Column

In the above query replace the 'Table_Name' with your table name. There is an issue in Zoho DB & Reports when there is a column named 'Date' in the table. Kindly rename the column named 'Date' as 'Date_Column' in your table. Right click on the column header 'Date' and click on the menuitem 'Rename column' to rename the column name.

4. Click on 'Execute' button. It displays a preview table with maximum ten rows.
5. Save the view by clicking on 'Save' button.
6. After saving, click on 'View Mode' button to view the table.

Kindly look at the below link for Video tutorials:
zohodbhelp.wiki.zoho.com/Video-Demo.html

If you need any assistance in doing the above steps please contact us in the toll free number 888 900 9646 we will be happy to assist you. Or please let us know your convenient time and your contact number to support@zohodb.com, so that we can arrange for a demo session through online meeting(meetings.zoho.com).

Thanks,
Ragupathi,
Zoho DB & Reports - Online Reporting and Business Intelligence
Hi, thank you for your reply, it worked flawlessly. Going a little further, I added a new column, "User", to my original table, as below:


Date Deposit/Withdraw User
--------------------------------------------------
01/01 +5,000.00 Dave
01/12 -500.00 John
02/14 +1200.00 John
03/15 -300.00 Dave


I need to calculate balance as before, but now taking users in account. Example:


Date Deposit/Withdraw User Balance
------------------------------------------------------------------
01/01 +5,000.00 Dave +5,000.00
01/12 -500.00 John -500.00
02/14 +1200.00 John +700.00
03/15 -300.00 Dave +4,700.00


What SQL query will achieve this?

Thanks,

Matheus
Hi Matheus,

Thanks for using Zoho DB & Reports.

The following query will give you the expected result

SELECT a.Date_Column 'Date Column', a."Deposit/Withdraw" 'Deposit/Withdraw',a."User" 'User', sum(b."Deposit/Withdraw") 'Balance' FROM "Table_Name" a, "Table_Name" b WHERE a.Date_Column >= b.Date_Column and a."User" = b."user" GROUP BY a.Date_Column

Thanks,
Ragupathi,
Zoho DB & Reports - Online Reporting and Business Intelligence

Hi there,

Could you please specify if there is any possibility to implement the same in Zoho Creator? Is there any possibility to access Zoho Creator tables from this system?

I would really appreciate your help!

 

Thanks!

Jane McCarty

http://www.webappsatwork.blogspot.com 

Hi Jane,

Sorry for the delayed response.

This can be achieved with a little bit of Deluge Scripting.

Lets say, you have a Form with fields Name, Dep/WD and Balance. You can write a Script in the On Validate of the Form as,



if (count(form1[name == input.name])  ==  0)
{
    input.balance = input.dep_wd;
}
else if (count(form1[name == input.name])  >  0)
{
    dat  =  form1  [name == input.name] sort by  Modified_Time desc range from 1 to 1;
    input.balance = (dat.balance  +  input.dep_wd);
}
The first if Statement checks if the name already exists. If not, then make the Balance as the Dep/WD.
The second if statement adds/subtracts the Dep/WD from the Balance if the name already exists. If a particular User does some transactions, then the variable "dat" will have more than one record with different Balances. So, we have to fecth only the Last Modified one, which is why we are using the Sort By.

Get back to us if you face any further queries.

Regards,
Harsha.

Hi,

Sorry, we missed out another question.

Please find below the response for the same:

Q: Is there any possibility to access Zoho Creator tables from this system?


You can access the data from Zoho Creator into Zoho DB & Reports using 'Schedule Import' Option.

Please follow the below steps to import data into Zoho DB & Reports from Zoho Creator.

  • Login to Zoho Creator.
  • Open the view which you want to import the data into Zoho DB & Reports.
  • Click More Actions -> Export Data.
  • Click on the CSV Feed.(Make sure that the generated link does not require any login)
  • Copy the URL.


Steps to import data into Zoho DB & Reports periodically.

  • Go to Zoho DB & Reports.
  • Create a new table.
  • Select the Data location as "web"
  • Paste the URL that is copied from Zoho Creator.
  • Proceed importing.
  • Click on the link "Would you like to schedule this import periodically?" at the 3rd step.
  • Schedule settings dialog will be opened.
  • Please fill the options URL, How do you want to import?, Specify the period to fetch data from Zoho Creator.
  • Save the settings.

Now Zoho DB & Reports scheduler will start running and imports the data from Zoho Creator in the particular interval that you have specified.

You can also disable/edit the schedule settings by opening the Schedule settings dialog(Click Import > Refetch/Schedule import).
 
To know more about this, checkout the following URL:

http://blogs.zoho.com/general/zoho-db-reports-integration-with-zoho-creator-step-1/

Please get back to us for further clarification.

Thanks,
S Balaganesh
Zoho DB & Reports - Online Reporting and Business Intelligence