---
title: "Lusid.Portfolio.AtoB"
slug: "lusidportfolioatob"
updated: 2024-08-21T12:54:40Z
published: 2024-08-21T12:54:40Z
canonical: "support.lusid.com/lusidportfolioatob"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Lusid.Portfolio.AtoB

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Data provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The `Lusid.Portfolio.AtoB` provider enables you to write a [Luminesce SQL query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that generates an [A2B report](/v1/docs/creating-an-a2b-report-to-understand-how-valuations-change-over-time) for a LUSID [transaction portfolio](/v1/docs/how-do-i-create-a-transaction-portfolio) to understand how it has changed in value between two points in time.

> **Note:** The LUSID user running the query must have sufficient [access control permissions](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) to both use the provider *and* perform A2B operations in LUSID. This should automatically be the case if you are the domain owner.

**See also:** [Lusid.Portfolio.Valuation](/v1/docs/lusidportfoliovaluation)

## Basic usage

```sql
select * from Lusid.Portfolio.AtoB 
where PortfolioScope = <scope> 
and PortfolioCode = <code> 
and Recipe = <recipe-scope>/<recipe-code>
and FromDate = <date> 
and ToDate = <date>;
```

## Query parameters

`Lusid.Portfolio.AtoB` has parameters that enable you to filter or refine a query.

> **Note:** The `Recipe`, `FromDate` and `ToDate` parameters are mandatory.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Portfolio.AtoB' and FieldType = 'Parameter';
```

## Data fields

By default, `Lusid.Portfolio.AtoB` returns a table of data populated with particular fields (columns). You can return a subset of these fields.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Portfolio.AtoB' and FieldType = 'Column';
```

> **Note:** Fields marked 'main' are returned by queries that start `select ^ from Lusid.Portfolio.AtoB`.

## Examples

> **Note:** For more example Luminesce SQL queries, visit our [Github repo](https://github.com/finbourne/luminesce-examples/tree/master/examples).

### Example 1: Generate an A2B report to understand changes in value to a portfolio during a week

```sql
select *
from Lusid.Portfolio.AtoB
where Recipe = 'FBNUniversity/Module-4-1Recipe' 
and PortfolioCode = 'Module-4-1'
and PortfolioScope = 'FBNUniversity'
and FromDate = #2022-03-07#
and ToDate = #2022-03-11#
```

For the purpose of comparison with the [output of the same A2B operation in LUSID](/v1/docs/valuing-a-multi-asset-multi-region-portfolio#creating-an-a2b-report) where each holding has [one row which can be expanded](/v1/docs/creating-an-a2b-report-to-understand-how-valuations-change-over-time#generating-an-a2b-report-for-the-example-portfolio), Luminesce returns a table of data like this, with two rows per holding:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/f0c5f705-5045-48d1-9e79-8bf7c86677c7.png)

### Example 2: Group holdings and aggregate columns to show one row per holding

You can group the holdings by `LusidInstrumentId&nbsp;`and then `Sum()` ([SQLite aggregate function](https://www.sqlite.org/lang_aggfunc.html#sumunc)) the grouped holdings in the `StartMarketValue`, `EndMarketValue`, `CapGain` and `Carry` columns to return one row per holding instead of two.

For example, aggregating and grouping the query in [Example 1](/v1/docs/lusidportfolioatob#example-1-generate-an-a2b-report-to-understand-changes-in-value-to-a-portfolio-during-a-week). The `Sum()` function parses each of the groups and returns the sum of all non-null values in the group. It then replaces the two values for each holding (one a non-null value, the other null) with the single value returned from `Sum()`:

```sql
select
    LusidInstrumentId,
    Currency,
    Sum(StartMarketValue) as StartMarketValue,
    Sum(EndMarketValue) as EndMarketValue,
    Sum(CapGain) as CapGain,
    Sum(Carry) as Carry
from Lusid.Portfolio.AtoB
    where PortfolioScope = 'FBNUniversity'
    and PortfolioCode = 'Module-4-1'
    and Recipe = 'FBNUniversity/Module-4-1Recipe'
    and FromDate = #2022-03-07#
    and ToDate = #2022-03-11#
group by LusidInstrumentId
;
```

...and returns a table of data like this, with one row per holding instead of two:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/f30ec9d8-652b-4f76-a131-d53ca73480fe.png)

### Example 3: Show the instrument name and type for each holding

We can join `Lusid.Portfolio.AtoB` with other providers to retrieve and include additional useful pieces of data in the A2B report.

For example, joining our query from [Example 2](/v1/docs/lusidportfolioatob#example-2-group-holdings-and-aggregate-columns-to-show-one-row-per-holding) with the `Lusid.Instrument` provider:

```sql
select 
    a.LusidInstrumentId,
    i.DisplayName as InstrumentName,
    i.Type as InstrumentType,
    a.Currency,
    Sum(a.StartMarketValue) as StartMarketValue,
    Sum(a.EndMarketValue) as EndMarketValue,
    Sum(a.CapGain) as CapGain,
    Sum(a.Carry) as Carry
from
    Lusid.Portfolio.AtoB a
    inner join Lusid.Instrument i
        on a.LusidInstrumentId = i.LusidInstrumentId
where
    Recipe = 'FBNUniversity/Module-4-1Recipe'
    and PortfolioCode = 'Module-4-1'
    and PortfolioScope = 'FBNUniversity'
    and FromDate = #2022-03-07#
    and ToDate = #2022-03-11#
group by a.LusidInstrumentId
;
```

...returns a table of data like this, with an instrument name and type to provide more context for each [LUID](/v1/docs/what-is-a-lusid-instrument-id-or-luid).

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/b79b42a7-8e01-4172-8962-5d8e3fc9abea.png)