Average Sales per Store

Need help modifying the RMS "Average Sales per Store" report that is found in 
CustomerSource. Would like to add filters for Department and Category if 
possible. I have tried to modify it myself, but I'm a beginner at this and I 
keep coming up short.  
0
10/4/2007 2:39:01 AM
pos 14173 articles. 0 followers. Follow

6 Replies
1027 Views

Similar Articles

[PageSpeed] 23

One other thing that may be useful is if it can summarize by date also - 
would be nice to see how the values change over the course of a week or so.

"Jason Kelton" wrote:

> Need help modifying the RMS "Average Sales per Store" report that is found in 
> CustomerSource. Would like to add filters for Department and Category if 
> possible. I have tried to modify it myself, but I'm a beginner at this and I 
> keep coming up short.  
0
10/4/2007 2:44:00 AM
Hi Jason,

If you are interested in custom reports , please drop a email to discuss 
further.

Regards,
manick
manick.m@hotmail.com

"Jason Kelton" wrote:

> One other thing that may be useful is if it can summarize by date also - 
> would be nice to see how the values change over the course of a week or so.
> 
> "Jason Kelton" wrote:
> 
> > Need help modifying the RMS "Average Sales per Store" report that is found in 
> > CustomerSource. Would like to add filters for Department and Category if 
> > possible. I have tried to modify it myself, but I'm a beginner at this and I 
> > keep coming up short.  
0
manick (194)
10/4/2007 4:12:00 AM
hi Jason,

open the file in notepad and do the following changes after INTO 
ViewStoreQty and repalce this

   FROM [Transaction] 
      LEFT JOIN TransactionEntry ON [Transaction].TransactionNumber = 
TransactionEntry.TransactionNumber
      LEFT JOIN Item ON TransactionEntry.ItemID=Item.ID
      LEFT JOIN Department ON Item.DepartmentID=Department.ID
      LEFT JOIN Category ON Item.CategoryID=Category.ID
      GROUP BY CONVERT(nvarchar, [Transaction].Time, 
101),Department.Name,Category.Name

Then go below the code and before at the End ReportSummary section you will 
see GroupBy: replace with it:
GroupBy = "VIEWStoreQty.DeptName,VIEWStoreQty.CatName"

Now go again below the whole file and add this:
Begin Column
   FieldName = "VIEWStoreQty.DeptName"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Department"
   VBDataType = vbString
   Formula = "VIEWStoreQty.DeptName"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2595
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "VIEWStoreQty.CatName"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Department"
   VBDataType = vbString
   Formula = "VIEWStoreQty.CatName"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2595
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Run the report have the date paramter already there so what you wanted to 
change.


"Jason Kelton" wrote:

> Need help modifying the RMS "Average Sales per Store" report that is found in 
> CustomerSource. Would like to add filters for Department and Category if 
> possible. I have tried to modify it myself, but I'm a beginner at this and I 
> keep coming up short.  
0
AkberAlwani (916)
10/4/2007 5:13:01 AM
Akber,
Thanks for that. I made those changes, but I am getting an "Invalid column 
name 'DeptName'" error message when I run the report. I noticed that you have 
used the field VIEWStoreQty.DeptName - is there such a field in that table?

"Akber Alwani" wrote:

