Report by two categories.

Folks,
I have the following data:

Date Product Subtotal Tax
01/12/2004 BA 253.36 25.36
01/16/2004 BA 456.36 12.36
02/05/2004 BA 546.36 12.36
02/18/2004 BA 1236.54 25.69
02/19/2004 BZ 650.36 36.36
02/25/2004 BZ 456.69 10.36
03/12/2004 BZ 789.36 21.36

I need to create a report that will give me a subtotal by Product and Date.
Now, the problem with doing a straight subtotal (and then a nested subtotal)
is that I will have too many subtotals by Date (imagine my dataset spanning
over several years with multiple monthly dates). Is there a way, I can flag
a certain number of date occurence as a particular month. For example, from
my data above, can I summarize BA in January 2004 as $747.44. So, although,
there are two occurences in January, my report is neatly summarized as
January 2004.

Thanks.

Regards,
Shams.

Shams --

You can do this pretty easily with the PivotTable tool. It allows you to
set up a table "logically" and then it just fills it in with summarized data.

"Shams" wrote:

> Folks,
> I have the following data:
>
> Date Product Subtotal Tax
> 01/12/2004 BA 253.36 25.36
> 01/16/2004 BA 456.36 12.36
> 02/05/2004 BA 546.36 12.36
> 02/18/2004 BA 1236.54 25.69
> 02/19/2004 BZ 650.36 36.36
> 02/25/2004 BZ 456.69 10.36
> 03/12/2004 BZ 789.36 21.36
>
> I need to create a report that will give me a subtotal by Product and Date.
> Now, the problem with doing a straight subtotal (and then a nested subtotal)
> is that I will have too many subtotals by Date (imagine my dataset spanning
> over several years with multiple monthly dates). Is there a way, I can flag
> a certain number of date occurence as a particular month. For example, from
> my data above, can I summarize BA in January 2004 as $747.44. So, although,
> there are two occurences in January, my report is neatly summarized as
> January 2004.
>
> Thanks.
>
> Regards,
> Shams.
>
>
>

Thanks for the info. I tried doing a Pivot selecting the Product and Date
column as Rows and then summing by subtotal. It is still giving me the same
dept of information as the Subtotal functiuon would have.

My goal is still quite simple. I want to show that under Product BZ, the
total for February was $1,153.77. I don't want to specify the two dates
under February but roll them up under February. Let me know if I am missing
something. Thanks.

"pdberger" wrote:

> Shams --
>
> You can do this pretty easily with the PivotTable tool. It allows you to
> set up a table "logically" and then it just fills it in with summarized data.
>
> "Shams" wrote:
>
> > Folks,
> > I have the following data:
> >
> > Date Product Subtotal Tax
> > 01/12/2004 BA 253.36 25.36
> > 01/16/2004 BA 456.36 12.36
> > 02/05/2004 BA 546.36 12.36
> > 02/18/2004 BA 1236.54 25.69
> > 02/19/2004 BZ 650.36 36.36
> > 02/25/2004 BZ 456.69 10.36
> > 03/12/2004 BZ 789.36 21.36
> >
> > I need to create a report that will give me a subtotal by Product and Date.
> > Now, the problem with doing a straight subtotal (and then a nested subtotal)
> > is that I will have too many subtotals by Date (imagine my dataset spanning
> > over several years with multiple monthly dates). Is there a way, I can flag
> > a certain number of date occurence as a particular month. For example, from
> > my data above, can I summarize BA in January 2004 as $747.44. So, although,
> > there are two occurences in January, my report is neatly summarized as
> > January 2004.
> >
> > Thanks.
> >
> > Regards,
> > Shams.
> >
> >
> >

You need to group the dates by month (right click in the dates column in the
pivot table and select group, then select month)


--

Regards,

Peo Sjoblom


