reports or mailmerge in powerapps

[davetqm]

Hi

Since access web apps are going, we have to migrate our data to SP lists, (the recommended way).

So I see that powerapps can be used to make forms to get the data entry. But how can we make reports?

Is it possible to import word docs or text to make reports in powerapps? If so, we need to format reports in a4 to print with page breaks in the right places.

Or should we use power BI reports for this?

Thanks for advice

Dave 

[v-micsh-msft]

Hi @davetqm,

Could you please explain a bit for what kind of reports that you would like to create?

There are also Charts available in PowerApps:

Show data in a line, or Bar Chart in PowerApps

For complex Reports, you may consider switch to Power BI.

Currently it is not available for PowerApps to import Word directly.

There is an idea submitted on this already:

Word / Office documents viewer control in PowerApps

Regards,

Michael

[JavoMejia]

@davetqmYou should use PowerBI for complex reporting like @v-micsh-msft says. Other option for light reporting is to use HTML Text. Please read https://powerapps.microsoft.com/en-us/blog/html-email-reporting-with-tabular-data/ 

[davetqm]

Hi Michael
I am trying to make a report like a text document or letter, what we would have previously made in mail merge. In sp lists we have students data. We then have to make letters and data sheets. These are mostly word docs now. Unfortunately you can’t merge from sp lists to word.
How can we generate rich text a4 size letters from sp list data?

[Drrickryp]

There are two possible solutions for printing from Powerapps.  The first is simpler but has some restrictions.

Create a report in Desktop access

1. Open Desktop Access and create a linked table to your sharepoint list. 
2. Highlight the table and create a query that you can use to control which data you want to use in your report, Ie. set the date range, etc.  Save and name the query.
3. Highlight the query. You can then use the Create Report feature to create your report/merge and print it from there. 
4. A potential  problem is that linked tables (as opposed to imported tables) have a 256 character limit on text fields and the printed output will be truncated if it contains more characters.  To avoid this problem, I use the following solution:

MailMerge with Word

1. Create an excel spreadsheet that contains all of your merge fields in the first row.   
2. Make it a table (highlight the first and second rows and click the "Format as Table". 
3. Check the box for "my table has headers". 
4. With the cursor anywhere in your new table, the Design feature will appear in top of the ribbon.
5.  In the far left column on the ribbon bar you can rename it from Table1 if you wish (ie. mymergedata). 
6. Save the spreadsheet in your OneDrive or Dropbox. 
7. Upload the spreadsheet to Powerapps. Select the Table name and Powerapps will add this name as a datasource. This will also add a Powerappsid column to the right side of the spreadsheet table.
8. Create a Collection (ie. mydatacollection) from your data that contains the EXACT names iof the spreadsheet table columns but do not include the Powerappsid as this will be automatically added when you populate the table. This can be a bit complicated if your data resides in several lists but I did it shaping the collection using AddColumns() with Lookups,RenameColumns() to match the datasource fields and ShowColumns() until the collection contained only the fields in the datasource. You can preview it by using the collection as the datasouce for a DataTable control.
9. Add the data to the datasource by using Collect(mymergedata,mydatacollection).
10. The spreadsheet table can then be used by Word as the source for a mailmerge (mailings<Select recipients<use an exisiting list).

I hope this helps. I did this for data stored in CDS but it should work for Sharepoint lists as well. I am not sure if this is the most efficient way to do it but it worked for me.

[quentinfr]

Hello, does it works with a picture?

[davetqm]

Hi Drrickryp

Thanks, the mailmerge is what I need.

in your example with excel, in the last step. How do you connect a word mailmerge to an excel document in power bi? I can only see an option for browsing for a local file.

[Drrickryp]

I don't use birthday for this. Use the excel file as the source for the mailmerge in word. There are many examples of how to use mailmerge in MS word. I hope this helps.

[Drrickryp]

Bi not birthday. Darn spellchecker.