Hello again - I'm still in the midst of learning your product.
Today I am trying to create what I know in Excel and Access as a pivot table report. I created a view that has three columns: Top (Containing city names), Left (people), and Total Money (Sum of money where Top and Left intersect) - The query seems fine and creates appropriate totals. I'd like to create an output looking like the following:
Munich Berlin Dresden Leipzig TOTAL
Anna 200 0 300 100 600
Mary 20 20 20 20 80
Brigitte 0 1 0 0 1
Jon 10 4 4 0 18
TOTAL 230 25 324 120 699
I tried pulling in a table but I'm only able to get a two column listed output like
Anna
Munich 200
Dresden 300
Leipzig 100
etc which wastes a lot of paper. Any ideas on how to make a pivot table?
Thanks!
Andy
Hello Andy,
Currently, the number of columns you can display in a table must be known at design time. In this example, it would mean that you know beforehand which cities there are to display. If that’s the case, you can implement this scenario using a table. Then, you can add a summary row at the end of the table where you write expressions for calculating the totals.
For a variable number of columns, the case is not directly supported by Reporting yet. This scenario is in our backlog.
Regards,
Luis
Thanks for your reply Luis!
I've created the table headings of my cities (hard coded them with the names for now). How do I get the name on the left side of the table and what do I put in the cells under each city so that the correct name and correct amount falls under the correct city (keeping in ind that some intersections will be 0)?
(I promise to bug you next on how to get the totals to the right and at the bottom but want to walk before I run first!)
I will assume that you have a table named PersonCityMoney with the columns: Person, City, Money. And you want to display the results for the cities Munich, Berlin, Dresden and Leipzig.
In order to do this, you need to get a result from the database that has exactly that form: for each row you will have the fields Person, Munich, Berlin, Dresden, Leipzig. There is no standard way in SQL to do it, but there could be some simple solutions in some DBMS. If you are using SqlServer, you can create a pivot query like this:
select * from PersonCityMoneypivot (sum (Money) for City in ([Munich],[Berlin],[Dresden],[Leipzig])) as PersonMoney
Other DBMS might have some other type of structure for making queries with pivot tables. If that is not an option, only using SQL will probably lead to a very complex query. Let's give it a try:
select Person, SUM(Munich) as Munich, SUM(Berlin) as Berlin, SUM(Dresden) as Dresden, SUM(Leipzig) as Leipzigfrom ( select Person, Money as Munich, 0 as Berlin, 0 as Dresden, 0 as Leipzig from PersonCityMoney where City = 'Munich' union select Person, 0 as Munich, Money as Berlin, 0 as Dresden, 0 as Leipzig from PersonCityMoney where City = 'Berlin' union select Person, 0 as Munich, 0 as Berlin, Money as Dresden, 0 as Leipzig from PersonCityMoney where City = 'Dresden' union select Person, 0 as Munich, 0 as Berlin, 0 as Dresden, Money as Leipzig from PersonCityMoney where City = 'Leipzig') as PersonMoneygroup by Person
Using one of those SQL queries, you can create a table in Reporting like the one shown in the image. With that table, you’ll get the results you want.
Hello Luis!
Thanks for your help! I had no idea that a pivot table could be created via SQL! Once you put me on that path everything came together! I ended up having to paste in the Pivot table view into my datasource definition and I created a seperate datasource for totals. So all works well!
I'll be in touch again if I run into new questions as I now have to create a report that will run to 4 hierarchy levels.
Best regards