Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

excel 2010 pivot table ---- Report filter

I have a report filter on my report that refers to items that the user can select (ex apples, pears, etc). This come from the data backend that has an item id assocaited with it.

I would like the users to seem not only the item but also the id when the select it from the dropdown

ex

1 apple
2 pears
3 etc
0
vbnetcoder
Asked:
vbnetcoder
  • 9
  • 8
1 Solution
 
helpfinderCommented:
could you please attach sample excel workbook to work with "real" data?
0
 
vbnetcoderAuthor Commented:
No, the datase backend is on our server. Anyway, it should not matter
0
 
helpfinderCommented:
sorry, maybe I am wrong, just want to be able to imagine your situation as much accurate as possible.
you have table where you have column with e.g. fruits and another with fruit ID?.
in report filter you want to see options to choose:
01 - Apples
02 - Pears
03 - Apricot
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vbnetcoderAuthor Commented:
Exactly. That is what i need

I don't just want to show the name of the field i also want to show it's associated id
0
 
helpfinderCommented:
then, form my point of view you have to add new column into your database which will merge these two fields - like:
=A2&" - "&B2

attaching  sample for better imagination
pivot-sample.xlsx
0
 
vbnetcoderAuthor Commented:
Ok I might have to do that.....
BTW, Could you tell me how you got the count of quanity thing to work? That is something else i need to do.....
0
 
helpfinderCommented:
just doubleclick on the label Count of Quantity (B3), Value field settings window pops up and choose desired calculation (Avg, Min, Mac, Count, Sum, .. )
pivot-2.PNG
0
 
vbnetcoderAuthor Commented:
How did the count of quantity field get in b3 in the first place?
0
 
helpfinderCommented:
excause me, now I am not sure about your question.
Do you mean how is it possible I have Count of quantity in B3? Or how is it possible count is default value when pivot is created? or .. ?
0
 
vbnetcoderAuthor Commented:
I guess both....

I have a field and i want the SUM of them that meets the critera. Currently, if i add a field it shows every item.... I want the SUM
0
 
helpfinderCommented:
as I know it´s Excelby default - to put row labels into 3th row (when you are inserting pivot on new sheet) and also Count value is set by default by MS Excel

If you need SUM instead of count, just double-click on column label and choose Sum.

If you have still problem, it would be better for me if you could prepare very simple sample of your situation (something like I prepared) to exactly see what is your problem. Probably you just need to play with "Report Filder", "Row Labels" and "Values" fields and put them according your needs
0
 
vbnetcoderAuthor Commented:
I guess my problem is i have a SSAS backend. Perhaps, i need to retreive that data into a tab and then read it from there??
0
 
vbnetcoderAuthor Commented:
I imported the data from a view so now i have it in a tab... how do i connect my data to it?
0
 
helpfinderCommented:
again, not sure about your question.
do you have data in excel spreadsheet and need to create pivot table?
0
 
vbnetcoderAuthor Commented:
Never mind i got it. Thank you for your help!
0
 
helpfinderCommented:
No problem, anytime. Then this threat could be closed
0
 
vbnetcoderAuthor Commented:
ty
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now