cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joham
New Member

Odd problem reading data via ODBC link

Hey all,

 

I'm relatively new to PowerBI so if I'm not clear, please bare with me.

 

I was tasked with creating a duplicate (or as near as possible) report in Power Bi from a Crystal report.

So far I've managed to:

 

  • Import the data sets I needed via an ODBC link.
    • Delete the information from those data sets that wasn't needed with the help of the original creator of the crystal report.
    • Create the relationships between the data sets required.
  • Create the Power BI report layout (basically an excel table with rows and columns) with the required information.
    • Create the formula's for specific columns.
  • Rename the columns so it's user friendly.
  • Apply the correct filters (per Crystal report).
  • Create a Date table with various date and time options.
    • Create the relationship with this date table and link it with my other tables successfully.

The issue I'm having is when I compare the newly create Power BI report to the Crystal Report, I'm finding that there's two columns of data that are incorrect values. These columns read data directly from my original ODBC data set.

For example, say Column A (data set; LANS.A) and Column B (data set; LANS.B) are showing 10 and 10 respectively on Power BI report, where as on Crystal (looking at the same data set) say 20 and 10 respectively instead.

 

This then throws off the calculations done in other columns thus making the Power BI report incorrect.

I'm not intirely sure what's causing this issue and I'm not too sure where to look. Would it be a relationship issue? Could it be that the data I'm trying to read just isn't made to work with Power BI? Keeping in mind, I didn't design nor do I manage the database I'm connected too.

 

If there's any other information you require or if you want a deeper explaination, please let me know.

Thanks in advance for any help, it's appreciated.

4 REPLIES 4
Community Support
Community Support

Re: Odd problem reading data via ODBC link

Hi @Joham ,

 

Please help post some captures , or dummy pbix is preferred. 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Joham
New Member

Re: Odd problem reading data via ODBC link

Hey @v-diye-msft ,

 

Thanks for replying to my question.

 

Below you can see the first row of my report. The issue I was mentioning before is to do with the report reading Ordered Quantity and Despatch Quantity incorrectly. These are just fields in a table, however to get them you need to make sure the relationships are correct as it requires an Account Code which comes from a different table.

 

PB1.pngFirst Row of my report

 

Below is the same row but in Crystal Reports (the report I'm trying to migrate to Power BI). As you can see, the ordered Quantity and Despatch Quantity are different. In this case, I was lucky in that the Value and Outstanding columns still came out the same due to the arithmatic involved.

Crystal Report 1.pngFirst Row of the Crystal Report I'm m

Here are the relationships currently on the Power BI Report. They match the relationships that Crystal has. You can ignore the LastRefresh_Local table as I'm using that for a card.

 

PB 2 - Relationship.PNGPower BI Relationships #1PB3 - Relationships.PNGPower BI Relationships #2

Below is how Outstanding is calculated. A simple formula.

 

PB4 - Outstanding.PNGHow Outstanding is calculated

 

I'll look into providing a dummy report shortly, but for now will that do? Is there any other pictures that could help you?

Thanks again.

Community Support
Community Support

Re: Odd problem reading data via ODBC link

Hi @Joham ,

 

The funtion is different between crystal report and power bi report, it's not applicative to copy the relationship/formula from crystal report, please share me your dummy pbix (Upload it to Google drive/Dropbox/Onedrive and share the link here) and let me know the expected result , I'd like to provdie the further valuable advice.

 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Joham
New Member

Re: Odd problem reading data via ODBC link

@v-diye-msft,

 

That's good to know. I've sent you a private message regarding this issue. Could you respond when you get a chance please?

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors