Menu

Service Help – DataSource

The Sharperlight service offers a wide variety of presentation methods, each with its own set of parameters.

In this post, we will introduce the following method from among the various presentation methods available.

http://abcd.com/mdService1Rest/DataSource/…

DataSource
To see this information with your sharperlight service help
{sharperlight service url}/Help#DataSource
Please note that dates are returned in yyyy-mm-dd format and binary data such as Pictures are encoded into base64 strings
query=[Group.Code] The Published Report Group and Code.
e.g /DataSource/?query=ADVW.Sample for a list of Published Reports use query=publishlist and for Entry Forms use query=entrylist
dfmt=xml Get data as XML
dfmt=xml2 Get data as detailed XML
dfmt=xlsx Get data as Excel xlsx format
dfmt=csv Get data in CSV format with UT8 encoding.
The default is comma delimited with Quotes and NewLine.
There are also options like &csvDelimiter=, and &csvQuotes= and &csvNewLine=
dfmt=csv_ansi Get data in CSV format with server ANSI encoding
The default is comma delimited with Quotes and NewLine.
There are also options like &csvDelimiter=, and &csvQuotes= and &csvNewLine=
dfmt=csv_bom Get data in CSV format with server with a BOM header for UTF-8 encoding which works with Excel
The default is comma delimited with Quotes and NewLine.
There are also options like &csvDelimiter=, and &csvQuotes= and &csvNewLine=
csvDelimiter=[,|comma|tab|space|fixed|blank|char] The CSV delimiter which defaults to comma.
e.g. &csvDelimiter=tab

If a fixed width CSV is required then suffix the output names with _FIX## where ## is the length of the column.
If the suffix is not set then the widths are auto calculated based on the data types and length.

e.g Account_FIX75 or Amount_FIX24
csvQuotes=[|blank|quotes|char] The CSV data quote protector which defaults to double quotes.
e.g. &csvQuotes= or &csvQuotes=blank or &csvQuotes=’
csvDecSep=[,|.|dot|comma|char] The CSV number decimal separator with defaults to . or dot For example when dealing with French or Dutch use &csvDecSep=comma
csvNewLine=[crlf|cr|lf|lfcr|blank|char] The CSV row new line chars which defaults to crlf.
e.g. &csvNewLine=cr or &csvNewLine=crlf
dfmt=json Get data in JSON format
Note if there is an array of data you should use jsonarray instead or put square brackets around the result yourself

                    
{"f1": "Foo", "f2" : 1234.76, "f3":"2004/07/31", "f4": "2004/07/31 10:45:33"}
dfmt=jsonarray Get data as a JSON array
Note if you want to get the Published Report or Query schema and property details see the dcat parameter.
Also see dfmt=jsonp for callbacks and jsondates=iso8601 for date formats
                    
[
    {"f1": "Foo", "f2" : 123.88, "f3": "2004/07/31"},
    {"f1": "Foo2", "f2": 1567.23, "f3": "2004/07/31", "f4": "2004/07/31 10:45:33"}
]

To get the jsonarray but with field names based on the Query Output names add &dcat=UseNames

http://abcd.com/mdService1Rest/DataSource/?query=publishlist&dfmt=jsonarray&dcat=UseNames&jsondates=iso8601

Get a list of Published Reports

http://abcd.com/mdService1Rest/DataSource/?query=publishlist&dfmt=jsonarray

 

Get a list of Published Reports for group ADVW

http://abcd.com/mdService1Rest/DataSource/?query=publishlist&dfmt=jsonarray&group=ADVW


If Totals and Sub Totals are required in the Data use the &dtotals=1&drowcat=1 

