SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a report to list totals from a table that doesn't require a re-design whenever a new field...


Creating a report to list totals from a table that doesn't require a re-design whenever a new field is added

Author
Message
Griffster
Griffster
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 713
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.
Scott Murray-240410
Scott Murray-240410
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 3118
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")
Steven.Howes
Steven.Howes
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 2346
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.
Griffster
Griffster
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 713
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?
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19172 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search