By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

Reports

OuTCasT
100+
P: 374
I am trying to write a stock report using data from 2 tables, products table and invoice table.

how it works is, everytime something is purchased that info is saved in the invoice table.
now i need to work out the total amount of each item bought in a day and the percentage of profit accumulated.

i was trying to use Crystal Reports but all the formulaes are taking abit long to learn.
i jst wld like to use sql queries to retrieve the values i need, multiply/subtract them and find the percentage of profit for each item per report.

IS THIS POSSIBLE ????
Jan 23 '08 #1
Share this Question
Share on Google+
12 Replies


ck9663
Expert 2.5K+
P: 2,878
I am trying to write a stock report using data from 2 tables, products table and invoice table.

how it works is, everytime something is purchased that info is saved in the invoice table.
now i need to work out the total amount of each item bought in a day and the percentage of profit accumulated.

i was trying to use Crystal Reports but all the formulaes are taking abit long to learn.
i jst wld like to use sql queries to retrieve the values i need, multiply/subtract them and find the percentage of profit for each item per report.

IS THIS POSSIBLE ????
would you mind posting any query that you have so far? we might also need to see the structure of your table, their relationship and the formula to get all these

-- ck
Jan 23 '08 #2

OuTCasT
100+
P: 374
would you mind posting any query that you have so far? we might also need to see the structure of your table, their relationship and the formula to get all these

-- ck
ok i have a table.....that has all the details necessary.
ProductID, Buying Price, Selling PRice, DateOf purchase , qty soldetc

now i need to find the percentage sold for each product. and the profit from each product which i think is easy enough, take the selling price and subtract the buying price from it and just multiply it by the qty sold.

and so for the percentage aswell.

Now do i use a SUM statement to work all that out ????
Jan 25 '08 #3

ck9663
Expert 2.5K+
P: 2,878
you can create a function or a view that will store the query...your query could look something like:

select ProductID, BuyingPrice, SellingPRice, DateOf purchase , qty,
totalsale = BuyingPrice * qty, Income = (SellingPrice-BuyingPrice) * qty
from mytable
where....

you can also create summaries by grouping ProductID...

-- ck
Jan 25 '08 #4

OuTCasT
100+
P: 374
you can create a function or a view that will store the query...your query could look something like:

select ProductID, BuyingPrice, SellingPRice, DateOf purchase , qty,
totalsale = BuyingPrice * qty, Income = (SellingPrice-BuyingPrice) * qty
from mytable
where....

you can also create summaries by grouping ProductID...

-- ck
well well, that did help a little.
Crystal Reports doesnt make it very easy unless you have done a full tutorial on it or had alot of experience with the program.

So i took the code u put in here to retrieve all the data that i want by the date selected in the datetimepicker.....ok so how do i get that information into a form, what control would i use......like a listbox or wat ???
Jan 25 '08 #5

ck9663
Expert 2.5K+
P: 2,878
well well, that did help a little.
Crystal Reports doesnt make it very easy unless you have done a full tutorial on it or had alot of experience with the program.

So i took the code u put in here to retrieve all the data that i want by the date selected in the datetimepicker.....ok so how do i get that information into a form, what control would i use......like a listbox or wat ???
that would depend on what kind of GUI you are using...

-- ck
Jan 25 '08 #6

Delerna
Expert 100+
P: 1,134
What kind of GUI

Intranet Web Page???
MSAccess Form???
Other????
Jan 25 '08 #7

OuTCasT
100+
P: 374
What kind of GUI

Intranet Web Page???
MSAccess Form???
Other????
its a normal windows form application.
i used a listbox for my shopping cart when they buy things
like this

Expand|Select|Wrap|Line Numbers
  1. Dim UnitPrice As Single
  2.         If TextBox1.Text = "" Then
  3.             MessageBox.Show("You must select a product.", "Purchase Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
  4.             Exit Sub
  5.         End If
  6.         'Find unit price of selected product
  7.         Dim NRec As Integer
  8.         For NRec = 0 To productsTable.Rows.Count - 1
  9.             If productsTable.Rows(NRec).Item("Description").ToString = TextBox1.Text.ToString Then
  10.                 UnitPrice = CSng(productsTable.Rows(NRec).Item("SellingPrice"))
  11.                 Exit For
  12.             End If
  13.         Next
  14.         Dim sqlcom1 As New SqlCommand("select description from products where productid = '" & TextBox1.Text & "'", sqlCon)
  15.         Dim sqlcom2 As New SqlCommand("select sellingprice from products where productid = '" & TextBox1.Text & "'", sqlCon)
  16.  
  17.  
  18.         lstCart.Items.Add(Format(nudQuantity.Value, "##") + " " + TextBox1.Text.ToString + "-" + sqlcom1.ExecuteScalar + " " + Format(sqlcom2.ExecuteScalar, "R0.00"))
  19.         'Adjust total price
  20.         lblTotal.Text = Format(Val(lblTotal.Text) + nudQuantity.Value * sqlcom2.ExecuteScalar, "0.00")
  21.         Label2.Text = sqlcom1.ExecuteScalar
  22.         TextBox1.Text = ""
  23.  
that just brought all the values that i needed into the listbox from the use input....not underneath each other but in a row.....
now i need to pull the report using the same means i think, is there another way instead of using a listbox ????

this is the code i was using
Expand|Select|Wrap|Line Numbers
  1. select Date,ProductID,Description,StockSold=quantity ,TotalSale = sellingprice * quantity, Income = (SellingPrice-buyingprice) * quantity
  2. from orders
  3. where date = '2008/01/25'
now i need all of these to be in a row in the listbox

DATE PRODUCTID DESCRIPTION STOCKSOLD TOTALSALE INCOME
Jan 26 '08 #8

OuTCasT
100+
P: 374
I got the reports to work using a gridview control.
used a table adapter and data table to retrieve the info from database and insert it into the gridview.


Thanks
Jan 28 '08 #9

P: 3
I am trying to write a stock report using data from 2 tables, products table and invoice table.

how it works is, everytime something is purchased that info is saved in the invoice table.
now i need to work out the total amount of each item bought in a day and the percentage of profit accumulated.

i was trying to use Crystal Reports but all the formulaes are taking abit long to learn.
i jst wld like to use sql queries to retrieve the values i need, multiply/subtract them and find the percentage of profit for each item per report.

IS THIS POSSIBLE ????
Try also to use this reporting tool:http://www.perpetuumsoft.com/Product.aspx?lang=en&pid=21

That is enough pretty that you can try to do it using free version of the tool.
Jan 28 '08 #10

OuTCasT
100+
P: 374
Try also to use this reporting tool:http://www.perpetuumsoft.com/Product.aspx?lang=en&pid=21

That is enough pretty that you can try to do it using free version of the tool.
i will try that thankyou.
Jan 28 '08 #11

P: 3
i will try that thankyou.

Did you manage to try this tool? Was it useful?

I am very interested in your experience / opinion on it.
Feb 1 '08 #12

OuTCasT
100+
P: 374
Did you manage to try this tool? Was it useful?

I am very interested in your experience / opinion on it.
I did check it out and its pretty impressive.
But i wrote a datagridview extension which lets me print from a datagridview, so i just pulled the items i needed into the datagridview and did all the calculations using sql queries and it works 100%,

Only problem is i cant get the SUMMED totals from the column i use to add a row into the datagrid so that the total values that i want to display are printed with the datagridview.
Feb 1 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.