Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a report to list totals from a table that doesn't require a re-design whenever a new field is added Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 5:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:06 AM
Points: 247, Visits: 703
I need to write a report which will provide totals (expressed as an overall percentage) for each subsequent field after field 1 in a SQL table grouped by the first field. It needs to cater for additional fields being added to the table without the report having to be re-designed and having extra columns added to the table. How would I go about this please if it's possible? I guess it would be basically mirroring the functionality of a matrix report but I'm not sure how to go about building it.
Post #1484698
Posted Friday, August 16, 2013 6:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 243, Visits: 2,735
I am not exactly sure what you are trying to do, but using a running value function may help:

http://technet.microsoft.com/en-us/library/dd255229.aspx

If you are just getting the percentage of the column or row total, then you can use an aggregrate function like SUM to get the percent. The expression would look something similar to:

=SUM(Fields!ID.Value)/SUM(Fields!ID.Value,"group_name")
Post #1485152
Posted Friday, August 16, 2013 7:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 610, Visits: 2,139
I think I see what you're getting at. You could create a temp table with all the column names in it, using a select from sys.columns. Then update that table with the column counts that you want. From there it's a select from the temp table and build your report as usual.
Post #1485219
Posted Monday, August 19, 2013 4:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:06 AM
Points: 247, Visits: 703
Steven.Howes (8/16/2013)
I think I see what you're getting at. You could create a temp table with all the column names in it, using a select from sys.columns. Then update that table with the column counts that you want. From there it's a select from the temp table and build your report as usual.


With this approach though, wouldn't I still need to re-design the report in BIDS each time additional columns are added? The only way I can see how to make the extra columns dynamic in the report structure would be to convert the data in to a format we can build a matrix report from, so copying each column to be a record i.e. convert the table from a :

ColumnGroupA ColB ColC structure to...

ColumnGroupA ColTitle ColValue

where ColTitle for record 1 would be ColB and ColTitle for record 2 would be ColC etc.

Not sure how to do that though. Any ideas?
Post #1485729
Posted Monday, August 19, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 10,342, Visits: 13,351
With your current design I think returning the data in a format that allows for a matrix report is probably your best option. Which means you'd also have to be using SELECT * in the query to return all columns from the table.

As a side note, in my experience adding columns to a table isn't something that is done often as that normally requires some major changes to the application as well, so adding that new column to a report is usually one of the simpler tasks. If you are regularly adding columns to a table, I would argue that there is a design flaw that should be addressed.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1485943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse