February 18, 2016 at 3:36 pm
In an new ssrs 2008 report, I need to come up with a way(s) to uniquely define how data will appear on the different parts of the report. The only thing that the two part of the report will have in common
is the customer name and customer number.
The data for the first report will look like the following and will need to be grouped by cso_date:
Customer number customer name cso_date item_number
45 customer1 12/03/2001 16
45 customer1 9/12/2010 15
45 customer1 9/12/2010 24
45 customer1 9/12/2010 82
45 customer1 11/03/2010 07
45 customer1 11/03/2010 16
Table defintions
Customer number int,
customer name varchar(30),
cso_date datetime,
item_number int
The data for the second report will look like the following and will need to be grouped by attend_date:
Customer number customer name attend_date Course Number
45 customer1 11/03/2011 2256
45 customer1 9/12/2012 1803
45 customer1 9/12/2012 5689
45 customer1 9/12/2012 1288
45 customer1 1/03/2013 1288
45 customer1 11/03/2013 1803
Table defintions
Customer number int,
customer name varchar(30),
attend_date datetime,
Course Number int
The t-sql that I write, I keep getting all the same data in all the same rows. I want data that is
unique for report 1 versus report 2.
Thus can you tell me how to do the following:
1. Write the t-sql so that I can uniquely define rows for report #1 versus the rows for report #2?
2. In the ssrs 2008 report, would you tell me how to uniquely identify the parts of the report that are just for that section of the report?
February 19, 2016 at 2:55 am
What does your t-sql look like?
You should just be able to write one piece of t-sql and add this as a data set
Then parameterise (so customer name or number are specified)
Filter the data set based upon this
Then add 2 separate report items (could be table or matrix or chart or ...) that are both based upon the dataset
Now just manipulate each report (group etc.) accordingly
You can also apply different filters to each report if that's what you require
Is that what you are after?
Damian
- Damian
February 19, 2016 at 8:30 am
I am told all unique reports are separate matrixes in the same rdl. I am going to have a parameter where you can select which report I want. However all the data will be obtained in one group sql. I am trying to figure out how to setup the sql so I can determine what data rows are for which report. I am also thinking that I would need a filter on a grouping to determine what data is to be selected. Can you tell me how to accomplish this goal?
February 21, 2016 at 6:48 pm
you can only filter a dataset as a whole, not just a matrix or whatever where you use the dataset. Unless there's something I've been overlooking all this time.
February 22, 2016 at 4:09 pm
Can't each embedded tablix filter the rows they need from the dataset by using a column called report_number? The value in each column will be for each embedded tablix.
February 23, 2016 at 2:38 am
I have reports that use a single dataset
These comprise of multiple small number of tables and charts to create a dashboard
Each part can, if necessary have it's own filter through chart or tablix properties
e.g. A chart for new business (for me, new business is a customer type and is applied as a filter) or a table for existing business etc.
So, same dataset, different filters
- Damian
February 23, 2016 at 8:11 am
what objects you place the filter on? I am assuming you are using the filter on the tablix and/or dataset. Can you show me and/or show me the places you are using the filter(s)? Also would you tell me why you chose to use the filter(s) on those objects?
February 23, 2016 at 9:13 am
wendy elizabeth (2/22/2016)
Can't each embedded tablix filter the rows they need from the dataset by using a column called report_number? The value in each column will be for each embedded tablix.
That is one way to do it.
It would be helpful to see the T-SQL you're using to get your dataset - is it a union or a number of different fields?
Assuming you go in the direction you suggested (adding a report_number field), create a parameter (e.g @ReportNo), then in tablix properties add a filter '[report_number]' as the expression and '=Parameters!ReportNo.Value' for the value.
Is that what you were after?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply