Customer Order Frequencies

[ksolutions]

I would like to build a report that shows how many times customers have ordered. I'm looking for something like this:

# Times Ordered Count of Customers Fitting This Criteria
1 100
3 75
4 74
etc...

Customers are identified by [CustomerNumber]. Orders are identified by [OrderNumber]. The relationship between customers and order numbers is one to many (meaning customer numbers can have more than one order number associated with them).

I've created a report that shows the count of order numbers, grouped by total times ordered, and for each grouping it is showing the individual customer numbers. Instead of seeing those individual customer numbers, I need a total of how many customers placed that number of orders.

Any ideas?

 

[drlex]

What Cognos application are you using? ReportNet, Impromptu or what-have-you?

soi la, soi carré

 

[ksolutions]

Oops! Sorry - ReportNet. I use Report Studio to build all of my reports.

 

[ksolutions]

I wanted to let everyone know that I found the solution. Some of you may find this useful, so I thought I'd post it for you. This was done in Report Studio.

The solution was:

1) Create a list report

2) Drag over an item representing Order Numbers from the model. In my particular circumstance, it's [Sales Analysis].[Sales orders].[Order Nr], which are order numbers that will need to be totaled (next step). Call this column # of Times Ordered or something.

3) Edit the item so that its expression is this:
total(count(distinct([Sales Analysis].[Sales orders].[Order Nr])))

You don't need to aggregate in the properties of this data item.

4) Drag over an item that represents your Customers. In my circumstance it's [Sales Analysis].[All Customers].[Customer No]. Then cut this item from the report (not delete). You want to leave the item in the query.

5) Next, drag over a calculation from the toolbox. Call it "Count of Customers Calc" or something. Do it on the group level, and use the following for its expression definition:
Count([Customer No] for [Order Nr])

6) Group by # of Times Ordered and Count of Customers Calc (in other words, both columns).

7) Now you can add a list footer to the list for a summary of the count of customers calc, just so you can have a total at the bottom. Select the list, then click "Create Footer" in the upper toolbar.

8) Drag a calculation over from the toolbar. Do it on the group level. Use the following formula:
Count([Customer No] for report)

9) Depending on your data, when you run the report you may have some double lines (say, if many people ordered 1 time, you'll get a double line for some reason in the table). You can fix this by going into your list column properties, and setting the upper border to "None." This will eliminate the possibility of double border lines.

Hope this helps someone out there! Feel free to post questions if you need help.