Today, MTD, YTD Cost, Retail Gr Profit, and Margin % report

Does anybody have a report that will show the below information on one report?
Today Cost, Today Retail, Today Profit, Today Margin % MTD Cost, MTD
Retail, MTD Profit, MTD Margin % YTD Cost, YTD Retail, YTD Profit, YTD Margin %
So in other words I need the Cost, Retail, Profit, and Profit Margin % for Today, Month To Date, and Year To Date time frames in one report.
For now I just need these numbers for the entire store but it would even be better to have them broken down per Department and Category as well.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Alex, We do not have a report that can do this, and in fact an RMS report would be very difficult to make happen in this manner. We do however have a tool called Advanced Purchase Order that make this and other information readily available when creating purchase orders. If you are interested please contact Rick Feuling at my office who is in charge of that project. He can be reached via email at snipped-for-privacy@rite.us or on the phone at 1-888-267-RITE
--
Thank you,
Ryan Sakry
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
I was hoping that somebody had already written the SQL query that would extract these numbers out of the database and the report could easily figure out the percentages (just like the summary sales report for sales today, week to date, month to date, and year to date).
It seems strange that your module is called Advanced Purchase Order and but it actually generates report on historical sales and margins?
Anybody else that would be able to write this query for me to extract today's cost and sales, MTD cost and sales, and YTD cost and sales? I was trying to save myself some time....

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Hi Alex - Ryan's got a point - it'll be pretty difficult to pull in a single sql statement - what you may be able to do is create a couple of view for MTD and YTD then bring in those values into a sql query that also shows "today" or "week to date" - I've done this before and it got me the info I needed.
If you need help creating the views repost - but see if you can give it a shot yourself - if you're involved with RMS for the long haul it'll be worth the time invested.
Hope this helps
"Alex" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Convoluted,
Thanks, for your pointers I'll have to take a closer look at this over the next couple of days. Meanwhile if anybody else would like to jump in or post some similar reports/solutions please do so!

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Hi Alex, in case you still need this, I had a little downtime today and figured I'd see if I could pull off your need to see today, mtd and ytd on the same query. Getting the margin percent to show the percent sign posed a little challenge but I eventually found the right syntax on books online.
Unfortunately I don't have headquarters on my machine, so this works on a store operations database. If you need it for headquarters join transactionentry and transaction tables to the store table on your views.
First I created three views, one for each time period
create view view_gross_today as select c.storename as Store_Name, (cast(sum(te.cost*te.quantity) as money)) as Today_COGS, (cast(sum(te.price*te.quantity) as money)) as Today_Retail, cast(sum((te.price*te.quantity)-(te.cost*te.quantity)) as money) as Today_Profit, cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity) * 100 as numeric(10,2)) as varchar(5)) +'%' as [Today_M%] from transactionentry te inner join [transaction] t on te.transactionnumber = t.transactionnumber inner join configuration c on te.storeid = c.storeid where t.time >= dateadd(dd,datediff(dd,0,getdate()),0) group by c.storename
create view view_gross_mtd as select c.storename as Store_Name, (cast(sum(te.cost*te.quantity) as money)) as MTD_COGS, (cast(sum(te.price*te.quantity) as money)) as MTD_Retail, cast(sum((te.price*te.quantity)-(te.cost*te.quantity)) as money) as MTD_Profit, cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity) * 100 as numeric(10,2)) as varchar(5)) +'%' as [MTD_M%] from transactionentry te inner join [transaction] t on te.transactionnumber = t.transactionnumber inner join configuration c on te.storeid = c.storeid where t.time >= dateadd(mm,datediff(mm,0,getdate()),0) group by c.storename
create view view_gross_ytd as select c.storename as Store_Name, (cast(sum(te.cost*te.quantity) as money)) as YTD_COGS, (cast(sum(te.price*te.quantity) as money)) as YTD_Retail, cast(sum((te.price*te.quantity)-(te.cost*te.quantity)) as money) as YTD_Profit, cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity) * 100 as numeric(10,2)) as varchar(5)) +'%' as [YTD_M%] from transactionentry te inner join [transaction] t on te.transactionnumber = t.transactionnumber inner join configuration c on te.storeid = c.storeid where t.time >= dateadd(yy,datediff(yy,0,getdate()),0) group by c.storename
Then I pulled them all into one query...
select today_cogs, today_retail, today_profit, [today_M%], mtd_cogs, mtd_retail, mtd_profit, [mtd_m%], ytd_cogs, ytd_retail, ytd_profit, [ytd_m%] from view_gross_today t inner join view_gross_mtd mtd on t.store_name = mtd.store_name inner join view_gross_ytd ytd on t.store_name = ytd.store_name
Hope this helps....
"Alex" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
convoluted, I could use this info as well, but don't know what to do with it. Do you run these queries in Admin, all at the same time, separate, or in excel. Could you please give me a few details on how to run it. Thanks. Craig

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Hi Craig - the queries are run in Store Ops Administrator - connect to your database and backup your database, then open a new query, and run the first "create view" statement (feel free to paste my syntax using ctrl-c and ctrl-v) then execute the query using F5 or Query-Run)
create view view_gross_today as select c.storename as Store_Name yada yada yada - - in the results screen you'll see "-1 record affected"
to confirm that the view was created successfully, run select * from view_gross_today - - you will see the info for "today"
open a new query and run the second view creation create view view_gross_mtd as select c.storename as Store_Name yada yada yada - - in the results screen you'll see "-1 record affected"
Do the same for the third view
Once the three views are created, you can pull all view columns into one query, which I think is what Alex was trying to do. The last query does the trick - run it by opening a new query and type
select today_cogs, today_retail, today_profit, [today_M%], mtd_cogs, mtd_retail, mtd_profit, [mtd_m%], ytd_cogs, ytd_retail, ytd_profit, [ytd_m%] from view_gross_today t inner join view_gross_mtd mtd on t.store_name = mtd.store_name inner join view_gross_ytd ytd on t.store_name = ytd.store_name
**Remember to backup the database** - If I have some time, I'll try to get this to print on a custom report (I've never tried doing my own reports based on my own views in the RMS database - it should be fun) - hope this helps...
"Craig" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Thanks Convoluted I appreciate it and if you can get it to run in a custom report that would be great so the RMS end users can actually use it. I wasn't sure if you can create more then 1 view in (the pre-query section of) a custom report, can you?

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Anybody?

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.