cancel
Showing results for 
Search instead for 
Did you mean: 

How can I use one Query to get different results in the same Workbook??

sherif_elkoumy
Participant
0 Kudos

Hello Experts,

I would like to show about 10 company codes in Workbook beside each others. All company Codes are maintained in the query, but I need to create one table for each company code, for example table one to show company code A600, table two beside table one to show company code A601 an so on..all these tables should show the query results, how could I restrict that in the workbook??? Definitely, i can not restrict that in the query, I need only to restrict the results of each table on a specific company code in order to compare between them .....all that should go with only one refresh to refresh the results of all table for the company codes????

is that clear??

Thanks,

Sherif

Accepted Solutions (0)

Answers (6)

Answers (6)

sherif_elkoumy
Participant
0 Kudos

Hey Paulo and Suman,

I did as you exactly described; it worked very good, your illustration is perfect. Thanks a lot dude 🙂

Paulo´s suggestion is smarter, I test that in Dev as you proposed, I restricted each table through Dropdown box or Navigation Fields and saved the workbook including all restrictions that I entered before saving, when the end user opens it, he will prompted by variable windows, when he enters the variable, for instance, year and month, the workbook will be refreshed for all tables according to the restriction criteria you defined for each table before in the Dropdown box or the navigation fields.

From a performance view, I find Paulo´s suggestions awesome and smarter. The time delay is significantly reduced.

Best regards,

Sherif

former_member182470
Active Contributor
0 Kudos

Nice to know this. Great suggestion by Mr.Paulo..You can close this thread..

Former Member
0 Kudos

Hi Sherif,

I did it and it works..

Just create multiples DataProviders using the same query,  and one dropdown for each DataProvider.

Regards,

Paulo.

Former Member
0 Kudos

Hi Paulo,

This is very good work around.

Regard

Shankar

sherif_elkoumy
Participant
0 Kudos

Hello Guys,

Thanks a lot for your great advice. I took all into my account.

I created multiple queries for each company code and assigned each one of them to one table as Suman and SVU said. It worked. However, If I need to include for instance about 20 Company codes, I have in this case to create 20 queries for only one report. does have this something to do in terms of performance? > Suman and SVU

However, there is another solution as Paulo said, we can use the same query for all table but we have to create for each table Navigation Fields (Dropdown box) and restrict manually the company code for each table from this Navigation Fields..I think, this works. According to Paulo´s solution, if I could do that and restrict the company codes for each table in Design mode, saved and transported into productive system for users ...when the user would like to open this file and use it for his/her daily report, Does he/she have each time at refreshing the report to enter or restrict the company code for each table or he would get all table restricted and filtered according to the company code because I did that in Design mode and saved ??? Do I think rightfully??? > Paulo


Thanks again for all of you, Paulo, Suman, and SVU

@Paulo Carvalho

@Suman Chakravarthy K

@svu123 svu123

Former Member
0 Kudos

Hi Sherif,

You will get all table restricted, the end user don't need to filter each company.. if you don't want to show the dropdown boxes, you can put it in a hidden worksheet tab for example.

Regards,

Paulo

former_member182470
Active Contributor
0 Kudos

Hi,

Splitting into 20 queries does not hamper the performance. Parallel execution can happen for all your queries.

I liked Paulo's suggestion too. But I am wondering if you want to run with 20 different company codes, hows does the refresh will effect in all 20 queries.

Please test this scenario in Dev and try to play with this workbook by refreshing multiple codes and observe whetehr only the required table is getting refreshed or all?

Regards,

Suman

former_member202718
Active Contributor
0 Kudos

Hi Sherif,

Agree with Suman...that we can create multiple queries on the same base query and if you are sure about the format and Co Code,then Hard code the values in all the queries(diff co codes) and Embed them next to each other in the same Worksheet in Design Mode.

Rgds

SVU

former_member182470
Active Contributor
0 Kudos

Hi,

It doesn't work when you keep same query multiple times in a same sheet.

If you try to filter with one company code in second table, your first table will also changes along with second table.

The only workaround for your requirement is, make different copies of your query into different technical names and put Company code restriction as hard coded.

Now you can try to insert all those queries in the same sheet. Your variable will be uniform across all your queries and will work as per your requirement.

Regards,

Suman

Former Member
0 Kudos

Hi Sherif,

You can insert the same query many times and then put many dropdown box to filter each query..

Let me know if you need more details

Regards,

Paulo.