How to specify dataset scope in business intelligence studio

Posted on 2012-08-29
Last Modified: 2012-08-30
I have a report that has multiple datasets and does not have a table or matrix container.  Is there a way to specify scope for an expression so that I can use fields from one of the datasets?
Question by:kpbarem
    LVL 10

    Accepted Solution

    Not exactly sure what you mean by 'scope' but here are some thoughts:

    I believe inorder to add an expression that would read a field from a dataset you need to have a data region (table, matrix, textbox, etc) defined.

    try this:
    1) add textbox
    2) right-click textbox and add expression
    3) Add lookup value for expression picking a dataset
         for example,  =First(Fields!customer_name.Value, "AllCustomersDataSet")

    The expression formula needs to be an aggregate function since resultset *could* return more than 1 row. If you need to define a smaller scope to filter the dataset, then you could create another dataset and modify the Query so that it returns the scope needed.

    For example, Add dataset = 'SelectedCustomer'
    Query= Select customer_name From CustomerTable where customer_name = @Customer

    @Customer is a Parameter field that would be set after the user selects a customer. The expression would use SelectedCustomer dataset and First() function would return only return the selected customer.

    Author Closing Comment

    Thank you this was well put.  I'm not entirely sure I understood the functionality before.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now