@paulc Sorry about that, didn’t realize it. Thanks for the heads up.
Best method to solve this would be to create two custom fields on the screen where you are printing this report from, do it in the same area of the screen where the start and end dates marked in yellow are getting their dates from. Seems like it would be the header in this case. Create a read only custom field that has a select statement which joins to the view where the green start date or end date columns are, and use the min or max function to get the correct date. Once you have added the custom field and it shows on the screen, you should be able to reopen report designer and see the new custom fields in there somewhere in one of the folders on the left side. Then swap them out on the report header instead of the existing column values.
So I am guessing you are talking about the IFS Order reports/Operational reports. By default these are only available in pdf format. However, after they are generated you can create a quick report that selects the from their underlying table/view, and then export that quick report to excel. For example. Say wanted to export the customer ledger age analysis order report to excel. First I would go to order report screen to run it. Data for it would be generated in the underlying view, and you could see the result key for it in the report archive. Then I would then have already created a quick report with something like select * from cust_age_analysis_rep where result_key = (select max(result_key) from ifsapp.cust_age_analysis_rep). This will get you the most recent result key of data for that report. Can then export that quick report to excel. To figure out what view the order report uses, go to the report definitions screen, and can find it there, it’s the report id and always has the f
Hi Setayesh,Yes IFS has their own out of the box DW and cubes that you can purchase, I used them back in version 8 and 9 and it was on prem SQL Server for DW with SSIS for ETL jobs and SSAS Multidimensional cubes that you could purchase for various IFS functional modules. Looks like they have changed to tabular in the cloud version. As with any out of the box solution, they would sometimes have what you needed in them and sometimes not. They were also a bit difficult to modify in my opinion. If you need a more custom solution for a data warehouse then yes can definitely develop your own using same SQL Server Stack, and add some Power BI in for reporting, which integrates with Aurena actually.
If you have access to connect to the Oracle database then could use any ETL tool that can connect to it, just the same as you would in an on-prem environment. Could use SSIS if building SQL Server DW on prem, or Azure Synapse/Data Factory, etc. if you want to do it in the cloud.
I have done it and made cubes off of the datawarehouse as well. Can use something like ssis for ETL and SQL Server to and create the data warehouse in, or if you want cloud technology then equivalent is azure data factory and azure sql database or sql datawarehouse (think it’s called synapse now). Then create some cubes on top of it for tools like PowerBI or excel to connect to. The above is a set up for a larger amount of data however. If data is smaller and wouldn’t take as long to load, you can just use Power BI Only and connect directly to Oracle using the data gateway use it for your ETL and modeling process all in one. Much simpler and faster development process, and works really nice.
Addomaby I haven’t used IFS Cloud yet, but seems like it from these posts. Would be a huge drawback to cloud if you couldn’t.
I haven’t used IFS Cloud yet, but have connected Power BI to both on prem and AWS IFS instances. Should be possible just the same. If you can connect to the Oracle DB for IFS cloud from your machine, then you can connect Power BI desktop to it. Just have to set up a gateway to refresh the data after that, to get it up to Power BI Cloud. Setting up Oracle connection correctly can be a pain. Have to use Power BI desktop from the Microsoft website, not the one from the windows store, that one won’t work with Oracle. Then make sure Have all Oracle client stuff installed, I usually do the admin client. Even then Power BI will wrongfully throw an error saying you need the client installed even though do already, just keep going anyway and it will work.
Should be able to, I have done it with power bi and other api sources that return json. Never tried with IFS.Have to transform and expand the columns after you pull it like link here, and will vary depending on the hierarchical structure of the json you are pulling.https://community.powerbi.com/t5/Power-Query/Using-a-REST-API-as-a-data-source/td-p/50400 What is the use of this though, and what problem would it solve that you can’t get using Power BI and Oracle db? This is my usual method.
Already have an account? Login
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
