Aggregete() function doesn't work for SQL query results! - Part 2

I am writing my own MDX query inside Reporting Services, bypassing the graphic MDX generator.

Both MDX query and SQL query return identical results and both have detail level values and high level values.

During the flattenning process, null value from MDX is still null, but null value from SQL is translated into blank.

Thus only the MDX result works for Aggregate() function, not the SQL result. Because Aggregate() function is triggered by null value, not blank.

Someone suggested to use MDX stored procedure to do custom calculations. Thus my report is calling a MDX query, not a SQL query. I have tried this approach already, but MDX is not as flexible as SQL. For example, in SQL, I can easily join, pivot, order by, group by, filter...None of these is easy in MDX.

If I know how to make reporting services treat null as null or to make Aggregate() function be triggered by a blank value, then my problem is solved.

Does Aggregate() funciton only works with MDX, not SQL?

Thanks,

Bo Dong

bo_dong@.yahoo.com

The Aggregate() function only works in combination with a data extension that implements the IDataReaderExtension interface (see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_9lit.asp).

The "Analysis Services" data extension available in RS 2005 implements this interface and internally detects aggregation rows based on nulls in hierarchies and exposes that information through the IDataReaderExtension interface.

Note: The "SQL Server" data extension available in RS 2005 does not implement this interface.

You can implement your own custom data extension (http://msdn2.microsoft.com/en-us/library/ms154655.aspx) and then implement the IDataReaderExtension interface - based on that you have full control in your custom data extension of how exactly aggregate rows from your relational query are marked.

-- Robert

Aggregate Query Help

I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high.

How do I write a query to obtain the correct totals?

Table 1 - dbo.QM_Results
Columns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumber

Table 2 - dbo.PM_ForecastView
Columns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.Forecast

Select substring(dbo.QM_Results.Special8,0,6) AS Hierarchy,
substring(dbo.QM_Results.SessionName,0,11) As CourseCode,
count(dbo.QM_Results.PersonNumber) TotalAssociates,
sum(dbo.PM_ForecastView.Forecast) TotalForecast

From dbo.QM_Results INNER JOIN dbo.PM_ForecastView
ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)

where SessionMid in ('96882139', '23620891', '45077427', '29721437')
AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')
Group By substring(dbo.QM_Results.Special8,0,6),
substring(dbo.QM_Results.SessionName,0,11)

Sample of data returned with my current query.

Hierarchy CourseCode TotalAssociates TotalForecast
EZHBA CARD167200 1179 141480
EZHBA CARD167201 1416 169920
EZHBA CARD167202 1119 134280
EZHBA CARD167204 99 11880

Results when I run aggregate query separately
Actual Total taken
Hierarchy CourseCode TotalTaken
EZHBA CARD167200 393
EZHBA CARD167201 472
EZHBA CARD167202 373
EZHBA CARD167204 33

Forecasted Total taken
Hierarchy CourseCode Forecast
EZHBA CARD167200 999
EZHBA CARD167201 900
EZHBA CARD167202 800
EZHBA CARD167204 800

I strongly advise you to create real columns for Hierarchy and CourseCode. It will simplify everything, and querying with substring criteria is a disaster for performance.

Joining with a View is somewhat dangerous, since the query plan can give you more rows than you expect.

How is the View defined?

|||

Ok what is the best way to accomplish this.

I've attached sample data for each table.

I just want to return

Hierarchy, -- From Forecast table
Course Code, -- From Forecast Table
Total forecast, -- From Forecast table
TotalActualAtteneded -- From PM_QMDetails table. Count(PersonNumber)

Sample PM_QMActual Data

