"Who's the idiot that designed the file destination? "

Hi everybody,

I'm new -- sorry, I couldn't figure out how to reply to this thread, I think because it's locked- so I started a continuation.

What I am wondering about is if you indicate "text qualified= true" then why does it still get confused by embedded commas in quote-delimited text; I would think that the "text qualified" parameter would take care of that. Can anyone explain?

So as a work around I am thinking of running a perl script via "Execute Process Task" and clean up the commas that way, since I have little control over how it is produced.. the app it comes from has no ability to contol output, and it outputs it's tables as comma seperated files, with text fields surrounded by quotes. I was hoping to stay inside SIIS for the whole etl job-- must admit some disappointment.

Any suggestions would be greatly appreciated!!

Thanks!!

jk

At the outset, I wonder if you went to the school when you were a child.|||

enric vives wrote:

At the outset, I wonder if you went to the school when you were a child.

Enric,

If you're referring to the title of the thread, that was the name of the original thread. So its not this guy's fault.

-Jamie

|||

jk-olap wrote:

Hi everybody,

I'm new -- sorry, I couldn't figure out how to reply to this thread, I think because it's locked- so I started a continuation.

What I am wondering about is if you indicate "text qualified= true" then why does it still get confused by embedded commas in quote-delimited text; I would think that the "text qualified" parameter would take care of that. Can anyone explain?

So as a work around I am thinking of running a perl script via "Execute Process Task" and clean up the commas that way, since I have little control over how it is produced.. the app it comes from has no ability to contol output, and it outputs it's tables as comma seperated files, with text fields surrounded by quotes. I was hoping to stay inside SIIS for the whole etl job-- must admit some disappointment.

Any suggestions would be greatly appreciated!!

Thanks!!

jk

Hi jk,

Could you post a sample that exhibits the problem?

Thanks

Jamie

"Show Group Subtotals" Reappear when opening a report

This should be pretty simple...

I have a report that I've designed in Report Builder with many groups. I added an aggregate. By default, it subtotals at each level. For all the groups (but 1) I right clicked on the group header and unchecked "Show Group Subtotals".

I save the report to the report server in "My Reports". When I open the report, all of the subtotals return.

Does anyone know why this occurs? I'm going to have a hard time selling this to business users if they have to go through each group and uncheck the "Show Group Subtotals" every time they want to modify a report.

Regards,

Dan

This is a known issue that is fixed in internal builds. The release in which this fix will be available has not been determined yet. If it is a showstopper for you, you might consider calling customer support and requesting a hotfix.

|||

Thanks for the quick response! I'll wait to see how users react before pursuing it further.

Thanks again,

Dan

"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...