Apologies for my lack of technical knowledge about BO but we are at a critical juncture with a BO development project and this element is critical.
Basically we have 3 universes relating to our library service, all with transaction data. The first lists all the loans made, the second has all the computer sessions listed, and the third shows all the eformat loans made. I want to add these all together and have a report as below, so all the records from the three universes would be listed out under three common headings below. If I was doing this in Excel I’d be copying and pasting each dataset underneath each other.
Customer ID | Date of transaction | Library used
Is this possible? Everything I’ve seen shows datasets being merged, but this is not what I want to do, I want all the unique transactions in a long list from which I can build a picture of how our libraries are used.
I currently use crystal reports for one of the databases so starting to wonder if this might handle combining datasets into one a bit better?
It seems to me you want to have a table corresponding to separate queries on each of your three universes. So you just create three tables and set the layout in the format table properties of the second table to be relative to the first and the third table relative to the second.
Here is an example of data from two queries based on the eFashion universe. Even though they are based on the same universe they could just as well be based on two universes.
One thing to watch out for in a scenario like this is that if you are exporting to Excel and your columns between your tables are not the same width you may get some unexpected merging of cells. You can take care of that by explicitly setting the columns’ width so that the first column in each table is the same, the width of the second column in each table is the same, and so on.
Alternatively, you may also want to consider creating a tab (technically referred to as a report in Web Intelligence) for the data from each of your queries.
That is exactly how I would want the three datasets to look - however I then want to be able to interrogate this whole combined dataset as one report - we currently make do in access/excel but there are over 1.5m records so it’s unworkable. This is why we want to bring all three datasets together in BO.
If I have the layout as you have included above, can I do a report/query in Web Intelligence that will bring together the three datasets?
Can you mock up a sample in Excel of how you want it to look? How do you want to interact with the results? What is your objective without consideration of what tool you are using?
What I am trying to get at is that perhaps Web Intelligence is not the best tool for this task. Maybe it is, but you need to use it in a different way than you have been considering to this point.
Thank you again. Hopefully you can see this image which shows all the transactions listed, so the three datasets on different systems are loans, computer and eformat, this is all the transactions done across all the libraries by all our customers. We have the same fields in each database (ID, date and BranchUsed).
The 4th column would need to be some sort of calculated field according to the database it came from, which I assume would be doable (or I could just add a field to each of the three databases and bring the fields in with the other data.
I am starting to think the work I do in Access and then Excel might not be the craziest way after all, I was just really hoping that we could do this in BO to streamline and speed up our reporting systems.
I would then add other fields, so for example demographic data, to gain insight into who is using our libraries, what their habits are, do a large % borrow books as well as use computers, do eformat users also borrow physical books. All of this I currently do in Excel, but with 1.5m records and 100k unique customers it is hard work!
If sort order of the output isn’t important in Webi itself, you could create three identical tables, one from each data source. Use relative positioning to align tables 2 and 3 below 1 and 2 respectively, then hide the headers for tables 2 ands 3 so it looks like one large table. It will behave like one if you export the result to Excel and you can provide your final formatting there.
In terms of your “source” column, you could simply then add whatever text you want in that column for each source.
In general terms, this would be a situation where I believe a SQL UNION would work. However, this is not possible in WebI universe based query if the sources of the individual queries are different universes. I believe this may be possible in 4.3 SP3 and higher, but I cannot speak to that since we are on 4.3 SP02 Patch 8.
Are your databases for each universe perhaps on the same database server or at least reachable from one another? We use SQL Server in which our DBAs can create linked servers such that I can reach tables in Databases B and C when connected to Database A. If such a scenario is possible you could create a free-hand SQL (FHSQL) query unioning the results together from each database.
If that sounds too complicated or is just not possible, then I think @MarkP’s suggestion is your best bet.
Thank you Mark and Noel, really appreciate you taking the time to respond. I will take these suggestions back to my colleagues, but the Union option does seem very exciting. I’m guessing my organisation won’t have anything near the best spec of WebI but it’s worth an ask and exploration of your suggestion.
My solution will work from 6.5 (2005 onwards) at least, so you will be able to achieve it. If they’re struggling, key things to note: make sure all columns are fixed width and the same width in each table. Position the table relative to the bottom and left of the table above. On the bottom two tables, in table properties, hide the header once you’ve achieved the alignment. On all three tables, hide the footer. Good luck.
