DNS Report

Requirements and Purpose

Creates a report of all tickets with either a DNS note as well as ones that should potentially be DNS. Can search by event, venue, pricer, status, and date.

Report Definition

Stakeholders
1. Kaitlyn Niemann
2. Jake Kern
Author
1. Kaitlyn Niemann
Subject matter expert(s)
1. Jake Kern
Presentation Medium
1. SSRS
Fields (data)
1. Pricer
2. Event
3. Opponent
4. Venue
5. Event Date
6. Section Row
7. Seats
8. Ticket Price
9. Note
10. Resolution Date
11. Status

Data source(s)

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

Tables:

eiboxoffice.dbo
1. dbo.TicketGroup
2. dbo.Event(Host and Opponent)
3. dbo.Venue
4. dbo.def_ShareType
5. dbo.SellOrderTicketGroup
6. dbo.SellOrder
STG
1. stg.users.pricerevents
2. stg.users.users
Eibo Marketplace

Calculations

Column Name	Data Type	Calculation	Description

Column Name

Data Type

Calculation

Description

Event Date

Date

=tg.EventDate + ISNULL(tg.EventTime,0)

Combines date and time

Seats

VARCHAR

=CONCAT(tg.StartingSeat,'-',tg.EndingSeat)

Lists seats in a single field.

Status

INT

=CASE
WHEN CTE.dnstype = 0 THEN 'GOOD'
WHEN CTE.dnstype = 1 THEN 'DELIST'
WHEN CTE.dnstype = 2 THEN 'VERIFY'
WHEN CTE.dnstype = 3 THEN 'SOLD'
ELSE 'IF YOU SEE THIS THERE IS AN ISSUE'
END errmsg

cte.dnstype =CASE
WHEN CHARINDEX('DNS',tg.InternalNote) > 0 AND ss.ShareTypeID != 2 THEN 0
WHEN CHARINDEX('DNS',tg.InternalNote) > 0 and ss.ShareTypeID = 2 THEN 1
WHEN CHARINDEX('DNS',tg.InternalNote) = 0 AND COALESCE((sg.TotalTicketPrice/sg.quantity),tg.AverageTicketPrice) > 5000 THEN 2
WHEN CHARINDEX('DNS',tg.InternalNote) > 0 AND tg.TicketStatusTypeID = 3 THEN 3
ELSE 4

Assigns a status depending on the note and price of the tickets.

Parameters

Filters
1. @Pricer VARCHAR
  1. Last name of pricer.
2. @Event
  1. If left blank will return all results
  2. Must match eibo event.
3. @venue
  1. If left blank will return all results.
  2. Must match eibo venue.
4. @startDate
  1. Defines start of date range for search
  2. Defaults to today.
5. @end date
  1. Defines end of date range for search.
  2. Defaults to +7 days.
6. @Status
  1. GOOD Tickets that are unshared with a DNS note.
  2. DELIST Tickets that are shared with a DNS note.
  3. VERIFY Tickets that are priced at a significant margin that may need dns.
  4. SOLD Tickets sold with a dns note.
  5. ALL RECORDS Default value, returns all statuses.
7. @Sold Status
  1. Unsold
  2. Sold
8. @Note Type
  1. DNS.
  2. DNE
  3. DNP
  4. ALL
Page breaks (Dashboards/Worksheets/Etc.)
1. Summary At top of page of current data set.
Intentions for printing vs. online viewing
1. The report is delivered via SSRS
2. This report can be accessed here: SSRS→ Asset → DNS Report.
Intended delivery mechanism (run on demand vs. delivered by subscription)
1. On Demand.
Future maintenance tasks or manually maintained items
1. None

Results

Data Accuracy
1. N/a
Limitations
1. N/a

Data Governance