By using this website, you agree to our Terms of Use (click here)
To further explain, I am attempting to group by numerical postal codes. And to preface i am testing with only 5 digit numerical postal codes
My issue is that the postal code field is a varchar in order to allow for alpha numeric postal code strings.
The goal is to convert these postal codes to any numerical type (int,double,dec, anyhting) at the time of grouping, My attempts at using the functions provided in the designer (Cint(), etc...) have provided the error "converting the varchar field to an int has failed"
I was successful in my attempt at applying logic to the converted postal codes in the detail section that would only work if the field had successfully converted to an int so as expected it works within the text boxes and variables
My main question for all of this, After all I have tried to make this work. Is if what I am attempting is even possible within the rules of the report designer?
ARM Schema Builder and GI both have this annoying limitation of supporting grouping on data fields only - no functions.
Thank you for the reply,
We have been able to create control statements in the grouping collection that allow us to change the ordering of data views by doing sort of a replacement.
But are you saying that we cannot use functions specifically for manipulating or converting the data field value?
I put together the attached Report Designer report showing a grouping with CInt in the formula. I had to filter out Postal Codes with characters in them to get it to run which you can see on the Filters tab in File -> Build schema...
The grouping can be seen here:
But I'm missing here why Postal Code even needs to be converted to a number. My preference would be to keep it text since it can have text values. Even something like a dash is considered text.
Thank you very much for the information. I do agree with the preference of of keeping it text to allow for alpha numeric postal codes. But my current task is creating sub territories within only US States based on zip code ranges. Converting the postal codes to numeric and applying > or < is currently my best option as opposed to hard coding each zip in the sub territories for each state. I hope the explanation of my goal clarifies the weird request 🙂
Ah, I see. Creating sub territories on zip code is something that Power BI is really good at. You just need a mapping tables that allows you to map to Metro area or whatever type of grouping you're looking for. Then you through some Census data into the mix (also by zip code) with things like population, median age, median income, etc. and things get really interesting.