State edition users can build SSRS reports that serve multiple use cases, such as for state edition users from the state edition database, for district edition users from the state edition database, or for district edition users on their own district's database. Any data source compatible with SSRS can be used, but this document will focus solely on those three options.
State edition users have several additional considerations when building reports for this tool. Specifically:
- Configuration by Campus for initial use.
- Which database are you looking to run the query on?
- How do you scope the results to the correct districts?
Configuration by Campus for initial use
No additional configuration is needed if reports are not being published to districts. Running a report on the state edition database with a state edition user has no special considerations.
Before published reports can be run from district edition sites, Managed Services needs to ensure the proper account and folder structure exists on the SSRS server to allow report sharing. Publishing reports before this happens will succeed, but dummy connection records will be created that will need to be filled in by Infinite Campus Personnel before the report will work.
Which database are you looking to run the query on?
For a report is published to districts, it is possible to run on either that district's database or the state edition database (or any other SQL server database). This would be determined by the report itself via a data source. To make running on the district edition database possible, one parameter you can pass to the report (suggested as a hidden parameter) is 'connectionString'. This will include the URL of the database and the database name for the campus application that made the request. This is enough information to either connect back to the database or pick an SSRS data source associated with the database.
How do you scope the results to the correct districts?
For reports published to districts, it is crucial to scope data appropriately with SSRS report and within the SQL queries of the report themselves. As a safeguard, Infinite Campus requires that published reports include a parameter that filters by either connection string (connectionString), district number (auto_districtNumber), or school number (auto_schoolNumber) in order for the report to work. However, this is the extent of safeguarding that it’s possible for Campus Development to do.
It is the responsibility of the State Edition individual designing the report to ensure these parameters are implemented properly within the SSRS report, and that the report’s SQL queries (ex. WHERE statements) utilize them.
These parameters provide the ability to filter a query to only the results a user should have access to. These parameters are generated server-side to ensure users running the reports cannot modify their values.
schoolID and districtID are not enforced to be the same between district edition and state edition, so they should not be used for the purpose of scoping report results.