Filtering Large Theobald Reports for Sales Order Data in Alteryx

Hi community,

I need help with obtaining information from a list of sales orders using a Theobald report connected from SAP. My initial plan was to join this list with the whole-ran report(left side of screenshot), but the report is too large to load. In SAP, there are variants to set and filter to make it smaller, but unfortunately, this one can't be filtered and needs to be run whole.

Is there a way to filter the data BEFORE running the Theobald report(right side of screenshot)? Or what would be the best approach to handle this?

Please see the attached screenshot for a clearer explanation of the logic. Any guidance on the workflow would be greatly appreciated.

Thank you

Workflow

Accepted answers

All comments

griffinwelsh

Your Theobald connector looks different than what the version we use at my company, but generally I skip reports entirely and just pull data in from the table level. This takes longer to build but is more efficient when running and *should* avoid the report size errors. You can use t-code ST05 to find the underlying tables if you want to take that approach.

Shoeby

Sorry for the late reply, not sure if you figured this out or not, but I also try to build from the table level.

A couple thoughts. When I do build from reports, I've gotten around this 2 different ways.

1. Pull smaller chunks in SAP... I've needed 3 years of data, and created 6 Variants for 6 month time periods, and then used 6 Xtract Reports, unioned the results, and wrote that to a yxdb file because it was massive.

2. I have also had luck with setting the report to background processing (and upping the background job time out.). This does normally require you to do some formatting to clean up SAP's hideous background output. In most of my reports I have to skip 4 rows from the top as a start to the formatting.

Hope this helps.

Ryanz3653

thank you for your reply,

1, unfortunately the specific SAP report i am using does not have a date range option for me to set, i am trying to find a way to filter before the theobald tool, not sure if that is doable

2, sounds like that needs to be done in SAP level by the specialist, right?

IAJ1966

FYI .... this is standard functionlity which can be applied directly from within Designer when you use the SAP connectivity software from DVW Analytics.

Message me directly and I will gladly show you how this works!

Ryanz3653

interested!

thanks

Shoeby

One other thing, as I've been thinking about this. Your #2 method above is something that does work, but I don't think it'll help you shrink your pull down.

You can do that by setting values to the input anchors in the tool. I'm sure you've probably figured this out, but I've attached screenshots of an example of how this works.

Once you attach the variables to the input anchor, go to the WHERE clause tab and click "Edit Parameters" and then "Add Scalar" to add the runtime parameters.

Then in your WHERE clause editor, you should then be able to add the parameters

The problem here is that this works if you're filtering on a range of values, (works fantastic on date ranges). However, if you are looking for very specific values, this doesn't really work in that situation. If you have specific values that you're looking to filter, you need an IN statement in your WHERE clause. One way to get around that in Theobald is to use the batch macro functionality. One caveat, is that this method stops working if you're looking for more than 25,000 individual records, so it may not assist you with this specific issue, but it is handy to understand. I've put my basic batch template macro in this post, and a quick walkthrough using the example of finding specific inbound delivery information by delivery number from table LIKP.

I started doing this before Theobald added input anchors to their tools, and this is how I still pass the majority of my parameters into these extractions. I've attached my basic batch macro workflow, this is my template file for starting all of these. You need to configure this for your connection, and then there are few minor things to do. I will go through this with the example of pulling inbound delivery header data (Table LIKP) for specific deliveries, that I mentioned previously.

1. Give the control parameter a short name, I named mine IBD for example.

2. Configure the table extractor.

a. Set the IN statement within the Where clause. Leave a space between the parenthesis and control parameter on both sides. For Example. LIKP~VBELN IN ( IBD )

b. Select the fields for output like any other table extraction.

3. Configure the Action

a. This is the movie clacker. It should already say Update Value, but if not, set the action type to Update Value

b. Click on WhereClause in the Config window

c. At the bottom of that window click the replace a specific string checkbox, and make sure just your control parameter is named. In this example IBD

4. Configure the Workflow that uses the macro

In the workflow that is going to run this data you need to do the following steps. (You can bypass A & B if your data is already concatenated).

a. Create a summarize, Group By. to get your field alone.

b. Create a summarize, Concatenate (Start ' Separator ',' End ' ) to get your field in the correct input format.

c. Connect Macro to that dataflow.

Let's say I have Inbound Delivery #'s of 1234, 1255, and 1296, and any other information, when you hit the 2 summarizes, you're going to get unique Inbound delivery numbers, and then they're going to be concatenated into a single string with each value encased in single quotes with a comma between them. For example this would be my input into the macro for those 3 Inbound Deliveries '1234','1255','1296'

That's what gets passed into the macro, which looks at it's action and says okay, I need to replace the word IBD in the Where clause, with this concatenated string. So what was WHERE LIKP~VBEN IN ( IBD ) becomes WHERE LIKP~VBELN IN ( '1234','1255','1296' ). This is a valid Statement that can be used by Xtract table, and so you will get the results for just these 3 values. (As opposed to pulling the range and getting 60 results.)

Hope this helps!

Untitled picture.png

Untitled picture2.png

BasicBatchMacro.yxmc

BrianSmoot_TS

Hello Ryanz3653,

In this scenario Shoeby gave a perfect example where it's possible to pass values at runtime.

I'm also happy to jump on a session to work through any of your concerns and provide a live demo tailored to your use case.

Here are links to our comprehensive Help Center, and excellent customer support to assist with setup and anything product-related. We're also happy to support you with a live demo tailored to your use case.

Let me know if you'd be interested in that - I'm here to help!

Best regards,
Brian Smoot | Technical Account Manager
Theobald Software

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703