I'm trying to write a query in SSRS (using SQL) to calculate an income statement percentage of sales for each month (the year is a parameter chosen by the user at runtime). However, the table I have to use for the data lists all of the years, months, accounts, dollars, etc together and looks like this:

etc, etc (ACCOUNT_ID =4000 happens to be the sales account)

As an example, I need to calculate

CREDIT_AMOUNT when ACCT_YEAR = 2021, ACCT_PERIOD=1, and ACCOUNT_ID=5000
/
CREDIT_AMOUNT when ACCT_YEAR = 2021, ACCT_PERIOD=1, and ACCOUNT_ID=4000

* 100

I would then do that for each ACCT_PERIOD in the ACCT_YEAR. Hope that makes sense...What I want would look like this:

I'm trying to create a graph that shows the percentage of sales of roughly 10 different accounts (I know their specific account_ID's and will filter by those ID's) and use the line chart widget to show the trends by month.
I've tried CASE scenarios, OVER scenarios, and nested subqueries. I feel like this should be simple but I'm being hardheaded and not seeing the obvious solution. Any suggestions? Thank you!

CodePudding user response：

You just to use a matrix with a parent column group for ACCT_YEAR and a child column group for ACCT_PERIOD. Then you can use your calculation. If you format the textbox for percentage, you won't need to multiply it by 100.

Textbox value: =IIF(ACCOUNT_ID=4000, Sum(CREDIT_AMOUNT), 0) = 0, 0, IIF(ACCOUNT_ID=5000, Sum(CREDIT_AMOUNT), 0) / IIF(ACCOUNT_ID=4000, Sum(CREDIT_AMOUNT), 0)

CodePudding user response：

One important behaviour to note is that window functions are applied after the where clause.

Because you need the window functions to be applied before any where clause (which would filter account 4000 out), they need to be used in one scope, and the where clause in another scope.

WITH
  perc AS
(
  SELECT
    *,
    credit_amount * 100.0
    /
    SUM(
      CASE WHEN account_id = 4000 THEN credit_amount END
    )
    OVER (
      PARTITION BY acct_year, accr_period
    )
      AS credit_percentage
  FROM
    your_table
)
SELECT
  *
FROM
  perc
WHERE
  account_id IN (5000,6000)