I am trying to create a crosstab report showing multiple values. I am using a technique that I found as described by Allen Browne, create a single column table with the field names as rows, add it withoiut joining in the query, and an IIF statement to test the value in this table in the TRANSFORM which determine what is aggregated.
TRANSFORM IIf(IsNull(IIf([Fieldname]="Upsells",
SUM(trk.LengthOfStay),
COUNT(trk.StaffID)
)
),
0,
IIf([Fieldname]="Upsells",SUM(trk.LengthOfStay),Count(trk.StaffID))
) AS TotalUpsells
SELECT prop.PropertyName
FROM tblColumns,
Region AS reg
INNER JOIN ((Country AS cty
INNER JOIN (Brand AS brnd
INNER JOIN (Area INNER JOIN (Division AS div
INNER JOIN (Contract AS con
INNER JOIN Property AS prop
ON con.ContractID = prop.ContractID)
ON div.DivisionID = prop.DivisionID)
ON area.AreaID = prop.AreaID)
ON brnd.BrandID = prop.BrandID)
ON cty.CountryID = prop.CountryID)
INNER JOIN Tracking AS trk
ON prop.PropertyID = trk.PropertyID)
ON reg.RegionID = prop.RegionID
WHERE trk.TrackingYear = 2016
GROUP BY reg.RegionID, prop.PropertyName, FieldName PIVOT Fieldname & reg.RegionID;
It works to an extent, but I get two lines per property, the first shows the nights, the second shows the upsells. Allen's example creates single rows.
Can anyone give me a steer as to what to change to get them on the same line?
TIA
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar