Hello, I have a summary report which has a pramater Element Hierarchy and the data source is cube. User can select multiple accounts from the account hierarchy parameter and report gives a result as a total of all these accounts in one page. The requirement is, when user selects multiple accounts, the report should not be grouped and it should display the results each account per page. The data source is cube. Please let me know how we can accomplish this? Thanks.

It sounds like you need to group your table or matrix on the account field. You can set a page break on the group to display one account per page. If this answer is not complete enough to answer your question, you will need to provide more information about your query and report structure.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Paul...thanks for the response. In my report there are multiple data sets and used in multiple tables. Also the report is using the cube as data source. This is very easy if i have one table in a report and if I am using the transaction db as data source. Somehow I am not able to make it working in my this report. Any suggestion would be helpful in this regard.

I understand that you have multiple datasets providing data for multiple tables but I don't have enough information to help. A data region (table, matrix, list, etc.) can only be bound to one dataset. Let's focus on the table and dataset in question. What fields does the dataset return? How is the table grouped?Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Paul...the table dataset returns the Account Hiererchy and Amount and the table is gruped using Account Hierarchy. I have a parameter in my report from a different dimension called Account Element Hierarchy. I want to group the data based on Account Element Hierarchy selection. (if two values selected in the paramater drop down then two pages should display each element per page). When I use the list, put the table inside the list and group the list based on Account Element Hierarchy, I get an error message "report item expressions can only refer to fields within the current data set scope"

When you nest data regions (in this case, a table within a list), both of the regions must be bound to the same dataset. If it's feasible, it would be best to use a single dataset to achieve this. If not, you would have to use a subreport with correlated parameters - which is often not the best option if you can avoid it. See if you can build a single query to return all the necessary members and then you can either use a single table or a table within a list region. I'm sorry but it's 2:30 AM my time and I need to retire. I will check this thread again tomorrow.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Paul...I tried using sub report but still no luck. Here is what I am doing... 1. Create a rdl file and used the sub report section inside this...in the sub report I am calling my main report . 2. my requirement is if I select two two values from the paramter, I should get 2 pages (one page per parameter) 3. I am using a list box and putting the sub report inside this...I tried using the Split function in the group expression, so that if two values are selected, it should be passed one parameter value at a time. but not working and getting error... Any idea on how we can make this working. These things are very simple when I use the transactional db as a data sourec, become very complex with cube and mdx.

I'll assume that for some good reason you have to use two datasets. Again, try to avoid this if possible. You shouldn't have to use the Split function. Just group the List data region on the same field that your parameter filters/slices on. If the user selects threee accounts and the list is grouped on the Account field, you should see three group instances of the List. It really should be that simple. If this doesn't work, please post your query script.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com