80237f10-a1ba-4229-ba32-86304591ea962a832447-8f60-4f99-a9ee-1e9023bbc25b2a832447-8f60-4f99-a9ee-1e9023bbc25bStateNameSystem.StringPaxCountCurrentYearSystem.IntegerPaxCountPreviousYearSystem.IntegerVoucherLogoFileSystem.StringDataPathSystem.StringTotalPaxCurrentYearSystem.IntegerTotalPaxPreviousYearSystem.IntegerStatePax
----------------
SELECT
s.StateName,
ISNULL(PaxCountCurrentYear, 0) PaxCountCurrentYear,
ISNULL(PaxCountPreviousYear, 0) PaxCountPreviousYear,
VoucherLogoFile,
(
SELECT
ExternalFilesPath
FROM AppSettings
)
DataPath
--While getting the total PAXCount, we cannot include Itinerary multiple times if same Itinerary is listed
--under different states
,(SELECT SUM(PaxCount)
FROM
(
SELECT DISTINCT
ItineraryID
,ISNULL(PaxCount, 0) PaxCount
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= @StartDate
AND PurchaseItemStartDate < @EndDate
AND P.[StateName] IS NOT NULL
AND LEN(P.[StateName]) > 0
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)tableSum
) TotalPaxCurrentYear
,(SELECT SUM(PaxCount)
FROM
(
SELECT DISTINCT
ItineraryID
,ISNULL(PaxCount, 0) PaxCount
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= DATEADD(YEAR, -1, @StartDate)
AND PurchaseItemStartDate < DATEADD(YEAR, -1, @EndDate)
AND P.[StateName] IS NOT NULL
AND LEN(P.[StateName]) > 0
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)tableSum
) TotalPaxPreviousYear
FROM [State] s
OUTER APPLY
(
SELECT
ISNULL(SUM(PaxCount), 0) PaxCountCurrentYear
FROM
(
SELECT DISTINCT
ItineraryID,
StateName,
ISNULL(PaxCount, 0) PaxCount
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= @StartDate
AND PurchaseItemStartDate < @EndDate
AND P.[StateName] IS NOT NULL
AND LEN(P.[StateName]) > 0
AND P.StateName = s.StateName
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
) CurrentYear
GROUP BY StateName
) tablePaxCountCurrentYear
OUTER APPLY
(
SELECT
ISNULL(SUM(PaxCount), 0) PaxCountPreviousYear
FROM
(
SELECT DISTINCT
ItineraryID,
StateName,
ISNULL(PaxCount, 0) PaxCount
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= DATEADD(YEAR, -1, @StartDate)
AND PurchaseItemStartDate < DATEADD(YEAR, -1, @EndDate)
AND P.[StateName] IS NOT NULL
AND LEN(P.[StateName]) > 0
AND P.StateName = s.StateName
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
) PreviousYear
GROUP BY StateName
) tablePaxCountPreviousYear
OUTER APPLY
(
SELECT TOP (1)
CompanyName,
StreetAddress,
WorkPhone,
Email1,
Website,
VoucherLogoFile,
ClientFooter
FROM Agent A
LEFT JOIN AgentContact AC
ON AC.AgentID = A.AgentID
LEFT JOIN Contact C
ON C.ContactID = AC.ContactID
WHERE A.IsDefaultAgent = 1
) AgentContact
WHERE s.StateName IS NOT NULL
AND LEN(s.StateName) > 0
AND (PaxCountCurrentYear IS NOT NULL
OR PaxCountPreviousYear IS NOT NULL) ORDER BY StateName
-------------
trueStatePaxStatePaxFillGetDataStatePaxTableAdapterStateNameSystem.StringQuantityAndNumberOfDaysCountCurrentYearSystem.IntegerQuantityAndNumberOfDaysPreviousYearSystem.IntegerStateNights
Select s.StateName , IsNULL(QuantityAndNumberOfDaysCurrentYear,0) QuantityAndNumberOfDaysCountCurrentYear
,IsNULL(QuantityAndNumberOfDaysPreviousYear,0)QuantityAndNumberOfDaysPreviousYear
FROM [State] s
OUTER APPLY
(
Select Convert(Int,SUM(QuantityAndNumberOfDays)) QuantityAndNumberOfDaysCurrentYear
FROM
(
Select ItineraryID, StateName, IsNULL(NumberOfDays,1) * isNull(Quantity,1) QuantityAndNumberOfDays
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= @StartDate
AND PurchaseItemStartDate < @EndDate
AND P.[StateName] IS NOT NULL AND Len(P.[StateName]) > 0
AND P.StateName=s.StateName AND P.ServiceTypeName in('Accommodation','Appartement')
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)CurrentYear GROUP BY StateName
)tableQuantityAndNumberOfDaysCurrentYear
OUTER APPLY
(
Select Convert(Int,SUM(QuantityAndNumberOfDays)) QuantityAndNumberOfDaysPreviousYear
FROM
(
Select ItineraryID, StateName, IsNULL(NumberOfDays,1) * isNull(Quantity,1) QuantityAndNumberOfDays
FROM PurchaseItemDetail P
WHERE PurchaseItemStartDate >= DATEADD(YEAR, -1, @StartDate)
AND PurchaseItemStartDate < DATEADD(YEAR, -1, @EndDate)
AND P.[StateName] IS NOT NULL AND Len(P.[StateName]) > 0
AND P.StateName=s.StateName AND P.ServiceTypeName in('Accommodation','Appartement')
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)PreviousYear GROUP BY StateName
)tableQuantityAndNumberOfDaysPreviousYear
WHERE s.StateName IS NOT NULL AND Len(s.StateName) > 0
AND (QuantityAndNumberOfDaysCurrentYear IS NOT NULL OR QuantityAndNumberOfDaysPreviousYear IS NOT NULL)
ORDER BY StateName
trueStateNightsStateNightsStateNameSystem.StringCurrencyFormatCurrentYearSystem.StringCurrencyFormatPreviousYearSystem.StringNetFinalTotalCurrentYearSystem.DecimalNetFinalTotalCurrentYear_NoFOCSystem.DecimalGrossFinalTotalCurrentYearSystem.DecimalNetFinalTotalPreviousYearSystem.DecimalNetFinalTotalPreviousYear_NoFOCSystem.DecimalGrossFinalTotalPreviousYearSystem.DecimalDummyYearSystem.IntegerSortOrderCurrentYearSystem.IntegerStateAmt
Select 2017 as DummyYear
,IsNULL(SortOrderCurrentYear,SortOrderPreviousYear) SortOrderCurrentYear
, IsNULL(StateNameCurrentYear,StateNamePreviousYear) as StateName, CurrencyFormatCurrentYear, GrossFinalTotalCurrentYear, NetFinalTotalCurrentYear_NoFOC, NetFinalTotalCurrentYear, StateNamePreviousYear,CurrencyFormatPreviousYear, GrossFinalTotalPreviousYear, NetFinalTotalPreviousYear_NoFOC, NetFinalTotalPreviousYear
FROM
(
Select StateName StateNameCurrentYear,SortOrderCurrentYear,CurrencyFormat CurrencyFormatCurrentYear, SUM(GrossFinalTotal) GrossFinalTotalCurrentYear, SUM(NetFinalTotal) NetFinalTotalCurrentYear_NoFOC,SUM(NetFinalTotalScreen)NetFinalTotalCurrentYear --this is NetFinalTotalScreenCurrentYear
FROM
(
Select ItineraryID
,Case when P.StateName IS NULL OR Len(StateName)=0 THEN 'Other' ELSE StateName END StateName
,Case when P.StateName IS NULL OR Len(StateName)=0 THEN 100 ELSE 1 END SortOrderCurrentYear
,NetFinalTotal, GrossFinalTotal,CurrencyFormat CurrencyFormat
,(NetBaseUnit*
(
UnitMultiplier -
(
(Select Top(1) IsNull(DiscountUnits,0) from PurchaseItem where PurchaseItemID = P.PurchaseItemID) * IsNULL(NumberOfDays,1)
)
) * IsNULL(CurrencyRate,1)) NetFinalTotalScreen --Net (final) on Screen,
,PurchaseItemID,PurchaseItemName
,c.CountryID
FROM PurchaseItemDetail P
OUTER APPLY
(Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c
WHERE PurchaseItemStartDate >= @StartDate
AND PurchaseItemStartDate < @EndDate
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)CurrentYear GROUP BY StateName,CurrencyFormat ,SortOrderCurrentYear
)tableAmtCurrentYear
FULL OUTER JOIN
(
Select StateName StateNamePreviousYear,SortOrderPreviousYear, CurrencyFormat CurrencyFormatPreviousYear, SUM(GrossFinalTotal) GrossFinalTotalPreviousYear, SUM(NetFinalTotal) NetFinalTotalPreviousYear_NoFOC, SUM(NetFinalTotalScreen) NetFinalTotalPreviousYear --this is NetFinalTotalScreenPreviousYear
FROM
(
Select ItineraryID
,Case when P.StateName IS NULL OR Len(StateName)=0 THEN 'Other' ELSE StateName END StateName
,Case when P.StateName IS NULL OR Len(StateName)=0 THEN 100 ELSE 1 END SortOrderPreviousYear
, NetFinalTotal, GrossFinalTotal,CurrencyFormat
,(NetBaseUnit*
(
UnitMultiplier -
(
(Select Top(1) IsNull(DiscountUnits,0) from PurchaseItem where PurchaseItemID = P.PurchaseItemID) * IsNULL(NumberOfDays,1)
)
) * IsNULL(CurrencyRate,1)) NetFinalTotalScreen --Net (final) on Screen,
,PurchaseItemID,PurchaseItemName
,c.CountryID
FROM PurchaseItemDetail P
OUTER APPLY
(Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c
WHERE PurchaseItemStartDate >= DATEADD(YEAR, -1, @StartDate)
AND PurchaseItemStartDate < DATEADD(YEAR, -1, @EndDate)
AND (AgentID in @AgentIDList)
AND (ItineraryStatusID IN @ItineraryStatusIDList)
AND (RequestStatusID IN @RequestStatusIDList)
AND (BranchID in @BranchIDList OR BranchID IS NULL)
AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL)
AND (CountryID in @CountryIDList)
)PreviousYear GROUP BY StateName,CurrencyFormat,SortOrderPreviousYear
)tableAmtPreviousYear
ON tableAmtCurrentYear.StateNameCurrentYear=tableAmtPreviousYear.StateNamePreviousYear
AND tableAmtCurrentYear.CurrencyFormatCurrentYear=tableAmtPreviousYear.CurrencyFormatPreviousYear
WHERE ((GrossFinalTotalCurrentYear IS NOT NULL and GrossFinalTotalCurrentYear > 0)
OR (GrossFinalTotalPreviousYear IS NOT NULL and GrossFinalTotalPreviousYear > 0))
ORDER BY IsNULL(tableAmtCurrentYear.SortOrderCurrentYear,tableAmtPreviousYear.SortOrderPreviousYear)
,tableAmtCurrentYear.StateNameCurrentYear
trueStateAmtStateAmt2.75591in1.2in1.2in1.2in0.98583in1.04331intrueAnalysis by StateTextbox1Black1pt2pt2pt2pt2pt20.43307in2.95276in1.04331intruetrueTextbox432pt2pt2pt2ptExternal=IIF(Len(First(Fields!VoucherLogoFile.Value, "StatePax")) > 0,
IIF(Instr(lcase(First(Fields!VoucherLogoFile.Value, "StatePax")), "https:") > 0, First(Fields!VoucherLogoFile.Value, "StatePax"), "file:///" & First(Fields!DataPath.Value , "StatePax") & First(Fields!VoucherLogoFile.Value, "StatePax")),
"file:///" & "\Images\AgentLogo\TWTravelLogo.jpg")
'"file:///" & Parameters!DefaultImagePath.Value & "\Images\AgentLogo\TWTravelLogo.jpg")FitProportional30.14247intruetrueTextbox7Black3pt2pt2pt2pt2pttruetrueTextbox8Black3pt2pt2pt2pt2pttruetrueTextbox10Black3pt2pt2pt2pt2pttruetrueTextbox11Black3pt2pt2pt2pt2pttruetrueTextbox12Black3pt2pt2pt2pt2pt0.23622intruetruePAXTextbox281pt2pt2pt2pt2pttruetrue=DateAdd("yyyy",-1,DateValue(Parameters!StartDate.Value)).ToString("dd MMM yyyy") & " - " & vbcrlf & DateValue(DateAdd("yyyy",-1,Parameters!EndDate.Value)).ToString("dd MMM yyyy")
'=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'="01 JAN " & (Year(Parameters!StartDate.Value)-1) & " - " & vbcrlf & "31 DEC " & (Year(Parameters!StartDate.Value) -1)Textbox281pt2pt2pt2pt2pttruetrue=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'"01 JAN " & Year(Parameters!StartDate.Value) & " - " & vbcrlf & "31 DEC " & Year(Parameters!StartDate.Value)Textbox281pt2pt2pt2pt2pttruetrue# changeTextbox381pt2pt2pt2pt2pttruetrue% changeTextbox151pt2pt2pt2pt2pt0.06955intruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox91pt2pt2pt2pt2pt0.23622intruetrue=Fields!StateName.ValueStateName1ptGainsboro1pt2pt2pt2pt2pttruetrue=Format(Fields!PaxCountPreviousYear.Value,"###,##0")PaxCountPreviousYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=Fields!PaxCountCurrentYear.Value1ptGainsboro1pt2pt2pt2pt2pttruetrue=Fields!PaxCountCurrentYear.Value-Fields!PaxCountPreviousYear.ValuePaxCountCurrentYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(Fields!PaxCountPreviousYear.Value>0,Round((Fields!PaxCountCurrentYear.Value-Fields!PaxCountPreviousYear.Value)*100/Fields!PaxCountPreviousYear.Value,0) & "%","-")Textbox131ptGainsboro1pt2pt2pt2pt2pt0.05914intruetrueTextbox51ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox61ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox91ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox111ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox121ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrueTOTALTextbox16Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=Format(Fields!TotalPaxPreviousYear.Value,"###,###")
'Format(SUM(Fields!PaxCountPreviousYear.Value),"###,###")PaxCountPreviousYear1Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=Format(Fields!TotalPaxCurrentYear.Value,"###,###")
'=Format(SUM(Fields!PaxCountCurrentYear.Value),"###,###")PaxCountCurrentYear1Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=Format(IIF(Fields!TotalPaxCurrentYear.Value-Fields!TotalPaxPreviousYear.Value<>0,
Fields!TotalPaxCurrentYear.Value-Fields!TotalPaxPreviousYear.Value,"-")
,"###,###")
'=Format(IIF(SUM(Fields!PaxCountCurrentYear.Value)-SUM(Fields!PaxCountPreviousYear.Value)<>0,
'SUM(Fields!PaxCountCurrentYear.Value)-SUM(Fields!PaxCountPreviousYear.Value),"-")
',"###,###")Textbox23Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(Fields!TotalPaxPreviousYear.Value>0,Round((Fields!TotalPaxCurrentYear.Value-Fields!TotalPaxPreviousYear.Value)*100/Fields!TotalPaxPreviousYear.Value,0) & "%","-")
'=IIF(SUM(Fields!PaxCountPreviousYear.Value)>0,Round((SUM(Fields!PaxCountCurrentYear.Value)-SUM(Fields!PaxCountPreviousYear.Value))*100/SUM(Fields!PaxCountPreviousYear.Value),0) & "%","-")Textbox24Black3ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrueTextbox1Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox2Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox3Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox4Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox7Black3ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrue*Total displays unique pax. So if 2 pax visited 2 States, they are included in each State as 2 pax, but not counted twice in the Total.Textbox8Black3ptGainsboro1pt2pt2pt2pt2pt5AfterAfterAfterAfter=Fields!StateName.ValueBeforeBeforeBeforeBeforeStatePax6.33875cm18.64801cm2.75591in1.2in1.2in1.2in1in0.23622intruetrueROOM NIGHTSTextbox281pt2pt2pt2pt2pttruetrue=DateAdd("yyyy",-1,DateValue(Parameters!StartDate.Value)).ToString("dd MMM yyyy") & " - " & vbcrlf & DateValue(DateAdd("yyyy",-1,Parameters!EndDate.Value)).ToString("dd MMM yyyy")
'=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'="01 JAN " & (Year(Parameters!StartDate.Value)-1) & " - " & vbcrlf & "31 DEC " & (Year(Parameters!StartDate.Value) -1)Textbox281pt2pt2pt2pt2pttruetrue=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'"01 JAN " & Year(Parameters!StartDate.Value) & " - " & vbcrlf & "31 DEC " & Year(Parameters!StartDate.Value)Textbox281pt2pt2pt2pt2pttruetrue# changeTextbox381pt2pt2pt2pt2pttruetrue% changeTextbox151pt2pt2pt2pt2pt0.06955intruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox6941pt2pt2pt2pt2pttruetrueTextbox6951pt2pt2pt2pt2pttruetrueTextbox141pt2pt2pt2pt2pttruetrueTextbox261pt2pt2pt2pt2pt0.23622intruetrue=Fields!StateName.ValueStateName1ptGainsboro1pt2pt2pt2pt2pttruetrue=Fields!QuantityAndNumberOfDaysPreviousYear.ValuePaxCountPreviousYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=Fields!QuantityAndNumberOfDaysCountCurrentYear.Value1ptGainsboro1pt2pt2pt2pt2pttruetrue=Fields!QuantityAndNumberOfDaysCountCurrentYear.Value-Fields!QuantityAndNumberOfDaysPreviousYear.ValuePaxCountCurrentYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(Fields!QuantityAndNumberOfDaysPreviousYear.Value>0,Round((Fields!QuantityAndNumberOfDaysCountCurrentYear.Value-Fields!QuantityAndNumberOfDaysPreviousYear.Value)*100/Fields!QuantityAndNumberOfDaysPreviousYear.Value,0) & "%","-")Textbox131ptGainsboro1pt2pt2pt2pt2pt0.21539intruetrueTextbox141ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox151ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox161ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox171ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox261ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrueTOTALTextbox16Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value)PaxCountPreviousYear1Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=SUM(Fields!QuantityAndNumberOfDaysCountCurrentYear.Value)PaxCountCurrentYear1Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(SUM(Fields!QuantityAndNumberOfDaysCountCurrentYear.Value)-SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value)<>0,
SUM(Fields!QuantityAndNumberOfDaysCountCurrentYear.Value)-SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value),"-")Textbox23Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value)>0,Round((SUM(Fields!QuantityAndNumberOfDaysCountCurrentYear.Value)-SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value))*100/SUM(Fields!QuantityAndNumberOfDaysPreviousYear.Value),0) & "%","-")Textbox24Black3ptGainsboro1pt2pt2pt2pt2ptAfterAfter=Fields!StateName.ValueBeforeBeforeStateNightsStart6.53841cm2.52374cm18.684cm12.75591in1.2in1.2in1.2in1in0.23622intruetruePAYABLESTextbox281pt2pt2pt2pt2pttruetruetrue=DateAdd("yyyy",-1,DateValue(Parameters!StartDate.Value)).ToString("dd MMM yyyy") & " - " & vbcrlf & DateValue(DateAdd("yyyy",-1,Parameters!EndDate.Value)).ToString("dd MMM yyyy")
'=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'="01 JAN " & (Year(Parameters!StartDate.Value)-1) & " - " & vbcrlf & "31 DEC " & (Year(Parameters!StartDate.Value) -1)Textbox281pt2pt2pt2pt2pttruetrue=Format(Parameters!StartDate.Value,"dd MMM yyyy") & " - " & vbcrlf & Format(Parameters!EndDate.Value,"dd MMM yyyy")
'"01 JAN " & Year(Parameters!StartDate.Value) & " - " & vbcrlf & "31 DEC " & Year(Parameters!StartDate.Value)Textbox281pt2pt2pt2pt2pttruetrue# changeTextbox381pt2pt2pt2pt2pttruetrue% changeTextbox151pt2pt2pt2pt2pt0.23622intruetrueTextbox91pt2pt2pt2pt2pttruetrueTextbox861pt2pt2pt2pt2pttruetrueTextbox851pt2pt2pt2pt2pttruetrueTextbox141pt2pt2pt2pt2pttruetrueTextbox261pt2pt2pt2pt2pt0.23622intruetrue=Fields!StateName.ValueStateName1ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round(Fields!NetFinalTotalPreviousYear.Value,0),Fields!CurrencyFormatPreviousYear.Value),".00","")PaxCountPreviousYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round(Fields!NetFinalTotalCurrentYear.Value,0),Fields!CurrencyFormatCurrentYear.Value),".00","")1ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round((Fields!NetFinalTotalCurrentYear.Value-Fields!NetFinalTotalPreviousYear.Value),0),IIF(Len(Fields!CurrencyFormatCurrentYear.Value)>0,Fields!CurrencyFormatCurrentYear.Value,Fields!CurrencyFormatPreviousYear.Value)),".00","")PaxCountCurrentYear1ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(code.CalPercent(Fields!NetFinalTotalPreviousYear.Value,Fields!NetFinalTotalCurrentYear.Value)=0,"-",code.CalPercent(Fields!NetFinalTotalPreviousYear.Value,Fields!NetFinalTotalCurrentYear.Value) & "%")
'code.CheckAmt(Fields!CurrencyFormatPreviousYear.Value)Textbox131ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrueTextbox98Gainsboro1ptBlack3pt2pt2pt2pt2pttruetrueTextbox98Gainsboro1ptBlack3pt2pt2pt2pt2pttruetrueTextbox98Gainsboro1ptBlack3pt2pt2pt2pt2pttruetrueTextbox98Gainsboro1ptBlack3pt2pt2pt2pt2pttruetrueTextbox98Gainsboro1ptBlack3pt2pt2pt2pt2pt0.23622intrueGroup1true="TOTAL"Textbox16Black3ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round(SUM(Fields!NetFinalTotalPreviousYear.Value),0),IIF(Len(Fields!CurrencyFormatPreviousYear.Value)>0,Fields!CurrencyFormatPreviousYear.Value,Fields!CurrencyFormatCurrentYear.Value)),".00","")
'=Replace(Format(Round(SUM(Fields!NetFinalTotalPreviousYear.Value),0),Fields!CurrencyFormatPreviousYear.Value),".00","")Textbox92Gainsboro1ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round(SUM(Fields!NetFinalTotalCurrentYear.Value),0),IIF(Len(Fields!CurrencyFormatCurrentYear.Value)>0,Fields!CurrencyFormatCurrentYear.Value,Fields!CurrencyFormatPreviousYear.Value)),".00","")Textbox93Gainsboro1ptGainsboro1pt2pt2pt2pt2pttruetrue=Replace(Format(Round(SUM(Fields!NetFinalTotalCurrentYear.Value)-SUM(Fields!NetFinalTotalPreviousYear.Value),0),IIF(Len(Fields!CurrencyFormatCurrentYear.Value)>0,Fields!CurrencyFormatCurrentYear.Value,Fields!CurrencyFormatPreviousYear.Value)),".00","")Textbox94Gainsboro1ptGainsboro1pt2pt2pt2pt2pttruetrue=IIF(code.CalPercent(SUM(Fields!NetFinalTotalPreviousYear.Value),SUM(Fields!NetFinalTotalCurrentYear.Value))=0,"-",code.CalPercent(SUM(Fields!NetFinalTotalPreviousYear.Value),SUM(Fields!NetFinalTotalCurrentYear.Value)) & "%")
'=IIF(SUM(Fields!NetFinalTotalPreviousYear.Value)>0,Round((SUM(Fields!NetFinalTotalCurrentYear.Value)-SUM(Fields!NetFinalTotalPreviousYear.Value))*100/SUM(Fields!NetFinalTotalPreviousYear.Value),2) & "%","-")Textbox95Gainsboro1ptGainsboro1pt2pt2pt2pt2pt0.23622intruetrueTextbox97Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox87Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox88Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox89Black3ptGainsboro1pt2pt2pt2pt2pttruetrueTextbox90Black3ptGainsboro1pt2pt2pt2pt2pt=Fields!SortOrderCurrentYear.Value=Fields!StateName.Value=Fields!DummyYear.Value=Fields!DummyYear.Value=IIF(Len(Fields!CurrencyFormatCurrentYear.Value)>0,Fields!CurrencyFormatCurrentYear.Value,Fields!CurrencyFormatPreviousYear.Value)=IIF(Len(Fields!CurrencyFormatCurrentYear.Value)>0,Fields!CurrencyFormatCurrentYear.Value,Fields!CurrencyFormatPreviousYear.Value)StateAmtStart9.17cm3.59999cm18.684cm212.76999cmStringClient locationsReportNametrueDateTime=Today().AddMonths(-6)
'CDate("1/1" & year(Now()).ToString())StartDatetrueDateTime=Today()
'Now().AddMonths(6)EndDatetrueString[!AgentName]-[!ItineraryId]-[!ItineraryName]-[!ItineraryType]-[!DisplayName]-[!CustomId]-[!ItineraryStatus]-[!ReportName]-[!DDMM]trueReportFileNameFormattruePublic Function CheckAmt(Amt As double) As String
if (Amt >0) Then
Return "Yes"
Else
Return "No"
End If
End Function
Public Function CalPercent(AmtPrev As double, AmtCurrent As double) As double
if (AmtPrev <=0) Then
Return 0
Else
Return Round((AmtCurrent-AmtPrev)*100/AmtPrev,0)
End If
End Function18.684cm0.50002cmtruetruetruetrue="Printed " + Format(Now(),"dd MMM yyyy,")2.17in0.5cm4.89591cmtruetrue="Page " & Globals!PageNumber & " of " & Globals!TotalPages0in4.118in0.5cm2.49126cm129.7cm21cm11in8.5in1cm1cm1cm1cm1cm=User.Languagetrueeff67c7a-44fa-4c85-9116-a6dfb4bd2fa6Cm