BO CR 11.5 - How can I do a Daily, MTD, YTD, and Last YTD together

[mirogak]

Hi all,

I am trying to create a report with a Daily Sales view, MTD Sales view, YTD Sales view, and even a last YTD sales view. Do I really need a different subreport for each one of these?

I mean if I start out with a daily sales view, then obviously my 'selection criteria' is only going to have it for that day, and hence can't do a MTD (month to date) or YTD (year to date). But if in my selection criteria I choose YTD, then how, within my report, will I focus/extract only that one day, or MTD.

So it looks like I will have to create a differen subreport for each, and then somehow combine them together in one table.

Please confirm or make suggestions.

Thanks,
Mirogak

 

[synapsevampire]

No, retrieve all of the data at once, and use conditional formulas to do the aggregates.

You use the term view here, are you referring to datbase Views, or is this just you way of repeating that you need each of the aggregates?

To get all of the data back in one report, you would use:

(
{table.date} in lastyearytd
or
{table.date} in yeartodate
)

So now you have all of the appropriate data.

Then use condtional Running Totals to preform each aggreagate by filtering in the evaluate-Use a formula, as in:

{table.date} = currentdate-1

This asumes that the daily view is for yesterday,remove the -1 for today.

then another Running total with:

{table.date} = monthtodate

then another with:

{table.date} = yeartodate

then the last with:

{table.date} = lastyearytd

The Running totals would be placed in the report footer, and you can use any of the types of aggregates available within, sum, count, average, etc..

You give no indication of what your are intending to output, so the above theory should handle it all.

If you need specific help beyond this, give examples of the data and the expected output.

-k

 

[mirogak]

Hey Snapsevampire,

You understood me correctly, when I said "view", I meant I need all those different types of aggregates.

Your information seems valuable, I will go ahead and put it to work and see how it goes. I'll post an update.

Many thanks,

Mirogak

 

[mirogak]

Hey Snapsevampire,

thanks for the solution... its great, man. All in one query ....

However, here is another further requirement.

Can those running totals be drilled down into.

Let me expand on that:
Now what I have is, the Net Sales for each of the following,

Daily Sales | MTD | LYMTD | YTD | LYYTD

You see i know how drilling works; I did it separately for the Daily Sales, where the final dollar figure drills down to product category (because I joined Sales table to the Product table), then product sub-category, and then down to the invoice lines, obviously through grouping and summaries.

MY QUESTION IS THIS: can I do the same thing for the solution that you recommended with the Running Totals. If not what do you recomend

Once again thanks for the running totals idea, and its wonderful application,

Mirogak