Report Builder - output table as columns not rows
Discussion
I've a report to write in Report Builder 3.0 , but I've run out of talent and I can't get it to do what I want.
Scenio:
I want to output a table of addresses on a horizontal line based on the persons ID in this format:
Name, 1st address, to, from, 2nd address, to, from, 3rd address, to, from
At the moment I've a table with:
1st Name, 1st address, 01/01/2004,
1st Name, 2nd address, 20/05/2000, 31/12/2003
2nd Name, 1st address, 01/30/2018,
I WANT TO transpose the data across the end of a spreadsheet record so that it is easy to look at previous addresses on a single line and not on multiple lines.
I can do a bodge job with Vlookup, but it is a lot of hassle and it is reliant on my remembering to learn to do it correctly every time after I've stupidly paste specialed the values over the formulae.
I've tried searching how to do it, but I'm not searching for the right thing as I'm sure I'm not the only person with this issue.
Scenio:
I want to output a table of addresses on a horizontal line based on the persons ID in this format:
Name, 1st address, to, from, 2nd address, to, from, 3rd address, to, from
At the moment I've a table with:
1st Name, 1st address, 01/01/2004,
1st Name, 2nd address, 20/05/2000, 31/12/2003
2nd Name, 1st address, 01/30/2018,
I WANT TO transpose the data across the end of a spreadsheet record so that it is easy to look at previous addresses on a single line and not on multiple lines.
I can do a bodge job with Vlookup, but it is a lot of hassle and it is reliant on my remembering to learn to do it correctly every time after I've stupidly paste specialed the values over the formulae.
I've tried searching how to do it, but I'm not searching for the right thing as I'm sure I'm not the only person with this issue.
Edited by thepeoplespal on Wednesday 24th October 10:22
Your are not the only person with this issue, it is basically the fundamental issue of database architecture, and it is not something anyone has been able to solve, atleast not as a single all encompassing solution.
The current approach is to either do as you are doing a basically build they query that does the work but can be slow or require many indexes, or to have a separate data warehouse for online analytics processing, where the data is structured differently perhaps something column based rather than row based.
The current approach is to either do as you are doing a basically build they query that does the work but can be slow or require many indexes, or to have a separate data warehouse for online analytics processing, where the data is structured differently perhaps something column based rather than row based.
How about you count the highest number of address entries that an 'id' has in your query and then create a temporary table that has enough column space to hold the biggest record.
Then populate the temporary table with the data and report from the temp table?
Edit to add:
MySQL has a PIVOT function that allegedly does this, anything similar in Report Build?
Then populate the temporary table with the data and report from the temp table?
Edit to add:
MySQL has a PIVOT function that allegedly does this, anything similar in Report Build?
Edited by JustALooseScrew on Thursday 25th October 12:31
You'd think this would be the bread and butter and be easy, I don't need all the info, all I need is the last two or three addresses, if these are available, when they moved in and when they left, some of the records will have 20+ addresses.
I've got one of my account managers to ask the system makers techies to see if they can write the report or a number of similar reports in Report Builder 3.0 (which is getting on in years) for me, as it is starting to get beyond my skill set. If I've got a basic suite of reports I am pretty good at adapting these into a bigger one, even if it means bodging it with a VLookUp using the unique reference number.
I think report builder 3.0 is very much drag and drop for me, I could do with an intensive course using it, as I have only picked things up by trial and error, a bit of teamwork, a bit of YouTube, as well as having a look at the pre-supplied reports by the consultants.
I've got one of my account managers to ask the system makers techies to see if they can write the report or a number of similar reports in Report Builder 3.0 (which is getting on in years) for me, as it is starting to get beyond my skill set. If I've got a basic suite of reports I am pretty good at adapting these into a bigger one, even if it means bodging it with a VLookUp using the unique reference number.
I think report builder 3.0 is very much drag and drop for me, I could do with an intensive course using it, as I have only picked things up by trial and error, a bit of teamwork, a bit of YouTube, as well as having a look at the pre-supplied reports by the consultants.
thepeoplespal said:
You'd think this would be the bread and butter and be easy, I don't need all the info, all I need is the last two or three addresses, if these are available, when they moved in and when they left, some of the records will have 20+ addresses.
I've got one of my account managers to ask the system makers techies to see if they can write the report or a number of similar reports in Report Builder 3.0 (which is getting on in years) for me, as it is starting to get beyond my skill set. If I've got a basic suite of reports I am pretty good at adapting these into a bigger one, even if it means bodging it with a VLookUp using the unique reference number.
I think report builder 3.0 is very much drag and drop for me, I could do with an intensive course using it, as I have only picked things up by trial and error, a bit of teamwork, a bit of YouTube, as well as having a look at the pre-supplied reports by the consultants.
I've been using Report Builder 3.0 for one of our databases for the last couple of years - and whilst old it is not a very mature report building package (not a patch on Cognos, Business Objects, Crystal or Tableaux). MS have invested more into Excel's business intelligence packages and I'd be tempted to use power pivot in Excel to do what you require (it can be pointed at your data source) .I've got one of my account managers to ask the system makers techies to see if they can write the report or a number of similar reports in Report Builder 3.0 (which is getting on in years) for me, as it is starting to get beyond my skill set. If I've got a basic suite of reports I am pretty good at adapting these into a bigger one, even if it means bodging it with a VLookUp using the unique reference number.
I think report builder 3.0 is very much drag and drop for me, I could do with an intensive course using it, as I have only picked things up by trial and error, a bit of teamwork, a bit of YouTube, as well as having a look at the pre-supplied reports by the consultants.
Without knowing the structure of your data it's a little difficult to visualize how report builder can display the report they way you want but I suspect you may need to create a few more "calculated fields" for first and latest addresses and then use a bit of boolean filtering to mask the individual column outputs.
Good luck.
Thanks for the suggestions, I might not be able to do it, but there are a few paid project days left to use up, so I'll just have to make sure we get the one who knows his onions to do this.
Not overly keen on Report Builder 3.0, too easy to mess things up, but I'm happy enough to tinker.
Today I've been busy chasing workflows that have damaged the underlying data today. 8 months of data cleansing and my reports are starting to give the answers I know are very close to being right. I really need an idiot's guide to SQL and our underlying database structure.
Not overly keen on Report Builder 3.0, too easy to mess things up, but I'm happy enough to tinker.
Today I've been busy chasing workflows that have damaged the underlying data today. 8 months of data cleansing and my reports are starting to give the answers I know are very close to being right. I really need an idiot's guide to SQL and our underlying database structure.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