An additional column called fcat will appear when drowcat=1 is used and this column will have metadata about the row category {T:} = Total Row  {S:1} Subtotal level 1 {S:#} Subtotal level # etc
formatting tags will also appear and these are used for conditional formatting of background colors, fonts and values etc
_format:coltext[1,1]=Sub Total|clear[4,4]
These start with _format: and are separated by a | char

                    
{"fcat":,"f1":2004,"f2":0,"f3":"north america","f4":"2004\ />01","f5":null,"f6":null,"f7":null},
{"fcat":,"f1":2004,"f2":1,"f3":"North America","f4":"2004\/03","f5":null,"f6":null,"f7":null},
{"fcat":{S:2}_format:coltext[1,1]=|coltext[4,4]=|coltext[5,5]=|coltext[8,8]=,"f1":null,"f2":13,"f3":"North America","f4":null,"f5":null,"f6":0,"f7":0},
{"fcat":{S:1}_format:coltext[3,3]=|coltext[4,4]=|coltext[5,5]=|coltext[8,8]=,"f1":2004,"f2":26,"f3":null,"f4":null,"f5":null,"f6":0,"f7":0},
{"fcat":{T:}_format:coltext[1,1]=Sub Total|clear[3,3]|clear[4,4]|clear[5,5]|clear[8,8],"f1":null,"f2":134,"f3":null,"f4":null,"f5":null,"f6":0,"f7":0},
{"fcat":{T:},"f1":Grand Total,"f2":134,"f3":null,"f4":null,"f5":null,"f6":0,"f7":0}
dfmt=jsonp Get data as a callback JSONP array.
You can specify the callback function name by using &jsonpfunc=MyfuncName

if jQuery is being used the parameter callback parameter is also respected 

                    
func( [{"f1": "Foo", "f2" : 1200.76, "f3": "2004/07/31"}, {"f1": "Foo2", "f2": 653.64, "f3": "2004/07/31"}] )


http://abcd.com/mdService1Rest/DataSource/?query=publishlist&dfmt=jsonp&dcat=UseNames
jsondates=iso8601|iso9601m|js|jsunix|rfc2822|simple Specifiy the JSON date time format to use e.g. jsondates=unix
If this parameter is not set then date and date time data will be returned using the default format of “YYYY/MM/DD” or “YYYY/MM/DD HH-MM-SS”
If a invalid value is passed then it will default to iso8601

jsondates=
{“f1″:”2018/05/27”, “f2″:”2018/05/27 09:54:36”}


jsondates=iso8601
{“f1”: “2018-05-27T00:00:00Z”, “f2”: “2018-05-27T08:56:49Z”}


jsondates=iso8601m
{“f1”: “2018-05-27T00:00:00.000Z”, “f2”: “2018-05-27T08:56:49.761Z”}


jsondates=rfc2822
{“f1”: “Sun, 27 May 2018 00:00:00 GMT”, “f2″:”Sun, 27 May 2018 10:54:23 GMT”}


jsondates=js
{“f1”: new Date(2018,4,27) , “f2”: new Date(2018,4,27,9,23,17,761)}


jsondates=jsunix
{“f1”: new Date(1527350400000), “f2”: new Date(1527384315904)}


jsondates=simple
{“f1″:”2018-05-27”, “f2″:”2018-05-27 09:05:48”}
drowcat=[1|0] Show the Row Category meta data that holds information about if the Row is a Total, Sub Total and also any conditional formatting details

See the section on dfmt=jsonarray for an example 
                    
[
{"fcat":,"f1":"2004\/06","f2":6728034.9873,"f3":487097.5517,"f4":152218.0362},
{"fcat":,"f1":"2004\/07","f2":56178.9223,"f3":4067.2504,"f4":1271.0419},
{"fcat":{T:}_format:coltext[1,1]=Total:,"f1":null,"f2":140707584.8246,"f3":10186974.4602,"f4":3183430.2518}
]
dfmt=text Get data in Text format, uses Tab as separator and New Line
dfmt=text2 Get data in Text format including schema information, uses Tab as separator and New Line

Schema format is basically attribute1=value|attribute2=value|..attribute(n)=value separated by tabs

UniqueCode=/TotDue:Sum|DataTypeBase=Number|DataType=Currency|ShowTotal=Sum|Format=|Other=|Caption=Total Due|Header=Sales

dfmt=bzip2 This is basically the same format as dfmt=text2 but the data is bzip compressed to reduce the size
This can speed up download times by quite a bit on large datasets. 

                    
public static string DecompressMemoryBZip2(byte[] gzBuffer)
{
    System.IO.MemoryStream ms = new System.IO.MemoryStream(gzBuffer);
    ms.Position = 0;
    SharpCompress.Compressor.BZip2.BZip2Stream zip = new SharpCompress.Compressor.BZip2.BZip2Stream(ms, SharpCompress.Compressor.CompressionMode.Decompress);

    System.IO.MemoryStream outStream = new System.IO.MemoryStream();
    int ch = zip.ReadByte();
    while (ch != -1){
        outStream.WriteByte((byte)ch);
        ch = zip.ReadByte();
    }
    outStream.Position = 0;
    return System.Text.Encoding.UTF8.GetString(outStream.ToArray(),0,(int)outStream.Length);
}
dfmt=cache Used to force a cache dataset to refresh
dcat=PublishProperties Get the various Properties of the Published Report in the specified Format jasonarray, csv etc instead of the Data.
e.g. &dfmt=jasonarray&dcat=PublishProperties

The Properties will included will be things like the Report Title, Formatting, Colors and URL links to the other details like where to get the data or Query schema details.

The result set will contain just two columns “code” and “value”

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=jsonarray&dcat=publishproperties

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=xml&dcat=publishproperties 

                    
[
    {"code":"reporcodeunique","value":"test.xyz"},
    {"code":"reporCode","value":"Test"},
    {"code":"reporGroup","value":"XYZ"},
    {"code":"reportEnabled","value":"true"},
    {"code":"reportDefaultType","value":"Table"},
    {"code":"reportLanguageCode","value":"ja"},
    {"code":"reportCultureCode","value":"ja-JP"},
    {"code":"reportTitleMenu","value":"Title Goes Here"},
    {"code":"reportTitleReport","value":"15/04/2012 Report Table Title"},
    {"code":"reportTitleAlign","value":"Center"},
    {"code":"reportTitleFontSize","value":"21.75"},
    ....
]


dcat=QueryFilters Get the various Query Filter Properties of the Published Report in the specified Format jasonarray, csv etc instead of the Data.
e.g. &dfmt=jasonarray&dcat=QueryFilters

The Properties will included will be things like the Filter Name, Description, Data Type and URL links to the data and Lookups

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=jsonarray&dcat=QueryFilters

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=xml&dcat=QueryFilters 

                    
[
    {
        "codePath":"/Name",
        "name":"Name",
        "description":"Name",
        "dataTypeBase":"String",
        "dataType":"String",
        "isSingleValuesOnly":"False",
        "isMandatory":"False",
        "isPassword":"False",
        "cultureCode":"en-US",
        "defaultValueFrom":"<\u003d",
        "defaultvalueto":"zzzz",
        "urlLookup":"http://abcd.com/mdService1Rest/Table/?query\u003dDrillZeros&qmode\u003dlookup&usid\u003df097b22e9a4b4b98b4c3a2a580680763Z&lookupcode\u003dfltName",
        "urlLookupDataSource":"http://abcd.com/mdService1Rest/DataSource/?query\u003dDrillZeros&qmode\u003dlookup&usid\u003df097b22e9a4b4b98b4c3a2a580680763Z&lookupcode\u003dfltName",
        "urlLookupDataSourceJsonArray":"http://abcd.com/mdService1Rest/DataSource/?query\u003dDrillZeros&qmode\u003dlookup&usid\u003df097b22e9a4b4b98b4c3a2a580680763Z&dfmt\u003djsonarray&lookupcode\u003dfltName"},

        ....
    }
]
dcat=QueryOutputs Get the various Query Output Column Properties of the Published Report in the specified Format jasonarray, csv etc instead of the Data.
e.g. &dfmt=jasonarray&dcat=QueryOutputs

