How to make PB query as a JSON and send to remote server via REST?

bliako

New Member
Joined
Apr 11, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Web
Hello there,

I am querying a remote PB server by doing a POST to its REST url (something like
Code:
https://XYZ/public/reports/querydata
). The query is contained in a JSON file which looks like this

Code:
{
  "queries" : [
   {
     "ApplicationContext" : {
      "Sources" : [
        {
         "ReportId" : "ZZZ"
        }
      ],
      "DatasetId" : "YYY"
     },
     "Query" : {
      "Commands" : [
        {
         "SemanticQueryDataShapeCommand" : {
           "Query" : {
            "Where" : [
              {
               "Condition" : {
                 "In" : {
                  "Values" : [
                    [
                     {
                       "Like" : {
                        "Value" : "'%abc%'"
                       }
                     }
                    ],
                    null
                  ],
                  "Expressions" : [
                    {
                     "Column" : {
                       "Property" : "APROPERTY",
                       "Expression" : {
                        "SourceRef" : {
                          "Source" : "A"
                        }
                       }
                     }
                    }
                  ]
                 }
               }
              }
            ],
            "Select" : [
              {
               "Name" : "AVIEW.APROPERTY",
               "Column" : {
                 "Expression" : {
                  "SourceRef" : {
                    "Source" : "A"
                  }
                 },
                 "Property" : "APROPERTY"
               }
              }
            ],
            "Version" : "2",
            "From" : [
              {
               "Name" : "A",
               "Entity" : "AVIEW"
              }
            ]
           },
           "Binding" : {
            "Primary" : {
              "Groupings" : [
               {
                 "Projections" : [
                  "0",
                  "1"
                 ]
               }
              ]
            },
            "Version" : "1",
            "DataReduction" : {
              "DataVolume" : "3",
              "Primary" : {
               "Top" : {}
              }
            }
           }
         }
        }
      ]
     },
     "QueryId" : ""
   }
  ],
  "cancelQueries" : [],
  "modelId" : "XXX",
  "version" : "1.0.0"
}

For human readable version, this is the YAML equivalent:

Code:
---
cancelQueries: []
modelId: XXX
queries:
- ApplicationContext:
DatasetId: YYY
Sources:
- ReportId: ZZZ
Query:
Commands:
- SemanticQueryDataShapeCommand:
Binding:
DataReduction:
DataVolume: 3
Primary:
Top: {}
Primary:
Groupings:
- Projections:
- 0
- 1
Version: 1
Query:
From:
- Entity: AVIEW
Name: A
Select:
- Column:
Expression:
SourceRef:
Source: A
Property: APROPERTY
Name: AVIEW.APROPERTY
Version: 2
Where:
- Condition:
In:
Expressions:
- Column:
Expression:
SourceRef:
Source: A
Property: APROPERTY
Values:
-
- Like:
Value: "'%abc%'"
-
QueryId: ''
version: 1.0.0

The reason I am using this approach is that I am on an OS which is not supported by the PB desktop (Linux, I know it may sound like the devil's work to you people :) but that's what I have).

which does not work because there is something wrong with my syntax. Generally, this method works for other JSON-like queries. What I am trying to do here is to experiment with a working example and see my mileage. And I like this method of editing my JSON (or YAML) and doing a POST using
Code:
curl
from the command line, which then I can program into my scripts and automate the procedure while I am having some holidays, presto!

So, the immediate question is how to query a table and return all columns based on some WHERE/LIKE condition? Is this like SQL? The Select looks like but the WHERE I can't get it to LIKE!

Secondly, is there any documentation for what I am trying to do? What is the name of the JSON content? I have read about DAX and command-lets but that does not remotely look like it although it is supposed to do the same thing.

Many thanks,

b.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top