Quick Reports Reference Guide

Summary

Quick Reports is a module for SalesPad GP. Its primary purpose is to provide a quick mechanism to create and run simple reports based on SQL queries and user input. End users can view reports via the Quick Reports Viewer or via the Dashboard Viewer. At this time report writing must be performed with an external (text or XML) editor. The purpose of this Reference Guide is to provide report writers with the syntax (tags and attributes) that can be used to create a Quick Report.

Quick Reports XML Reference Purpose 1

The <report> tag is the required root element in a Quick Report.

Attributes
Attribute Name Description Value Options
Name The name or short description of the Quick Report (string value)
autoRun A Boolean telling the Quick Reports viewer to automatically run the report when it is loaded. True, false
DevExpress The file name of DevExpress report filed tied to this Quick Report (usually *.repx). The file name
should not include a path, and the file needs to be in the same folder as the Quick Report file.
(string value)
HideUndeclaredColumns Set this to true to hide the columns from the query unless they are declared with a <column> tag that
has a Visible attribute set to true.
 
AutoLinks A Boolean telling the Quick Reports viewer to turn on/off the AutoLinks feature. SalesPad GP
contains the following AutoLinks (the report should return the columns in the 2nd column to enable
the desired AutoLink): Customer – Customer_Num Item – Item_Number SalesDocument – Source,
Sales_Doc_Type, Sales_Doc_Num
 
GroupFooterShowMode    
ShowAutoFilterRow    
ShowFooter    
UsePivotTable   True, false
ViewerName   PivotGridViewer
Child Tags

<description>, <search>, <query>, <column>, <compare>, <gridlayout>, <contextMenuItem>, <dropScript>

Example

<report name="All Quote Line Items And Quantities" autoRun="false" HideUndeclaredColumns="false" AutoLinks="false">

Purpose 2

The <description> tag is optional. It should contain a description of the report.

Attributes

None.

Child Tags

None.

Example

<description Text=”Text Description, this doesn’t show anywhere” />

Purpose 3

The <search> tag is used to describe a user input. The viewer will create an input box that the end user can populate and the query can use the input to filter data.

Attributes

>=, <, <=

Attribute Name Description Value Options
Name The name/label of the input box that will be displayed to the user (string value)
Column The name of the column or parameter that will be used in the report query. The column name should be
preceeded by an @ symbol if the query is a stored procedure.
(string value)
searchOp The search operator used by Quick Reports to build the report query. (any valid SQL operator like the following? like, =, <>, >,
Default Value The default value for the input field (string value)
PromptWidth   i.e. “200”
Type   DateTime,
Child Tags

None.

Example

<search name=”Item_Number” column=”Item_Number” searchOp=”like” />

Purpose 4

The <query> tag should surround the SQL query (or stored procedure) for the report.

Attributes
Attribute Name Description Value Options
addWhere Tells Quick Reports to add (or not add) the “where” keyword to the query when running the SQL. True, false
Inner Text

The SQL query can take the form of a raw query or a stored procedure call. A call to a stored procedure should be preceded by the "exec" keyword.

If you are using <search> tags to get user input, Quick Reports will build a "where" clause or pass in parameters to a stored procedure. To manually specify the location of the "where" clause, you can use the key word “/*where*/".

Example:

<report name="Customer List">
<search name="Customers Named Like:" column="CUSTNAME" searchOp="like"/>
<query addWhere="true">
<![CDATA[
select TOP 10 * into #tmp from RM00101 as Customers /*where*/ select * from #tmp where CreatedOn>’1/1/2007’
]]>
</query> </report>

Note: Because this query is contained in an XML file, appropriate XML conventions must be used; greater-than and less- than symbol escape codes must be used (&gt;@lt;) unless you embed the query in a <![CDATA]]> section (see example above).

Child Tags

None.

Example

<query addWhere=”true”>
<! [CDATA[
select * from (
select sli.Sales_Doc_Num, sli.Item_Number, sli.Quantity from spv3SalesLineItem
as sli
where sli.Sales_Doc_Type = ‘QUOTE’
)
as a
/*where*/
] ]>
</query>

Purpose 5

The <column> tag is used to format a column returned by the query and displayed with the Quick Reports grid viewer.