The Properties will included will be things like the Output Id, Code, Description, Data Type, Grouping and Formatting details

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=jsonarray&dcat=QueryOutputs

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=xml&dcat=QueryOutputs 

                    
[
    {"Id":"f1","code":"/HirDat:DateYearMonth","dataTypeBase":"String","dataType":"String","description":"Hire Year/Month","header":"",
                    "group":"true","showNulls":"false","showTotals":"None","format":"",
                    "formatOptons":"","attributes":"","other":""},
    {"Id":"f2","code":"BirDat","dataTypeBase":"Date","dataType":"Date","description":"Birth Date","header":"Employee",
                    "group":"false","showNulls":"false","showTotals":"None","format":"{0:dd/MM/yyyy}",
                    "formatOptons":"","attributes":"","other":""},
    {"Id":"f3","code":"/Name","dataTypeBase":"String","dataType":"String","description":"Name","header":"Employee",
                    "group":"false","showNulls":"false","showTotals":"None","format":"",
                    "formatOptons":"","attributes":"","other":""},
    {"Id":"f4","code":"/VacHou","dataTypeBase":"Number","dataType":"Integer","description":"Vacation Hours","header":"Employee",
                    "group":"false","showNulls":"false","showTotals":"None","format":"{0:#,###,##0.0;(#,###,##0.0)}",
                    "formatOptons":"[ShowNegAsRed]","attributes":"","other":""}
]

