Does anyone know why a report created against a report model interprets data differently to a report created and published in BIDS?
I have a report that's based on an sp. Although the tables are relational, the relational data isn't very big. For example I have a service provider and a related set of opening times. At most I'll have seven days worth of opening times. The same applies to their accreditations (maximum of 4). So, I've written a function that converts the related table into a string. So:
Table X
Provider 1, Mon, 9-5
Provider 1, Tue, 10-5
becomes: Provider 1 - Mon 9-5, Tue 10-5
This works fine for some of them, but for others I want to display the data on separate lines (like in the original table). I'm not keen on using sub-reports, and my conversion works fine, so I'm happy there.
Trouble is, when my function uses char(10) + char(13) to separate the data it's ignored in BIDS. To get around this, I use <br> to separate the data and my BIDS expression is:
=Replace(ColumnX, "<br>", vbCrLf)
Again this works fine. However, when I call the same function for an expression in a report model it doesn't like either vbCrLf or Char(10) & Char(13). However, if the function uses Char(10) and Char(13) as the separator, the report model is happy again and reports the column correctly.
Now I could use both in the function (since the chars are ignored by BIDS), which will work, but it's a fudge rather than a solution.
After all that preamble, my question is why do BIDS reports interpret data differently to reports based on a report model, and what is the correct way to make it work in both environments?
I have a report that's based on an sp. Although the tables are relational, the relational data isn't very big. For example I have a service provider and a related set of opening times. At most I'll have seven days worth of opening times. The same applies to their accreditations (maximum of 4). So, I've written a function that converts the related table into a string. So:
Table X
Provider 1, Mon, 9-5
Provider 1, Tue, 10-5
becomes: Provider 1 - Mon 9-5, Tue 10-5
This works fine for some of them, but for others I want to display the data on separate lines (like in the original table). I'm not keen on using sub-reports, and my conversion works fine, so I'm happy there.
Trouble is, when my function uses char(10) + char(13) to separate the data it's ignored in BIDS. To get around this, I use <br> to separate the data and my BIDS expression is:
=Replace(ColumnX, "<br>", vbCrLf)
Again this works fine. However, when I call the same function for an expression in a report model it doesn't like either vbCrLf or Char(10) & Char(13). However, if the function uses Char(10) and Char(13) as the separator, the report model is happy again and reports the column correctly.
Now I could use both in the function (since the chars are ignored by BIDS), which will work, but it's a fudge rather than a solution.
After all that preamble, my question is why do BIDS reports interpret data differently to reports based on a report model, and what is the correct way to make it work in both environments?