CODEWITHSUNDEEP
sql-serverdatereporting-servicesparameters
Justin Harris
Justin Harris

Reputation: 27

SSRS Quarter period custom

I need to build a report that has custom quarter periods as shown below:

Quarter 1 - Jul, Aug, Sep Quarter 2 - Oct, Nov, Dec Quarter 3 - Jan, Feb, Mar Quarter 4 - Apr, May, June

I'm a bit lost on how to approach this as I need to create a parameter where if they select quarter 1 it should only display data for the corresponding months.

Is there anyone that can suggest something?

Script looks as follows:

SELECT
ATD.TrnYear, 
ATD.TrnMonth, 
ATD.Invoice, 
ATD.InvoiceDate, 
ATD.Salesperson, 
ATD.Customer, 
ATD.StockCode, 
ATD.ProductClass, 
ATD.QtyInvoiced, 
ATD.NetSalesValue, 
ATD.CostValue, 
AC.Name

FROM ArTrnDetail ATD JOIN ArCustomer AC ON ATD.Customer = AC.Customer

So I would need to add how to distinguish when the user selects the quarter.

Much appreciated.

Upvotes: 0

Views: 1156

Answers (2)

papermoon88
papermoon88

Reputation: 476

Assuming your ATD.TrnMonth column use integer to represent months, I added an additional column to your query to indicate quarters (QTR). Also added the condition which will filter your result based on the user selection (WHERE Qtr = @quarter). This will be your dataset query.

SELECT *
FROM 
    (SELECT
    CASE 
        WHEN ATD.TrnMonth IN (7,8,9) THEN 1
        WHEN ATD.TrnMonth IN (10,11,12) THEN 2
        WHEN ATD.TrnMonth IN (1,2,3) THEN 3
        WHEN ATD.TrnMonth IN (4,5,6) THEN 4
    END as Qtr,
    ATD.TrnYear, 
    ATD.TrnMonth, 
    ATD.Invoice, 
    ATD.InvoiceDate, 
    ATD.Salesperson, 
    ATD.Customer, 
    ATD.StockCode, 
    ATD.ProductClass, 
    ATD.QtyInvoiced, 
    ATD.NetSalesValue, 
    ATD.CostValue, 
    AC.Name
    FROM ArTrnDetail ATD JOIN ArCustomer AC ON ATD.Customer = AC.Customer
    ) as ArTrnDetail
WHERE Qtr = @quarter

In your report builder, create paramater with these settings:

GENERAL TAB

  • Name: quarter
  • Prompt: (whatever you want displayed for user)

AVAILABLE VALUES TAB

  • select Specify Values, and add 4 available values.
  • The label should be 1, 2, 3, and 4 (quarters) and the value should be the labels you want displayed in your dropdown list. Make sure the labels correspond with your values (quarters)

DEFAULT VALUES TAB

  • For example, when the report runs and you want the report to default to the first quarter. Select Specify Values, put 1 in the value. Leave this tab if you don't want defaults.

Note - This is the configuration for a single value dropdown. If you want the user to be able to select multiple values. In the "General" tab of the parameter, check "Allow multiple values" and change your query condition from WHERE Qtr = @quarter to WHERE Qtr IN (@quarter).

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You need to map your data to a Date Dimension.

When you have done that you would have a fact table that can be joined to your date table. Either by an surrogatekey or a businesskey.

An example could be.

Query for Report

Select SalesAmount,SalesDate,Employee from fact.Sales S
inner join Dim.Datedimension d on s.SalesDate = d.Date

where d.QuarterNo = @DateParameter

Query for filter

Select distinct QuarterNo,QuarterLabel(Your Quarter 1 - Jul, Aug, Sep) From Dim.Datedimension

QuarterNo will be your value in parameter settings. QuarterLabel will be your label.

Remember to adjust your date dimension to match your special quarters.

Upvotes: 0

Related Questions