Home

SIM User Manual V14.0

1. 21 Mew Query Lis Ci H 3 Object Explorer rX Connect 32 A m YF RA E A WIN VEABKODGRO41SIMSERVERDYS SOL Server Databases E Security El Logins R Newton fees Filter Start PowerShell Reports Refresh DRD DRE D A WIh VeABEKODGR941 Administrator Server Roles Credentials Server Objects Replication Management 8 Inthe popup window perform the following actions i Enter a login name for example report ii Check SQL Server authentication iii Enter and confirm the password iv Uncheck Enforce password policy this depends on your password security preferences for this example it is disabled v Set the Default database to SIMdb30 vi Then click on User Mapping from left menu titled Select a page vii In the User Mapping window check SIMdb30 and SIMdb30Audit Make sure in the bottom list public is selected viii Press OK External Reports Instruction Guide Prod Code 02580 02 Page 26 of 39 E Login New i E Login New Fife x Select a page 4 E Script 3 Help gt General LAA Server Roles u ed to this login TERRE sers mapped to this login 2 Securables Database Default Schema gt Status master Select a page i q Script Te Help LAA General 2A Server Roles Lak a Feo seat Securables Windows authentication Ea Status SQL Server authentication Password Jeeeeee Confirm password Joceeee a Specify ald passwor
2. 4 e False x Binaiy gt 2 Fals lt ipy r n lt binaiy gt Page 37 of 39 A WARNING If the user selects multiple tables without knowledge of the database structure the system may cease to function correctly and the data in the reports may show incorrect data 12 The selected fields need to be selected and arranged as required before going to Next layout window where the report layout can be selected p Arrange fields Arrange fields to group data in rows columns or both and choose walues to display Data expands across the page in column groups and down the page in row groups Use functions such as Sum Avg and Count on the fields in the Values box Available fields Hi Column groups CreatedTimestamp PressureUlcerRiskScorelD QuestionResponsiblelD AbilityToUnderstandLevellD ClentUserlD SkinConditianID CognitiveSkillLevels_Cognitives E Ross groups a CognitiveSkillLevelGID Locale New Table or Matrix p Choose the layout If you choose to show subtotals and grand totals you can place them above or below the group Stepped reports show hierarchical structure with indented groups in the same column Options Preview C al Show subtotals and grand totals FirstName LastName Description Blocked subtotal below First ame leita Description Blocked subtotal above Stepped subtotal above Expand collapse groups 13 In the Choose a style window different pre defined styles
3. Filter by State Y What type of rule would you like to create Y Rule Type Filter by Group d d gt d Q Refresh ise Export List Help C Predefined core Nel Rule that controls connections for a Windows experience Core Nel core Nel C Custom Core Nel Custom rule Core Nel Core Nel Core Nel Core Nel iv Core Nel 0 Core Nel O core nel Learn more about rule types iv Core Nel Core Nel O core Nel iv Core Nel Core Nel Distribut Distributed Transaction Coordinator RPC EP Distributed Transaction Coordi All No Allow No Syste Any of gt 15 In the next page enter 1433 for Specific local ports and click Next Protocol and Ports Specify the protocols and ports to which this rule applies Steps Rule Type Does this rule apply to TCP or UDP Protocol and Ports TCP Action C UDP Profile Name Does this rule apply to all local ports or specific local ports Learn more about protocol and ports 16 In next 2 pages Press Next button In the last page in Name field enter SQL Server and click Finish 17 Restart the SIM system server External Reports Instruction Guide Prod Code 02580_02 Page 30 of 39 6 2 CHANGING CONNECTION STRINGS To modify the reports from client computers the client computers need to have Report Builder 3 0 Microsoft SQL Server or Microsoft SQL Server Reporting Service
4. External Reports Instruction Guide COPYRIGHT INFORMATION Copyright 2014 Simavita Aust Pty Ltd All rights reserved SIM and SIM with logo are trademarks of Simavita Aust Pty Ltd The information contained in this External Report Writer is the sole property of Simavita and may not be duplicated without permission This External Report Write instruction guide may be revised or replaced by Simavita Aust Pty Ltd at any time and without notice You should ensure that you have the most current applicable version of this guide if in doubt contact Simavita customer service department External Reports Instruction Guide Prod Code 02580_02 Page 2 of 39 Table of Contents C pyrieht PMO Wii Ol OI sesser asce ienero rE SA EE EEEN N SEEEN TEE E EEEREN 2 Tane gi ONTEN iseni EEEE A AE EGT 3 1 0 Installing External Reports for SIM cccccccccssssscccccessecccceeseceesseseececsaeeseceeseeasecessseuseceessaaseeessuaneeessagaeses 4 2 0 Notes for the Person Installing sicdavnsesnsoinsavcdeswesnieeavcneisanlsswveneeeswrenercinwadvareamveredeverennvainacsedtarsenestynxeanlaneiesassanrenee 5 3 0 Information on Server User Permission ccccccccssssecccceeseccceceeseeccesauseccecsaeseecesseaeecessuaasecessaaaeeeessageeeeees 6 4 0 Client Computer Access for Reporting no editing ccccccccsssssseececceeeeeesseeccceeeseeeesececeeeeeaeaesseecessssuaaaeeees 8 5 0 Adding External Reports to the Server ssssssssssses
5. Server Service Account Reporting Services Configuration Connection ee Web Service URL J Mi Database e SOL Server 2008 R2 Reporting Services I Report Manager URL Please specify a server name click the Find button and select a report server instance to configure Server Name WIN VEABKODGR94 Find C Execution Account Report Server Instance SIMSERVERDYS Encryption keys y a Scale out Deployment Cancel E mail Settings Ei Apply Exit 3 Click on Report Manager URL Change the Virtual Directory to Reports from Reports _SIMSERVERDV3 and press Advanced button Connect Report Manager URL J WIN VEABRKODGR94 1 SIMSERVERDYS Configure a URL to access Report Manager Click Adwanced to define multiple URLs or to specify additional parameters on the URL Service Account Report Manager Site Identification 28 Web Service URL j E Virtual Directory ig Database amp Report Manager URL E mail Settings URLs http WIN VEABKODGR94 80 Reports SIMSERVE Te A Execution Account A Encryption Keys 1 Scale out Deployment Fesults Copy K Apply Exit External Reports Instruction Guide Prod Code 02580 02 Page 9 of 39 4 Onclicking Advanced button as seen in Step 3 will bring up a pop up window as below Click on the first Edit button Advanced Multiple Web Site Configuration E3 Configure various identities for Report Man
6. which should say Test connection succeeded Then press OK WS eri dle WU Serrulimn dle LI dW Male zEApI Data Source Properties Change name type and connection options Name 192 168 0241 1433 SIMSERVERDV3 DS_SIMdbs0Audit 6 Use a shared connection or report model i Use Windows Authentication Use SQL Server Authentication Use a connection embedded in my report Select connection type Microsoft SQL Server Connection string Data Source SIMSERVERDV3 Initial Catalog SIMdbs0Audit Select or enter a database name S Mdb 20 Audit Attach a database file PC e Logical name Use single transaction when processing the queries 10 Now you can run or modify the report External Reports Instruction Guide Prod Code 02580_02 Page 32 of 39 7 0 Using SQL Report Builder This section provides an example of running an external report template as provided in the SIM system software package or creating a new report The information provided here should be considered a high level introduction to running or creating reports It is assumed that the user has significant prior knowledge of how to use SQL databases and develop SQL reports Please refer to Microsoft s manuals and instructions for further details Different report examples and or templates can be found at http simavita com training External Reports Instruction Guide Prod Code 02580 02 Page 33 of 39 7 1 E
7. Schena a View connection properties E Asymmetric Key s ner C Certificates E Feferencez T Symmetric Keys Select cr Security Show plan DO Server Objects i Replication Subscribe query not E Management External Reports Instruction Guide Prod Code 02580_02 Page 18 of 39 27 Now anyone can access the reporting system with the newly created username and password through the http lt IPADDRESS gt Reports link External Reports Instruction Guide Prod Code 02580_02 Page 19 of 39 5 0 Adding External Reports to the Server External reports may be added to regularly to Simavita s training resource website http simavita com training Once these external report templates are downloaded these need to be added to the server so that they become available for all users via a web based interface The addition of these reports can be achieved via a client computer through the interface described in preceding sections Alternatively the files can be added directly to the server External Reports Instruction Guide Prod Code 02580 02 Page 20 of 39 5 1 ADDING REPORTS FROM CLIENT COMPUTERS The procedure below describes how to add new external reports to the system from a client computer 1 On a client computer open Internet Explorer and enter the http SIM Server IP Reports for example http 192 168 0 161 Reports into the Address Bar and press Enter The following pop up window will appear for login
8. http 192 168 0 161 Reports Windows Internet Explorer or 2 http 192 168 0 161 Reports J E x Ez waiting for 192 168 0 161 x P w TE Windows Security Connecting to 192 168 0 161 a Domain WIN V64BKODGR94 E Remember my credentials 2 Enter the login credentials for an administrator for SIM server SQL Server Reporting Services page will be displayed Home Report Manager Windows Internet Explorer fel xX g9 P http 192 168 0 161 Reports o me Ey hed Home Report Manager x _ h K TE Home Site Settings Help F SQL Server Reporting Services ERT Home Search e Cy New Folder a New Data Source R Folder Settings T Upload File EE Details View There are no items in Home Click Help for more information about this page 3 Ifthere isa red message saying This page might not function correctly because either your browser does not support scripts or active scripting is disabled then follow the instruction provided on this link Error html to enable active scripting for the browser 4 To Add the External Reports to Reporting Services for web access click on the Upload File highlighted in red in Step 2 In the subsequently displayed screen click the Browse button which will pop up a File Browser window Go to the folder where the new report file was downloaded and select the report and press Open and then OK in the Upload File page Now the report w
9. click on Properties In the Properties window go to second tab IP Addresses In the IP All section enter the TCP Port as 1433 and press OK Protocol Name Status d _ 4 Shared Memory Enabled Y Mamed Pipes Disabled TCP IP Properties Enable Disable Help Wve Protocol IP Addresses E 1p4 Active Yes Enabled Mo IP Address 127 0 0 1 TCP Dynamic Ports 0 TCP Fort IPS Active Yes Enabled Mo IP Address FesO Sefe 192 168 0 161 12 TCP Dynamic Ports 0 TCP Fort E TEF Dynamic Ports TCP Fort O w Cancel Apply Help 5 Open to SQL Server Management Studio Programs 2 i SQL Server Configuration Manager Location Ssms C Program Files x86 Microsoft SOL Server 100 Tools Binn SShell Common IDE 7 See more results SOL Server Man E3 Logoff JN levston fay by E External Reports Instruction Guide Prod Code 02580_02 Page 25 of 39 6 Press connect in the Connect to Server window J Microsoft SQL Server 2008 R2 Server tupe Database Engine Semer name WIH EABKODGRIASIMSERYERDW A Authentication windows Authentication User name WIN VEABKODGR94 Administrator Password C O Remember password Cancel Help Options gt gt 7 Inthe Tree View expand Security gt Logins Right Click on Logins and Click New Login E Microsoft SQL Server Management Studio File Edit View Debug Tools Window Community Help
10. or user name report Select one or more roles to assign to the group or user L Role Description Browser May view folders reports and subscribe to reports Content Manager May manage content in the Report Server This includes folders reports and resources O My Reports May publish reports and linked reports manage folders reports and resources in a users My Reports folder Publisher May publish reports and linked reports to the Report Server Report Builder May view report definitions External Reports Instruction Guide Prod Code 02580 02 Page 15 of 39 20 Data read permissions also need to be given to the newly created user from the SQL Server On the server open SQL Server Management Studio Programs 2 i SQL Server Configuration Manager Location Ssms C Program Files x86 Microsoft SQL Server 100 Tools Binn SShell Common IDE 7 See more results SOL Server Man E3 Logoff JN lt 2 ertan s by E OF 21 Press connect in the Connect to Server window Microsoft a SQL Server 2008 R2 Server type Databaze Engine z WIM VEABKODGAI4SSIMSERVERDYS Server name Authentication windows Authentication Z WIN VEABEROD GR I4 Administrator E azzword eeo Remember password Cancel Help Options gt gt User name External Reports Instruction Guide Prod Code 02580 02 Page 16 of 39 22 From the Tree View on the left Exp
11. IUSR j 5Y5 Roles Leris CE Schemas 4 25 Repeat the previous 2 steps for SIMdb30Audit database 26 Right Click on SIMdb30Audit database and click on Properties Then click on Permission and then select BUILTIN Users From the list of checkboxes scroll down to Execute and check the Grant checkbox and press OK L Microsoft SQL Server Management Studio File Edit View Debug Tools Window Community Help 1 T New Query C C a 5 E T Database Properties SIMdb30Audit Select a page E a Script TE Help Connect 2 S e T E S A General E leo WIN VESBKODGR941SIMSERVERDYS SOL Server 10 50 25 Files Server name wiIN VEABKODGRS4 SIMSERVERDYS El Databases T Nop s ADEDE jew gever permissions ReportServerf SIMSERVERDYS a Database name SIMdb30Audit ReportServer SIMSERVERDYSTempDb s SIMdb30 E Users or roles Search SIMdbso4udik AS BUILTIN Users Object Explorer Oe Database New Database Tables New Query H OI views E Synonym ai ae Gaede AL LocalSystem Program Tasks A NT AUTHORITYSIUSA Service B aoe Storage Policies eai Facets El Users E Start Powershell d g Reports ae eS Permissions for BUILTIN Users I Connection 2 I Rename _ Explicit Effective L Delete S A Grant With Grant E WIN VEABKODGRS4 SIMSERVE emission rantor Grant With Gran eny p Remeh Camer Create SML schem Ci Roles Properties WIN VEABKOD GA S4 Administrate Delete E
12. References Select HAAA EEEE Cancel 4 External Reports Instruction Guide Prod Code 02580 02 Page 28 of 39 12 Go back to SQL Server Configuration manager step 2 Select SQL Server Services In the right panel select SQL Server SIMSERVERDV3 right click and press Restart P Sql Server Configuration Manager File Action View Help e9 AHH O toe Log EH SOL Server Configuration Manager Locals Mame State Start Mode SQL Server Services Te SQL Server SIMSERVERDYS uocis Automatic NT AUTHORITAN a eee SQL Server Network Configuration 32bit PSOL Full text Filter Daemon Launcher SIME tat Manual NT AUTHORITY SOL Native Client 10 0 orrat razbit SOL Server Reporting Services SIMSERVEF Stop ace NT AUTHORITYIN Fl go SQL Server Network Configuration FfeiSOL Server Agent SIMSERVERDYS Pause Other Boot System NT AUTHORITYIN ie Protocols for SIMSERVERDYS YHSOL Server Browser Heals Other Boot System NT AUTHORITYIL Restart El Ss SOL Native Client 10 0 Configuration Client Protocols EL Aliases Properties Help 13 Open Windows Firewall with Advance Security va Microsoft SOL Server Management Studio File Edit View Debug Tools Window Community Help T1 New Query Ly La a E Object Explorer Connect S S m T RA H WIN V64BEKODGR O41 SIMSERVERDYS SOL Server 10 50 2550 re El Databases System Databases Reportserve
13. XISTING REPORTS Once downloaded and installed as per the instruction provided in this document these reports are directly accessible on the server by double clicking on the report files rdl files and opening these reports via Report Builder Alternatively the reports may be accessible from client computers depending on the setup The steps described below assume that a report has been opened directly on the server 1 2 3 Either double click the report template to be opened or open SQL Report Builder and select the file from Open file option from FILE menu By default the report opens in the design view To view the report click the Run button as shown below Observation List for Resident rdl Microsoft SQL Server Report Builder Home Insert View Al amp Ir E Merge ty Split Run Paste B TAE Es FARE n mla de ae e Sor wd i 00 Fa Align F vie eno NEW ZOIL AN Gy Built in Fields H E Parameters H E Images H 0 Data Sources Client ID Full Name 5 0 Datasets Observation Event Details ObservationEvent ModifiedDetails Staff Memeber StaffMemeber Date Of Birth Gender clientid FullName DateOfBirth Gender EventTimeStamp Event Time Stamp anoncncncanencscccncnsonencncnnproccecannncnencecanewcscscnaancnenencanencececaannenencacen
14. ager Multiple Identities for Report Manager IF Address TCP Pork Host Header All Assigned a0 Add Remove Edit Multiple SSL Identities for Report Manager IP Address SSL Port SSL Certificate Issued To Add Remove Edit F OF Cancel In the next pop up window change the IP Address drop down list from All Assigned to the specific IP address of the server in the example below this is 192 168 0 161 and press OK and then click Apply in the main window please make sure the Virtual Directory has changed to Reports from Reports SIMSERVERDV3 or change it again as described in Step 3 5 Identification IP Address 192 168 0 161 All Assigned Host Header Name All Unassigned 192 168 0 161 TCP Patt 1 n 127 0 0 1 URL http w NA BAB KODGA94 80 Reports CFI OF Cancel 6 Now the external reporting system is accessible with entered IP address within the network http SIM Server IP Reports for example http 192 168 0 161 Reports Open Internet Explorer on the server machine and enter the IP address as above into the Address Bar and press Enter The following pop up External Reports Instruction Guide Prod Code 02580_02 Page 10 of 39 window will appear for login http 192 168 0 161 Reports Windows Internet Explorer JO e http 192 168 0 161 Reports Ox E X C waiting for 192 168 0 161 x Si h Saf TE Windows Securit
15. als of the SIM system server Windows login Note that this is a significant security risk It is preferable to create one or more normal user accounts without admin privileges on the SIM system server and give the individual users permissions for viewing the reports To do this follow the subsequent steps 12 To create a user account with standard login privileges on the server open Control Panel and click Add or remove user accounts under User Accounts E gt Control Panel Of x a ty CJC vem O e E Adjust your computer s settings View by Category Y System and Security Review your computer s status Check Firewall status view event logs User Accounts Py Add or remove user accounts Appearance Change desktop background Adjust screen resolution Network and Internet Hihaikd O S A TAE y backs External Reports Instruction Guide Prod Code 02580_02 Page 12 of 39 13 Click Create a new account aa i Manage Accounts S 7 User Manage A Choose the account you would like to change Administrator Administrator Password protected Guest Guest account is off Create a new account What is 4 user account Additional things you can do 60 to the main User Accounts page E 14 In the next page enter report or any other preferred username in the textbox and check the Standard user and then click Create Account i Cre
16. and Database gt SIMdb30 gt Security gt Users B Microsoft SQL Server Management Studio File Edit wiew Debug Tools Window Community Help 2 New Query Ga a Gl amp amp eg Object Explorer Connect a a a T RA F WIN V6ABKODGR94 SIMSERVERDYS SOL Server i J System Databases T ReportServerfSIMSERVERDYS T ReportServer SIMSERYEROY 3TempCB aL SIMdb30_ Database Diagrams Tables Views Synonyms Frogrammability Service Broker Storage 4 dbo i guest s5 L INFORMATION SCHEMA LocalSystem NT 4UTHORITYIUSR Ay sys Roles Schemas Asymmetric Keys Certificates Symmetric Keys H AA A 23 Right click Users and click on New User In the pop up window for User name and Login name text boxes enter BUILTIN Users In the 2 group of checkboxes Database role membership select db_datareader and press OK T Database User New Me Select a page E Es Script TE Help Beg General if Exended sername BUILTINUseo OOO O ooo Extended Properties User name BUILTIN Users Login name BUILTIN Users Certificate name C Key name without login Default schema a Schemas owned by this user Owned Schemas db_accessadmin db_backupoperatar db_ datareader db_datawriter db_ddladmin db_denydatareader Connection E k db_denpdatawriter Sener WIN VEABROD GA S445 I MSERYE Connection WIN VEABKODGR94 Administrate dh accesvadmin i db_backupoperator 3 Vie
17. atabases 0 System Databases ReportServer SIMSERYERDY3 ReportServer SIMSERVERDY3TempDE SIMdb30 SIMdb304udit E Security 0 Logins A M5_PolicyEventProcessingLogi ay M5_PolicyTsqlExecutionLogin A BUILTIN Users j NT AUTHORITYIUSR A NT AUTHORITYINETWORK SERVIC A NT AUTHORITYISYSTEM j NT SERVICE MSSOL SIMSERYERD 4 Server Properties WIN YGABKODGR94 SIMSERYERDY3 Select apage 7 Ss Script TE Help LAA General LAA Memory AA Processors Server authentication Connect Di t eer pete onnections Windows Authentication mode LS Database Settings SOL Server and Windows Authentication mode LA Advanced LAA Permissions Register New Query i Login auditing C None Activity Monitor Start ic Stop Failed logins only Pause Successful logins only Resume Both failed and successful logins Restart Server proxy account amp sa Policies Enable server proxy account AL WIN V6ABKODGR94 Administrator Facets Proxy account es AY report Password Options Server Roles C Credentials Server Objects Ca Replication CI Management Start PowerShell Connection Reports Server WIN VEABKODGR94 SIMSERVE Refresh Properties o E Connection a 7 Enable C2 audit tracing WIN VBABKODGR94 Administrate i Visi coniinal Cross database ownership chaining er lew connection properties Cancel 7 AA 11 Right Click on SIMdb30Audit an
18. ate New Account a J t Mana Create M Name the account and choose an account type This name will appear on the Welcome screen and on the Stark menu report Standard user Standard account users can use most software and change system settings that do not affect other users or the security of the computer Administrator Administrators have complete access to the computer and can make any desired changes Based on notification settings administrators may be asked to provide their password or confirmation before making changes that affect other users We recommend that vou protect every account with a strong password Why is a standard account recommended al Create Account Cancel A External Reports Instruction Guide Prod Code 02580_02 Page 13 of 39 15 You will be brought back to the Manage Account window Click on the report or the newly created user In the next window click on Create a password d Change an Account Mana Change a Search Control Panel Make changes to report s account Change the account name Create a password report Change the picture Standard user Change the account type Delete the account Manage another account 16 Provide a strong password which should be at least 8 characters containing at least one capital letter one number and one special character For example RepOrt Provide a
19. can be selected Once the selection is complete click Finish gt gt to view the report External Reports Instruction Guide Prod Code 02580 02 Page 38 of 39 ul MADE IN AUSTRALIA by Simavita Aust Pty Ltd L13 54 Miller Street North Sydney NSW 2060 Australia See www simavita com for more information External Reports Instruction Guide Prod Code 02580_02 Page 39 of 39
20. d Old password I Enforce password poli J Enforce password egpiration model msdb ReportServer SIMSER ReportServer SIMSER J User must change password at next login Mapped to certificate Mapped to asymmetric key I Map to Credential m Add Connection Mapped Credentials Credential Provider F Guest account enabled for SIMdbstAudit Database role membership for SIMdb304udit db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin Connection Server WIN VBABKODGR94 SIMSERVE Server WIN VE4ABKODGRI4 SIMSERVE Connection WIN VBABKODGR94 Administrate Connection WIN VEABKODGRI4 Administrate 32 View connection properties Progress Remove Default database SIMdb30 Default language kadeto o YS cms 9 Expand the Tree View on the left to Databases gt SIMdb30 gt Security gt Users Right click on the user you created in this example report and click Properties In the pop up window check db_ datareader from the second list and press OK Repeat the same step for SIMdb30Audit database File Edit view Debug Tools Window Community Help 33 View connection properties OOOOOOOoOO Database User report fel xi Select a page 3 4 Script 7 Help LAA General A Securables LA Extended Properties User name report ReportServer SIMSERVERDY3 Cert
21. d click on Properties On the pop up window select Permission from the left Select a page options Then select the user you created and in the bottom grid scroll down to Execute and check the checkbox under Grant column and press OK B Microsoft SQL Server Management Studio File Edit view Debug Tools Window Community Help Object Explorer Database Properties SIMdb30Audit _ Oy x Connect 39 a m T RA a select a page ES Script Help General Files Server name WIN VEAB EODGAS4 SIMSERYERDYS LA Filegroups LA Options View server permissions eee Database name Slhidb30Audit A Extended Properties Users or roles Search BUILTIN Users IIS LocalSystem NT AUTHORITY IUSA report stem Databases ReportserverfSIMSERVERDYS Reportserverf SIMSERVERDY3TempDB SIMdb30 New Database Tables New Query Wiews Synonym Frogram Tale Service B Storage Folicies Security PE Script Database as OHA wk Start PowerShell Reports Rename Permissions for report Connection T Delete Explicit Effective Server WIN VEABKODGRI4 SIMSERVE Permission Grantor with Grant Create type Refresh Roles _j Schemas symmetric keys Certificates Symmetric Keys J Security J Server Objects J Replication Jj Management Connection WIN VEASBRODGR S4 Administrate Create view Create 85ML schem Delete ay View connection properties HAH Progress Execute Insert
22. eck For Updates am ar as nm 2 The New Report wizard option will be shown as below Create a report from a wizard or from a blank report Table or Matrix Wizard zd Guides you through choosing the data source connection layout and style for a table or matrix report Chart Wizard Share queried data among N Guides you through creating column line pie bar and area charts multiple reports Map Wizard QB Displays report data against a geographical background F Blank Report 3 Click on Table or Matrix Wizard if tabular report is needed and Chart Wizard if report with chart is needed Once either of these are selected the user will be prompted to select the dataset Select Create a dataset before selecting Next gt External Reports Instruction Guide Prod Code 02580_02 Page 35 of 39 4 Inthe next screen click the New button to create a Data Source Connection Choose a dataset Choose a dataset Choose an existing dataset in this report or a shared dataset Browse Choose a connection to a data source Choose a published data source or create a connection for use only in this report Data Source Connections Test Connection 5 Inthe Data Source Properties window provide a name for the data source Click the Build to create the connection 7 A pop up window for Connection Properties will be displayed Enter SIMSERVERDV3 for the Server name a
23. erver External Reports Instruction Guide Prod Code 02580 02 Page 23 of 39 6 1 SERVER SETTINGS 1 By default SQL Server does not accept remote connections To enable remote connections following steps have to be taken on the SIM software server 2 Open SQL Server Configuration Manager Programs 6 Ik SQL Server Management Studio oF SQL Server Business Intelligence Development Studio FE SOL Server Configuration Manager l SQL Server Error apdtleane amp io Location mme CA Windows Sys Wide 5 SOL Server Installation Center 64 bi i xf SQL Server Installation Center 64 bit See more results J sau Server Log off gt estar zA ez Cc 3 Expand SQL Server Network Configuration and click on Protocols for SIMSERVERDV3 Right click on TCP IP at the right window and Enable Sql Server Configuration Manager File Action View Help es A ais i SQL Server Configuration Manager Local Protocol Name SQL Server Services Y Shared Memory Enabled SOL Server Network Configuration 32bit Y Named Pipes Disabled SOL Native Client 10 0 Configuration 32bit E 4 SQL Server Network Configuration Disable d Protocols for SIMSERYERDYS a SOL Native Client 10 0 Configuration a Client Protocols amp Aliases Properties Help External Reports Instruction Guide Prod Code 02580_02 Page 24 of 39 4 After enabling TCP IP right click again and
24. h useable as they are are provided as a starting point for your facility to design reports tailored specifically to your needs The number and variety of external reports will increase over time please refer to Simavita s training web site regularly for updates http simavita com training External Reports Instruction Guide Prod Code 02580 02 Page 4 of 39 2 0 Notes for the Person Installing The SIM system utilises an SQL database to store information This database is installed on the SIM server on which the SIM software operates By default this database is not accessible from client machines To access external reports either for editing or running reports additional system setup steps need to be taken This document provides a guide to the steps required to gain remote access to the server s database These steps are not specific to SIM but are related to Microsoft s SQL database infrastructure and Microsoft s SQL report writing tools Please refer to information provided by Microsoft for additional details Following are five main sections e 3 0 Information on Server User Permission This section covers required permissions in the SIM server to access external reports from client machines e 4 0 Client Computer Access for Reporting no editing This section describes the process of configuring the server running the SIM software and SQL database to allow client computers database access via an SQL
25. he reports will not be accessible or function from remote machines unless step described in this document are performed External Reports Instruction Guide Prod Code 02580_02 Page 5 of 39 3 0 Information on Server User Permission To access the SIM system database for reporting purposes the SIM system server user often the Administrator must have clicked Select and Execute permissions in SQL server for SIMdb30 and SIMdb30Audit databases To do this follow these steps 1 Onthe SIM Server open SQL Server Management Studio Programs 2 SOL Server Management Studio ia SQL Server Configuration Manager Location Ssms C Program Files x86 Microsoft SOL Serveri 100Tools Binni SShell Common IDE a See more results SOL Server Man E3 Logoff gt erst 2A s o 2 Press connect in the Connect to Server window Microsoft 7 SQL Server 2008 R2 Server type Database Engine Server name TW AICAPE NINE OdL CIRCE EWE Pru Authentication windows Authentication z User name Password Remember password Cancel Help Options gt gt External Reports Instruction Guide Prod Code 02580 02 Page 6 of 39 3 From the Tree View on the left Expand Database gt SIMdb30 Right click on SIMdb30 and press Properties In the pop up window click on Permissions from left menu titled Select a page Select NT AUTHORITY IUSR user In the bot
26. hint for the password and press Create password 17 Go to web report URL http lt IPADDRESS gt Reports and login with Admin or logged in user Click on the Folder Settings link Home Report Manager Internet Explorer Miel x DL E http 192 168 0 161 R JO gt Home Report Manager de ke O Home Site Settings Help SQL Server Reporting Services ame Tij New Folder H New Data Source t Upload File be ACF Report Audit Report z 3 Details View Continence Schedule Wing Report bee Resident Outcome Report External Reports Instruction Guide Prod Code 02580 02 Page 14 of 39 18 In the next page click New Role Assignment E http 192 1668 0 161 P PE s A Report Manager w int LE Home Site Settings Help SQL Server Reporting Services Home Seach x Delt Security LI Group or User Role s Edit BUILTINAdministrators Content Manager 19 In the New Role Assignment page put the report or the newly created username in the Group or user name text box Check the Browser checkbox and press OK New Role Assignment Report Manager Windows Internet Explorer le http 192 168 0 161 R px hea A Mew Role Assignment Repo X E ini uf Home Home Site Settings Help oN SQL Server Reporting Services New Role Assignment Seach O JA Use this page to define role based security for Home Group
27. ide Prod Code 02580 02 Page 7 of 39 4 0 Client Computer Access for Reporting no editing This section describes the process of configuring the server running the SIM software and SQL database to allow client computers database access via an SQL web interface This is useful for accessing reports for reporting purposes only no editing is possible 1 On the server running the SIM system database open Reporting Service Configuration Manager Programs 4 tal Report Builder 3 0 E Report Builder 3 0 Help Be Reporting Services Configuration Manager SQL Server Error and Usage Reporting Location RSCanfigTool C Program Files x66 Micrasoft SQL Server 1004Toals Control Panel 10 Lah P Choose how to report problems E view all problem reports E view the Microsoft error reporting privacy statement online Generate a system health report P view recent messages about your computer E Review vour computer s status and resolve issues al view solutions to problems E Fix problems with your computer P view recommended actions to keep Windows running smoothly ball View message archive See more results E repor E3 Logoff ler Start amp External Reports Instruction Guide Prod Code 02580 02 Page 8 of 39 2 Press Connect on the pop up window Fe Reporting Services Configuration Manager M ge SOL Server 2008 R2 Reporting Services Configuration Manager
28. ificate name ReportServer SIMSERVYERDY3TempDB Prane TTT Database Diagrams without login _jj Tables n views Default schema dbo ey Synonyms Programmability Service Broker Owned Schemas Storage db_accessadmin BUILTIN Users db_datawriter dbo IIS db_denydatareader INFORMATION_SCHEMA Connection E db_denydatawriter Schemas owned by this user OH AAA A db_backupoperator db_datareader LocalSystem a a NT AUTHORITYLUUSR erver atabase role membership WIN VEABKODGR94 SIMSERVE A pom N U sys er Connection Roles Script User as gt WIN VEABKODGRI4 Administrate Schemas ene E p 3 View connection properties Asymmet Policies Certificat Facets 7 db_datawriter Symmetr oge E db_ddladmin u SIMdb304udit Start PowerShell db_denydatareader J Security a Server Objects Reports pni Replication EER Management Delete Refresh Ready 10 Right click on the server start of the Tree View in the left panel and select Properties In the pop up window select Security from the left menu titled Select a page Check SQL Server and Window External Reports Instruction Guide Prod Code 02580_02 Page 27 of 39 Authentication mode and then click OK File Edit view Debug Tools Window Community Help New Query D Lh GF el G Object Explorer mops Comnet 3 3 m Y F S EES WIN V6aBKODGR94 SIMSERVERDYS SQL Ser pee L3 D
29. ill show in the SQL Server Reporting Services Homepage 6 Now any user on client machines can go to address http lt IPADDRESS gt Reports in Internet Explorer and view the reports with the login credentials External Reports Instruction Guide Prod Code 02580 02 Page 21 of 39 5 1 ADDING REPORTS DIRECTLY ON SERVER External report template files can be added directly to the server by following these steps 1 Log into the server through the standard Windows login On the SIM s Desktop you will see a folder named External Report Templates This folder is automatically created during SIM installation If this folder does not exist and SIM has been installed you can create this folder manually 3 Download template files from Simavita s training website Copy the downloaded files into External Report Templates folder on the desktop External Reports Instruction Guide Prod Code 02580_02 Page 22 of 39 6 0 Editing and Reporting from Client Computers This section describes the process of configuring the server running the SIM software and SQL database to allow client computers running SQL Report Builder remote database access This is useful for accessing full report editing and reporting The following sections describe the changes needed on the server The subsequent section describes how to change the connection strings in the template reports so that these point to the now remote SIM system s
30. nd select SIMdb30 as the database and click OK If used from client machine then connection string has to include IP address of SIM Server and 1433 port number as Server name and also username and password needs to be entered See Section O part b for details External Reports Instruction Guide Prod Code 02580 02 Page 36 of 39 8 10 11 On Data Source Properties window the connection can be tested by clicking Test Connection button Data Source Properties Generar Change name type and connection options Credentials Name Use a shared connection or report model Use a connection embedded in my report User name Select connection type Microsoft SQL Server v Connection string Password _ Save my password Click here to type or paste a connection string Use single transaction when processing the queries If the connection test is successful press the Ok button to return to Choose a connection to data source window With the new connection selected press Next The Design a query window will be displayed This window lists the Tables Views Stored Procedures and table valued Functions of the selected database SIMdb30 Pressing the plus beside the Table will list all the tables available to select for the report Tables can be chosen for reporting by clicking the checkboxes beside the table names If there a
31. re multiple tables selected the Auto Detect button must be clicked to ensure the relationships between the tables are properly selected Run Query can be clicked to see if the data can be properly accessed Click Next gt to select the fields for the report wn li Design a query Build a query to specify the data you want from the data source OD Edit as Text Ey Import P Run Query Database view f amp E ClientRooms E ClientSkinProblems E ClientStatus Iv E CognitiveSkillLevels JJ E Contacts E contactUsers ContinenceAidCapacityTypes E ContinenceAidCategories E ContinenceAidColors ContinenceAidManufacturers E ContinenceAidPadTypes E ContinenceAidProductGroups B 6 H H H 6 IoooooOooOo Query results ClientID ClientGID ClientUID FirstName f UNi 4255 N PF 9333 v U 7962 4 TM 5726 X Tv 3791 OTS aa2b Fe9f CO dN a7a3 3 nm External Reports Instruction Guide Prod Code 02580 02 ClientGID Group and Aggregate X 4 Aggregate none none Relationships Applied filters Field name Auto Detect JEdit Fields 39 X Operator Value DateOfBirth GenderlID 6 08 1933 17 0 1 33 08 2525 Lt 50 96 2013 11 x 1 1O33 ref R U U01 i2 1 20 NA 1 02 gt 12 12 52 2002 12 4 O a Weight Clients Cogniti IsHistorical Photo a i A ov U t Gise lt oinanv gt a0 Fels Binary
32. rf SIMSERVERDYS ReportServer 5IMSERVERDYSTempDb SIMdb30 Database Diagrams Tables views O H H Programs 14 windows Firewall with Advanced Security Control Pamely Configure policies that provide enhanced network security For Windows computers ida Windows Firewall das Allow a program through Windows Firewall daa Check firewall status kag Check security status D See more results firewall i 4 Log off eratan A ea a o ae 14 In the Windows Firewall with Advance Security window first click on Inbound Rules then from the right of the window Actions click New Rule In the pop up window check the Port radio button and click the External Reports Instruction Guide Prod Code 02580 02 Page 29 of 39 Next button Windows Firewall with Advanced Security File Action view Help e m lHa enopes Frenel Mih snes gt anda n Inbound Rules New Inbound Rule Wizard xi E3 Outbound Rules Name Rule Type Bay Connection Security Rules Port 100 E Monitoring Port 888 Select the type of firewall rule to create Oportaoat Oteamvie Steps teamvie Orteamvie tTeamvie Protocol and Ports View BranchC Action Program i Branchc Profil Rule that controls connections for a program Opranchc S GCOM R Rule that controls connections for a TCP or UDP port iv Core Nel v Core Nel iv Core Nel BranchCache Content Retrieval Uses HTTP
33. s SSRS installed 1 Copy the report templates provided by Simavita or available on the server to the client machine By default the reports will be on the desktop on SIM system server The folder name is External Report Templates Start the Report Builder 3 0 on the client computer From file menu open any of the external reports By default the reports will not work as the reports are set up to access the database on same computer Therefore the connection string must now be changed in the report so that it connects to the SIM system server database remotely from the client machine 5 Expand the Data Sources right click on the data source and press Data Source Properties 2 Built in Fields H E Parameters H Images fl Data Sources o d DS SIMdb30Aucg H E Datasets Period StartDate oe Ree ee TT Changed On Add Dataset Delete Data Source Properties 6 Inthe first pop up window click on the Build button In the next pop up window change the server name from SIMSERVERDV3 to lt SIMSERVERIP gt 1433 SIMSERVERDV3 see the screenshot below for an example 8 Select the Use SQL Server Authentication radio button and enter the username and password created for enabling remote access to SIM Server Check the Save my password option External Reports Instruction Guide Prod Code 02580 02 Page 31 of 39 9 Press Test Connection
34. sconcacaasnoncecncasevencncenencseconsascceneneananencccncacencncacanencsesencanenencacanenssccensanencncacassncncncncsnencscecasenensaenannencscecasenenencasaneecocacnsanenencasenensccacanencnencacsenenencanenenescncansncnencanencsescacancaeneneanene Once the Run button is clicked the parameter prompt will be displayed if parameters are needed for the report Once the parameters are selected the report can be viewed by clicking on View Report button Observation List for Resident rdl Microsoft SQL Server Report Builder _ x Run e E 5 KIRI a a eon TO Lal Design Zoom First Previous of Net Last Stop Print Page Views Zoom i ja Back Setup Navigation Print UT DINE s LS WUL 2E2 losie Tesi 12 Dec 2002 eorie Muihcrar 02 Aug 1933 Lukt Faris i4 Noy 2935 7aul rish al 1533 Tesi Mian 95 Dec 1933 iest me ME cu s p Los5 a bet wile 44 22 p is55 External Reports Instruction Guide Prod Code 02580 02 Page 34 of 39 7 2 CREATING A NEW REPORT 1 Tocreate a new report the user needs to open the SQL Report Builder and click on New from the File Menu Care Plan Care Plan In Use Report rdl I z Print Page Setup Print Observation List for Resident rdl ResidentList rdl Facility Details rdl Staff List rdl Audit Report rdl ResidentList rdl Report1 rdl CarePlanReport rdl test rdl T Publish Report Parts o wy Check For Updat oe
35. srersssrrssrrrsssrenssrressrerssreessreessrersererssrressreesseresererssreeseeerese 20 5 1 Adding Reports from Client Computers s sesssessseeessresssreresrrrrererresrrerssrrressrreeseeresreeeeseeree 21 5 1 Adding Reports Directly on Server sssssssssessssrrsesrrresrrrsssreresrrrrssreressrerssrereserereseereserereseeree 22 6 0 Editing and Reporting from Client Computers sssesssssssessreesssressrrreserersrrressrerssrreserersrreessrerssrerssrerssreeseeerese 23 EE EEE n i 5 EEE A E A T E E E E P E T ETA E E E E S 24 62 Changing connection SUNES sapene csn arr NE mabesdonee 31 70 Using SOL Report BuildET vacepsecnpaarististanadudanpase aeeiniaienagnpatehiarspaitink papeaenbenneintanpeidstpbonsinigasheenieinicenteasenianiseeneabesl 33 PAE UNE EEPO a E en eie na etepians 34 Fee Geane a NOW REDO orreina eninin A 35 External Reports Instruction Guide Prod Code 02580_02 Page 3 of 39 1 0 Installing External Reports for SIM Key reports are accessible from within SIM manager via the Reports Tab Additional reports are available external to the SIM system These reports rely on Microsoft s SQL database and report writer tools To access external reports your IT department may need to make changes to the server on which the SIM system operates Instructions on how to set this up are provided in this manual and may need to be modified depending on your facility s specific IT infrastructure The external reports althoug
36. tom grid ensure that for Execute and Select row the checkboxes are checked under the Grant column is checked Fa Microsoft SOL Server Management Studio File Edit view Debug Tools Window Community Help Hi New Query i G A slAn Object Explorer 4 x J p Database oe eos SIMdb30 Connect gm T amp EE E elect a page L5 Script 4 Help E Lf WIN V64BE0DGR 94 SIMSERVERDYS SOL Server 1ODGR94 s ew General Databases LA Files WINVEABKODGRONSIMSERVERDVS Gy System Databases 4 Fiegroups Sener name WIN VEABEODGR 44S IMSERVERDYS Reportserver SIMSERVERDYS Options View Server permissions Reoortserver SIMSERVERDY 3TempDE 4 Change Trackin ea 3 Databaze name SIM db30 5 Ly Simdb30 J Database Diagrams Extended Properties leereoonoles Search Tables views Synonyms IIS Programmability LocalSystern Sects NT AUTHORITYSIUSR Storage Qa Security Ca Users Ca Roles Ca Schemas QI Asymmetric Keys 9 Certificates E Symmetric Keys E T SIMdb304udit Permissions for NT AUTHORITY SUSAR Connection BA Security E xplicit Effective ia Server Objects Carver Replication WIN VBABKODGRS4 SIMSERVE Betmission Evatt aLr Eral Management EE E Execute ia D WIN VBABKODGR94 Administrate Insert Iw C aj View connection properties References Iv E M E Show plan E Ci Subscribe query not dbo E E Take ownership dbo D D Update dbo a C e gt th External Reports Instruction Gu
37. truction Guide Prod Code 02580_02 Page 11 of 39 Upload File Report Manager Internet Explorer JS http 192 168 0 161 R O gt Upload File Report Manager X A K 03 Home Home Site Settings Help SQL Server Reporting Services O Upload File Search e A P Upload a report rdl model smdl shared dataset rsd report part rsc or other Choose File to Upload ES f ur y va File to upload wey _ C Users Publiq Desktop External Report Templates Y Search External Report Templ Name New Item Ea Organize New folder Fil a Overwrite item if it exists a E sr Favorites j Name Date modified Type ME Desktop taal ACFI Report 8 20 2014 2 42 PM Report Builder Repo a Downloads hl Audit Report 1 6 2014 10 03 AM Report Builder Repo a aa ail Continence Schedule Wing Report 7 17 2014 1 49 PM aa Resident Outcome Report 8 19 2014 2 23 PM Report Builder Repo Report Builder Repo Libraries Documents a Music Pictures Ew videos jE Computer ay i ew Local Disk C E 4 gt File name Audit Report fai Files 10 Now the report will show in the SQL Server Reporting Services Homepage Follow the step above to add all the reports to the web reporting system 11 Now any user on client machines can go to address http lt IPADDRESS gt Reports in Internet Explorer and view the reports with the login credenti
38. w connection properties db datareader db_datawriter db_ddladrnin db_ denydatareader db_derydatawnter External Reports Instruction Guide Prod Code 02580 02 Page 17 of 39 24 Double click on NT AUTHORITY IUSR From the second checkbox list Database role membership select db datareader and press OK EEA New Query Lig Lay 27 lel S B Database User NT AUTHORITY IUSR A eve gt s E Ss ia p Sbject Explorer a i Securables A Extended Properties User name NT AUTHORITYSIUSA Connect 32 92 m FT Gls Storage Login name NT AUTHORITY SUSAR aaa El security Certificate name El Users dbo Key name in p quest f without login a ITS Ba i _ INFORMATION SCHEMA Default schema A LocalSyster E NT AUTHORITYIIUSR F SVS Owned Schemas Schemas owned by this user Roles db_secessadmin achemas db_backupoperator Asymmetric Keys Certificates db_datareader Symmetric Keys db_datawriter El J SIMdb30audit db_ddladmin Database Diagrams db_derydatareader ae Connection e db_denydatawriter Synonyms Server Database role membership Programmability WIE VEABEODGR 44S IMSERYVE Bae Mennee setae Bie Connection in db_accessadmin storage WIN VBABEOD GR S4 Administrate E Security o l l db_backupoperator E Uker ay View connection properties db datareader dbo db_datavwiter db_ddladmin INFORMATION SCHEMA db nenpastargader LocalSystem db_derydatayriter NT AUTHORIT
39. web interface This is useful for accessing reports for reporting purposes only no editing is possible e 5 0 Adding External Reports to the Server This section covers access to reports on client machines for reporting and editing e 6 0 Editing and Reporting from Client Computers This section is similar to section 4 0 Client Computer Access for Reporting no editing but includes additional instructions to provide editing capability from the client computer e 7 0 Using SQL Report Builder This section provides a brief primer to writing and running SQL reports using SQL Report Builder Note It is expected that the reader has prior knowledge of SQL configuring an SQL database server and SQL Report Builder before applying any information provided in this document This document is provided as a setup guide only and setup may differ depending on specific IT infrastructure CAUTION Editing or modifying any information in the SIM SQL database may render the SIM system inoperable CAUTION There are important data security implications with providing remote database access This document does not cover or discuss any these Please refer to information provided by Microsoft for further details Pre requisites To view modify or create the external reports Report Builder 3 0 Microsoft SQL Server or Microsoft SQL Server Reporting Services SSRS must installed on the server running the SIM system and or the client machines T
40. y Connecting to 192 168 0 161 IE er name Password Domain WIN V64BKODGR94 EEE E Remember my credentials 7 Enter the Windows login credentials for the server Administrator or the currently logged in server user SQL Server Reporting Services page will come up Home Report Manager Windows Internet Explorer fel E4 Go http 192 168 0 161 Reports O E w bed P ae her nes e FAG Fo Home Site Settings Help F SQL Server Reporting Services EEN Home Search 2 Cy New Folder E New Data Source R Folder Settings t Upload File EE Details View There sre no items in Home Click Help for more information about this page 8 Ifthere is a red message saying This page might not function correctly because either your browser does not support scripts or active scripting is disabled then follow the instruction provided on this link Error htm to enable active scripting for the browser 9 To Add the External Reports to Reporting Services for web access click on the Upload File red box in Step 7 Note that report templates can be downloaded from Simavita s training website In the subsequently displayed screen click the Browse button which will pop up a File Browser window Go to External Report Templates folder on the server s desktop Enter a filename or select a file to replace and press Open and then OK in the Upload File page External Reports Ins

SIM User Manual V14.0

Contents

Download Pdf Manuals

Related Search

Related Contents