/
Monthly Graphs - Orders

Monthly Graphs - Orders

Owned by April Dianne Gahuman
Last updated: Jan 22, 2024 by Albert Saludaga

This report displays the trend of orders by month and year.

Report Definition

  1. Author

    1. April Dianne Gahuman (Full Scale developer)

  2. Presentation Medium

    1. SSRS Portal

  3. Fields (data)

    1. Orders

      1. TotalTicketPrice

      2. Month

      3. Year

    2. YearDataSet

      1. Year

  4. Parameters

    1. year

Calculations

Column Name

Data Type

Calculation

Column Name

Data Type

Calculation

TotalTicketPrice

int

COUNT(TotalTicketPrice)

Data Sources

The main data source will be the TB_SalesView view in the eiboxoffice database (192.168.10.215).

Data Set

Data Set

 Query

Description

Data Set

 Query

Description

1

Orders

SELECT COUNT(TotalTicketPrice) AS TotalTicketPrice, DATENAME(MONTH, DATEADD(MONTH, MONTH(FinalizedDatetime), -1 )) AS Month, YEAR(FinalizedDatetime) AS Year FROM [eiboxoffice].[dbo].[TB_SalesView] WHERE IsCanceled = 0 AND MONTH(FinalizedDatetime) IS NOT NULL AND YEAR(FinalizedDatetime) IS NOT NULL AND YEAR(FinalizedDatetime) IN (@year) GROUP BY MONTH(FinalizedDatetime), YEAR(FinalizedDatetime) ORDER BY YEAR(FinalizedDatetime) DESC, MONTH(FinalizedDatetime)

This gets the count of the total ticket price and its corresponding month and year that were tagged as not canceled and falls within the year of selection.

2

YearDataSet

SELECT DISTINCT YEAR(sv.FinalizedDatetime) AS Year FROM [eiboxoffice].[dbo].[TB_SalesView] sv WHERE sv.IsCanceled = 0 AND YEAR(sv.FinalizedDatetime) IS NOT NULL ORDER BY YEAR(sv.FinalizedDatetime) DESC

This gets the list of years in the table to be shown as a dropdown selection in the parameter.

 

Related content