> hi Jason,
> 
> open the file in notepad and do the following changes after INTO 
> ViewStoreQty and repalce this
> 
>    FROM [Transaction] 
>       LEFT JOIN TransactionEntry ON [Transaction].TransactionNumber = 
> TransactionEntry.TransactionNumber
>       LEFT JOIN Item ON TransactionEntry.ItemID=Item.ID
>       LEFT JOIN Department ON Item.DepartmentID=Department.ID
>       LEFT JOIN Category ON Item.CategoryID=Category.ID
>       GROUP BY CONVERT(nvarchar, [Transaction].Time, 
> 101),Department.Name,Category.Name
> 
> Then go below the code and before at the End ReportSummary section you will 
> see GroupBy: replace with it:
> GroupBy = "VIEWStoreQty.DeptName,VIEWStoreQty.CatName"
> 
> Now go again below the whole file and add this:
> Begin Column
>    FieldName = "VIEWStoreQty.DeptName"
>    DrillDownFieldName = ""
>    DrillDownReportName = ""
>    Title = "Department"
>    VBDataType = vbString
>    Formula = "VIEWStoreQty.DeptName"
>    ColHidden = False
>    ColNotDisplayable = False
>    FilterDisabled = False
>    ColWidth = 2595
>    GroupMethod = groupmethodNone
>    ColFormat = ""
> End Column
> 
> Begin Column
>    FieldName = "VIEWStoreQty.CatName"
>    DrillDownFieldName = ""
>    DrillDownReportName = ""
>    Title = "Department"
>    VBDataType = vbString
>    Formula = "VIEWStoreQty.CatName"
>    ColHidden = False
>    ColNotDisplayable = False
>    FilterDisabled = False
>    ColWidth = 2595
>    GroupMethod = groupmethodNone
>    ColFormat = ""
> End Column
> 
> Run the report have the date paramter already there so what you wanted to 
> change.
> 
> 
> "Jason Kelton" wrote:
> 
> > Need help modifying the RMS "Average Sales per Store" report that is found in 
> > CustomerSource. Would like to add filters for Department and Category if 
> > possible. I have tried to modify it myself, but I'm a beginner at this and I 
> > keep coming up short.  
0
10/4/2007 1:56:02 PM
hi Jason, sorry I mistakenly copied the half portion of query here is full 
code:
SELECT 
	CONVERT(nvarchar, [Transaction].Time, 101)  as DayOfYear,
        SUM(TransactionEntry.Cost*TransactionEntry.Quantity) as TotalCost,  
	SUM(TransactionEntry.Quantity) as TotalQty,
        Department.Name DeptName,Category.Name CatName

   INTO VIEWStoreQty

   FROM [Transaction] 
      LEFT JOIN TransactionEntry ON [Transaction].TransactionNumber = 
TransactionEntry.TransactionNumber
      LEFT JOIN Item ON TransactionEntry.ItemID=Item.ID
      LEFT JOIN Department ON Item.DepartmentID=Department.ID
      LEFT JOIN Category ON Item.CategoryID=Category.ID
      GROUP BY CONVERT(nvarchar, [Transaction].Time, 
101),Department.Name,Category.Name


"Jason Kelton" wrote:

> Akber,
> Thanks for that. I made those changes, but I am getting an "Invalid column 
> name 'DeptName'" error message when I run the report. I noticed that you have 
> used the field VIEWStoreQty.DeptName - is there such a field in that table?
> 
> "Akber Alwani" wrote:
> 
> > hi Jason,
> > 
> > open the file in notepad and do the following changes after INTO 
> > ViewStoreQty and repalce this
> > 
> >    FROM [Transaction] 
> >       LEFT JOIN TransactionEntry ON [Transaction].TransactionNumber = 
> > TransactionEntry.TransactionNumber
> >       LEFT JOIN Item ON TransactionEntry.ItemID=Item.ID
> >       LEFT JOIN Department ON Item.DepartmentID=Department.ID
> >       LEFT JOIN Category ON Item.CategoryID=Category.ID
> >       GROUP BY CONVERT(nvarchar, [Transaction].Time, 
> > 101),Department.Name,Category.Name
> > 
> > Then go below the code and before at the End ReportSummary section you will 
> > see GroupBy: replace with it:
> > GroupBy = "VIEWStoreQty.DeptName,VIEWStoreQty.CatName"
> > 
> > Now go again below the whole file and add this:
> > Begin Column
> >    FieldName = "VIEWStoreQty.DeptName"
> >    DrillDownFieldName = ""
> >    DrillDownReportName = ""
> >    Title = "Department"
> >    VBDataType = vbString
> >    Formula = "VIEWStoreQty.DeptName"
> >    ColHidden = False
> >    ColNotDisplayable = False
> >    FilterDisabled = False
> >    ColWidth = 2595
> >    GroupMethod = groupmethodNone
> >    ColFormat = ""
> > End Column
> > 
> > Begin Column
> >    FieldName = "VIEWStoreQty.CatName"
> >    DrillDownFieldName = ""
> >    DrillDownReportName = ""
> >    Title = "Department"
> >    VBDataType = vbString
> >    Formula = "VIEWStoreQty.CatName"
> >    ColHidden = False
> >    ColNotDisplayable = False
> >    FilterDisabled = False
> >    ColWidth = 2595
> >    GroupMethod = groupmethodNone
> >    ColFormat = ""
> > End Column
> > 
> > Run the report have the date paramter already there so what you wanted to 
> > change.
> > 
> > 
> > "Jason Kelton" wrote:
> > 
> > > Need help modifying the RMS "Average Sales per Store" report that is found in 
> > > CustomerSource. Would like to add filters for Department and Category if 
> > > possible. I have tried to modify it myself, but I'm a beginner at this and I 
> > > keep coming up short.  
0
AkberAlwani (916)
10/6/2007 6:33:01 AM
Hi there,

