nerdexam
Microsoft

70-466 · Question #110

A large manufacturing company has manufacturing plants in many states. Each state has a dedicated SQL Server instance that stores its manufacturing data. Each SQL Server instance is configured identic

The correct answer is C. Use a report-specific data source that defines an expression-based connection string based on the state parameter.. SSRS supports expression-based connection strings only in embedded report-specific data sources, enabling a single report to dynamically connect to different SQL Server instances based on a state parameter.

Implement a reporting services solution

Question

A large manufacturing company has manufacturing plants in many states. Each state has a dedicated SQL Server instance that stores its manufacturing data. Each SQL Server instance is configured identically and all instances have identical database structures. You are developing a daily report that summarizes information on manufacturing activity. The report has the following requirements: - It must have a state name as one of the report parameters. - It must provide a daily summary of manufacturing activity of a selected state. - It must require minimal development and maintenance effort. You need to develop the report to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Exhibit

70-466 question #110 exhibit

Options

  • AUse a shared data source that defines an expression-based connection string based on the state parameter
  • BBuild a centralized data repository, schedule a regular Extract, Transform, and Load (ETL) process on all manufacturing data, and then use the repository to generate the report.
  • CUse a report-specific data source that defines an expression-based connection string based on the state parameter.
  • DBuild one report for each state and instruct users to execute reports as needed.

How the community answered

(45 responses)
  • A
    11% (5)
  • B
    7% (3)
  • C
    80% (36)
  • D
    2% (1)

Why each option

SSRS supports expression-based connection strings only in embedded report-specific data sources, enabling a single report to dynamically connect to different SQL Server instances based on a state parameter.

AUse a shared data source that defines an expression-based connection string based on the state parameter

SSRS shared data sources do not support expression-based connection strings - the connection string in a shared data source must be static, so this approach cannot dynamically target different SQL Server instances based on a parameter value.

BBuild a centralized data repository, schedule a regular Extract, Transform, and Load (ETL) process on all manufacturing data, and then use the repository to generate the report.

Building a centralized data repository with a scheduled ETL process requires substantially more development and ongoing maintenance effort, directly violating the minimal development and maintenance requirement.

CUse a report-specific data source that defines an expression-based connection string based on the state parameter.Correct

An embedded report-specific data source in SSRS supports expression-based connection strings, allowing the connection string to be dynamically constructed from the state parameter value to target the correct SQL Server instance at runtime. Because all instances share identical database structures, a single report serves all states without additional development, minimizing both development and ongoing maintenance effort.

DBuild one report for each state and instruct users to execute reports as needed.

Creating one report per state multiplies development and maintenance effort by the number of states and does not scale when new plants are added, contradicting the minimal effort requirement.

Concept tested: SSRS expression-based connection string with embedded data source

Source: https://learn.microsoft.com/en-us/sql/reporting-services/report-data/specify-credential-and-connection-information-for-report-data-sources

Topics

#expression-based connection string#report data source#report parameters#dynamic connection

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice