Fixed expression in text object

[#1]

Hi everyone,

I could need some help with fixing an expression in a text object so that I can display a certain calculated value no matter what selections are made.

Here's the problem:

I have a text object with the following text and expression:

='Bankdaten ' & Count (Distinct( if( [SAP Tabelle]='LFBK - Lieferantenstamm (Bankverbindungen)',  Änderungsbelegnr )))

Now, I would like to fix this expression, so that the calculated value is shown even if another selection is made.

At the same time, this text box is only supposed to pop up when a certain button is pressed.

Therefore I already created a variable (vBankdatenGesamt), which is set to 1 by clicking the button.

So, I came up with an expression like:

=if(vBankdatenGesamt = '1', 'Bankdaten Gesamt ' & Count ( Distinct( if( [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)', Änderungsbelegnr))))

But I still need to fix this one.

Any help is appreciated!

Kind regards,

Daniela

[Jason_Michaelides · Accepted solution]

Hi Daniela,

In the text box expression definition put this:

Count({1<[SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}>} DISTINCT Änderungsbelegnr)

This will ignore ALL current selections and make a selection in Änderungsbelegnr. If you only want to replace any selections in Änderungsbelegnr, just remove the '1' at the start of the set analysis.

To only show the text box when your button is pressed, put this in the "Conditional" box in the Layout tab of the text box properties:

vBankdatenGesamt = 1

Hope this helps,

Jason

[#3]

Hey Jason,

many thanks. This works perfectly.

Is there also an option to make it flexible again only for certain selections?

Example:

I press my button "Show nr. bank data" and get my overall number due to the fixed expression. Some further buttons with different selections won't influence the number, which is good, but then I would like to change the time frame. Instead of the overall number, the number should be change  in respect to selections concerning month and year.

I got a field "year" and a field "month" in my data set.

Thanks in advance.

Regards,

Daniela

[Jason_Michaelides]

dmeyersieck‌ if my response has solved your issue please mark it as correct to close the thread.

Thanks,

Jason

[Jason_Michaelides]

Sure. This should do it:

Count({1<[SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, year=P(), month=P()>} DISTINCT Änderungsbelegnr)

[#6]

I guess this is close, but it is not working yet.

The expression starts to be "wrong" with the closing bracket after the first "P". It seems something needs to be put into brackets, or .... I don't know. I already tried to forgo the brackets after the Ps, but this ends in rubbish

Count ({1< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, Geschäftsjahr=P(), Monat=P() >} DISTINCT Änderungsbelegnr)

Another Suggestion to try?

Thank you!

[Jason_Michaelides]

The expression syntax checker is broken after using P(), but it should still work OK. Is the expression not returning anything? Can you post your app?

[#8]

I get some values, but it changes now for any selection again, not only for selections in "year" (Geschäftsjahr) and "month" (Monat).

I have attached an extract of the overall file, since this might already do.

The required text object is called "Test Object" and is only displayed if you press the button "Bankdatenänderungen" or one of the below buttons. It should generally have the same value as the text object with "Bankdaten" on the righthand side - at least as long as you only do selections on the year and month field.

The general value to be displayed here is 14 (all changes in bank data for all years in scope).

Selections in year, month (and later also quarter, day and weekday) should lead to new values according to the selection. Pressing the below button "CPD-Kreditoren" should not change the value of 14, but it does.

The same is true for selections in any other list box like "Änderer" or "Geändertes Feld" or ...

I hope we can figure this out.

Regards,

Daniela

[Jason_Michaelides]

Oops - sorry!  You need to pass specific text strings into the Year and Month set modifiers.  Create two variables:

vGeschäftsjahr

='Geschäftsjahr='

& If(GetSelectedCount(Geschäftsjahr)=0

  ,''

  ,'{' & Chr(39) & GetFieldSelections(Geschäftsjahr,Chr(39) & ',' & Chr(39),100) & Chr(39) & '}'

  )

vMonat

='Monat='

& If(GetSelectedCount(Monat)=0

  ,''

  ,'{' & Chr(39) & GetFieldSelections(Monat,Chr(39) & ',' & Chr(39),12) & Chr(39) & '}'

  )

Then, your main expression should be:

='Bankdaten Gesamt        ' & Count ({1< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'},$(vGeschäftsjahr), $(vMonat)>} DISTINCT Änderungsbelegnr)

See attached,

Jason

[#10]

Hey Jason,

many thanks for your help. This way, it works exactly the way I want it to.

Two (hopefully) last "small" questions:

1. Is it possible to have a bookmark on a selection combination that is not actually possible?

Example: Field: "SAP Tabelle" with value: "LFBK" and field: "CPD-Kreditoren" with value: "ja".

Since there are no CPD-Kreditoren available for my bank data (represented by selection of SAP Tabelle LFBK) I can not select "ja" in the field CPD-Kreditoren when I first select the LFBK value in the field SAP-Tabelle. However, I would like to show exactly this result, namely that there are no CPD-Kreditroren for bank data.  In my text object I would like to see the value "0" then and in my graphic I would like to see something like "no data available".

Maybe there is also a way to work around this. I need this comination for my button "CPD-Kreditoren" beneath the button "Bankdaten".

2. I would like to display my titles dynamically and generally I already figured out how to do this, but again I've got a little trouble with the field CPD-Kreditoren. As long as I have not selected anything in the field "CPD-Kreditoren" I would like to see the title "Bankdaten" with the corresponding value. However, if I dispay my overall bank data (with generally no CPD-Kreditoren available) it already changes the title to "Kreditoren exkl. CPD" since the possible selections in the field "CPD-Kreditoren" are already restricted.

This is the expression I am using so far, but it is not fully right yet.

=if(IsNull([CPD-Kreditoren]), 'Bankdaten ', if([CPD-Kreditoren] ='ja', 'CPD-Kreditoren ', if([CPD-Kreditoren]= 'nein', 'Kreditoren exkl. CPD ', 'Bankdaten '))) & Count (Distinct( if( [SAP Tabelle]='LFBK - Lieferantenstamm (Bankverbindungen)',  Änderungsbelegnr )))

See attached.

Any suggestions appreciated.

Regards,

Daniela