Deriving the report based on the country selected

[ak00428233]

Hi All,

Could you please suggest me the expression in such a way that when i select a country only that report country should be visible irrespective of the other countries accessed.

Ex:Below is the screenshot from the report where china is selected but i am able to view the other reports accessed.So could you please suggest me the expression so that i can get only cash flow Budget china,Master Budget China,Operating Budget China.

Thanks in advance.

Regards,

Anu.

[vamsee]

Hello,

Try this

[ak00428233]

Hi,

The rest of them are excluded if i use that option.I think we need to write expression to exclude the other countries  using wildmatch.Please correct me if i am wrong.

[vishsaggi]

Can you share your actual script on how you got these two listboxes Report and Country. Based on your csv files i dont find anything wrong. Check below

I used this script:

Report:

LOAD @1 AS Report,

     UPPER(Trim(Subfield(@1, 'Budget', 2))) AS Country

FROM

[..\..\Downloads\budget reports_test.txt]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Country:

LOAD Upper(Trim(@1)) AS Country

FROM

[..\..\Downloads\Countries.txt]

(txt, codepage is 1252, no labels, delimiter is ' ', msq);

[ak00428233]

In this Hi Nagaraju,

I have loaded the same data which is in taxt to inline load

Users:

Load* inline [

Name,Report,COUNTRY

Jhansi,Master Budget   india,india

Swathi,Master Budget   india,india

Jaya,Master Budget   CAMBODIA,cambodia

Renuka,Master Budget   nepal,china

Hari,Master Budget   china,china

Krishna,Master Budget   bhutan,bhutan

Madhu,Master Budget   andaman and nicobar,andaman and nicobar

Shanti,Master Budget   hong kong,china

Karthik,Master Budget   ECUADOR,ECUADOR

Nani,Master Budget   india,india

Doan,Mater Master Budget,china

Lucy,Master Budget   china,china

satish,Operating Budget  india,india

Sree,Operating Budget  nepal,nepal

jithin,Operating Budget  singapore,singapore

latha,Operating Budget  china,china

kavya,Operating Budget  bhutan,bhutan

sita,Operating Budget  andaman and nicobar,china

raghu,Cash Flow Budget  china,china

raju,Cash Flow Budget  AUSTRALIA,australia

navya,Cash Flow Budget  bhutan,bhutan

satti,Cash Flow Budget  andaman and nicobar,china

neetu,Cash Flow Budget  ECUADOR,ECUADOR

Satya,Cash Flow Budget  EL SALVADOR,EL SALVADOR

Nalanda,Cash Flow Budget  china,china

aditya,Cash Flow Budget  JAMAICA,JAMAICA];

In this sample example budget is the last

word but in my real scenario all the report name do not have same name at the ending so i think i cannot use this logic.

Could you please suggest the expression such that when a country china is selected.Only china country data need to be visible.

The report is working fine for other  countries but issue is with china only.

[vishsaggi · Accepted solution]

Because you have other report items linked to that country China you are seeing it so you want like look for country China and reduce data in your Report listbox only those values which has China in its text?

Try this in your listbox expression like

= IF(WildMatch(Report, '*'&GetFieldSelections(COUNTRY)), Report)

[ak00428233]

Thank you Nagaraju.It worked well for my scenario.

Can you please suggest me to remove the country name after getting the report names corresponding to that country.

Because i have two charts one with report name and other with report name and country.The fix you have suggested has successfully given the count for the country i select but as the report name will have report names without concatenating with country.It gives the different count.

Can you please suggest any fix to solve this issue.

[ak00428233]

I mean that chart having only report doesnt consists of country appended with it.So is there any way to get the required solution

[vishsaggi]

Can you give an example and some snapshots of what you are trying to see in your charts? And what charts are they?

[ak00428233]

Hi Nagaraju,

I have edited the code in this way :

Users:

LOAD Name,

     Report,

     Country,

     Session

FROM

(ooxml, embedded labels, table is Sheet1);

Countrytable:

Mapping LOAD

Country,

'' as XYZ

resident Users;

Reports:

LOAD

Report,

Trim(MapSubString('Countrytable',Report)) as Report_Name

resident Users;

In the second screenshot the country is different from the first screenshot when country is selected because all the report names which do not belong to the country selected will be appearing as null and excluded that null and thus the two charts are varying.

But the requirement was we need to make the country selections applicable to this first chart and we need to get the count as 5 in first chart also when china is selected.

Thanks in advance.

Sorry for the delay.

Regards,

Anu !