To display a column value for every row if its repeated in report builder.

Question

To display a column value for every row if its repeated in report builder.

s.lekha

Right there with Babe

Points: 766
More actions
January 12, 2009 at 2:12 am

#198132

Hi all,
Iam creating a report which displays journals and articles using report builder. If there is a set of articles under the same journal name, the subsequent rows for a journal does'nt display the journal name in report builder, it displays only once and all set of articles under that are displayed for every row . I have not applied any grouping or sorting conditions so not able to figure out what's the fix here:ermm:
ex. the report looks something like this
journal article date
j1 a1 d1
------------------------------------
a2 d2
d3
-----------------------------------------
Even if an article is repeated , it is displayed only once as shown above.
It should be displayed like this
journal article date
j1 a1 d1
-------------------------------------
j1 a2 d2
----------------------------------
j1 a2 d2
Can this be acheived using report builder or is that the way a report builder is designed? Please help.
Thanks,
Lekha

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

There should be a property on the text box for Hide Duplicates. This should be set to false.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

s.lekha Right there with Babe Points: 766 More actions · Answer 2

Hi,

Thanks for your reply.

As you had mentioned about the textbox, this is available for report designer, but report builders reports are built on datatable columns, hence such properties are not available. I was going through some links for the same, in report builder F1 help too, they describe like this:

Use the Remove Duplicates dialog box to indicate that you want to keep all of the duplicate data or to remove some or all of the duplicate data. Each set of duplicate data is grouped by heading. The number of options below each heading group depends on the number of duplicates. To open the Remove Duplicates dialog box, you need to expand the formula in the Define Formula dialog box and click Remove duplicates of.

My understanding is for any column which has to be repeated, you need to right click that column and there is a 'edit formula' link, when you click this the 'define formula dialog box' opens where you can expand the formula properties, as the above note says i dont find any remove duplicates dialog box,there is only 'No filter'option present when you expand .

So guys has anyone worked on such a requirement, if you have come across something like this, i would appreciate if you could guide me.

Thanks,

lekha

vicky-954852 SSC Enthusiast Points: 192 More actions · Answer 3

Hi!!

In the journal and article fields properties there is a checkbox that say "Hide duplicates", this shouldn't be checked

I send you screens

s.lekha Right there with Babe Points: 766 More actions · Answer 4

Hi vicky,

thanks for your reply.

The screens which you have added is from report designer, but what iam working is on report builder. I have attached the screens and have pointed out about the remove duplicates option which should be enabled for any column in report builder.

Please have a look at the image attached.

For report builder, this is where the option should be available unlike the 'hide duplicates ' option in report designer..

any suggestions.......

thanks,

lekha

neha.kamal21 SSC Enthusiast Points: 164 More actions · Answer 5

Hi,

I have also encountered the same issue.

Have you got a reply how to resolve this ,if yes please post it here.

thanks,

Neha

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

It's actually pretty easy to do... but in T-SQL and you need something to guarantee the order. If you have something to guarantee the order, I can show you how.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

neha.kamal21 SSC Enthusiast Points: 164 More actions · Answer 7

I have an entity "Invoice" returning 1 row and an entity "Component" (linked to Invoice) returning multiple rows per Invoice.

e.g.

Invoice has fields

Invoice No. Details

---------------------------

123 ABC

And Component has fields

Invoice No. components

-----------------------

123 Component1

123 Component2

When i run a report with Invoice grouping it gives,

123 ABC Component1

However ,i want it to display,

123 ABC Component1

123 ABC Component2.

I don't want it to hide duplicates on primary group.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Neha,

That's a whole lot different than what I thought you were asking. It's also simpler to resolve... a simple inner join between the two tables should do the trick. As others have said, you may have to turn of some of the "hide" features to get the display to work correctly.

I do have to ask just to be sure so no insult intended... you do know how to join two tables using aliased table names, don't you? And It's OK to say "NO".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

neha.kamal21 SSC Enthusiast Points: 164 More actions · Answer 9

I know how to hide duplicates in Report designer.

And also the inner joins ,i am aware of.

But when i am building a report through report builder ,it hides duplicates.

Is it coz my cardinality is wrong?

I have :

Invoice Component Role Properties:

Binding: Component - Invoice (Source)

Cardinality:OptionalOne

Related Role - Invoice

Binding: Component - Invoice (Target)

Cardinality:OptionalMany

This is because ,one invoice can have zero or many components but one component can have one invoice.

Also, i checked the quries generated by SQL profiler when i run report builder report.

It shows the Invoice Left join Component if i use above cardinality(which is what i want).

So ,i am so confused about cardinalities.

I have attached two scenarios :

One if i select Invoice entity first and then Component enity (which groups component to Invoice and hides duplicates)

Another is if i select Component entity first and then Invoice.

Thanks for your help...

Scott Murray-240410 SSCarpal Tunnel Points: 4649 More actions · Answer 10

You need to check your groupings on the Report Builder. Make sure you have all your fields in one detail group and not have multiple levels of grouping. You can check your groupings by noticing if tabs exists above each textbox. For each tab, your data will be grouped.

neha.kamal21 SSC Enthusiast Points: 164 More actions · Answer 11

Thanks,

So that means it by default does the grouping by first group selected.

Everytime i dont wanna group by an entity i need to drag the new field to the left of the entity - not to the right else it would be grouped.

Thanks for letting me know.

Scott Murray-240410 SSCarpal Tunnel Points: 4649 More actions · Answer 12

I believe your actions are correct. To get all detail there should be just one tab above the textboxes.