Retail Analytics, from Professional Advantage, is designed to help retailers 
manage their business by delivering analytical reporting on business 
performance. Easily identify trends, buying patterns, seasonality, and other 
key performance indicators for your business.   If you enter it in RMS you 
can report on it in Retail Analytics.

If you would like to take a quick look at the product, please go to 
http://www.profad.com/files/RAMP%20Movie/RADemoProfessionalAdvantage.wmv

We also have the UCAP (Up Close and Personal) program available.  UCAP - we 
have streamlined a process to get RMS data in house, load the product and 
then demonstrate the full power of the product on the data - for FREE!

Please do not hesitate to contact me if you have any questions or if there 
is anything that we can do for you.

Thanks,

Jen Bridgeford
701 235 2363 ext 248
jenl@profad.com

"Jason Kelton" wrote:

> Need help modifying the RMS "Average Sales per Store" report that is found in 
> CustomerSource. Would like to add filters for Department and Category if 
> possible. I have tried to modify it myself, but I'm a beginner at this and I 
> keep coming up short.  
0
JenLosch (82)
10/24/2007 6:44:03 PM
Reply:

Similar Artilces:

Store similar types of records all in one table or separate tables?
Suppose you want to have four different types of records. Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: Keeping track of all 3 types of records in a single table. Or Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit <tryit.ca@gmail.com> w...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Wrong Capital Gain on Short Sales (Money 2007)
This may have been discussed, but I can not find it any more. I do some futures. So they are long and shorts. Short sale is very common. But M-2007 does not calculate capital gain correctly. Say, I sold one future at $10,000 total notional value, then bought it back at $9,900 and made $100. M-2007 somehow has a capital loss of $19,900 or something like that. Is this a known Money error? Thx. Money handles short positions perfectly for me. I use M2002. When you enter a short position you have to use Short Sell to open and Cover Short to close. Regards Bill Wood "Andrew&qu...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

Sales for Outlook
Hi, When in offline mode in Outlook, I always get an Access Denied error. The process of going offline always goes without error though. It's when I want to access the data that I get the message. In this newsgroup, I found that changing the value of a registry key solves the problem. And it does. The only thing is that I have to tweak the registry value each time I go offline. The key is HKEY_LOCAL_MACHINES\SOFTWARE\MICROSOFT\MSCRM\UserSecInfo You change the first pair of numbers to 00 This can't be acceptable since "normal users" will use this feature. No way I...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

Opening a window(form) with VBA / storing data in a separate table
Hi all, First Question---- I have a third party application (Additional Inventory Fields in the ActNow series from Hawk Mountain) that currently is accessed from the Extra menu while in item maintenance. I have added a button to the item maintenance form and I am trying to figure our what VBA code I need to put in so that when the user clicks on the button that it launches the form that is currently accessed via the menu. This is not currently a VBA form but the window that comes with the application. I have tried to put an "OnUserChanged" event on the button with a FormName.Open...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

averages
Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

Sales for Outlook will not go offline
Hi have installed Sales for Outlook, and can connect successfully to the CRM Server, but when I try to go offline,a get a series of screens showing 'System cannot find specified file' and eventually a 'Metadata Cache Error, Entity does not exist'. I have not been ableto track down the problem, has anyone else seen this? This is usually results after uninstalling Microsoft CRM Server, and then backing up the databases and moving them to a different SQL Server computer. The Metadata cache error - no entity is seen when the user tries to go offline with Sales for Outlook. ...

dividing sales reps into logical groups
The company I am currently implementing CRM is not such a big company that has many sales representatives assigned to a city or a localized place, but the sales representatives have been divided into logical groups which a person has his/her own customer. In this scenario can I use territories to categorize sales reps ? what else do you suggest ? regards, Territories is the normal way to do this, as this also shows up in reporting. Alternatively, using teams can be more flexible, but you'd have to customise and sales reports -- David Jennaway - Microsoft Dynamics CRM MVP Web: ht...

Option to have the Allow Sales Documents Commitments Default as Ma
Customer would like the 'Allow Sales Documents Commitments' option default as marked in Purchase Order Entry. I used modifier to check the box automatically after a po number is entered. Robert "anonymous" <anonymous@discussions.microsoft.com> wrote in message news:C0531B8B-BBA4-4B7D-8BF8-C27B851929BC@microsoft.com... > Customer would like the 'Allow Sales Documents Commitments' option default > as > marked in Purchase Order Entry. ...

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

