Cross Tab

building reports desktopintelligence
1

I am facing a problem with cross tab report.
The cross tab report that I have created is having employee names along row,
weeks along column and number of hours each employee has put in each week
appear as cell values i.e.
W#1 W#2 W#3 W#4 W#5… Total Avg
John Doe 40 40 40 120

There may be blank cells indicating unknown values. To calculate we need to
divide total by numbe of weeks in consideration. Number of weeks is not
constant but it will depend on starting week and ending week specified as
part of prompt. If I try to calculate num_weeks as Count(<Week_period>) In
() then I get 3 for the above case i.e. count ignores unknown
values. I need to consider them also and divide the total by 5 for this
case. Can anybody provide any solution ? I will really appreciate that.
Thanks
Abhijit Bhattacharya

Listserv Archives (BOB member since 2002-06-25)

2 
 Abhijit,

 Depending on what format 'starting week' and 'ending week' are stored
 in, you should be able to create 'num_weeks' as a variable in BO using
 the UserResponse function i.e. <num_weeks> =
 UserResponse(<Ending_week>) - UserResponse(<Starting_week>).  If the
 end week and start week are dates instead of numbers, you may have to
 do a bit more manipulation to subtract the values.  Either way, this
 will give you a number you can use as described.

 Of course, another problem might be if your end_week number is in a
 different month that your start_week number (i.e. end_week is Feb week
 1, start week is Jan week 3).  You may have to incorporate months,
 years, etc as integers into the calculation to get it to work
 correctly.  But this should give you a start...

 Jason Beard
 ---------------------------------------------------------------------

I am facing a problem with cross tab report.
The cross tab report that I have created is having employee names along row,
weeks along column and number of hours each employee has put in each week
appear as cell values i.e.
W#1 W#2 W#3 W#4 W#5… Total Avg
John Doe 40 40 40 120

There may be blank cells indicating unknown values. To calculate we need to
divide total by numbe of weeks in consideration. Number of weeks is not
constant but it will depend on starting week and ending week specified as
part of prompt. If I try to calculate num_weeks as Count(<Week_period>) In
() then I get 3 for the above case i.e. count ignores unknown
values. I need to consider them also and divide the total by 5 for this
case. Can anybody provide any solution ? I will really appreciate that.
Thanks
Abhijit Bhattacharya

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 99-04-16 11:16:45 EDT, you write:

[ stuff deleted ]

If I try to calculate num_weeks as Count(<Week_period>) In
() then I get 3 for the above case i.e. count ignores unknown
values. I need to consider them also and divide the total by 5 for this
case. Can anybody provide any solution ?

Have you tried Count(Body)? That formula counts the number of rows (or
columns) rather than the data values in the rows (or columns). If your
crosstab has 5 weeks, then that formula should return a value of 5.

The drawback is that if all 5 weeks are not displayed (due to missing values
for ALL employees) then this count will reflect the actual number of weekly
columns, which could be less.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

4

From: Joy Williams joyw@pobox.upenn.edu

Mime-Version: 1.0

Susan,

Thanks for the details - can’t wait to get started! Please add my name to
the wait list for that full-day training class.

The budget code to pay for the software, liscense and training:
930-9312-1-000000-5249-7910-0000

Please let me know if there’s anything else to do .
Thanks,
Joy

Joy,

I have to apologize; I have searched through my files and e-mail archives,
and cannot find the budget number you sent to pay the $100 for the Business
Objects CBT training. Could you please send it again?

In the meantime, I have reserved the CBT for you for 9-11 AM on April 26th
and 27th. The training workstation is in the Computer Resource Center, on
the 2nd floor of Sansom West. You will need to bring your Penn ID. The
staff at the CRC front desk will be alerted that you are coming, and they
will give you the CD and the workbook and get you up & running on the CBT.

If you need more time or additional days, just let me know. Also, once you
have been through the training, if you ever want to go back over for a
“refesher” you may do so; just let me know what day/time you want.

The workbook is yours to write in & keep. The full manual from the vendor
is available in .pdf format from

Penn: Page not found

The instructions for installing the software are also found on this site.
Are you on Win95/7/8 or NT, or something else?

Also, there is going to be a Business Objects full-day class in early June;
but unfortunately it is already filled-up. (We originally set it up for the
Med School, but there were some extra spots.) It costs $330 per person.
If you are interested in this instead of the CBT, I can put you on a
wait-list.

Finally, with regard to installing the software, was the budget # you sent
to cover the cost of that as well? Licenses are $580 apiece. Again, my
apologies if you already sent me this information; I can’t figure out what
I did with it!

sincerely,

Susan Quant
ISC Data Administration
215-573-6038

Joy Williams
University of Pennsylvania
Campus Card Services
3451 Walnut Street, Suite 150
Philadelphia, PA 19104-6205

Approved-By: thompson@POBOX.UPENN.EDU

Date: Fri, 16 Apr 1999 10:06:01 -0500

try establishing the no. of weeks in your report (ie 5) as a variable.
then you can make averages with this.

Abhijit Bhattacharya abhijitb@CADENCE.COM 04/14/99 11:56am >>>
…divide total by numbe of weeks in consideration. Number of weeks is
not
constant but it will depend on starting week and ending week specified
as
part of prompt. If I try to calculate num_weeks as Count(<Week_period>)
In
() then I get 3 for the above case i.e. count ignores unknown
values. I need to consider them also and divide the total by 5 for this
case.

Listserv Archives (BOB member since 2002-06-25)

5

Dear Listers,

