Anyone ever see an issue where Count() and CountRows() in SSRS don't actually match the rows in the report? Nothing special I can see and when I run the query in SSMS with the same parameters, it matches.

The report originally used Count(Fields!SomeField.Value, "DSet") and I changed it to CountRows("DSet") to see if that was the issue, but it's still the same. SSRS 2016

Not seeing any obvious correlation in the number returned either. For my date range of April 1, 2017 - April 1, 2017 the correct count is 87, it shows 186. For April 1, 2017 - April 30, 2017 the correct count is 11306, report shows 22460

I export the report to Excel, it has the correct number of rows in both cases.

There is some kind of DISTINCT issue. The query in the report has a DISTINCT (which is a smell, obviously, I'll need to dig into this query and see why they even need that). Changing the expression to =CountDistinct() fixes the count. Still doesn't make sense since the SQL itself is not meant to be exposing any duplicates to SSRS.

The Rubber Duck – Consultancy: another win.

Morning

@CadeRoux iirc you can put filters on the tablix object as well, not just the dataset

when in doubt, rebuild the report ground-up (as small as possible of course)

if you can't see, [mcve]

when you don't know, [repro]

Morning

Good Morning

@James how fitting.

crap, I won two hats

Fitting too, I hope

just turn off hats

@JohnP ha :D

@PaulWhitesaysGoFundMonica but I don't hate hats in general.

@ypercubeᵀᴹ I kind of expected to get one at some point without trying but so far I haven't

well except for the one where they forgot to add AND year=2019 which they then retracted

@TomV-TeamMonica one of them was secret. If it is from talking in chat, you may get it in about 10 min ;)

or it could be from the answer it got accepted today (or the enlightened badge it brought)

@TomV-TeamMonica Oh, so that must have been what I got a notification about some days ago. (Which I must have noticed too late, because when I went to look what it was, I found nothing.)

@AndriyM Yeah there was one for posting on new year or something in chat, and we all got one for posting in chat 11 and a half months ago

dba.stackexchange.com/posts/191188/revisions

stop trying to make that question happen, it's not going to happen

> The Community user will bump non-negatively scored, open questions every hour that have at least one answer scoring 0 and none scoring more than that.

from meta.stackexchange.com/a/48579

They should award a hat for hitting "I hate hats".

I wonder what Alanis Morissette would have to say about that

anyone 'round these parts use the sqlalchemy python lib much?

i'm being assured it has certain features and i'm beginning to suspect i'm being fibbed to

@PeterVandivier It certainly has some features.

@PeterVandivier i use it a bit

there's a lot of answers out on SO which plain just don't work any more, this is how i connect

if you're using pandas, you use the engine, if you're passing sql, use cnxn

...which looking at it, needs some tidying up lol

@PeterVandivier I just went through it with the report's author, there are no filters. But when fixed to no longer need the DISTINCT in the query, the problem goes away. Really seems odd, almost like there is some rewriting of the query or something letting SSRS see the set before DISTINCT. Not my preference for how this was built, but I can move on.

Preference would be to get as much of that SQL into direct calls to procs, but even still - I see in profiler that that is exactly what SSRS does, it wraps all that into a prepared SQL, it doesn't seem to re-write the query.

So... this company uses the IBM standards for database naming and design - it seems to me to be autistic, and making everybody's lives harder. Am I wrong to think that?

for example, all the tables and collumns are UPPER CASE @EvanCarroll, then they take out the vowels because apparently bits are still rationed.

makes it almost impossible for me to read

then why the hell do they demand everything be a fact or dimension?

especially when the definition for fact is 'something that is true'

dimensions are true, too

who named this shit?

A dimension may reside in the fact table if it is degenerate.

Fact and dimension have meaning in a data warehouse

But yes, I have lived in a few shops where they have the high holy naming standards and I have the scars to prove it. Tables were only allowed to use 18 characters in the name

Typically, facts usually means just scalars (which may or may not aggregate) and the keys to the dimension tables, but some dimensions (source transaction numbers, for instance) will pull into the fact because otherwise your dimension table cardinality will scale exactly with your fact table, which basically defeats the purpose.

@James we have a naming convention very similar to that. names are limited in length to 30 characters, though most are significantly shorter than that. Also, consonants don't necessarily exist the way you'd think (hope) they would. Each "word" in the name can only be a maximum of 5 characters... so, [CustomerNumber] would be [CUST_NMBR], not [CSTMR_NUM]. It's quite maddening.

Combine the upper case with my personal requirement to use the correct case for T-SQL keywords, and you pretty much get an ALL UPPER CASE STATEMENT. Lovely.