"Shams" <Shams@discussions.microsoft.com> wrote in message
news:7198FD15-3612-4432-B238-8849FB91B8D5@microsoft.com...
> Thanks for the info. I tried doing a Pivot selecting the Product and Date
> column as Rows and then summing by subtotal. It is still giving me the
same
> dept of information as the Subtotal functiuon would have.
>
> My goal is still quite simple. I want to show that under Product BZ, the
> total for February was $1,153.77. I don't want to specify the two dates
> under February but roll them up under February. Let me know if I am
missing
> something. Thanks.
>
> "pdberger" wrote:
>
> > Shams --
> >
> > You can do this pretty easily with the PivotTable tool. It allows you
to
> > set up a table "logically" and then it just fills it in with summarized
data.
> >
> > "Shams" wrote:
> >
> > > Folks,
> > > I have the following data:
> > >
> > > Date Product Subtotal Tax
> > > 01/12/2004 BA 253.36 25.36
> > > 01/16/2004 BA 456.36 12.36
> > > 02/05/2004 BA 546.36 12.36
> > > 02/18/2004 BA 1236.54 25.69
> > > 02/19/2004 BZ 650.36 36.36
> > > 02/25/2004 BZ 456.69 10.36
> > > 03/12/2004 BZ 789.36 21.36
> > >
> > > I need to create a report that will give me a subtotal by Product and
Date.
> > > Now, the problem with doing a straight subtotal (and then a nested
subtotal)
> > > is that I will have too many subtotals by Date (imagine my dataset
spanning
> > > over several years with multiple monthly dates). Is there a way, I
can flag
> > > a certain number of date occurence as a particular month. For
example, from
> > > my data above, can I summarize BA in January 2004 as $747.44. So,
although,
> > > there are two occurences in January, my report is neatly summarized as
> > > January 2004.
> > >
> > > Thanks.
> > >
> > > Regards,
> > > Shams.
> > >
> > >
> > >

Peo,
Thank you very much for your help. It now works very well...

"Peo Sjoblom" wrote:

> You need to group the dates by month (right click in the dates column in the
> pivot table and select group, then select month)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Shams" <Shams@discussions.microsoft.com> wrote in message
> news:7198FD15-3612-4432-B238-8849FB91B8D5@microsoft.com...
> > Thanks for the info. I tried doing a Pivot selecting the Product and Date
> > column as Rows and then summing by subtotal. It is still giving me the
> same
> > dept of information as the Subtotal functiuon would have.
> >
> > My goal is still quite simple. I want to show that under Product BZ, the
> > total for February was $1,153.77. I don't want to specify the two dates
> > under February but roll them up under February. Let me know if I am
> missing
> > something. Thanks.
> >
> > "pdberger" wrote:
> >
> > > Shams --
> > >
> > > You can do this pretty easily with the PivotTable tool. It allows you
> to
> > > set up a table "logically" and then it just fills it in with summarized
> data.
> > >
> > > "Shams" wrote:
> > >
> > > > Folks,
> > > > I have the following data:
> > > >
> > > > Date Product Subtotal Tax
> > > > 01/12/2004 BA 253.36 25.36
> > > > 01/16/2004 BA 456.36 12.36
> > > > 02/05/2004 BA 546.36 12.36
> > > > 02/18/2004 BA 1236.54 25.69
> > > > 02/19/2004 BZ 650.36 36.36
> > > > 02/25/2004 BZ 456.69 10.36
> > > > 03/12/2004 BZ 789.36 21.36
> > > >
> > > > I need to create a report that will give me a subtotal by Product and
> Date.
> > > > Now, the problem with doing a straight subtotal (and then a nested
> subtotal)
> > > > is that I will have too many subtotals by Date (imagine my dataset
> spanning
> > > > over several years with multiple monthly dates). Is there a way, I
> can flag
> > > > a certain number of date occurence as a particular month. For
> example, from
> > > > my data above, can I summarize BA in January 2004 as $747.44. So,
> although,
> > > > there are two occurences in January, my report is neatly summarized as
> > > > January 2004.
> > > >
> > > > Thanks.
> > > >
> > > > Regards,
> > > > Shams.
> > > >
> > > >
> > > >
>
>
>