June 23, 2008 at 7:11 am
Hi I'm a novice user of SSRS. I really really need some help in solving the following problem for which I'm completely stumped.
Performing the same action in excel is relatively easy, however not so easy perhaps here.
My data is basically as follows, this shows sales between two companies (one who is the supplier the other the buyer).
Organization ID | Sales ID | Product Costs | Deposit |
2 | 31 | £100 | £20 |
3 | 31 | £100 | £20 |
71 | 44 | £150 | £25 |
52 | 44 | £150 | £25 |
2 | 51 | £100 | £20 |
8 | 51 | £130 | £20 |
I want to show the values for Product Costs & Deposit only once for each Sales ID. So that my data will appear as follows:
Organization ID | Sales ID | Product Costs | Deposit |
2 | 31 | £100 | £20 |
3 | 31 | | |
71 | 44 | £150 | £25 |
52 | 44 | | |
2 | 51 | £130 | £20 |
8 | 51 | | |
Can anyone tell me what expression i should use to do this?
June 23, 2008 at 9:00 am
It really depends on how you want to present your data in the report, but one method I can give you is the following:
Create a table for the report.
Create a group in the table, and the group expression should be the sales id.
Place the sales id, product cost and deposit in columns in the group header.
Place the organisation id (the detail) into the detail line.
Print the report.
This is just one of a number of ways that you can approach this problem, other things also need to be considered, such as.....do you want totals for those values.
Anyway, let me know if you need more help.
Nigel West
UK
June 23, 2008 at 10:09 am
Thanks so much for getting back to me!
The steps you've given work well, but it isn't quite the format I have been asked to put this in.
Following these steps I get:
Organization ID |Sales ID |Product Costs |Deposit |
|31 |£100 |£20 |
2 | | | |
3 | | | |
|44 |£150 |£25 |
71 | | | |
52 | | | |
|51 |£130 | |
2 | | |£20 |
8 | | | |
Would you be able to provide steps to produce the layout of this data as in my original posting, rather than dropping the Sales ID field etc into the Header?
Many thanks for any advice you can provide!
June 23, 2008 at 1:28 pm
OK, so the way to do this is as follows:
1. move the data down to the detail level so that nothing is printed at the group header level.
2. you can either remove the group header, or leave it, if you leave it then it will break up the various sales ids with some space (your choice).
NOTE: The group you created must have a name, so I am assuming you left it as default which would be table1_group1, but you might want to rename this to something that makes more sense to you (e.g. table1_salesid).
3. on each of the fields where you do not want duplicates, click on the field and go to the properties area, you will find an item there called hide duplicates, this gives you a drop down list and from this you should select your group name.
This should do it for you.
Good luck,
Nigel West
UK
June 24, 2008 at 2:39 am
Just what I wanted! This is great thank you for your help in resolving my question! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply