Avoiding Multiple Rows in the Report

[Anu1284]

Hi,

I'm facing an issue in BO reports.

My requirement is:
For each employee, based on scheduled field (in the database), I need the count of scheduled incidents and un-scheduled incidents.

For ex: Values in the Database for Absence Table

EmplID SCheduled
1 Y
1 Y
1 N
2 Y
2 N
2 N

Required Output is:
EmplID Sch Incidents(Y) Unsch Incidents (N)
1 2 1
2 1 2

My approach:
I created 2 variables in the report.
One for
Sch Incidents = If Scheduled ='Y' count(scheduled)
and the other for
Unsch Incidents = If scheduled='N' count(scheduled)
Using these variables in the report, the output that I receive is:

EmplID Sch Incidents Unsch Incidents
1 2
1 1
2 1
2 2

I get two rows one with scheduled ='Y' and the other with scheduled='N', since scheduled field is selected in the select query. If scheduled field is not selected in the select query, I'm unable to create the 2 variables Sch Incidents and Unsch Incidents.

Please let me know the best approach to get the required output.

Thanks,
Anu.

 

[Sridharan]

If you are looking for a Designer Level Solution then create two objects as follows...

Scheduled  Count   

COUNT(CASE WHEN Scheduled = 'Y' THEN Scheduled END)

UnScheduled  Count

COUNT(CASE WHEN Scheduled = 'N' THEN Scheduled END)

Now create the report with Emp ID and these two measures you should get the desired output

Sri

 

[Anu1284]

Hi Sridharan,

My requirement is to create the same in the report level.

I have similiar conditions for hours and days also. Hence there will be a total of 6 extra objects in the designer.

This is going to be an overhead as far as maintenance to the universe is concerned.

Could you suggest some method for adopting the same in the report level????

 

[naga5566]

Try this

in the report varables ,just say Count(Sch incidents)
and count(Unsch incedents)

 

[Sridharan]

Let me first tell you the reason why I gave you the universe level solution... You wrote...


Anu Wrote:
. If scheduled field is not selected in the select query, I'm unable to create the 2 variables Sch Incidents and Unsch Incidents


Now with those objects created at the Universe there is no need to include Scheduled Field as the report object...

You asked for the best solution and gave you a solution which can be used in other reports which require similar calculation.. Everytime you look for something which requires this calculation in a new report you need to create these new variables... If such a scenario exists then doing it at the Universe is the best option... If you require only in some one off reports then fine...

To do this at the report level the simplest thing that comes to my mind is with the given output which you have given...

EmplID Sch Incidents Unsch Incidents
1 2
1 1
2 1
2 2



Insert a break on Emp ID and in the Sub Totals Sum Sch Incidents and Unsch Incidents... Now choose Format Breaks and Untick Break Header and Tick Fold... After than Right Click the table and choose Format Table and choose Show Header in the first tab... You get the desired output...

Sri

 

[Anu1284]

Hi Sridharan,
Based on your suggestion, I made the corresponding change.
I also need to get the total for each employee as below:

EmplID Sch Incidents Unsch Incidents
1 2 1

Total of 1 2 1

2 1 2

Total of 2 1 2

How will I get the total based on each employee??
When I check the Fold option, it displays only the footer for the break. I'm unable to get the total for each employee.

Could you please suggest a solution for this??

 

[skrandel]

2 choices

1) Don't check the fold option. Put the total in and then turn on Outlining by turning on the Outline toolbar. Then you can fold it manually.

2) Just put the right formula in the blank cells that are there when you do the fold.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com