Report Builder - output table as columns not rows
Report Builder - output table as columns not rows
Author
Discussion

thepeoplespal

Original Poster:

1,694 posts

308 months

Wednesday 24th October 2018
quotequote all
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.

Edited by thepeoplespal on Wednesday 24th October 10:22

ging84

9,548 posts

177 months

Wednesday 24th October 2018
quotequote all
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.

JustALooseScrew

1,154 posts

98 months

Thursday 25th October 2018
quotequote all
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?

Edited by JustALooseScrew on Thursday 25th October 12:31

thepeoplespal

Original Poster:

1,694 posts

308 months

Thursday 25th October 2018
quotequote all
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.

Jinx

12,023 posts

291 months

Friday 26th October 2018
quotequote all
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) .
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.

ZesPak

26,165 posts

227 months

Friday 26th October 2018
quotequote all
Do you have any way to prepare it in SQL beforehand? Like a view that prepares this?

SQL has a PIVOT function but it's not for the faint hearted.

thepeoplespal

Original Poster:

1,694 posts

308 months

Friday 26th October 2018
quotequote all
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.