Edit

Prepare data for Reporting Services mobile reports

  • Article

Note

SQL Server Mobile Report Publisher is deprecated for all releases of SQL Server Reporting Services after SQL Server Reporting Services 2019. It is discontinued starting in SQL Server Reporting Services 2022 and Power BI Report Server.

SQL Server Mobile Report Publisher supports many complex data operations, including filtering, aggregation, and time slicing. This article offers some points to keep in mind while preparing data. Preaggregating data can optimize both mobile report creation and use, and some mobile report designs require it.

Date and time formats

When you deal with date and time intervals for use in a mobile report, particularly with the TimeNavigator, it's important to format the date/time column properly so the Mobile Report Publisher can identify it as such. Here are examples of valid date/time formats:

05/01/2009
2009-05-01
05/01/2009 14:57:32.8
2009-05-01 14:57:32.8
2009-05-01T14:57:32.8375298-04:00
5/01/2008 14:57:32.80 -07:00
1 May 2008 2:57:32.8 PM
Fri, 15 May 2009 20:10:57 GMT

One or more date/time intervals, such as hourly, daily, monthly, quarterly, and yearly, can describe date- and time-based datasets. Mobile Report Publisher can combine multiple tables of different granularity and display them on a single mobile report. However, keep in mind the relevant intervals from the original dataset(s), as they can help when deciding what date/time filter options to present to the user in the final mobile report.

Date fields in Analysis Services multidimensional and tabular models can lose their date formatting in shared datasets. See Retain date formatting for Analysis Services in mobile reports for a solution that keeps their formatting.

Prepare filter data

Mobile Report Publisher can filter data based on both date/time fields and key fields. Though key fields can be numeric, in most cases they're either an ID or a string value. To prepare a filter field for use with a navigator element such as the Selection List, the filter key should be a single column in the data table. That way, you can group the table rows according to the value in the filter column. Having multiple columns contain different filter keys, or filter criteria, allows for mobile reports with multiple filter navigators to be used together hierarchically or individually.

Industry Location Region
Banks AFGHANISTAN ASIA
Commercial & Professional Services AFGHANISTAN ASIA
Food, Beverage & Tobacco AFGHANISTAN ASIA
Media AFGHANISTAN ASIA
Pharmaceuticals AFGHANISTAN ASIA
Food & Staples Retailing ALBANIA EUROPE

Key-based filters might also be hierarchically structured for use with a Selection List in a tree structure. To prepare data for use in this type of scenario, create a look-up table describing the hierarchical structure. Use a table structure that includes a Key column and a Parent Key column to indicate where a node belongs in the overall hierarchy.

In this table, the ParentKey items are first listed in the ItemKey column, then listed in the ParentItemKey column next to their child items.

ItemKey ParentItemKey
Financials
Industrials
Consumer Staples
Consumer Discretionary
Health Care
Information Technology
Banks Financials
Real Estate Financials
Diversified Financials Financials
Insurance Financials
Commercial & Professional Services Industrials
Capital Goods Industrials
Transportation Industrials
Food, Beverage & Tobacco Consumer Staples
Food & Staples Retailing Consumer Staples
Household & Personal Products Consumer Staples
Media Consumer Discretionary
Automobiles and Components Consumer Discretionary
Consumer Durables and Apparel Consumer Discretionary
Consumer Services Consumer Discretionary
Retailing Consumer Discretionary
Pharmaceuticals Health Care
Health Care Equipment & Services Health Care
Software & Services Information Technology
Technology Hardware & Equipment Information Technology
Telecommunication Services Information Technology

Related content