ResultIDCourseCodePersonNumber SessionNameSessionMidSessionLidStartDateEndDateTotalScoreScoreBandTitleHierarchyHierarchyAll-371630300CARD1672001CARD167200_More_Module_1968821393949674116:02.024:00.07FailedEZHBAEZHBA...AC-371637743CARD1672001CARD167200_More_Module_1968821393949674124:52.025:54.010PassedEZHBAEZHBA...AC-371558197CARD1672011CARD167201_More_Module_223620891638252528:49.033:34.06FailedEZHBAEZHBA...AC-371560271CARD1672011CARD167201_More_Module_223620891638252534:30.035:50.07FailedEZHBAEZHBA...AC-371579578CARD1672011CARD167201_More_Module_223620891638252536:44.037:57.010PassedEZHBAEZHBA...AC-371589091CARD1672021CARD167202_More_Module_3450774271690432345:13.046:32.04FailedEZHBAEZHBA...AC-371561743CARD1672021CARD167202_More_Module_3450774271690432347:22.048:01.06PassedEZHBAEZHBA...AC-357270804CARD1672041CARD167204_More_Module_4_CUSTOMER_SATISFACTION297214375675925933:50.036:23.011PassedEZHBGEZHBG...AA-382315943CARD1672021CARD167202_More_Module_3450774271690432341:46.043:31.06PassedEZHBGEZHBG...AA-382448705CARD1672001CARD167200_More_Module_1968821393949674150:22.053:59.010PassedEZHBGEZHBG...AA-382317954CARD1672011CARD167201_More_Module_223620891638252504:08.012:43.09PassedEZHBGEZHBG...AA-374600403CARD1672001CARD167200_More_Module_1968821393949674145:46.048:11.09PassedEZHBDEZHBD...AA-374575303CARD1672011CARD167201_More_Module_223620891638252549:04.032:44.09PassedEZHBDEZHBD...AA-373276591CARD1672021CARD167202_More_Module_3450774271690432340:34.041:25.03FailedEZHBDEZHBD...AA-373271117CARD1672021CARD167202_More_Module_3450774271690432342:16.042:54.06PassedEZHBDEZHBD...AA-374385179CARD1672021CARD167202_More_Module_3450774271690432330:21.034:36.06PassedEZHBGEZHBG...AA-374505709CARD1672001CARD167200_More_Module_1968821393949674138:42.047:07.09PassedEZHBGEZHBG...AA-374465177CARD1672011CARD167201_More_Module_223620891638252552:39.002:01.010PassedEZHBGEZHBG...AA-357277598CARD1672041CARD167204_More_Module_4_CUSTOMER_SATISFACTION297214375675925933:58.039:13.012PassedEZHBGEZHBG...AA-381914391CARD1672021CARD167202_More_Module_3450774271690432320:52.023:40.06PassedEZHBAEZHBA...AC-392795564CARD1672001CARD167200_More_Module_1968821393949674109:25.018:26.010PassedEZHBAEZHBA...AC-392798106CARD1672011CARD167201_More_Module_223620891638252525:38.042:54.010PassedEZHBAEZHBA...AC-384293893CARD1672001CARD167200_More_Module_1968821393949674135:57.040:01.08PassedEZHBAEZHBA...AC-384236041CARD1672011CARD167201_More_Module_223620891638252545:29.052:54.05FailedEZHBAEZHBA...AC-384224651CARD1672011CARD167201_More_Module_223620891638252554:54.056:13.07FailedEZHBAEZHBA...AC-384234655CARD1672011CARD167201_More_Module_223620891638252556:52.057:45.010PassedEZHBAEZHBA...AC

Sample Forecast data

SiteHierarchyCourseCodeCourseNameStartTimeEndTimeForecastCustomer SatisfactionEZHBACARD167202Module 38:00 AM6:00 PM800Customer SatisfactionEZHBDCARD167201Module 2 5:00 PM9:00 PM100Customer SatisfactionEZHBGCARD167202Module 31:00 PM9:00 PM25Customer SatisfactionEZHBDCARD167204Module 48:00 AM5:00 PM300Customer SatisfactionEZHBDCARD167201Module 2 10:00 AM5:00 PM145Customer SatisfactionEZHBACARD167204Module 48:00 AM12:00 PM100Customer SatisfactionEZHBGCARD167204Module 46:00 AM9:00 AM300Customer SatisfactionEZHBGCARD167202Module 39:00 AM5:00 PM45Customer SatisfactionEZHBACARD167201Module 2 8:00 AM6:00 PM900Customer SatisfactionEZHBACARD167204Module 48:00 AM6:00 PM700Customer SatisfactionEZHBACARD167200Module 11:00 AM9:00 AM999

|||

Something like this...

