0

Data exclusion in Publish schedule

Hi

I have a scenario where I have a sales publication going out to a lot of email recipients via a dynamic email list against a slicer with 3 entries. Let's say those 3 entries are "Cars", "Planes" and "Boats". A schedule is set, so each morning a pdf with the relevant publication is sent to each recipient. Works a treat!

Let us now say we have acquired a new car brand, "PyramidCar".

As this is new business we want an additional sales report that excludes the "PyramidCar" sales, but sent out with the other ones. 

In the multi page publication in some of the discovers, the Brands are split out. This is the reason I can't just add a slicer with a grouping on this field.

So the 4 reports we need are

Cars (all) - going to recipients A

Cars (all except PyramidCar) - going to recipients A

Planes - going to recipients B

Boats - going to recipients C

There are ways to work around this. We can deep paste the publication and then edit each discovery to exclude the data. Or we can create a Present where we subscribe with specific slicer selections. 

But it's not the first time I've encountered this issue and I'm sure others have.

7 replies

null
    • "making the sophisticated simple"
    • AviPerez
    • 1 yr ago
    • Reported - view

    There are plenty of built-in techniques for handling your scenarios.

    You should review schedule, report and page triggers. Also look at using a "list" with logic built into it as the source for a slicer - rather than just the raw list of elements.  Page repeaters may also be useful. 

    Digest these functions and then it will be easier to help you attack your specific problem.

    • Ove_Sandau
    • 1 yr ago
    • Reported - view

    Hi Avi

    Thank you for getting back to me. I was looking into several of these things already, but nothing seemed to do the trick. I'll pass your suggestions back to our consultants as well and see if they can come up with a solution. They too tried a few of these things before I raised the ticket, so it's clearly not a straight forward way to achieve it.

    This is on BW data source by the way. There are a few limitations on the formula logic we can put in.

      • Ove_Sandau
      • 1 yr ago
      • Reported - view

      Hi Avi Perez

      Do you have any further response to this? I have had a similar request again, and there is just not an easy way to do this. 

      If I do a grouping on customers for example, then I can't have the details of each customer. They are getting grouped. 

      If I instead create a list, then I have to select a specific customer on the schedule.

      I cannot see a good workaround for this other than to use Present with multiselect slicers and then unselect the bits I want to exclude. But as Publish is what should be used for a proper sendout of publications, then I expect to be able to do it in there. 

      Regards

      Ove

      • "making the sophisticated simple"
      • AviPerez
      • 1 yr ago
      • Reported - view

      Ove Sandau Please supply a detailed example (with exhibits) of what you want to do and explain why the current tools don't get you there in that example. The problem set you're sharing above is too vague at the moment. 

      • Ove_Sandau
      • 1 yr ago
      • Reported - view

      Hi Avi Perez 

      Thank you. I'll message you directly if that's ok. I would have to spend too much time anonymising data for the forum.

      • "making the sophisticated simple"
      • AviPerez
      • 1 yr ago
      • Reported - view

      Ove Sandau makes sense.

    • "making the sophisticated simple"
    • AviPerez
    • 1 yr ago
    • Reported - view

    Your problem involves hiding and showing actual elements in queries - as opposed to hiding and showing entire queries (page triggers), entire reports (report triggers) or indeed the entire execution of a scheduled batch (schedule triggers).  Therefore the solution lies in how you hide or show elements in attributes/dimensions used in queries based on other selections.

    Here is a template for the solution using a generic example. It requires 3 lists/sets and 1 custom member formula. Use the orange Formulate app to drive all of it. It should work in both MDX (possibly not in SAP BW) and PQL languages. 

    We'll assume you have 'business units' that drive a report of 'manufacturers'. 

    1. create 2 lists/sets for the chosen attribute (say manufacturer). One set will have all manufacturer elements starting with an "A", the other will have all manufacturer elements starting with a "B". (You can dream up any advanced logic you want to drive each of these lists.)
    2. create a custom member on the business unit attribute, that represents a variation of the existing BU you need to handle. Example: "Adjusted Grocery" which is simply a data point of the original "Grocery" business unit. 
    3. Create a third list ("C") that incorporates an "IF" statement. The 'If' will check the current member from the BU attribute. If it is "Grocery" then the list uses (and resolves to) list A. If its not, it uses list B.
    4. Build a query using the "C" list and metrics in Discover.  
    5. Go to your Presentation or Publication. Drop the report into the presentation/publication. Create a slice of all BU's, including our custom BU "Adjusted Grocery" from step 2. 
    6. Run your content. When using 'Grocery' - you'll see "A". When using 'Adjusted Grocery' you'll see "B".

    Here is some code to show the formula for step 3

    iif([Promotion].[Promotion Category].currentmember is [Promotion].[Promotion Category].&[No Discount], [*898b2186-c1fd-423a-8620-2dd994668d56*], [*a3ea2bcd-30e3-4b78-aab6-7a83b3d7cf3b*])

    SAP BW doesn't seem to support "IF" statements in MDX that handle sets/lists. So the BW treatment would look something like this instead:

    1. Create a new custom measure. Call it "hide-show". Set its value according to the following IF structure:
      1. If the current member of the BU is 'Grocery' AND the intersect function of the current manufacturer with set "A" above has a count above 0
      2. OR If the current member of the BU is 'Adjusted Grocery' AND the intersect function of the current manufacturer with set "B" above has a count above 0
      3. Then "1"
      4. Otherwise "0"
    2. in your discover report, add a numerical filter, using a new hide-show measure filtering to show everything above "1".
    3. Use the Discover in the presentation/publication with the same BU slice.

    Here is some code to show the formula (using different elements and hierarchies):

    iif(([0CALYEAR].currentmember.uniquename = '[0CALYEAR].[+d93e54da-f8dd-4c10-94d6-1cd56179446e+]' and intersect({[ZEPM_BP].currentmember}, [*8f77e5aa-1d78-477e-a417-2624e323bfef*]).count > 0)OR[0CALYEAR].currentmember.uniquename <> '[0CALYEAR].[+d93e54da-f8dd-4c10-94d6-1cd56179446e+]',1,0)
Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 7Replies
  • 60Views
  • 2 Following