I have a variable that I want to use for an input box that will determine data to be displayed in a straight table. This input box will accept a serial number. I have the variable defined as ($(vSerialNbr)). I'm not sure I wrote the definition correctly. I linked it to the straight table using the Dimension Limits tab with the serial number dimension (the first field on the table) set as Show Only 1st (variable value).
The straight box has 10 total columns including the serial number. The input box accepts the input but nothing happens afterwards. The hourglass come on and it goes through the motions but no data is displayed in the straight table. I've been stumped looking for a solution. Please help.
It's not really counting anything. Every serialized item after being received into the system goes through many processes and each step is recorded until it is shipped out. I simply want to display the history with each line of displayed data representing a process. So when you input the serial number the table displays the history of the serial. I can't send the file as this table is only on one tab and the file itself has 12 tabs. I just tried to paste a screen shot of the tab here but that didn't work.
It should look something like this:
Serial InDate Process OutDate Program Stream
1 1/2/2017 Rcv 1/4/2017 3-Rtn 3
1 1/5//2017 Transfer In 1/6/2017 3-Rtn 3
1 1/7/2017 Kitted 1/7/2017 3-Rtn 3
As an alternative to using a variable you could use a listbox for the serial number, set it to Always One Selected Value. Set the calculation Condition for your straight table to Count(Distinct Serial) = 1.
I know I'm not directly addressing your question but this may be simpler. The listbox will make it easy to search in your data for a serial number.
Thanks Andrew. This worked when I changed it from Straight to Pivot and added an expression to count serial. I would still like to try the input box as the list box doesn't offer the functionality I was looking for with the input box and not as clean. Could you look at the way I wrote my variable and see if it's fine.
Why did you add an expression to count serial number? Have you tried setting the Calculation Condition in the pivot table's General tab to Count(Distinct Serial) = 1? In the General tab you can add an error message against Calculation condition unfulfilled to read "Please select one serial number" or whatever you want to guide the user.
I wasn't getting the result I wanted and was messing around to see what works. I pulled that out and went with your original suggestion and things worked out fine. I put the objects on this sheet in alternative states so they don't interfere with the other tabs. I would still like to at least find out how to solve the issue with an input box but overall I am quite satisfied. Thanks for your help. It's very much appreciated.