SELECT dt.Hierarchy, dt.CourseCode,dt.TotalForeCast,COUNT(d.PersonNumber)as TotalActualAttendedFROM(SELECT f.Hierarchy, f.CourseCode,SUM(ForeCast)as TotalForeCastFROM PM_ForecastGROUP BY f.Hierarchy, f.CourseCode) dtLEFTJOIN PM_QMDetails dON dt.Hierarchy=d.HierarchyAND dt.CourseCode=d.CourseCode

|||

Thanks that is exactly what I needed to get the final result. Here it is.

[Code]

SELECT dt.Hierarchy,

dt.CourseCode,

dt.TotalForeCast,

COUNT(d.PersonNumber)as TotalActualAttended

FROM

(

SELECT f.Hierarchy, f.CourseCode,SUM(ForeCast)as TotalForeCast

FROM dbo.PM_ForecastView f

GROUPBY f.Hierarchy, f.CourseCode

) dt

LEFTJOIN PM_QMDetails dON dt.Hierarchy=d.Hierarchy

AND dt.CourseCode=d.CourseCode

GroupBy dt.Hierarchy,

dt.CourseCode,

dt.TotalForeCast

[/CODE]

After Transaction Log Grows, it won't shrink.

The other day I had someone truncate an extremely large transaction log (100GB) but it wouldn't return the space to the OS, not even after a backup and restore operation. There’s an interesting bit of trivia around shrinking this file. When the log file is truncated, you may not see all of the space returned to the OS. The reason is the way the data might be spread. Here’s the way we think data looks in the log file:

<BeginFile>xxxxxxxx--<EndFile>

So when you truncate it and it gets another couple of transactions, it should do this:

<BeginFile>xx<EndFile>

But in fact it might look more like this:

<BeginFile>xx-xx-xxxxxxx<EndFile>

So if you truncate the entries and a couple are left it really looks like this:

<BeginFile>x--x<EndFile>

So when you shrink it, you don’t gain any space back because of this file marking. So what do you do?

There are two ways to shrink a large log file, both of which are a bit painful, and not always practical in production. The first is to write enough transactions into the log to “bump” the data all the way to the end. Since that makes it contiguous, when you truncate it you’ll get the shrink you’re looking for – probably. You can do this by creating a table, adding thousands or millions of rows to it, and then deleting them and the table. Of course this slows down a production system while you do it, and it’s difficult to figure the math.

The other way is more problematic in a production system, but works every time. Take a full backup of the database. Follow the process to “detach” the database as described in Books Online. Find the transaction log file for that database (and only that database) and delete it. Then, “attach” the database. The system will automatically build a new, clean log file. Obviously, there are a lot of places you can be careless here, so I recommend this process only for extreme cases.

Hi,

have a look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Good info - but you'll find that the DBCC shrink commands won't work with the TL as I mentioned. It's less a question than some real-world experience - but thanks for the links! I think they will help people to understand how the logs and files are used.

After instalation the report server returns:Attempted to load a 64-bit assembly on a 32-bit plat

Our server is 64 bits OS. After SQL instalation we try to load the Reports web site and the server return the following error, can anyone help us?:

Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.Server
Error in '/Reports' Application.

Attempted to load a 64-bit assembly on a 32-bit platform. Use
ReflectionOnlyLoad() instead if trying to load for reflection purposes.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code.

Exception Details: System.BadImageFormatException: Attempted to load a 64-bit
assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to
load for reflection purposes.

Source Error:

An unhandled exception was generated during the execution of the current
web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:

[BadImageFormatException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0
System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211
System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141
System.Reflection.Assembly.Load(String assemblyString) +25
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32

[ConfigurationErrorsException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596
System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479065
System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46
System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177
System.Web.Compilation.BuildProvidersCompiler..ctor(VirtualPath configPath, Boolean supportLocalization, String outputAssemblyName) +180
System.Web.Compilation.ApplicationBuildProvider.GetGlobalAsaxBuildResult(Boolean isPrecompiledApp) +3446645
System.Web.Compilation.BuildManager.CompileGlobalAsax() +51
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +462

[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Compilation.BuildManager.ReportTopLevelCompilationException() +57
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +612
System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) +456

[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +3426871
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +88
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +149

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET
Version:2.0.50727.42

http://www.codeplex.com/powershellremoting/WorkItem/View.aspx?WorkItemId=903