/
Tour Comparison

Tour Comparison

Owned by Kaitlyn Niemann (Unlicensed), created
Last updated: May 29, 2024 by Albert Saludaga
2 min read

Requirements and Purpose

Compares events from 2 different dateranges to identify performance changes between datasets.

Report Definition

  1. Stakeholders

    1. @Mark Shaver

    2. @Kaitlyn Niemann (Unlicensed)

  2. Author

    1. @Kaitlyn Niemann (Unlicensed)

  3. Subject matter expert(s)

    1. @Mark Shaver

  4. Presentation Medium

    1. SSRS

  5. Fields (data)

    1. Tour 1 Pricer (varchar)

    2. Tour 2 Pricer (varchar)

    3. Event (varchar)

    4. Venue(varchar)

    5. Tour 1 Event Date (datetime)

    6. Tour 2 Event Date (datetime)

    7. Section (varchar)

    8. Tour 1 Profit (decimal)

    9. Tour 1 Margin (decimal)

    10. Tour 2 Profit (decimal)

    11. Tour 2 Margin (decimal)

    12. Margin Change (decimal)

Data source(s)

The data source(s) for this report are the Eibo-215 Database and the STG Database.

  1. eiboxoffice.dbo

    1. TicketGroup

    2. Event

    3. Venue

    4. Region

    5. SellOrderTicketGroup

  2. STG.users

    1. PricerEvents

    2. Pricer

Calculations

Column Name

Data Type

Calculation

Description

Column Name

Data Type

Calculation

Description

OldMargin

decimal

=CASE
WHEN SUM(ISNULL(oldsales.TotalSale,0)) = 0 AND SUM(ISNULL(oldcost.TotalCost,0)) > 0
THEN CAST(-1.00 as decimal(12,2))
WHEN SUM(ISNULL(oldsales.TotalSale,0)) = 0 AND SUM(ISNULL(oldcost.TotalCost,0)) = 0
THEN CAST(0.00 as decimal(12,2))
ELSE CAST((SUM(ISNULL(oldsales.TotalSale,0))-SUM(ISNULL(oldcost.TotalCost,0))+0.0001)/(SUM(ISNULL(oldsales.TotalSale,0))+0.0001) as decimal(12,2))
END OldMargin,

Calculates margin for event performance between @Tour1Start and @Tour1End

NewMargin

decimal

=CASE
WHEN SUM(ISNULL(newsales.TotalSale,0)) = 0 AND SUM(ISNULL(newcost.TotalCost,0)) > 0
THEN CAST(-1.00 as decimal(12,2))
WHEN SUM(ISNULL(newsales.TotalSale,0)) = 0 AND SUM(ISNULL(newcost.TotalCost,0)) = 0
THEN CAST(0.00 as decimal(12,2))
ELSE CAST((SUM(ISNULL(newsales.TotalSale,0))-SUM(ISNULL(newcost.TotalCost,0))+0.0001)/(SUM(ISNULL(newsales.TotalSale,0))+0.0001) as decimal(12,2))
END NewMargin

Calculates margin for event performance between @Tour2Start and @Tour2End

MarginChange

decimal

=CASE
WHEN SUM(ISNULL(oldsales.TotalSale,0)) - SUM(ISNULL(oldcost.TotalCost,0)) = 0 AND sum(ISNULL(newsales.TotalSale,0)) - SUM(ISNULL(newcost.TotalCost,0)) != 0
THEN CAST(1.00 as decimal(12,2))
WHEN SUM(ISNULL(oldsales.TotalSale,0)) - SUM(ISNULL(oldcost.TotalCost,0)) != 0 AND sum(ISNULL(newsales.TotalSale,0)) - SUM(ISNULL(newcost.TotalCost,0)) = 0
THEN CAST(0.00 as decimal(12,2))
ELSE
CAST(((SUM(ISNULL(newsales.TotalSale,0))-SUM(ISNULL(newcost.TotalCost,0))+0.0001)/(SUM(ISNULL(newsales.TotalSale,0))+0.0001)/*New Margin*/ - (SUM(ISNULL(oldsales.TotalSale,0))-SUM(ISNULL(oldcost.TotalCost,0))+0.0001)/(SUM(ISNULL(oldsales.TotalSale,0))+0.0001)/*Old Margin*/) as decimal(12,2))END MarginChange

Calculates margin difference between tour 1 and 2

Parameters

  1. Filters

    1. @Event

      1. text

      2. can be blank

    2. @venue

      1. text

      2. can be blank

    3. @Tour1Start

      1. Datetime

      2. must be before tour2 date range

    4. @Tour1End

      1. DateTime

      2. must be before tour2 date range

    5. @Tour2Start

      1. Datetime

      2. must be after tour1 date range

    6. @Tour2End

      1. DateTime

      2. must be after tour1 date range

    7. @pricers

      1. Multiple Values

      2. Defaults to all

      3. Only applies to tour 2

  2. Grouping

    1. eEvent

    2. eVenue

  3. Intentions for printing vs. online viewing

    1. The report is delivered via SSRS

    2. This report can be accessed here: SSRS → Asset → Tour Comparison

  4. Intended delivery mechanism (run on demand vs. delivered by subscription)

    1. On demand

  5. Future maintenance tasks or manually maintained items

    1. None

Results

  1. Data Accuracy

    1. QA

      1. Validate against income by event report

  2. Limitations

    1. Report shortfalls

Change Log

  1. https://ticketboat.atlassian.net/browse/SP-287

Images



Related content