Prev: Linking 2 subreports to main
Next: Strange join created by report model 2005
From: fig000 on 10 Apr 2010 13:31
Hi,

Two issues: (I am using SSRS 2005)

1. I have a report that gives very different results for one grand total between the dev version and the desployed version. Same report, same stored procedure, but a differece of 20000 for this one total between the two versions. I even made sure that the report was being deployed to the report server by putting a text box that said "test" on the dev version. I had thought that perhaps I'd actually been deploying to a different folder by accident. But this "test" label appeared on the deployed version after I added it in the dev version.Obvioulsy it's the same report, just functioning differently between dev and server environments.

Tjhe differences are not great as a percentage of the whole number (perhaps a half a per cent) but are noticable and I would think the same report would render the same results. Any ideas?

2. This is more of an sql related question but impacts my ssrs reports: I have a query that was giving me consistent numbers; I've cloned it and gotten differents reports to tie. I wanted to add anothter table to the query that I know has a one to one relationship to records in the current query. No dups there.
I added the table I will refer to as "newtable" and then a field from that same "newtable" to the query and got some different total results. I went back to the original query that was giving consistent numbers and this time only added the table in a join without putting the field from that table into the query. I still got different total results from adding the the join only.

It turns out that still another table I will call "history table" has occasional problems with unique keys. We can have records with Order 1 and order line 2 as a key in this table with an associated cost fields with a value of, say, 1.5. We can also have records in this history table that have a key of Order 1 and Order Line 2 (same key) with the same cost field having a value of 2.5. Obvously, though the keys should bring about unique records, we have some cases where they don't.

Obviously a garbage in garbage out problem and if which of these nonunique records get's picked is random, based on the sql engine ,possibly none of the returned values are correct because what's chosen is based on some internal logic of the query and not a unique key

The odd thing is strange that "new table" and "history table" or not joined to each other and are not at all releated. What I've found is that simply adding the "new table and joining it to another table entirely in a one to one relationship actually makes the choice of the non unique records in the history table change. This wouldn't seem a problem except this report is tied out to other reports in terms of totals. My only recourse is, even if all the records in "history table" are wrong, I'd like it if all variations of the query choose the same "wrong" records so I can have agreement in the grand total between reports. I had thought of adding another field to the key to alleivate this but it doesn't exist in the history table.

I'm assuming that there may be no answer for this, short of moving the join of "new table" around in the query and hope for the best. In the long run that is not a good solution becaue the results could change based on what data is being pulled.

My point in bringing this up is to find how to control this phenomenon and what might cause it.Dealing with dirty datat is never pretty but I'm hoping there is a way to have my reports tie.

Any ideas?

Fig000



From http://www.developmentnow.com/g/115_2005_9_0_29_0/sql-server-reporting-services.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
 | 
Pages: 1
Prev: Linking 2 subreports to main
Next: Strange join created by report model 2005