but, at least they have a convention, I guess.

Ugh, I prefer GodGivenMixedCase in identifier names, UPPER CASE FOR SQL, commas on the starts of lists.

When I was working on banking DW, everything was upper case like that - but the architect was from Microsoft.

But in banking, there is so much COBOL and stuff, it probably makes sense to them.

EBCDIC FTW

the arcitecht here did come from banking

EBCDIC is the best.

@billinkc Because Oracle

Nope, big blue

The best part was after I was venting to one of the Mainframers about the cobol limitation for the short table names it was "no, that's not been an issue for ages. We can address tables with 64 or 128 characters in the name"

So, naive idealistic me brings this to the standards committee (yes, that was part of a group of people's full time job) and got them to increase the length to 32 characters but in actually passing, they dropped it to 30

Cool, cool, now I can get useful table names.

Nope, it gave the data modelers more room to encode metadata about the table name to "help" the developers

CNFL_PRTF_CMPSTN_SS_U

Can't remember what CNFL was. PRTF was portfolio. CMPSTN was composition. SS was snapshot and U was ... also lost to memory

The best was they generated a table, can't find it in my notes that was 32 characters so violating their own standards an it was a dogs breakfast. Nothing but cryptic identifiers for something as trivial as like a Trade table

we have a list of contractions for words like that - the list is around 2000 words long or so. Without the list, it's almost impossible to be accurate in determining what the column actually stores.

Also, for some ungodly reason, IBM refuses to create VM disks greater than 2TB here. As if 2TB is a limit nowadays. So, we have "disks" that have mount points all over the place. Talk about painful.

I really enjoy encoding the data type in the column names

that's just SO helpful, isn't it?

TRD_DT_DTS

"trade date" DateTimeStamp even though it's just a date data type the dictionary hasn't been updated to account for fancy new types

"fancy". nice.

CNFL = conflation ?

conflagration?

as if DT doesn't already mean its a date field. that's obvious, hah.

unless it means date-time

or DOT

oh, no that would just be DOT.

They also had this habit of trying to precisely nail the precision of a number so whole numbers would always been numeric(X,0)

genius

^

can't ever be too specific about that stuff.

lol

I put together a graph showing wasted space over using numeric in favor of an int/smallint/tinyint and it was in hundreds of gigabytes (12+ years ago) that was wasted just due to bad data types

personally, I prefer to encode everything by hand. it makes life more interesting. lol, no, not actually.

And of course, we had how many instances where "This will never be more than 2 digits" until it hit three...

Funny enough, I was not popular with that team but they were the only ones who could produce a data model. It was a vexing time in my life

@billinkc the old gem, "never". that's the best indicator that it will certainly change, and probably tomorrow.

wow, Access sucks for supportability. dba.stackexchange.com/questions/255812/…

when designing table and column names, it's best to try to use keywords as part of the name. makes life more interesting™

We have a table here called [Case]

does it have two rows: upper and lower?

That would be funny. But they are literally medical cases

shouldn't the table be called [Cases] in that case?

Looks like the convention here is singular table names

ah.

On the other hand, there is a lot of redundant "Data" on the end of tables that contain specific breakdowns of cardiovascular observations. So abnormalityData, pathologyData, syndromeData, chamberData, etc. But some don't have it, like cardiovascularTopology, and it's not just due to length, like there is complicationTreatmentData (also, all this particular DB is camel-case for some reason - while the transactional database is proper-case)

ahhh, legacy can be a bitch sometimes.

They just aren't sticklers for a standard. It's not a terrible design. It's got issues, but not the worst I have seen.

Partly too much faith in the optimizer. High-performance reporting just needs a lot more thoughtful indexing and/or persistence/denormalization.

Or well, at least "reasonable" performance

healthcare DB's can get pretty horrendous. You're not looking at Nextgen, are you?

@James No, this is a product we sell for cardiovascular - it is used inside of IBM's Merge Hemo product and sold also by Cerner.

Well, there's several bits. Most of our knowledgebase product is basically structured cardiovascular document management ("reporting") with light database, then this is shredded to an analytics database for reporting but I have a new turnkey datamart product which eventually will replace a lot of that in phases.

Which will then get customized to hell and back as nobody likes the turnkey solution because their business model couldn't possibly be changed to use what they bought

</cynical>

@billinkc Well, it's all about making the data accessible. Right now the data is so structured and complex that it's not easy to plug it into PowerBI. This schema is much more accessible for people with PowerBI, Tableau or whatever.