How can I create Dynamic Query between two dimension parameters.

How can I create Dynamic Query between two dimension parameters.

• SneakerXZ

  You can set the Report Parameters Default value to come from a query. For an example see the Builds report. When you select a Flavor, for example, the list of values in the Build parameter change accordingly.

  The default value for a parameter can be set (in a Report Server Project) by selecting Report -> Report Parameters (having the Data or Layout view active -- not the Preview view).

  To see the example in the Builds report:

  Download the report:
  Browse to http://<ReportServer>/Reports
  Select the folder with your project name
  Select the Builds report
  Select the Properties Tab -> General
  Under "Report Definition" select "Edit" and save it somewhere

  Create a new Report Server Project and import the Builds Report & Data Sources
  Add TfsReportDS and TfsOlapReportDS (these will not be deployed because they already exist) with same properties as existing sources
  
  Import the Builds Report

  From the Data Tab select Report -> Report Parameters -> BuildParam
  Default Values set to "From query" & Dataset = DefaultBuilds & Value field = ParameterValue

  You can view the DefaultBuilds query by selecting it in the Dataset dropdown directly under the Data tab

  To execute the queries you will have to make additional changes to the report described here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=598358&SiteID=1

  Let me know how it goes.

  
  

• Erik Miller

  Thank you so much for your help. But I still have not created the Dynamic Query parameters .

  I think maybe the Build parameter of the Builds report is filtered by the following MDX in dataset DefaultBuilds:

  NONEMPTYCROSSJOIN
  (
  [Build].[Build].[Build],
  [Measures].[Build Project Count],
  1
  )

  and

  WHERE
  (
  STRTOSET("[Team Project].[Team Project].[" + @Project + "]"),
  STRTOSET(@PlatformParam),
  STRTOSET(@FlavorParam)
  )

  But how can I make the @Run filtered by the MDX I don't know which Measure or Dimension should be the second parameter of the NONEMPTYCROSSJOIN function. I created a dataset, it's MDX are as below:

  NONEMPTYCROSSJOIN
  (
  [Run].[Run].[Run],
  [Measures].[Build Project Count],
  1
  )

  ...

  WHERE
  (
  STRTOSET("[Team Project].[Team Project].[" + @Project + "]"),
  STRTOSET(@PlatformParam),
  STRTOSET(@FlavorParam)
  )
  

  But it does not work. It only can query out all the Run values. :-(

  
  

• c-sharper2005

  I used the other dimension Agent.Machine as the parameter and resolved this problem.

  Thanks again.

  
  
• How can I create Dynamic Query between two dimension parameters.