I’ve a problem with cross tab report
this includes in the COLUMN is PROJECT CODE as Dimension object and ROW is Department also dimension object (function which shows all departments like Development, Production and Maintenance…) and in the BODY sum(Current month), sum(YTD Budget) and sum(FY Budget) all are Measure objects.

I need to apply a condition as EXP_Code = 4566 for FY Budget only for Development section. Modified the FY Budget but it applies all the departments. Any Help or Suggestion greatly appreciated

Report looks like

Development Production
C-Month ETD-Bud FY-Bud C-Month ETD-Bud FY-Bud
p-0001 0.00 0.00 0.00 0.00 0.00 0.00
p-0002 1.34 1.34 2.50 0.00 1.45 2.45
p-0004 0.00 0.00 0.00 0.00 0.34 0.65
p-0006 4.50 4.50 5.00 0.00 0.00 0.00

Version BO 5.0.3
Database Oracle 8
Thanks in Advance
Prathiba

__________________________________________________ Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text

Listserv Archives (BOB member since 2002-06-25)

6

In a message dated 01-03-30 16:40:50 EST, you write:

I’ve a problem with cross tab report
this includes in the COLUMN is PROJECT CODE as Dimension object and ROW is Department also dimension object (function which shows all departments
like Development, Production and Maintenance…) and in the BODY sum(Current month), sum(YTD Budget) and sum(FY Budget) all are Measure objects.

I need to apply a condition as EXP_Code = 4566 for FY Budget only for Development section.
Modified the FY Budget but it applies all the departments.

Do you want a condition or a filter?

A condition reduces the amount of data downloaded from your server. A filter leaves the data in the cube but does not display on the report.

If you want a condition, it would be on the query panel. You would have

EXP_Code Equal To 4566
and Department Equal To ‘Development’
or
Department Not Equal To ‘Development’

In the case of a filter, you will have to create a complex filter. Select the Department object from your crosstab. Select Format + Filters from the menu. Click the “Add” button. Now, rather than selecting an idividual department, you have to click the “Define” button to create a boolean expression that will be used as a filter. It would look something like:

=( = “Development” AND <EXP_Code>=4566) OR (<Department <> “Development”)

That would allow any department other than Development to show up, but Development will only show if the EXP_Code restriction is also met.

At least I think so. :slight_smile: I don’t have your data available to test, but this should hopefully give you some idea of where to go to find a solution.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

7

Thanks Dave! Actually, Department is a filter which groups the one or more function descriptions and it displays the Development, production … Code for Department.
=If ( In list(“Software”,“Hardware”) Then ("Development)) Else If ( In list(“Something”,“Anything”) Then (“Production”)) Else ()

But User wants to apply a filter FY Budget is Expense Code = “4566” only for Development section and it should not apply Current month and YTD Budget.

For that I created new Universe object which Selects FY Budget4566 where Expense code=4566 and Then in the report created another filter called as =If ( = “Development” Then Else
But I’m getting Computation Error.
Is anything wrong doing this way?
Please Advice.

Report looks like

Development Production
C-Month YTD-Bud FY-Bud C-Month YTD-Bud FY-Bud
p-0001 0.00 0.00 0.00 0.00 0.00 0.00
p-0002 1.34 1.34 2.50 0.00 1.45 2.45
p-0004 0.00 0.00 0.00 0.00 0.34 0.65
p-0006 4.50 4.50 5.00 0.00 0.00 0.00

Regards,
Prathiba

— DRathbun@AOL.COM wrote:
In a message dated 01-03-30 16:40:50 EST, you write:

I’ve a problem with cross tab report
this includes in the COLUMN is PROJECT CODE as Dimension object and ROW is Department also dimension object (function which shows all departments
like Development, Production and Maintenance…) and in the BODY sum(Current month), sum(YTD Budget) and sum(FY Budget) all are Measure objects.

I need to apply a condition as EXP_Code = 4566 for FY Budget only for Development section.
Modified the FY Budget but it applies all the departments.

Do you want a condition or a filter?

A condition reduces the amount of data downloaded from your server. A filter leaves the data in the cube but does not display on the report.

If you want a condition, it would be on the query panel. You would have

EXP_Code Equal To 4566
and Department Equal To ‘Development’
or
Department Not Equal To ‘Development’

In the case of a filter, you will have to create a complex filter. Select the Department object from your crosstab. Select Format + Filters from the menu. Click the “Add” button. Now, rather than selecting an idividual department, you have to click the “Define” button to create a boolean expression that will be used as a filter. It would look something like:

=( = “Development” AND <EXP_Code>=4566) OR (<Department <> “Development”)

That would allow any department other than Development to show up, but Development will only show if the EXP_Code restriction is also met.

At least I think so. :slight_smile: I don’t have your data available to test, but this should hopefully give you some idea of where to go to find a solution.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

__________________________________________________ Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text

Listserv Archives (BOB member since 2002-06-25)

8

Listers

I have a cross tab report

                           VOLUME

           200201    200202   200203 200204 and so on

Division 12 12 12 12 (This is Volume)

What i need is one heading at the top called Volume at the top as close to
the cross tab . Cant i do it without a separate cell?

Thanks & Regards :–)
Kishore

Listserv Archives (BOB member since 2002-06-25)

9

What you need is a dimension that has only 1 value, “VOLUME”. Unfortunately, you have to do some weird stuff to make a constant a dimension.

I built a variable called Header: =If (=) Then “VOLUME” Else Upper()

Then I put the variable on top of the month in the crosstab. Put a break on HEADER. Then you can center it across the break.

Listserv Archives (BOB member since 2002-06-25)