Create XLS with Pivot on the fly

DullGreyGuy

We have a bit of a silly form in XLS  which lists each team/team member on rows and a columns of products. The manager has to fill-in for each team the split of effort by member and product over the next month so the sum of the team must be 100%. This then goes into a db via an upload in the central team. 

The form is regenerated each month with last months answers but may have more columns if new products have been introduced or new rows if new staff have joined etc. This currently works ok but often its late being loaded because the person updating for a team reduces "Bob" 20% from Car intending to reallocate 10% to Bike but gets distracted and so submits with only 90% allocation which takes half a day to be spotted then has to go back to the manager again, who has to update it again and sending it back again which all adds delays. 

Without having the back and forth of doing validation on loading to the db, how can we check each team adds to 100%? At the moment the SQL produces a single tab XLS and the proposal is  a person then goes into and adds a second tab with a pivot table but it doesn't feel like manual intervention should be required. Know how we could add a fixed data table to show the sums on generation but need it to be a real formula driven thing so it updates when the changes are made

DE_612183

can you do a bit of conditional formatting that highlights any check sum that doesn't equal 100%?

DullGreyGuy

DE_612183 said:

can you do a bit of conditional formatting that highlights any check sum that doesn't equal 100%?

How can I add the conditional formatting on the fly? At the moment SSRS spits out the XLS on the 25th of every month

DE_612183

can't you get the app to populate a template XLS rather than create a new one?

It maybe I haven't understood the ask exactly - difficult to see without the actual data and code in front of me.

It might be better to ask on a more dedicated forum.

Heedtheadvice

@DE has the right idea but I agree that the description of the issue is too generalised to give an answer . To get answers we have to properly understand the problem in detail and the processes.

It is usually best to do an automatic validation or to flag up with conditional formating at data entry time so that errors are picked up prior to any processing and can be corrected early the the data entry personnel.

However I am unclear if data entry is done in the dB or in single sheet or multiples and if the 100% allocation requires all teams/ members to be populated before it can be validated and why anysus by pivot is done at the end of the process.

Examples of (anonymised?) workbooks etc would be useful. Need not be that many data entries.

DullGreyGuy

		Team	Member	Car	Bike	Breakdown	Van	Home	HE
		Alpha	Bob	10%	5%	10%
		Alpha	John	10%		5%
		Alpha	Rosie					30%	10%
		Alpha	Rose				20%
		Alpha	Sam
		Gamma	Dan	50%
		Gamma	Sam	50%
		Omega	Rich	5%	3%	3%	1%
		Omega	Sue	5%	3%	3%	1%
		Omega	Pat	5%	3%	3%	1%
		Omega	Jez	5%	3%	3%	2%
		Omega	Ben T	5%	3%	3%	1%
		Omega	Ben C	5%	3%	3%	1%
		Omega	Rob	5%	3%	3%	1%
		Omega	KaMan	5%	3%	3%	1%

So the above is a cut down version of the workbook table, when its currently generated it populates with last months results but with new teams/members/products... so in this example Sam has joined team Alpha so is now present but has nothing allocated to them yet. 

The managers update the XLS and pass it back and the updated results are loaded back into the db. Validation is done at load that each sums to 100%. At the moment there is a fair bit of backwards and forwards where the teams dont sum to exactly 100%. Had discussed just scaling results to get it to 100% but was felt the mistake should be corrected by the person entering the data into the XLS

DE_612183

So here I've used an excel to use conditional formatting to show the values which are not 100% - I've added a couple of bits of data ( in yellow ) to show rows that add up to 100% are not called out in red.

DullGreyGuy

DE_612183 said:

So here I've used an excel to use conditional formatting to show the values which are not 100% - I've added a couple of bits of data ( in yellow ) to show rows that add up to 100% are not called out in red.

The team should sum to 100% not the individual members... the problem is I dont think using SSRS I can make it extract the data into an excel file and add conditional formatting to it all automatically in SSRS... SSRS can add a new sheet to an existing file but cannot add data to an existing sheet, it just replaces the sheet else would be several ways to do it. 

DE_612183

if you are looking at an SSRS report - you can conditionally format as well - it's not quite as straight forward as excel - but it's still possible

MeteredOut

If the export from the DB is to a known location and file name, have you tried creating a new sheet and referencing the exported sheet as an external sheet?

DullGreyGuy

DE_612183 said:

if you are looking at an SSRS report - you can conditionally format as well - it's not quite as straight forward as excel - but it's still possible

When its generated all will be 100%... I dont know how to get SSRS to put the conditional formatting into the XLS so when the user updates it to say the team is going to focus on Car this month after a bad month on Home just gone that the formatting updates as they manipulate the data in the spreadsheet?

MeteredOut said:

If the export from the DB is to a known location and file name, have you tried creating a new sheet and referencing the exported sheet as an external sheet?

It could work but not sure having to open two files each time isn't much less clunky than having someone go into the file and manually add either a checking grid or conditional formatting etc but worth considering