Prepare Excel data for Reporting Services mobile reports
Note
SQL Server Mobile Report Publisher is deprecated for all releases of SQL Server Reporting Services after SQL Server Reporting Services 2019. It's discontinued starting in SQL Server Reporting Services 2022 and Power BI Report Server.
Here are some things to keep in mind when preparing an Excel file and worksheets for use with a mobile report:
Do
- Have one worksheet per dataset.
- Have column headers in the first row.
- Keep data types consistent within each column.
- Format cells as proper types in Excel.
- Have the data in worksheets, not in the Data Model in Excel.
- When using formulas, ensure that the entire column is calculated using the same formula.
- Use Excel 2007 or later.
- Save Excel files with the extension XLSX.
Don't
- Include images, graphs, PivotTables, or other embedded objects in dataset worksheets.
- Include total or calculated rows.
- Keep the file open in Excel when importing.
- Format numbers manually by adding currency or other symbols.
- Use a workbook with data stored in the Data Model.
Worksheets
When preparing an Excel file as a dataset for a mobile report, make sure you have only one dataset per worksheet. Each individual worksheet is imported into the Mobile Report Publisher as a separate table. Identically named worksheets from multiple Excel sources are renamed upon importing by appending incrementing numbers. For example if a workbook has three worksheets entitled "MyWorksheet." the second and third are renamed "MyWorksheet0" and "MyWorksheet1." The following screenshot illustrates the first few rows of an ideal Excel worksheet ready for import.
Column headers
As you can see in the previous example, the first row contains the name of the metric in that column. Mobile Report Publisher preserves these column headers for easy reference in gallery element settings. Column headers aren't required, however. If missing, Mobile Report Publisher generates headings using the Excel A,B,C,...,AA,BB,... convention.
Mobile Report Publisher automatically detects first-row headers when importing Excel worksheets by comparing the data types of the first two cells in each column. If the data types of the first two cells in any column don't match, the first row is determined to contain column headers. Thus, if a table has numeric column headers, prefix the header names with a string so they're detected as headers in the import process.
Cells
Cell data in each column of a worksheet dataset needs to be consistent. Each column is assigned a data type upon importing. Mobile Report Publisher automatically detects data types as string, double (numeric), boolean (true/false), or datetime. Mixed data types in the same column can cause this detection to be inaccurate or fail completely. This detection accounts for possible column headers being of string type. Cells should be formatted as the correct type in Excel to ensure that the Mobile Report Publisher detects the desired types. In the previous example the six columns would be typed as:
- A datetime column
- A string column
- Four double columns
If a worksheet contains calculated cells or formulas, only the resulting display value is imported into the Mobile Report Publisher.
File location and refresh Excel data
There are no restrictions on where you store Excel files you import into the Mobile Report Publisher. However, if you move or rename the file after importing, you can't refresh that data through the refresh all data command found in the Data View.
Note
Mobile Report Publisher doesn't automatically refresh Excel data. You can refresh the data through the Mobile Report Publisher refresh command, but only if the file hasn't moved.
Dates
Date fields are essential to many mobile reports, so make cells are properly formatted as dates in Excel. In some cases, a conversion is necessary. Here are examples of formulas for converting cells from text to dates in Excel.
Week 24-2013=DATE(MID(A2,9,4),1,-2)-WEEKDAY(DATE(MID(A2,9,4),1,3))+MID(A2,6,2)*7
2013/03/21=DATEVALUE(A1)
2013-mar-12=DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4))
After you convert the cells, you have to format them as dates by selecting them, or the entire column. Then, open the Context menu, choose Format Cells, and choose Date from the Category list. You can also use the Excel text-to-columns wizard to convert text cells to properly formatted dates.
Unsupported
Worksheet data in formats other than those formats previously described could cause unpredictable results when imported. It's a good idea to restrict worksheets in an Excel file to only those worksheets that are in the correct format for use with a mobile report.
Custom objects in Excel worksheets, including PivotTables, visualizations, and images, aren't imported into the Mobile Report Publisher.