August 15, 2013 at 5:31 am
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.
August 16, 2013 at 6:32 am
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")
August 16, 2013 at 7:59 am
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.
August 19, 2013 at 4:49 am
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?
August 19, 2013 at 12:22 pm
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
Consultant - Straight Path Solutions
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply