|
|
OK, I'm stuck and this is getting way late. I'm going to post the whole bit of
code for this report. I'm thinking I'm using the array wrong. The structure
for this report was 'borrowed' from another one, but that other report didn't
have the date grouping. That's why I'm having these problems. Any help is
greatly appreciated!!
<cfcase value="12">
<h2>WIR - Week In Review</h2>
<h3>BY DATE</h3>
<cfset START = DateFormat(URL.ReportBeginDate,"mm/dd/yy")>
<cfset END = DateFormat(DateAdd("d",1,URL.ReportEndDate),"mm/dd/yy")>
<!--- This query sets up a distinct list of tickets for the report. --->
<cfquery name="qryDistTckts"
datasource="#Session.PMDBDataSource#">
select distinct a.SCTicket
from tblSCTicket a
where (a.OutageStart between '#START#' and '#END#')
order by a.SCTicket asc
</cfquery>
<!--- Define the array --->
<cfset aryDistTckts = arrayNew(1)>
<cfloop query="qryDistTckts">
<cfset aryDistTckts[CurrentRow] = SCTicket>
</cfloop>
<!--- *** Start outputting each ticket individually *** --->
<cfloop index="j" from="1" to="#qryDistTckts.RecordCount#">
<cfquery name ="qryWIRDate" datasource="#Session.PMDBDataSource#">
SELECT
'Date' as ReportGroup
, convert(datetime, convert(varchar, SCTicket.OutageStart, 104),104) as
OutageDate
, SCTicket.OutageStart
, SCTicket.OutageEnd
, '1' as SortField
, Pri.Abbrev
, SCTicket.SCTicket
, SCTicket.IssueTitle
, SCTicket.RootCause
, SCTicket.Impact
, SCTicket.Resolution
, SCTicket.ResultOfChange
, CASE WHEN SCTicket.ResultOfChange = '1' THEN ', Result Of Change' END
as
ROCIndicator
FROM
tblSCTicket SCTicket
INNER JOIN tblPriorities Pri
ON SCTicket.PriorityID = Pri.PriorityID
WHERE (SCTicket.SCDateEntered between '#Start#' and '#End#')
AND SCTicket.SCTicket = '#aryDistTckts[j]#'
ORDER BY OutageDate, OutageStart,
SCTicket
</cfquery>
<cfoutput query="qryWIRDate" group="OutageDate">
<P></P>
#DateFormat(OutageDate, "dddd, mm-dd-yy")#
<cfoutput>
<table class="ticket">
<tr>
<th>When:</th>
<td>#DateFormat(OutageStart, "mm/dd/yy")# from
#TimeFormat(OutageStart,
"h:mm tt")# until #TimeFormat(OutageEnd, "h:mm tt")#</td>
</tr>
<tr>
<th>Priority:</th>
<td>#Abbrev#, #SCTicket# #ROCIndicator#</td>
</tr>
<tr>
<th>Brief Description:</th>
<td>#IssueTitle#</td>
</tr>
<tr>
<th>Cause:</th>
<td>#RootCause#</td>
</tr>
<tr>
<th>Resolution:</th>
<td>#Resolution#</td>
</tr>
</table>
</cfoutput>
</cfoutput>
<!--- Ticket Impact Details --->
<cfquery name="qryAppInfo"
datasource="#Session.PMDBDataSource#">
select a.SCTicket
,bu.BusinessUnitName
,ce.CenterName
from tblSCTicket a
inner join tblUserFacingApps uf
on a.SCTicket = uf.SCTicket
left join
tblUserFacingAppImpact ufi on uf.UFAID = ufi.UFAID
inner join tblBusinessUnits bu
on ufi.BusinessUnitID = bu.BusinessUnitID
inner join tblCenters ce on
ufi.LocationID = ce.ID
where
a.SCTicket='#aryDistTckts[j]#'
<!--- Used to eliminate UK/US
Application --->
AND
#PreserveSingleQuotes(ukExcludeApp)#
order by ce.CenterName ASC,
bu.BusinessUnitName ASC
</cfquery>
<cfif qryAppInfo.RecordCount NEQ 0> <!--- Start
Ticket Impact Details
Verification --->
<table class="appInfoSm" width=50%>
<tr>
<th>Center</th>
<th>Business Unit</th>
</tr>
<cfoutput query="qryAppInfo">
<tr>
<td>#CenterName#</td>
<td>#BusinessUnitName#</td>
</tr>
</cfoutput>
</table>
<style type="text/css">
table.appInfoSm {
margin: auto auto;
padding: 0;
width: 50%;
border-collapse: collapse; }
table.appInfoSm th{
font: 0.75em normal;
padding: 0.25em;
text-transform: uppercase;
border-bottom: black 0.13em
solid; }
table.appInfoSm td{
font: 0.75em normal;
padding: 0.25em;
text-align: center;
border-bottom: black 0.06em
solid; }
</style>
</cfif> <!--- End Ticket Impact Details
Verification --->
</cfloop>
<!---***************************************************************************
**************************
<div style="page-break-after: always;"></div>
<cfoutput><h3>BY CENTER</h3></cfoutput>
<cfset START = DateFormat(URL.ReportBeginDate,"mm/dd/yy")>
<cfset END = DateFormat(DateAdd("d",1,URL.ReportEndDate),"mm/dd/yy")>
<!--- This query sets up a distinct list of tickets for the report. --->
<cfquery name="qryDistTckts"
datasource="#Session.PMDBDataSource#">
SELECT DISTINCT
a.SCTicket
...and it goes on from there to use the same dataset, only sorting first by
Center, then by Business Unit. A center can have many business units.
|
|