Integration Manager with Sales Configuration window
Hi all, I'm try to make a intregration of sales order to GP 9.0 from txt file, but I have the follow problems. 1.- In the quantity shortage, the intregration is failed because say that I can't have mapped the quantity field. this is the exactly error message: "DOC 1 ERROR: 'Override Shortage' is not a valid choice for field 'Items.Quantity' in this instance." 2.- When I try to load a producto with the sales configuration active to generate a production order, an run the integration manager, send me a message and say that I need close the sales confi...

Moving Average
Is there a formula for calculating the moving average for a group of numbers ? Thanks I'm assuming that you want a running average (at least, that's how I am interpreting it) Assuming your values are in column A and your average in column B the formula in B1 should be as follows: =Average($A$1:A1) then just copy and paste it down the rest of the column and it will calculate the average as numbers are added. Steven Bitaxi sbitaxi@yorku.ca wrote: > I'm assuming that you want a running average (at least, that's how I am > interpreting it) I think the OP want...

Disallow submission of more than 1 timesheet per user per week
In administering the timesheet system it is very frustrating to have employees submit more that one timesheet for the same pay-period and even at times have their managers once again approve them. When you in PDK searching for status of timesheets, trying to determine who you have to chase down, you scroll down on status. In addition you have to scroll down on the names and determine if any are duplicates. If approved and you hit process they get posted to the system and you are then required to go into GP and create a negative timesheet. As the managers don't have a tool to let the...

Averaging?
This must be easy to do but I cannot figure it out... I have 150 rows of data with information in cell A, B, C and D. Cell A is simply a description for each row and cells B, C, and D contain numeric values between 1 and 100. I need to average each row to its respective cell E and then sort row A from highest to lowest based on the average value in row E. Example >> A B C D E Song1 52 45 25 Song2 33 55 88 Song3 44 94 22 Anyone!?!? TIA!! David@Rocketmail.com One way: E1: =AVERAGE(B1:D1) Copy down to E150. Select E1. Choos...

Sales Order Copy in CRM 3.0 ?????
Hi Does anyone know how to setup a function to copy a sales order in CRM 3.0 Roll up 2. Not just the header but the sales order detail lines as well. We have some very large orders we do not want re-enter. Any ideas would be appreciated Thank you Paul Paul - There is a record clone utility from c360 that's helped us significantly reduce data entry. Here's a link (registration required): http://www.c360.com/Clone.aspx M On Jan 31, 6:30=A0pm, Paul Hart <PaulH...@discussions.microsoft.com> wrote: > Hi > > Does anyone know how to setup a function to copy a sales ...

select checks to have one check per vendor for staggered invoices
The system should print one check for one vendor only even though the customer manually picked out and included staggered invoices into one check batch that were issued to only one vendor. What the system is doing right now is that it prints several checks per invoice, that belong to one vendor even though the setting was configured to print one check per vendor. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not se...

Importing Sales into RMS
I need to import sales into RMS via a file or batch. This is important for our internet sales. check out the RMS Web Integrator product from New West - http://www.newestech.com -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information "johnsull" <johnsull@discussions.microsoft.com> wrote in message news:45C6A254-66F7-4CB6-80D1-AB5D89FA0D8D@microsoft.com... >I need to impor...

Running Average or YTD average
Is there a simple formula for calculating a running average? I can do accomplish what I want to do but it takes 4 different steps or functions to get there. Example of what I would like: A-1 thru A-52 equal weeks of the year. We'll say that A-5 represents the 5th week of the year. Income for these weeks will be entered in it's respective cell as it comes due. In the cells that represent the weeks that have not yet come, a "0" is in them. I want to be able to have one cell that reflects the year todate average of income, and not be affected by the zeros. -- lsmft -------...

How to obtain Averages from a list of multiple items?
Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column C has their age in days old (10,3,4,etc) I need (2) Averages- Average age of Cats and Average Age of Dogs Hi! =SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat") Do the same for dog. Biff "MadameJunk" <MadameJunk@discussions.microsoft.com> wrote in message news:B0E6C651-E09D-4FA4-A0BB-F859A88CA81A@microsoft.com... > Example:Excel worksheet-Column A has list of Cat or Dog (say 15 > items)-Column > C has their age in days old (10,3,4,etc) > I need (2) Averages- Aver...

Dynamic annual average
Hi, I keep a rough weekly diary on the development of a certain numeric value. Now I've got a series of data entries in a chart as follows: A1 - date of entry, eg. "23rd July 2006" A2 - reading data value, eg "100" I want to create a dynamic average on line A3 which tells me the average of data entries during the past year. Problem is, I've read the data value in irregular intervals and not every day (last year for example on 22nd July, the year before 10th July etc). My Excel understands the dates entered and draws a neat proportioned chart on development of data...