Comparing fields from multiple datasets

I have one report with two dataset (one from a table and one from a Cube).
Within my report are two tables, one uses the â'Tableâ' dataset and one uses
the â'Cubeâ' dataset. The two dataset are similar (i.e. they return similar
data/fields).
I would like to compare some of the fields from the first dataset with some
of the fields from the second dataset and then use conditional formatting to
turn the text fields red if the two fields do not match.
I know how to write a conditional formatting expression for a cell within a
table but I do not know how to compare a value from one dataset with a value
from another dataset. Note: I do not want to compare aggregates; I want the
comparisons to be at the detail row level. Is it even possible? If so, how
is it done?
Would one possibility be to compare the values/contents of textboxes at the
textbox level? If so, how is that done?
Any info on this topic would be greatâ?¦ Thanks=IIF(ReportItems!txtTextBox1.Value = ReportItems!txtTextBox2.Value,
"BLACK", "RED")
Matt A|||That's a nice idea but it won't work. I'm trying to compare values from two
different datasets that reside in two different tables. I tried your code
(plugging in my textbox names) and it errors out.
Error 2 [rsReportItemReference] The Color expression for the textbox
â'BalanceAmtâ' refers to the report item â'CompositBalanceâ'. Report item
expressions can only refer to other report items within the same grouping
scope or a containing grouping scope.
I welcome any more suggestions you may have though.
Thanks,
Bill
"reportdude" wrote:
> =IIF(ReportItems!txtTextBox1.Value = ReportItems!txtTextBox2.Value,
> "BLACK", "RED")
> Matt A
>|||Try placing each dataset in the same control such as a listbox to
encompass scope-

Comments on configuring partitions

Hello,

I'm currently building partitions into my cube. My scenario is thus:

I have 74 measure groups (yeah I know - it sounds ridiculous but trust me, it IS valid)

Each measure group is to be partitioned by year

We are (currently) going to partition them over 12 years

In other words I have to build 74*12 = 888 partitions

Quite apart from the fact that its going to take most of my working week (and it'll be the most boring working week of my life) there is huge potential for human error here.

As stated above I am applying the same partitioning strategy for each of my 74 measure groups so I am repeating the same basic steps 74 times. It occurs to me that there MUST be a better way and if there isn't - there should be!

Does anyone know of a better way? I'd like to automate it somehow but I don't see how that is possible seeing as I have to edit a SQL statement for each one.

Anyone got any thoughts? I'm happy to provide feedback to MSFT about how this could be improved - contact me offline if interested - jamie.thomson[@.]nospam.conchango.com

ta

Jamie

I've done something similar using an SSIS package - I just scripted out the XML/A necessary to build a partition to use as a template, then created a FOR loop inside my package, used some script to generate the XMLA (and the SQL inside it) and then executed it. It worked pretty well, and I'll send you the code even though I know you're more than capable of doing this yourself. I agree it would be nice to have this functionality built into BIDS.

One thing that caught me here though was the problem with setting the slices for partitions that came up at the BI evening we were at, and which is discussed on Mark Hill's blog:
http://markhill.org/blog/?p=11
I created around 600 partitions on my cube and some of them turned out to have very little or no data in; this meant that no slicers got set automatically and my query performance suffered as a result.

Chris

|||

[Chris sent me this stuff offline]

Cheers Chris. That's good stuff and works well.

HOWEVER, this only works once the cube has been deployed. I want the partitions to appear in my BIDS solution (I should have said that originally - sorry about that). Is there a way to reverse-engineer them back in?

-Jamie

|||

Yes, you can reverse engineer a project from a deployed database very easily. All you need to do is open BIDS, then File/New/Project and select "Import Analysis Services 9.0 Database".

Chris

|||

After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?

That would be brilliant!! Microsoft Connect here I come!!!

-Jamie

|||

Jamie Thomson wrote:

After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?

That would be brilliant!! Microsoft Connect here I come!!!

-Jamie

And here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202

Click through and vote would you please!!! And leave a comment. Much more likely to get it that way!

|||

Hi Jamie:

You could probably have relational one-to-one for your partitions. That way - you could iterate that in SSIS and conditionally create the partitions if required - and then process them.

Hope that helps.

Suranjan