So, I’ve searched all over and can’t find what I’m trying to do – and I realize I’m probably not searching correctly…but here I am…
I’ve created a BAQ to export Job information to be used to create some custom reports for production in an Access DB. I was SO proud of myself for creating the BAQ and getting it to export. Then we realized we had an issue with the formatting. Our internal part numbers are in xxx.xxxxxx format – so I see my problem is that . in the middle of that – when it gets to a .csv file opened in Excel it will drop and trailing 0’s - which we need to keep because it’s part of the part number.
The other issue is the Date column. It’s formatted just as mm/dd/yyyy in the BAQ but comes up in the .csv file as date/time and the time is causing problems.
I exported the BAQ using the BAQ Export Process. My only option on the file types to export are XML and CSV. I’ve not gotten very far on the SSRS report creation side of life.
Maybe I’m taking the long way around all this anyway. My ultimate goal is for the data in the BAQ to update a file on an automated schedule – if there is a way to do that using an Excel file that’s great too.
I haven’t used the BAQ Export Process before, but one idea you could try:
Create calculated fields in the BAQ for the part number and date in nvarchar format. For the date, use the convert to string function, for example: convert(varchar, Project.StartDate, 101)
I wonder if converting them to strings in the BAQ would keep the formatting once it gets to CSV.
Well…I’m happy to report that we were able to change the formatting on the output and save it and now it seems that when we overwrite it with the latest and greatest export from Epicor the formatting stays! I can rest my brain on this one and move on to other things that are tormenting me!
Thanks for the help! Even though we worked it out I’m going to test my skills and try doing it with a calculated field
We weren’t able to link to the XML automatically with the Access DB. Hopefully, at some point we’ll be able to generate all of the reporting that we need directly from Epicor and not have to rely on Access – but for now it’s a lifesaver while we’re in the learning process.
Have you thought about having Access query the SQL DB directly? Access365 has the ability to use external data. You can have Access import the data into a table, or create a linked table which will always fetch the data from the external source.
If you decide to import, it can create a task to make doing it repeatedly very easy.
Just when I think I’m out of the woods… engineering wants to link Altium to the DB to download our internal part numbers along with the associated manufacturer’s part numbers. IT doesn’t want to give engineering a profile to connect to the SQL database directly…I just want everyone to have the data they need… Does anyone have any experience with Altium? I feel like exporting as an XML file is easier in terms of keeping the data consistent but I don’t know if it’s possible for them to then link to it as a datasource.
I wouldn’t think this would be something you’d link to the Epicor DB. Maybe just pull in once when the part/library is created. And maybe occasionally when the E10 DB changes and that Altium part needs to be updated.
I’d personally be hesitant to let my Altium files be automatically updated when the Epicor DB changes. Things could go bad real bad, real fast.
Engineering likes to keep their library updated with all of our internal p/n’s and MPN so that when they are designing a board they can export the BOM with our p/n’s and it makes it easier for everyone.
Hi Wendy,
Just another thought. Could you solve your workflow/ report needs with a dashboard/BAQ(s), the Access DB attached as an external data source and use both the Epicor data and the Access data to drive a realtime solution instead of reports?
-Henry
This seems to be an issue that just won’t behave and let me move on to something else. Thought we had it…but we didn’t. I tried creating a BAQ Report and was doing pretty good… had the report looking not too bad and then when I started formatting the fields to look the way I wanted, something went haywire and the report wouldn’t run anymore. I decided to delete it all and start from scratch which was apparently a bad idea as now I can’t get any portion of the process to work. I desperately need to take a class on BAQ and SSRS reporting…
In the past i have had luck modifying the import Excel or Access to Hard Code the part number as a String before importing. That way the “number” gets left alone.
