Anyone creating reports outside of EspoCRM? What are you using? PowerBI? Excel?

[crmclients]

Cloud version, only Formula abilities:

One of my current clients, in addition to needing a merged Leads & Opps report I posted about earlier, now has vendor needing similar reports with total (not grid reports) but a list of 4-5 columns and then a total for each at the bottom, plus a list with certain statues, etc.

What are you using for reports outside of EspoCRM and how are you doing it?

[crmclients]

update found this https://github.com/rodekruis/EspoCRM...owerbi-via-api

connected successfully and can see the Leads in transform - fyi its not /api/v1/Leads but /api/v1/Lead (no 's')

[crmclients]

Update: unable to get the pagination to work so 200 lead limit

[crmclients]

Ok after a few hours trial and error this worked to load over 1000 leads
Uing PowerBI free desktop version
Espo Cloud version

1. go to Transform Data
2. click New Source
3. Web
4. Select Advanced
5. URL parts - enter https://esocrmwebsitegoeshere/api/v1/Lead
6. HTTP Request - enter X-API-Key then paste apikey
7. Ok
8. Right click the let side under Queries or click New Source again and click Blank Query
9. Query 1
10. Rename the query to getRecords
11. while highlited click on Advanced Editor or right click
12. delete the starter text
13. paste this below
14. update to your API key
15. save

(offset) =>
let
URL = "https://fahl1.espocloud.com/api/v1",
path = "Lead?maxSize=200&offset=" & Number.ToText(offset),
Source = Json.Document(Web.Contents(URL, [
Headers=[
#content-type = "application/json",
#"X-Api-Key" = "****---your---api---key---here----****"
],
RelativePath=path
])),
records = Source[list],
#"Converted to table" = Table.FromRecords(records)
in
#"Converted to table

1. Create another, click New Source again and click Blank Query
2. rename to Leads
3. while highlited click on Advanced Editor or right click
4. paste this and save

note:PBI will update this query after your leads load with all the fields

let
Source = List.Generate(
() => [ offset = 0, records = getRecords( 0 ) ],
each Table.RowCount([records]) > 0,
each [offset = [offset] + 200, records = getRecords( [offset] ) ],
each [records]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", Table.ColumnNames(getRecords(0)))
in
#"Expanded Column1"

1. PowerBi should load your Leads, it might take a while
2. Click Close & Apply to start building reports and charts
3. Your date fields should have Month Year Quarter Day, you need to find the code for Week
4. You might need to update the Data Type for datae fields to Date/Time (Date give an error, just undo if that happens)

I need to add Opportunites and then find a way to connect the Lead and Opp as the customer wants to see it's journey from birth to funding or loss
Feel free to ask questions although I'm probably the only one that needed more explanation than the guide. Just had trouble getting passed the crediential errors. The "records > 0" seem to do the trick.

Happy Connecting!