Grouping in Actuate

[dnboughton]

Hello, we use Actuate 7 with MAXIMO 5.2 Oracle. I'm new to Actuate, on my own, and I need help. I've been to IBM Actuate Training and that is it.

I'm developing a report for each school that shows all work scheduled for the following week similar to below:

Adams Elementary
wonum description scheduledate
xxxxxx XXXX xxxxxx xx-xxx-xx
xxxxxx XXXX xxxxxx xx-xxx-xx
xxxxxx XXXX xxxxxx xx-xxx-xx

Brooks Elementary
wonum description scheduledate
xxxxxx XXXX xxxxxx xx-xxx-xx
xxxxxx XXXX xxxxxx xx-xxx-xx
xxxxxx XXXX xxxxxx xx-xxx-xx

1. This report needs to be grouped by first four characters of our location code (I can accomplish this part), but I want to call each group a specified value, i.e. the school name, which the first four characters represent.
2. How can I create a parameter for all work orders for the next week starting on Sun.

Any help is appreciated.

TY--Denise

 

[ASG0856]

Why are you using just the first four characters of the locationcode to group? This field should be unique, so you can use the whole value.

Create a Prompt called P_SCHOOL, type string, drop down list, with your list of school names.
Create two date prompts P_DateFrom and P_DateTo

In the Sql, conditions, add the following
Field: ScheduleDate, Condition _DateFrom-_DateTo (equates to ScheduleDate between)

In the report Structure, Datastream, methods, override the "Function obtainselectstatement() As String"
Enter the following. note the ObtainSelectStatement = Super::ObtainSelectStatement( ) is now the last line before the End Function

Function ObtainSelectStatement( ) As String
' Insert your code here
Dim strSchool as String
strSchool = P_SCHOOL
Select Case strSchool
Case "School A"
WhereClause = WhereClause & " AND (Table.LocationCode = 'Location Code')"
Case "School B"
WhereClause = WhereClause & " AND (Table.LocationCode = 'Location Code')"
Case ELSE
WhereClause = WhereClause
End Select

ObtainSelectStatement = Super::ObtainSelectStatement( )
End Function

Now, when you select a school, the report will filter to its location code.

 

[ASG0856]

Oh forgot, put P_SCHOOL in the Group Header as a Text control

 

[dnboughton]

Our locations are unique, but the first four characters of the location code defines the school. We have over 50,000 locations in our system. The are similar to this: 0369A1101P1, 0369A1211AHU1, 0370A1101AHU1. So I want to group by the first four characters, in other words by school. I want the group SchoolX - where location like 0369%, SchoolY where location like 0370%. Thanks.

 

[ASG0856]

Check of understanding.
0369A1101P1, 0369A1211AHU1 refer to the same school, but different departments within the school? (the first four digits are the same)

There is no table link that links codes to school names within the system?

However, changing the line like this
WhereClause = WhereClause & " AND ((left$(Table.LocationCode,4) = 'Location Code'))"

Should achieve your objective. You will still have to create a list that links codes to schools.
Unfortunately the prompting in Actuate 7 is absolutely C**P. You cannot do anything beyond simple lists. Any processing has to be done in the SQL or method overrides.