Totals, Top-down etc

General discussions

Moderators: Bob Cergol, Data Access, Cintac

Totals, Top-down etc

Postby robinsonky » Tue Apr 10, 2012 11:33 pm

Couple of general questions. I'm trying to replicate some stuff from a point of sale to allow me to sharepoint something to people who don't have direct access to that database.

1. There are several reports that summarise daily sales by showing the daily quantity sold but the weekly value:
Like this (simplified):

Item Monday Tuesday Weds Thurs Friday Saturday Total

1234 Bread 10 10 15 19 25 50 $322.60
1706 Pears 11 77 66 55 97 133 $277.56

I have quantity and value on every record, but I want to hide the value in the daily columns and the weekly total quantity. Any suggestions?

2. Same style report. I want to show a running % of the total , for the row totals column only.

3. I also want to identify top 50 by revenue. This sorta works if I click on AI but I want to deliver the report for just the top 50 without interaction.

4. (added 11/4/12) Similar style report to (1), but the individual row/column entries are counts of products meeting certain conditions each day. I want to use the totals column to show the average count for the week, but DynAI seems to think I'm trying to average the (text) product codes, rather than the derived counts. This is no doubt me not getting my head around some basic concept....

Thanks in anticipation. If you want me to break this up into multiple posts and give better examples just say the word.
Last edited by robinsonky on Wed Apr 11, 2012 7:51 pm, edited 2 times in total.
Posts: 50
Joined: Wed Oct 19, 2011 8:41 pm

Re: Totals, Top-down etc

Postby admin » Wed Apr 11, 2012 2:08 pm

I'll post a reply for each of your points..

First the top 50..

If you sort on the sum, and enter 50 in "Show top..." you will get top 50:

Best regards

User avatar
Certified Dynamic AI Professional
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark

Re: Totals, Top-down etc

Postby robinsonky » Wed Apr 11, 2012 3:44 pm

i've a very fast PC.... that's almost a blur :D what is it you do after setting the top-count?

Just setting the sort order and the top count gives me a blank screen until I click the AI button. Obviously I'm missing something or have a wrong setting elsewhere in the report.....
Posts: 50
Joined: Wed Oct 19, 2011 8:41 pm

Re: Totals, Top-down etc

Postby Bob Cergol » Thu Apr 12, 2012 11:35 pm

Hi Dave,

I've been travelling ...

Top # reports are easy. You just set the Top # value in the Totals area of the list options tab in design as Bo's video demonstrates and then you set the sorting on the total to ascending or descending. You should also be able to us a column action most of the time. Click on the column title of the total column and select top / bottom 10. You can then change the 10 to any value you want in the full design interface. You can't do a Top # on pivot style reports.

Regarding the hiding of totals -- have you noticed the check box options in the totals area of list options for hiding grand column or row totals? There is also a "hide" checkbox on each individual total. Are these not working for what you want to to?

On the running % of total -- you might need to post more detailed information -- but basically in order to show the percentage any given row total is of the grand total -- you must move that grand total out of the scope of the report using a key figure. You can then reference that key figure total inside the scope of the report. I'm not not clear on what you mean by making it a running percent. There are options for a total to make it a running total but I'm not sure about running percent. I'll give it a try. (I'll be demonstrating how to do the percentage of total in a webinar next Thursday -- notice is going out tomorrow.)

.... a few minutes later....

Instead of using the key figure technique, lo and behold you can use a "running pct of total" and it works -- but not with my Top # reports.The thing I love about this product is that there seems to be no end to new things to learn! I've not used all of the "running column" options before ... more to explore ... you can also reset the running total based on group, etc. Explore Dave! :-)

User avatar
Bob Cergol
Certified Dynamic AI Professional
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Return to General discussions