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
vbnetcoderAsked:
Who is Participating?
 
helpfinderIT ConsultantCommented:
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
 
helpfinderIT ConsultantCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
helpfinderIT ConsultantCommented:
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
 
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
 
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
 
helpfinderIT ConsultantCommented:
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
 
helpfinderIT ConsultantCommented:
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
 
helpfinderIT ConsultantCommented:
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
 
helpfinderIT ConsultantCommented:
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
 
helpfinderIT ConsultantCommented:
No problem, anytime. Then this threat could be closed
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.