Best Way to Handle 12-Months Subset of Data?

[RobSil]

I have a year's worth of data with columns such as:

Date - BreakFastCalories - LunchCalories - SupperCalories - MiscCalories
(made-up columns, but you get the gist...)


I am doing up reports to outline a monthly view, such that there would be:

12 pie charts, 1 for each month showing breakdown of calories for the month
or...
12 lines in a stacked column chart, 1 line per month
(or other such combinations).

My first approach was to create the same query and have 12 copies with filter being for _add_months(XXX, -1), -2, -3 etc.
Then union those 12 months to have a total query as well.  Then use my 13 queries where needed... maybe have a yearly data pie chart then 12 sub-charts for each month, 1 line graph for entire year, etc. etc.

I'd like to have  (e.g.) 12 pie charts in 1 section of my report then 1 bar graph with 12 entries, 1 for each month.  Getting a bit confused on if I can do this with the above structure.   Do I keep going like I am doing or should i just have 1 query for 12 months range and then sub-set the data somehow on the chart?  (If so, how can you specify the grouping to be monthly for data?  Can't figure out how to do that)

Thanks,
Robin

[ydeliwala]

Robin,

I am assuming the data is coming on a daily level. You should modify the date column data item in the query to directly extract the month and year in mm-yyyy style (use vendor database or cognos functions). When you change this the measures will be automatically aggregated at the month level.

You can then use these items in the pie chart.

If you can recreate the problem on samples and paste the xml, I will be happy to show what I am saying.

Let me know if this helps

[RobSil]

I think I get what your saying... yes it is on a daily level, but there may be multiple day entries.  I should add a column to my sample....

Date - NameOfDieter - BreakFastCalories - LunchCalories - SupperCalories - MiscCalories

So my data can be

Sept 12, 2009 - Joe Smith - 143 - 234 - 453 - 564
Sept 12, 2009 - Frank Jones - 143 - 234 - 453 - 564
Sept 12, 2009 - Nancy Smith - 143 - 234 - 453 - 564
Sept 13, 2009 - Joe Smith - 143 - 234 - 453 - 564
Sept 13, 2009 - Steven Jones - 143 - 234 - 453 - 564
Sept 14, 2009 - Francine Anderson - 143 - 234 - 453 - 564

etc. etc.
can you still do that based on this?

R.

[RobSil]

I think I got it!

I created a new field in my Query... "MonthlyDate" and it's expression is "_first_of_month([Date])".  Works so far, applying it to my different graphs to see if it works in different uses :).

stay tuned. :)

R.

[ydeliwala]

Absolutely! It will give monthly calories for each of the person.

something like

Month        Dieter          MonthlyBkearfstCalorie   MonthlyLunchCalorie
Sep 2009 - Joe Smith -           5600                               7000

etc....

You can change the aggregate property for the measures to Average instead of Total to directly looks at the average monthly calories for each meal.

[RobSil]

Confirmed to work, and work fantastically!

I created a new Data Element called "MonthOfYear" and one "DayOfWeek".

I set the report expression to be the appropriate function _first_of_month([Date]) e.g. and things worked great!  I then use MonthOfYear in the charts instead of Date and it auto-summarizes by unique values, which basically means once per month. 

Thanks again Yo!