Hej,
Finns det möjlighet att få tillgång till de queries som används för Fortnox-datakällorna i mallen för Google Looker Studio?
Hej @Philip! Absolut. Vi är 100% transparenta med våra datamodeller. Se efterfrågade queries (databasfrågor) i SQL nedan.
Hör av dig om du behöver något mer.
Bokföring (vouchers)
select
v.OrgId,
c.OrgName,
fy.FinancialYear,
fy.FyCounter,
format_date("%Y%m%d", v.TransactionDate) as TransactionDate,
v.Description as VoucherDescription,
v.VoucherSeries,
v.VoucherNumber,
vs.Description as VoucherSeriesName,
v.Account,
a.AccountName,
a.AccountClassId,
case
when CAST(v.Account as INTEGER) between 1000 and 1999 then "1. Tillgångar" --1. Assets
when CAST(v.Account as INTEGER) between 2000 and 2999 then "2. Eget kapital och skulder" --2. Equity and Liabilities
when CAST(v.Account as INTEGER) between 3000 and 3999 then "3. Intäkter" --3. Operating income/revenue
when CAST(v.Account as INTEGER) between 4000 and 4999 then "4. Materialkostnader" --4. Cost of goods
when CAST(v.Account as INTEGER) between 5000 and 6999 then "5-6. Övriga kostnader" --5-6. Other external costs
when CAST(v.Account as INTEGER) between 7000 and 7999 then "7. Personal" --7. Personnel costs
when CAST(v.Account as INTEGER) between 8000 and 8999 then "8. Finansiella intäkter/kostnader" --8. Financials
ELSE CAST(v.Account as STRING)
END AccountClass,
-- a.AccountClass,
a.MainAccountId,
a.MainAccount,
a.SubAccountId,
a.SubAccount,
cc.Description as CostCenter,
p.Description as Project,
v.Balance,
from
`enhanza-elt.fortnox_bi.fact_vouchers` v
left join `enhanza-elt.fortnox_bi.dim_company` c on v.OrgId = c.OrgId
left join `enhanza-elt.fortnox_bi.dim_financial_years` fy on v.FinancialYearId = fy.FinancialYearId
left join `enhanza-elt.fortnox_bi.dim_voucher_series` vs on v.VoucherSeriesId = vs.VoucherSeriesId
left join `enhanza-elt.fortnox_bi.dim_accounts` a on v.AccountId = a.AccountId
left join `enhanza-elt.fortnox_bi.dim_cost_centers` cc on v.CostCenterId = cc.CostCenterId
left join `enhanza-elt.fortnox_bi.dim_projects` p on v.ProjectId = p.ProjectId
Fakturor (invoices)
SELECT
i.OrgId,
cs.OrgName,
i.InvoiceNo,
format_date("%Y%m%d", i.InvoiceDate) as InvoiceDate,
i.OurReference,
c.Name as CustomerName,
c.CustomerNumber,
c.City as CustomerCity,
c.Country as CustomerCountry,
i.Net,
i.Gross,
i.Freight,
i.TotalVAT,
i.TotalToPay,
i.Sent,
i.Balance,
format_date("%Y%m%d", i.DueDate) as DueDate,
IF (DueDate < current_date(), true, false) Due,
case
when Sent IS false then "NotSent"
when Sent IS true then case
when Balance = 0 then "Paid"
when Balance <> 0 then case
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 0
and 15 then "0-15d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 16
and 30 then "16-30d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 31
and 45 then "31-45d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 46
and 60 then "46-60d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 61
and 90 then "61-90d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) between 91
and 120 then "91-120d"
when ABS(DATE_DIFF(current_date(), DueDate, day)) > 120 then ">120d"
when DueDate is null then 'NO_DUE_DATE'
else 'WHAT?'
end
else 'WOW??'
end
else "[calc_error]"
end DueStatus,
i.Reminders,
format_date("%Y%m%d", i.LastRemindDate) as LastRemindDate,
from
`enhanza-elt.fortnox_bi.fact_invoices` i
left join `enhanza-elt.fortnox_bi.dim_company` cs ON i.OrgId = cs.OrgId
left join `enhanza-elt.fortnox_bi.dim_customers` c ON i.CustomerId = c.CustomerId
where
i.InvoiceDate between date_sub(current_date(), interval 3 year)
AND date_add(current_date(), interval 1 year)
Fakturarader (invoice rows)
select
i.OrgId,
cs.OrgName,
i.InvoiceNo,
format_date("%Y%m%d", i.InvoiceDate) as InvoiceDate,
i.OurReference,
c.Name as CustomerName,
c.CustomerNumber,
c.City as CustomerCity,
c.Country as CustomerCountry,
i.ArticleNumber as ArticleNumber,
i.DeliveredQuantity,
a.Description as ArticleName,
-- i.AccountId as AccountNumber,
i.SalesValue,
i.ContributionValue as ContributionValue,
cc.Description as CostCenter,
p.Description as Project
from
`enhanza-elt.fortnox_bi.fact_invoice_rows` i
left join `enhanza-elt.fortnox_bi.dim_company` cs ON i.OrgId = cs.OrgId
left join `enhanza-elt.fortnox_bi.dim_customers` c ON i.CustomerId = c.CustomerId
left join `enhanza-elt.fortnox_bi.dim_articles` a ON i.ArticleId = a.ArticleId
left join `enhanza-elt.fortnox_bi.dim_cost_centers` cc on i.CostCenterId = cc.CostCenterId
left join `enhanza-elt.fortnox_bi.dim_projects` p on i.ProjectId = p.ProjectId
where
i.InvoiceDate between date_sub(current_date(), interval 3 year)
and date_add(current_date(), interval 1 year)
Saldo & budget
with accounts as (
with months as (
-- create an array of dates per the end of each month from all financial years.
select
OrgId,
FinancialYearId,
FinancialYear,
FyCounter,
last_day(m) month_last_day
from
`enhanza-elt.fortnox_bi.dim_financial_years`
cross join unnest(
GENERATE_DATE_ARRAY(FromDate, ToDate, interval 1 month)
) as m
)
select
-- List all accounts with their BalanceBroughtForward and sum of balance from vouchers.
m.OrgId,
m.FinancialYearId,
m.FinancialYear,
m.FyCounter,
m.month_last_day Month,
a.Number Account,
a.AccountName,
concat(a.MainAccountId,". ", a.MainAccount) as MainAccount,
-- last_day(v.TransactionDate) VoucherMonth,
ifnull(sum(Balance) * -1, 0) Balance,
BalanceBroughtForward
from
months m
left join `enhanza-elt.fortnox_bi.dim_accounts` a on m.FinancialYearId = a.FinancialYearId
left join `enhanza-elt.fortnox_bi.fact_vouchers` v on v.Account = a.Number
and v.FinancialYearId = m.FinancialYearId
and last_day(v.TransactionDate) = m.month_last_day
where
a.Number between 1000
and 2999
group by
m.OrgId,
m.FinancialYearId,
m.FinancialYear,
m.FyCounter,
a.Number,
a.AccountName,
MainAccount,
Month,
a.BalanceBroughtForward
)
select
a.OrgId,
cs.OrgName,
a.FinancialYear,
a.FyCounter,
format_date("%Y%m%d", a.Month) as Month,
a.Account as AccountNumber,
a.AccountName,
a.MainAccount,
a.BalanceBroughtForward,
sum(a.balance) over (
partition by a.FinancialYearId,
a.Account
order by
Month
) + a.BalanceBroughtForward - a.balance as PeriodOpeningBalance,
balance PeriodBalance,
sum(balance) over (
partition by FinancialYearId,
Account
order by
Month
) + BalanceBroughtForward as BalanceCarriedForward
from
accounts a
left join `enhanza-elt.fortnox_bi.dim_company` cs on a.OrgId = cs.OrgId
-- ignore empty rows.
-- where a.BalanceBroughtForward <> 0 and PeriodOpeningBalance <> 0 and PeriodBalance <> 0 and BalanceCarriedForward <> 0
1 Like