[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Multiple datasets in report

Posted on 2012-09-20
3
Medium Priority
?
954 Views
Last Modified: 2012-09-21
Hello,

 I have developed a report model from which I have created a report.  The report contains two tables/tablices/tablix each pointing to it's own dataset.  The fields that make up both datasets are taken from the model.

I am using the same fieldnames in each dataset.

I want to show different results in each tablx e.g

dataset 1:  select * from country where salesteam='Active

dataset 2: select * from country where salesteam='Inactive'


Each dataset has a prompt parameter for address

My problem is that when I'm prompted to enter the address to search by ,  the second dataset ignores the search criteria and brings back everything.  Can anyone suggest how to go about this?  Can the two tables use the same dataset but using expression to limit the result e.g iif(field!status.value='Active',field!status.value,"") in the first table and iif(field!status.value='InActive',field!status.value,"") in the second table
0
Comment
Question by:turaks
3 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 750 total points
ID: 38419976
You can use the expressions or even do it right in your sql query:

select *, case when status = 'Inactive' then '' as FormattedStatus end from country where salesteam = 'active'


Also there is a row filter on the Tablix so that you can set your row filter to just pull back what you want from the same dataset.

http://www.mssqltips.com/sqlservertip/2597/dataset-and-tablix-filtering-in-sql-server-reporting-services/
0
 
LVL 2

Assisted Solution

by:Brainfeb
Brainfeb earned 750 total points
ID: 38420847
Step 1) : Create a  Dataset ‘Country’ with the Query ‘Select * from Country’.

Step 2) : Set the Dataset Name= ‘Country’  tables/tablices/tablix (Data region) Which you created.

Step 3) : Go to the FIRST  table and took the  tablix properties and set  salesteam =  Active  in the filer tab.

Step 4) : Go to the SECOND  table and took the  tablix properties and set  salesteam =  Inactive in the filer tab.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38420979
You can do it by different dataset.


Create 2 different data set for Active and Inactive.

Put 2 Matrix and give different Dataset name to property of matrix.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 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