Crosstab sums

[Former Member]

Hi,

I have a crosstab set up that shows the number of cases done by surgeon per month from July 2007 to September 2008.

My question is I want to sum by surgeon (summing across rows) for July 07 to June 08 and then again from June 08 to Sep 08 not just across the total months listed. Is there a way to do this? And can I insert a column just between June and July 08 for this sum (July 07-June08)

I know that I could run the data for July-June 08 and then insert another table for July-Sept 08, but then the surgeon data changes.

Even if a surgeon did no cases in a one of the dates ranges that I want to sum, I'd still like to keep that surgeon on the list to keep it consistent.

I hope this all makes sense.

Any help is appreciated.

Thanks, Cindy

[Former Member · Accepted solution]

Hi Cindy,

Looks like you have a fiscal year running from July to June next year.

If in your universe (assuming you are using one) you have available an object for fiscal year then please use that as one of the objects in your 'Result Objects'. You can then use that object in your crosstab and put a break on it.

If you don't have such an object available it's a bit more work. You then need to create a variable called 'fiscal year' (or anything else if you want).

The formula for the variable will then be:

=If <object year> = 2007 or (<object year> = 2008 and <object month> <=6) then 2007 else 2008

You can then use this variable in your crosstab and put a break on it.

After you created the break you can add a sum to the break footer.

Regards,

Harry

[Former Member]

Harry and Prashant,

Thank you both for your responses to my question. My problem is resolved. I did have to create a variable, but it worked perfectly.

One more question: When doing a sum, is there a way to show the sum where the row and column intersect, too? Ultimately, this report will be exported to Excel, so I can do that calculation there if needed, but I'm trying to do as much in BO as I can.

Thanks again for your help on the first question.

-Cindy

[former_member212749]

Hi Cindy,

Create a Variable like

=Sum(Cases) where Surgon="XYZ"

and put that at the desired location from the Pivot.

Regards

Prashant