Recent content by goofaholix

I'm trying to Sum a conditional count in Analysis Services. This is how I'd do it in SQL; Sum(case when dbo.Dim_TaskResult.SpeciesName is not null and dbo.Dim_TaskResult.SpeciesName <>'Unknown' then 1 else 0 end) as Measure. I've created a Named Calculation in the dsv; case when...

Everything I tried failed however changing from p.RankRows <= tr.RankRows to p.Placement_Id <= tr.Fact_Tasks_Id changed the runtime from 19 hours to 3 seconds, I couldn't believe it. Both of the above do the same thing, I'm not sure why I was fixated on using rankrows. Thanks Bruce...

fredericofonseca I'll try the clustered index as you suggest but the query you suggested won't work as it returns row 1 as the placement id in all cases. Qik3Coder yes the join you suggest is what I rtried first, I chanbged to a cross join and a where statement when I weas starting to get...

I have a task table that I need to derive for each task which of the previous tasks was the parent. The parents are found by ranking the tasks by various criteria then joining the task table to the previous task and comparing them. Depending on a series of business rules (compared with the...

I have a refreshable spreadsheet which populates a data tab from MS Query. I've based a Pivot table on this refreshable data, because I don't know how many rows I will get I've selected the entire columns as my Pivot table dataset. The Pivot table lumps all the empty rows into a row headed...

I could do it as a stored procedure, or 3, but then I couldn't join it to other tables as if it were a table which I think means I'd need to do one stored procedure per query, possibly one per report, so a lot of extra code to maintain. I've tried indexing by my tests hacve shown it makes the...

I have reports based on an operational data store / data warehouse type database. I'm trying to find some solutions to help with our performance issues, many reports are highly parameterised and also need to return detailed data so scheduling, caching, and aggregate tables don't really suit...

The database on both servers is set to auto create and update statistics. The table on each server doesn't have indexes, I found by testing that adding indexes didn't improve performance in fact it tended to reduce it. The table I'm using for testing was copied from the new server to the old...

I have a drill down report that has column headings for group 1 data in the report header. When the user drills down to the details I would like to change the column headings so that they correspond to the details group. I've tried to create to column heading rows and toggle their visibility...

In both environments the source database is on the same server as the Report Server. I assume both servers are in the same server room, they haven't been moved. Bruce http://homepages.woosh.co.nz/bf/

This sample report was made large on purpose to benchmark performance between our servers. Most of our reports run within 10 seconds or are scheduled, however these are also generally taking twice as long on the new server which is why I created the above report to test performance. Bruce...

We are in the process of migrating our SSRS 2005 reports onto a new SSRS 2008 server. We are seeing execution times of the same reports on each server considerably slower on the new server. A sample large report I've used for benchmarking by scheduling it to run hourly over a day typically...

Correction. I found when I exited the database and went back I could then edit the data in the linked table with the primary key I just added. I've added a primary key to the actual table I was using and all seems well. Thanks. http://homepages.woosh.co.nz/bf/

The tables don't have primary keys, however if that were the issue then this wouldn't work on the 2005 server as well as the 2008 server I'd think. To test I've created a linked table to another table that does have a primary key in the same database on 2008. This one has the same problem...

I forgot to mention that we are in the process of migrating our sql server tables from 2005 to 2008. The tables on both are exactly the same, the security on both is exactly the same as far as I can tell. If I create linked tables pointing to 2005 they work ok, if i create them pointing to...