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

Andy

Hello Andy,

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 PersonCityMoney
pivot (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 Leipzig
from (
   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 PersonMoney
group 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.

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!)

Thanks!