Attributes
Attribute Name Description Value Options
Name The name of the column returned by the query (string value)
Caption The caption to display in the grid column header (the ^ symbol can be used to break a caption into multiple lines) (string value)
BestFit Tells the grid viewer to “best fit” the column caption and data True, False
Sort Tells the grid viewer to sort the column’s data Ascending, Descending
DisplayFormat Describes the format of the column’s data (.NET string format syntax) (string value)
Band The name (and caption) of the band that will be created and in which the column will appear in the grid viewer (string value)
SummaryType The summary type to display for a column in the footer of the grid. If the “Custom” value is used, you should also specify the CustomSummaryType attribute. DisplayFormat is a required attribute when you use the CustomSummaryType attribute True, False
Visible Can be used to hide a column (the column is visible by default and will not be hidden unless the attribute is specified with a “false” value) True, False
CustomSummaryType Describes the custom summary to be used in the grid footer for the column Margin% (you must specify the SalesColumn and the CostColumn attributes)
SalesColumn The name of the column that contains the sales number for use by the “Margin%” (string value – column name)
CostColumn The name of the column that contains the cost number for use by the “Margin%” (string value – column name)
GroupSummaryType   Average, Sum, Min, Max, Count
Group   True, false
GroupDisplayFormat Describes the format of the column’s data (.NET string format syntax) (string value) Note: Only works with Average, Sum, Min, and Max as it should return integer value. Must use GroupSummaryType to access this item.
Condition   Equal, Less, Greater, true
ConditionVal1   (string value)
ConditionBackColor   .NET System.Color enum i.e. Red, Orange, Yellow, Blue, Green, White
ConditionToApplyRow   True
ConditionForeColor   .NET System.Color enum i.e. Red, Orange, Yellow, Blue, Green, White
ConditionFontStyle   Regular, Bold, Italic, Underline, Strikeout
ConditionFontStrikeout   True
ConditionFontSize   12.5, 14, etc.
ConditionFontName   Arial
PivotArea   RowArea, DataArea, FilterArea, ColumnArea
SalesDocXML    
Examples

<!------ Sets the color of the row without any conditions ------>

<column name="Sales_Doc_Num" Condition="true" ConditionApplyToRow="true"

ConditionFontName="Arial" ConditionFontSize="10.5" ConditionFontStrikeout="true"

ConditionBackColor="Blue" ConditionForeColor="White"/>

<!------ More Options for setting fonts on a column ------>

<column name="Item_Number" Condition="true" ConditionApplyToRow="true" ConditionFontName="Times New

Roman" ConditionFontSize="16" ConditionFontStyle="Bold" ConditionBackColor="White"

ConditionForeColor="Orange"/>

<!------ If Quantity Equals 1 then it will display the following condition colors ------>

<column name="Quantity" Condition="Equal" ConditionVal1="1" ConditionApplyToRow="true"

ConditionBackColor="Black" ConditionForeColor="White" DisplayFormat="#,##0.#####"/>

<!------ If Quantity Is Greater Than 1 then it will display the following condition colors ------>

<column name="Quantity" Condition="Greater" ConditionVal1="1" ConditionApplyToRow="true"

ConditionBackColor="White" ConditionForeColor="Black"/>

Child Tags

<update>

Purpose 6

The <update> tag is used to specify a stored procedure name (and parameters) that will be called after a change is made to data in the Quick Report (in the <update> tag’s parent column). Currently, only a stored procedure may be specified, and the procedure name and parameters must be enclosed with the corresponding child tags (see below).

Attributes

Attribute Name Description Value Options

ReturnVal The name of the column to update if a value is returned from the stored procedure.

Child Tags

None.

Script Tags
Script Tag

<OnRunScript>

p1.ExpandAllGroups
(); p1.CollapseAllGroups();

</OnRunScript>

Note: P1 is the name of the grid view. The OnRunScript tag allows you to call the grid view methods to update it.

<!--

On Right Mouse Click Gives option of New, This Opens New CRM Task Window Following fields are available:

val_Assigned_To
al_Changed_By
val_Changed_On
val_Completed_On
val_Contact_Name
val_Created_By
val_Created_On
val_Customer_Name
val_Customer_Num
val_Description
val_Duration
val_Ends_On
val_Event_ID
val_Is_Complete
val_Remind
val_Remind_On
val_Reminder_Enabled
val_Sales_Person_ ID
val_Starts_On
val_Status
val_Subject
val_Type
val_UserField

-->

<contextMenuItem text="New CRM Event...">

SalesPad.Bus.CRMEvent crmEvent = new SalesPad.Bus.CRMEvent(); crmEvent.val_Subject = "Call List Item - From Quick Report"; crmEvent.val_UserField = "Call List Created - CRM Item To Do"; crmEvent.val_Description = "Sample Note";
crmEvent.val_Customer_Num= "AARONFIT0001"; crmEvent.val_Customer_Name= "Aaron Fitz Electrical";
crmEvent.val_Reminder_Enabled= true; crmEvent.val_Remind= "5 Minutes Before";
System.DateTime today = System.DateTime.Now; System.DateTime answer = today.AddDays(30);
crmEvent.val_Starts_On = answer; crmEvent.val_Ends_On = answer.AddHours(1);;
crmEvent.val_Created_By = "STEVE"; crmEvent.val_Created_On = System.DateTime.Now;
SalesPad.Args.CRMEventEntryArgs args = new SalesPad.Args.CRMEventEntryArgs(crmEvent, null);
System.Framework.Controller.StartPlugin(args);

</contextMenuItem>

Script Tag

<OnRunScript>

System.Data.DataView dv = new System.Data.DataView(p1.GridControl.DataSource as System.Data.DataTable);
dv.RowFilter = "Created_By = '" + System.Framework.Controller.Credentials.UserName +
"'";
//System.Windows.Forms.MessageBox.Show(dv.RowFilter); p1.GridControl.DataSource = dv;

</OnRunScript>

Note: This script is used to only display results where the “Created By” name is the same as the “Username” currently logged into SalesPad.


Back to Reporting

Have a question or request?

Leave Feedback