Id: The unique column ID that is used to map to output columns in the dataset
Code: Unique code for output column
dataTypeBase: public enum dataTypeBase
{
    None, String, Number, Date, Time, Binary, Other
}
dataTypeBase: public enum dataTypeBase
{
    None, String, Number, Date, Time, Binary, Other
}
dataType: public enum dataType
{
   None, String, Integer, Decimal, Currency, Boolean, Percentage, Date, DateTime, Time, TimeSec,
   Period, Binary, BinaryPicture, BinaryDocument, BinarySound, BinaryVideo, GUID, URL, HTML, Icon
}
public enum showTotals
{
   Auto, None, Sum, Average, Count
}
showTotals: public enum showTotals
{
   Auto, None, Sum, Average, Count
}
description: The column description
header: The column header description  (not used by most reports)
showNulls: If true null values are displayed as <null> instead of blank
formatOptons: [ShowNegAsRed]: Show Neg Numbers as Red
[HideZeros]: Hide Number if Zero
[NumSepDecReversed]: When Spanish, French etc the comma and decimal separator should be reversed 10,123.56 => 10.123,56
format: Numbers: {0:#,###,##0.0;(#,###,##0.0)}   where ; sepates the positive format and negative format (see .NET formatting)
Dates: {0:dd/MM/yyyy}   See .NET date formatting options
other: futrue use



dcat=QueryDrillThroughs Get the various Query Output Column Properties of the Published Report in the specified Format jasonarray, csv etc instead of the Data.
e.g. &dfmt=jasonarray&dcat=QueryDrillThroughs

The Properties will included will be things like the Drill Through Id, Description, Media, Category and URL Links

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=jsonarray&dcat=QueryDrillThroughs

http://abcd.com/mdService1Rest/DataSource/?query=Group.Code&dfmt=xml&dcat=QueryDrillThroughs 

                    
[
    {"category":"BindToRow","ID":"db029209-c9aa-46b8-9e5f-6e95c7704436","code":"D1","titleMenu":"New Drill Through",
                                "titleReport":"report title {@Name}{%BirDat}",
                                "contentType":"Query","media":"TableBasic","urlLinkBase":"",
                                "urlLinkParams":"",
                                "splitOnQueryFilterName":""},
    {"category":"BindToRow","ID":"ca489d18-25b7-4c14-b5aa-5ff42fd912a6","code":"D2","titleMenu":"New Drill Through",
                                "titleReport":"",
                                "contentType":"Link","media":"Default","urlLinkBase":"http://www.google.com/search",
                                "urlLinkParams":"?q\u003dsharperLight&fieldcode\u003d{%outputName}",
                                "splitOnQueryFilterName":""}
]
appType=[value] Override the Content Type e.g. application/text would be appType=text
filename=[value] Override the Content-Disposition default filename e.g. filename=myfile.dat
dheader=[0|1] Include or remove Header details from Data used with dfmt=text and dfmt=csv
dtitle=[0|1] Include or remove Title, Description and Filter details from Data used with dfmt=csv. By default this setting is off
It is useful when exporting to Excel to show the scope of the data
dtotals=[0|1] Include or remove Total details from Data used with dfmt=text,csv,xml,json etc
drowcat=[0|1] Include or remove row category details
drowlimit=[0..(n)] Override or set the data row limit
accesscode=[Code] Published Report can have a security Access Code in addition to the Sign security
language=[en|de|es|ja…] Override the default user account language. Defaults to English if no translations exist
language=es
flt[Name]=[Value] Set the Prompt Filter Value for a Published Report’s named Filter.
e.g fltAccountCode=12345
flt[Name]_2=[Value] Set the Prompt Filter to Value for a Published Report’s named Filter.
e.g fltAccountCode_2=zzzzz
contentDisposition=[type] Optional.
Set the content Disposition Header information.
Handy when setting a download filename. If not set then defaults apply

eg. &contentDisposition=attachment; filename=”sharperlight A001.xml”
Sharperlight Service Help Top

Related Posts