Post reply

Lookup Value/Join 2 Datasets

  • August 11, 2015 at 2:51 am

    #320437

    Hi All

    I'm having some trouble with looking up a value in a dataset. In dataset1, I've got a running total:

    =Sum(Max(Fields!Amount.Value,"Group3"))

    If you consider there are 3 tables in dataset1, table a, b & c, where there are 1 to many relationships between all of them, the field I've summed above is from table b, and I've summed it in such a way to prevent multiplying the field by the no. records in table c. Hope you're all following...

    I've grouped the report by a field in table c; a big case statement which gathers records and lumps them into some more manageable group names. The problem occurs when records don't exist in table c for certain group names, the group name doesn't display in the tablix (which is to be expected). However, against each of these group names is an amount I want to display (which for now I'm using a simple IIF group name = "X" then amount to display). So, I'm trying to force these groups to show, and to do this I've created dataset2 which consists of a full list of the possible group names I've used in the case statement in dataset1:

    with mytable as (

    select 'groupname1' as Group,

    union all

    select 'groupname2',

    union all

    etc.....

    So, with all that in mind, how do I then retrieve/lookup the value of the formula in bold above? Where I've used the Lookup function in the past, it's been relatively simple because I'm either looking up a field within a different dataset, or the dataset I'm looking up from consists of a similar sql query/data structure. In this instance however, dataset2 contains none of the tables used in dataset1 (so I can't group a tablix and Lookup an expression based on the same tablix groupings).

    Does any of that make sense? Any help at all is much appreciated!

    Thanks

    Rich

  • August 13, 2015 at 10:44 am

    #1819802

    Hi,

    Do you at least have a field that is common between the datasets you're trying to join? You can use LOOKUPSET to get a set of data if that's any help.

    Cheers,

    Jim.

    SQL SERVER Central Forum Etiquette[/url]

  • August 14, 2015 at 12:26 am

    #1819953

    I think it would be easier to put the query you've created in Dataset2 for all the group names into Dataset1, and union those two result sets. For the result set that has the full list of group names, I'd also include $0.00 in the Amount column, so the groups show, and the running total isn't affected.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply