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

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

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

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:

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!

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:

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

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

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 pr> convoluted,

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

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?

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

Anybody?

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)

cast(cast(sum((te.price*te.quantity)-(te.cost*te.quantity))/sum(te.price*te.quantity)