/
Canadian Venues

Canadian Venues

Owned by Kaitlyn Niemann (Unlicensed), created
Last updated: Jan 24, 2024 by Albert Saludaga

Requirements and Purpose

Page with search functions and fields for identifying Canadian Venues. To be used when Identifying Inventory Discrepancies.

Report Definition

  1. Stakeholders

    1. @Kaitlyn Niemann (Unlicensed)

    2. Samantha Fortenberry

  2. Author

    1. @Kaitlyn Niemann (Unlicensed)

  3. Subject matter expert(s)

    1. Samantha Fortenberry

  4. Presentation Medium

    1. SSRS

  5. Fields (data)

    1. Venue

    2. Street Address

    3. City

    4. Postal Code

    5. Region Name

Data Source(s)

The data source for this report is the Eibo-215 database.

Stored Procedure: sr_GetCanadianVenues

Database

Table(s)

Database

Table(s)

eiboxoffice

  1. Venue

  2. Region

  3. Country

Calculations

Column Name

Data Type

Calculation

Description

Column Name

Data Type

Calculation

Description

n/a

SubQuery

;WITH VenueNames AS( SELECT v.VenueName eVenue, v.VenueID, CONCAT( v.Address1, v.Address2, v.Address3 ) StreetAddress, v.City, v.PostalCode, r.RegionName, c.CountryName, v.IsSystem, ROW_NUMBER() OVER (PARTITION BY VenueName ORDER BY isSystem DESC) AS rn, CASE WHEN CHARINDEX(ISNULL(@venue,v.venuename),v.venuename) > 0 THEN 1 ELSE 0 END vc, CASE WHEN CHARINDEX(ISNULL(@address,CONCAT(v.Address1,v.Address2,v.Address3)),CONCAT(v.Address1,v.Address2,v.Address3)) > 0 THEN 1 ELSE 0 END ac, CASE WHEN CHARINDEX(ISNULL(@city,v.City),v.City) > 0 THEN 1 ELSE 0 END cc, CASE WHEN CHARINDEX(ISNULL(@state,r.RegionName),r.RegionName) > 0 THEN 1 ELSE 0 END rc FROM Venue v LEFT JOIN Region r ON v.RegionID = r.RegionID LEFT JOIN Country c ON r.CountryID = c.CountryID WHERE CountryName = 'Canada')

Generates dataset

rn

int

=ROW_NUMBER() OVER (PARTITION BY VenueName ORDER BY isSystem DESC) AS rn,

priorities venue names by system

vc

tinyint

=CASE WHEN CHARINDEX(ISNULL(@venue,v.venuename),v.venuename) > 0 THEN 1 ELSE 0 END vc,

Allows for search by venue

ac

tinyint

=CASE WHEN CHARINDEX(ISNULL(@address,CONCAT(v.Address1,v.Address2,v.Address3)),CONCAT(v.Address1,v.Address2,v.Address3)) > 0 THEN 1 ELSE 0 END ac,

Allows for search by address

cc

tinyint

CASE WHEN CHARINDEX(ISNULL(@city,v.City),v.City) > 0 THEN 1 ELSE 0 END cc,

Allows for search by city

rc

tinyint

CASE WHEN CHARINDEX(ISNULL(@state,r.RegionName),r.RegionName) > 0 THEN 1 ELSE 0 END rc

Allows for search by region/state/territory

Parameters

  1. Filters

    1. @venue

      1. text

      2. can be blank

    2. @address

      1. text

      2. can be blank

    3. @city

      1. text

      2. can be blank

    4. @state

      1. text

      2. can be blank

  2. Grouping

    1. VenueName

    2. rn

  3. Intentions for printing vs. online viewing

    1. The report is delivered via SSRS

    2. The report has been moved to SSRS Reports → Accounting → Canadian Venues

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

    1. On Demand

  5. Future maintenance tasks or manually maintained items

    1. N/a

Results

  1. Data Accuracy

    1. QA

      1. Validate against Maintenance Module in Eibo for Venues

        1. Venues should only appear in Canada

  2. Limitations

    1. n/a

Change Log

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

Images

Reports's UI:





Related content