The purpose of this is to check the Excel file structure is correct before it is amended. The validation of the schema, checks the Excel file is in the correct format and the parameter names are correct. It does not check the parameter values are correct. To check the schema and parameter values are correct press the Validate Parameters button. You might ask why not just have a validate parameters button, the answer is time, if there are lots of subscriptions, it will take time to validate each of the parameter values.
Process to Validate the Excel File Schema
1.In the options screen press the restore icon.
2.In the restore subscriptions screen...
2.1.Select the folder where the reports are stored
2.2.Select Use Excel for the username and password. Otherwise you will need to enter an username and password( they will not be used as you are only validating the schema)
2.3.Select the report(s) you want to validate the excel schema.
3.Press Process button
4.In the Confirm Information for Restore screen. Press the Schema validate button.
If there are any errors the log will identify where the error is.
Worked Example
for this example the report has the following parameters
DefaultPrm, PrmNull, PrmBlank,PrmMultiValue
here is a screenshot of the Excel file:
Now lets do a restore of these subscriptions with those parameters. Working from the Restore subscriptions screen:
In the Confirm Information for Restore screen. Press the Schema validate button.
If everything is correct, you will something similar to this:
From this point onwards rather than showing the whole screen shot only the log output is displayed.
Unknown Parameter
Report : /SALES/Demo Report
Restore Excel file : z:\SALES\Demo Report.xls
===================================================================
------------------------------------
Worksheet : Report Server FileShare
------------------------------------
Schema Validation : Failed
-------------------------------------------
Unknown parameter : 'DuffPrm'
-------------------------------------------
so now it is a case of editing the excel file and removing the column with the parameter called DuffPrm.
Parameter name missing
Report : /SALES/Demo Report
Restore Excel file : z:\SALES\Demo Report.xls
===================================================================
------------------------------------
Worksheet : Report Server FileShare
------------------------------------
Schema Validation : Failed
-------------------------------------------
Missing parameter : 'PrmMultiValue'
-------------------------------------------
Here you can see there is a missing Parameter called PrmMultiValue. In the Excel file add at the end of the parameters section the Parameter PrmMultiValue. You will also need to add the values for this parameter to each subscription.
Misspelt Parameter
This is likely to be shown as a missing and an unknown parameter.
For this example I renamed the parameter PrmMultiValue to PrmMultiValueX.
Report : /SALES/Demo Report
Restore Excel file : z:\SALES\Demo Report.xls
===================================================================
------------------------------------
Worksheet : Report Server FileShare
------------------------------------
Schema Validation : Failed
-------------------------------------------
Unknown parameter : 'PrmMultiValueX'
Missing parameter : 'PrmMultiValue'
-------------------------------------------
here you can see there the correct parameter name is PrmMultiValue but the validation has found an unknown parameter PrmMultiValueX.
Validating Multiple Reports
You can validate more than one report schema at time, simply select which reports you want to validate on the restore screen.
Report : /SALES/CountrySales
Restore Excel file : z:\SALES\CountrySales.xls
===================================================================
Warning: XLS file not found
Report : /SALES/Demo Report
Restore Excel file : z:\SALES\Demo Report.xls
===================================================================
------------------------------------
Worksheet : Report Server FileShare
------------------------------------
Schema Validation : Passed
here you can see 2 reports schema validated. SSRS Subscription Manager will only validate selected reports with an Excel file. If the Excel file is missing you will get a warning. This warning can be ignored by selecting the Hide warnings for reports with no XLS option.
