nerdexam
Microsoft

70-466 · Question #151

A multinational retailer has retail locations on several continents. A single SQL Server Reporting Services (SSRS) instance is used for global reporting. A SQL Server Analysis Services (SSAS) instance

The correct answer is C. ="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=RetailSales". SSRS dynamic connection strings for SSAS must use 'Data Source=' syntax and reference report parameters via the VB expression syntax using Parameters!ParameterName.Value.

Build reports

Question

A multinational retailer has retail locations on several continents. A single SQL Server Reporting Services (SSRS) instance is used for global reporting. A SQL Server Analysis Services (SSAS) instance for each continent hosts a multidimensional database named RetailSales. Each RetailSales database stores data only for the continent in which it resides. All of the SSAS instances are configured identically. The cube names and objects are identical. Reports must meet the following requirements:
  • A report parameter named ServerName must be defined in each report. When running a report, users must be prompted to select a server instance.
  • The report data source must use the Microsoft SQL Server Analysis Services data source type. You need to create a data source to meet the requirements. How should you define the expression that is assigned to the connection string property of the data source?

Options

  • A="Server=" & Parameters!ServerName.Value & ";Initial Catalog=RetailSales"
  • B="Data Source=@ServerName;Initial Catalog=RetailSales"
  • C="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=RetailSales"
  • D="Server=" & Parameters!ServerName.Value
  • E="Server=@ServerName;Initial Catalog=RetailSales"

How the community answered

(53 responses)
  • A
    2% (1)
  • B
    4% (2)
  • C
    75% (40)
  • D
    13% (7)
  • E
    6% (3)

Why each option

SSRS dynamic connection strings for SSAS must use 'Data Source=' syntax and reference report parameters via the VB expression syntax using Parameters!ParameterName.Value.

A="Server=" & Parameters!ServerName.Value & ";Initial Catalog=RetailSales"

This uses 'Server=' which is the SQL Server relational engine connection string keyword; SSAS connections require 'Data Source=' instead.

B="Data Source=@ServerName;Initial Catalog=RetailSales"

'@ServerName' is T-SQL parameter notation and is not recognized by the SSRS expression engine as a report parameter reference - it would be treated as a literal string.

C="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=RetailSales"Correct

The SSAS data source type requires 'Data Source=' as the connection string keyword, not 'Server='. SSRS expressions that embed report parameters must use the concatenation operator (&) with Parameters!ServerName.Value to dynamically inject the selected server at runtime, making the connection string valid and fully parameterized.

D="Server=" & Parameters!ServerName.Value

This uses the incorrect 'Server=' keyword for SSAS and also omits the required 'Initial Catalog=RetailSales' portion needed to specify the database.

E="Server=@ServerName;Initial Catalog=RetailSales"

'@ServerName' is not valid SSRS expression syntax for referencing a report parameter, and it also uses the incorrect 'Server=' keyword for an SSAS connection string.

Concept tested: SSRS dynamic SSAS connection string with report parameters

Source: https://learn.microsoft.com/en-us/sql/reporting-services/report-data/analysis-services-connection-type-for-mdx-ssrs

Topics

#SSRS#dynamic connection string#report parameters#data source expression

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice