Reports

[OuTCasT]

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 ????

[ck9663]

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

[OuTCasT]

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 ????

[ck9663]

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

[OuTCasT]

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 ???

[ck9663]

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

[Delerna]

What kind of GUI

Intranet Web Page???
MSAccess Form???
Other????

[OuTCasT]

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

[CODE=sql]
Dim UnitPrice As Single
If TextBox1.Text = "" Then
MessageBox.Show ("You must select a product.", "Purchase Error", MessageBoxButto ns.OK, MessageBoxIcon. Information)
Exit Sub
End If
'Find unit price of selected product
Dim NRec As Integer
For NRec = 0 To productsTable.R ows.Count - 1
If productsTable.R ows(NRec).Item( "Description"). ToString = TextBox1.Text.T oString Then
UnitPrice = CSng(productsTa ble.Rows(NRec). Item("SellingPr ice"))
Exit For
End If
Next
Dim sqlcom1 As New SqlCommand("sel ect description from products where productid = '" & TextBox1.Text & "'", sqlCon)
Dim sqlcom2 As New SqlCommand("sel ect sellingprice from products where productid = '" & TextBox1.Text & "'", sqlCon)


lstCart.Items.A dd(Format(nudQu antity.Value, "##") + " " + TextBox1.Text.T oString + "-" + sqlcom1.Execute Scalar + " " + Format(sqlcom2. ExecuteScalar, "R0.00"))
'Adjust total price
lblTotal.Text = Format(Val(lblT otal.Text) + nudQuantity.Val ue * sqlcom2.Execute Scalar, "0.00")
Label2.Text = sqlcom1.Execute Scalar
TextBox1.Text = ""
[/CODE]

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
[CODE=sql]
select Date,ProductID, Description,Sto ckSold=quantity ,TotalSale = sellingprice * quantity, Income = (SellingPrice-buyingprice) * quantity
from orders
where date = '2008/01/25'[/CODE]

now i need all of these to be in a row in the listbox

DATE PRODUCTID DESCRIPTION STOCKSOLD TOTALSALE INCOME

[OuTCasT]

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

[Sindy Mon]

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.perpetuumso ft.com/Product.aspx?la ng=en&pid=21

That is enough pretty that you can try to do it using free version of the tool.

[OuTCasT]

Try also to use this reporting tool:http://www.perpetuumso ft.com/Product.aspx?la ng=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.

[Sindy Mon]

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.

[OuTCasT]

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.