to View the User Manual
Contents
1. SQL Deluxe 1 4 User Manual SMOKE RANCH DotNetNuke Modules SQL Deluxe 1 4 User Manual Page 1 SQL Deluxe 1 4 User Manual HAVE UC OU b 3 SO 3 Upgrading an existing ns AMOR es hdi 3 arcup scene nd ASTEAN POES E EE 3 Standatrd Martiis e EN a E EE a EE E A E E E a a EEA 3 PUTS FISSION a Se aden aan eMe eee 3 Enterprise Edition w SODTGS 4 Mod le SB Scale le enese ea aa aa Sedes 4 Force HITES GOHDOCHODS nee A R E Reese 4 Word NOR T en a a a nn 4 ANOW SOS a E EE E EES E E REEE ne 4 Aow paging nn c inasre a nn EEE EEE a E EASES RE SENA ESES Na iE ETEA aens 4 AOW OR AOU mestria e R RE AR RER 4 AOW DT ne E 4 R n Report co Ie RR sini 4 Set default TROIS 4 ROW SO le mi nn ane ni OR ee ae eee nan 4 jg ers au he setae E E E ne a 5 Header Styl MC ase 5 Pager Style M V 5 Cell PAR da T T 5 External Database Connections ns sn sos EEE E A 5 R nning SQL ESI c REEE EA AEAEE RAEE ATER 5 TOKENS en ESE 5 Saving SOL Queries CIR COINS ne a ea Nes 6 Report Propert eS in eu nex ne io E E EEE 7 iip en nn nd nn E M 7 R2. If you execute this query you will notice that all the records that are returned have the same value in the PortalID column Saving SQL Queries Reports Let s walk through an example of saving a query 1 Click the Execute SQL link at the bottom of the module 2 Type the following query into the editor SELECT FROM dnn prefix Users u dnn prefix UserPortals p WHERE u UserID p UserID AND p PortalID dnn portalid Click Execute and verify the query works Click Save as Report For Report Name enter Portal Users For Description enter Returns all users in this portal Click Update Select the newly created query from the drop down list of reports DP ON Da pe Page 6 SQL Deluxe 1 4 User Manual 9 Click Run Report and view the results Now you can run this query at any time directly from the main page of your module If you want to edit the SQL or any other settings simply select the query from the reports list and click the Edit Report link Report Properties The following are descriptions of each property available when creating or editing a report Report ID Read Only This property is only displayed when editing an existing report The report ID can be used with the dnn url token to create a hyperlink to this report from another report Report URL Read Only This property is only displayed when editing an existing report This is the direct URL to this specific report Report Name This is the name of
3. Select your new connection from the list and click Test Connection 5 If your connection does not work click the Edit link and edit the connection string If you still can t get it to work contact the administrator of your portal for assistance to ensure that your portal has visibility to the external database you are trying to access Note that if you want to use a connection string with integrated security you must give the ASP NET Identity account listed in Host Settings proper database permissions 6 Now that you have created your connection you can select it whenever you write an SQL query Running SQL Queries The simplest use of the SQL Deluxe module is as a replacement for the intrinsic DNN SQL module found under the Host menu For an example follow these steps 1 Click the Execute SQL link at the bottom of the module 2 Type the following query into the editor The text in blue represents a Token more about this later SELECT FROM dnn prefix Modules 3 Click Execute At this point you should see the contents of the Modules table from your portal displayed in a grid below If you have not disabled paging and sorting in the module settings you can click on the column headers and also page through the results if the record count exceeds the page size Read more about Tokens in the next section Tokens Page 5 SQL Deluxe 1 4 User Manual Tokens are placeholders that represent dynamic values in you
4. PONSE H 7 Report Name a E EO E E OERA 7 Report Descrip Oi soere Net E ae re Mum ere reenter 7 Report b sinite ERSS 7 R port H ad t RP ena 7 Rep rt Footer M aneo EA aw eae 7 HEME corvi aame n E E AE E E EAE EA E eee ee 7 Hide Report Header COMORES Ne needs 7 Aut ERC CUNE senisesse 8 Creating Interactive REPOTI Sessie and de nan idees 8 TMG rach ve TOURS ENSE TETTE 0 E 00S 8 Formatting Reports ans 8 Mod le Set I RS inea eiaa ae aaaeaii ea eee aaae iS 9 Report Header and POOIGE u nn Gaudio nS hi iue endi neue 9 Format Individual Columns or Cells send nn ep oe ta pde eU Ru eee it 9 Creating Drill Down Rep Otte esseiste cup ten Or ie iter tnde ia 9 ETHIC 10 Feedback Contact US Set 10 Page 2 SQL Deluxe 1 4 User Manual Introduction The Smoke Ranch SQL Deluxe module for DotNetNuke is a robust module that offers many features beyond the basic capabilities of the DotNetNuke SQL module found under the Host menu These features include e Automatic sorting and paging e The ability to save and manage your SQL scripts e Tokens insert tokens into your SQL code to dynamically represent common values such as the current User ID or the current Portal ID These tokens
5. are automatically replaced with the correct values when the SQL is executed e Create interactive parameterized reports allow the user to enter data which will be dynamically as parameters to your SQL queries e Connect to multiple databases Installation 1 Login as Host 2 Go to Host gt Module Definitions 3 Choose Install New Module 4 Browse to the zip file containing the module Note If you purchased the source code version of the file you will need to extract the zip containing the PA first the PA zip is the file you upload here 5 Click Install New Module or Save File in versions earlier than 4 5 1 That s it Now you can add the module to any location in your portal Warning We recommend that you only allow Host or possibly Admin access to this module as anyone who uses the module will have complete access to the database Upgrading an existing installation If you are installing a new version of the application simply follow the installation steps in the previous section Do not uninstall the existing module first otherwise your saved reports and other data will be deleted Licensing Standard Edition The Standard license gives you the right to use the module on a single portal installation Enterprise Edition The Enterprise license gives you the right to install the module on as many portals and machines as you want within your organization Currently the only difference between
6. Standard and Enterprise is in licensing although this may change in future releases and or patches It is likely that in the future the Page 3 SQL Deluxe 1 4 User Manual Enterprise Edition will gain features that the Standard Edition will not have Enterprise Edition w Source This option provides all the benefits of an Enterprise license with the addition of full source code This consists of a Visual Studio 2008 solution and C project What if I bought the Standard and want to upgrade to Enterprise or I bought Enterprise and now I want to purchase the source code Please just email us and we ll send you a discount code so you won t have to pay full price Send emails to info smokeranch com Module Settings The following are a description of each configuration option in the module settings These settings can be found under Module Settings gt SQL Deluxe Settings Force HTTPS connections If this box is checked the module will not display any data unless an SSL https connection was used Word wrap When checked display grid data will wrap inside the table cells when necessary note that data only wraps if it contains spaces Allow sorting If checked users can click on the column headers in the display grid to sort the data Allow paging If checked the display grid will only show the number of rows per page that you indicate If not checked the grid will show all the results in one long list Allow export If th
7. ays Hide header report controls 6 Click Update 7 Now the report is saved and you should be back at the main view of the module with the new Module Settings report selected in the drop down list Click Edit Report and take note of the Report ID which you will need to know when creating the master report Second create the master report Page 9 SQL Deluxe 1 4 User Manual 1 From the main page of the module click Create Report 2 Name the report Users 3 Enter the following SQL for the definition NOTE insert the correct report id from step 7 in the instructions above in the dnn url token For example if the ID was 10 then the token will look like dnn urll10 SELECT UserID Username FirstName LastName a href dnn url 1 userid ltrim str UserID gt Profile lt a gt as Profile FROM dnn prefix Users order by Username 4 Uncheck the box that says HTML Encode the report data 5 Check the box that says Hide header report controls 6 Click Update After these two reports are setup go to the module settings and select the Users report as the default report Now visiting the module will automatically execute the report and it will behave like a standard master detail report When viewing the report you may notice that it is necessary to hit the Back button on your browser to return to the master report when viewing the details To prevent this simply define your own hyperli
8. hat includes an explicit name so you can reference it from your CSS classes Format Individual Columns or Cells By default any data displayed in the report results table is HTML encoded This means that any HTML content will be displayed to the screen as the literal text so for instance if you have a field with a value of lt b gt My Bold Text lt b gt then on the screen you will see lt b gt My Bold Text lt b gt instead of seeing the enclosed text actually bolded To change this uncheck the box that says HTML encode the report data With the box unchecked you can work HTML formatting into your reports Here is an example query that lists all the usernames from the Users table but highlights the Superusers in bold blue text select case ISSuperUser when 1 then font color blue gt lt b gt Username lt b gt lt font gt else Username end as Username from dnn prefix Users Creating Drill Down Reports This example will demonstrate the creation of a very simple report that lists all the users in a portal Clicking on a hyperlink in the report will drill down to the detail report that will list the profile fields for that user First create the detail report Place an instance of the SQL Deluxe module on a page 2 Click Create Report 3 Name the report User Profile 4 Enter the following SQL for the definition SELECT from dnn prefix Users where UserID dnn prm userid 5 Check the box that s
9. is box is checked then the user viewing a report will be able to export its contents as a CSV file Allow print If this box is checked then the hyperlink to view the report will be visible Run Report Caption This setting allows the text for the Run Report hyperlink to be customized Set default report If a default report is selected here then this report will be automatically selected when visiting the module The user will still be able to select other reports if they exist Row Style This is the css class name to apply to each row in the report grid Page 4 SQL Deluxe 1 4 User Manual Alt Row Style This is the css class name to apply to alternating rows in the report grid Header Style This is the css class name to apply to the header row in the report grid Pager Style This is the css class name to apply to the paging hyperlinks in the report grid Cell Padding This is the cell padding to be applied to the report grid External Database Connections If you want to run queries against external databases any database other than the one that powers your DNN portal you will have to add a new connection To add an external database connection follow the steps below 1 Click the Manage External Connections link at the bottom of the module 2 Add the Name and Connection String in the appropriate text boxes 3 Click Add Connection 4 The connection now shows up in the drop down list titled Existing Connections
10. ly executed Selecting the report in this case refers to either selecting it from the drop down list or accessing it directly via its direct URL Creating Interactive Reports The most powerful feature of the SQL Deluxe module is the ability to create interactive reports Interactive reports can require the user to enter a value in a text box or select from a drop down list or list box in order to execute the report Drop down lists and list boxes can have a fixed set of choices or can pull from the database Let s see a simple example 1 Click the Execute SQL link at the bottom of the module 2 Type the following query into the editor SELECT FROM dnn prefix Users 3 Click Execute and verify that it works 4 Click Save as Report 5 Now we want to change this query to only return the results for a specific user Modify the SQL so that it looks like this SELECT FROM dnn prefix Users WHERE Username dnn textboxlEnter a username In the Report Name box enter User Info In the Description box enter Displays user info for the specified user Click the Update link Select the new report from the drop down list 10 Type admin or another valid username into the text box 11 Click Run Report Oo 90 There you go You have now created a very simple interactive report This report used an Interactive Token to allow user input Read more about Interactive Tokens in the next section Interactive Tokens The interacti
11. nk in the Header field of the User Profile report Tech Support Smoke Ranch Software offers free email tech support for all modules If have a tech support issue please send email to support smokeranch com Alternatively if you purchased the module from Snowcovered you can open a Help Desk ticket The current version of all our user manuals can be found at our web site at http www smokeranch com Feedback Contact Us For feature requests custom development requests or general questions send email to info smokeranch com If you like our modules please consider giving them a positive review in the store where you purchased them Thanks Page 10
12. r SQL The query above included a Token dnn prefix This token represents the DatabaseOwner and ObjectQualifier values that your portal is configured to use When you click Execute the correct values are substituted for the Token just before the SQL is actually executed For instance if your database owner is dbo and the object qualifier is empty then the actual SQL that gets executed will look like this SELECT FROM dbo Modules Note that we could have also written this query another way SELECT FROM databaseOwner objectQualifier Modules If you are familiar with the database scripts that DNN modules use you are likely familiar with this syntax Also if your portal does not use an ObjectQualifier setting you may be able to just leave off the prefix altogether SELECT FROM Modules For convenience the module provides a prefix token that you can use in place of typing databaseOwner objectQualifier If your database uses a custom object qualifier or you are unsure simply use the dnn prefix token before the names of any objects tables views stored procedures etc and it is handled automatically Tip To see additional tokens you can use click the View Available Tokens link Let s try another example Enter the following text into the editor SELECT FROM dnn prefix Modules WHERE PortalID dnn portalid Note that this time we used an additional token to represent the ID of the portal we are logged into
13. the report and will be displayed in the drop down list of reports Report Description Optional This description will be displayed under the drop down list when the report is selected Report Definition This is the SQL that makes up the query The SQL can contain embedded tokens See token definitions at the bottom of the page Report Header This field can contain any arbitrary text up to 3000 characters which will be displayed above the report results when the report is executed The text can be plain text or HTML Report Footer This field can contain any arbitrary text up to 3000 characters which will be displayed below the report results when the report is executed The text can be plain text or HTML HTML Encode If this box is checked then all report data in the results grid will be HTML encoded for display Uncheck this box if you want to be able to embed HTML in your data for example to format cell text or create hyperlinks Hide Report Header Controls If this box is checked then when viewing this report none of the controls at the top will be visible This includes the drop down list for selecting reports the link button for executing the report sorting and paging options etc Note that this setting only applies when you are in View mode in Edit mode these controls are always visible Page 7 SQL Deluxe 1 4 User Manual Auto Execute If this box is checked then upon selecting the report it will be automatical
14. ve report created in the preceding example used what we call an Interactive Token to create the text box Interactive Tokens are tokens that require user input when the report is run Tokens that simply get replaced with another value automatically such as the dnn portalid token are called Automatic Tokens Note that Interactive Tokens can ONLY be used when creating a saved report they cannot be used directly on the Execute SQL page Tip View the documentation at the bottom of the report creation screen for a list of the available Automatic and Interactive tokens along with examples of their usage Formatting Reports There are several options when formatting the look of reports Page 8 SQL Deluxe 1 4 User Manual Module Settings The values on the Module Settings page apply to all reports and when executing SQL queries on the fly Here you can select the CSS classes to use for the HTML table that contains the report results See the Module Settings section for the complete list of available settings Report Header and Footer Each report allows you to specify the Header and Footer text The text will appear above and below the report results table These fields allow you to enter in HTML so you can include images or any other kind of formatted text Even if you don t want any content to show up in the header and footer area you can use these fields to add explicit style declarations or to wrap the report in a lt div gt tag t
Download Pdf Manuals
Related Search