Issue with using two expressions to create another expression in SQL server 2005
Hello. I am brand new to SQL server databases so please bear with me. I am using Visual Studio to create a report. I created my dataset and the report is working properly excluding the following...
The report contains two groups, Category and Subcategory. Both totals are based on the sum of "ContactTotal" field in my dataset. My issue is that I need to divide the number of each subcategory by the overall Category total. For example, the percentage of 'converter issue' would be 50% (50/100). The others would be 25%, however, I cannot create the expression being as both totals are based on the sum of the same field name. I guess in easier way to approach this issue would be to find out if there is a way I can re-name the calculated fields (ie CategoryTotal, SubCategoryTotal) so visual studio will recognize that I am trying to divide the Subcategory Total by the Category Total. Any assistance would be tremendously appreciated!
Category Total Subcategory Total % of contacts
Tech Issue 100
Converter Issue 50
No Dial Tone 25
Modem Offline 25
Sincerely,
Nikki
The report contains two groups, Category and Subcategory. Both totals are based on the sum of "ContactTotal" field in my dataset. My issue is that I need to divide the number of each subcategory by the overall Category total. For example, the percentage of 'converter issue' would be 50% (50/100). The others would be 25%, however, I cannot create the expression being as both totals are based on the sum of the same field name. I guess in easier way to approach this issue would be to find out if there is a way I can re-name the calculated fields (ie CategoryTotal, SubCategoryTotal) so visual studio will recognize that I am trying to divide the Subcategory Total by the Category Total. Any assistance would be tremendously appreciated!
Category Total Subcategory Total % of contacts
Tech Issue 100
Converter Issue 50
No Dial Tone 25
Modem Offline 25
Sincerely,
Nikki
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello...again! I have been playing with the code you sent, and again, it works perfectly. I was then trying to enter parameters (ie customer location) to the dataset but can't figure it out. I know, down the road, I will be asked for a report separated by the customer location. I have been playing with this for a while and cannot figure it out. An example would be to pull from the same table "WHERE Site = '9'" and implementing the dataset you suggested.
Also, would you please explain the use of "st." before the fields names that you are using to group the data? Thank you so much for your patience!
Nikki
Also, would you please explain the use of "st." before the fields names that you are using to group the data? Thank you so much for your patience!
Nikki
ASKER
Good morning...you may disregard my previous entry...I figured it out. Again, thank you so much!!!
Nikki
Nikki
ASKER
Sincerely,
Nikki