Hi,
I have a reporting requirement based on the following:
Each client has between 1 and 10 fields (specific to an area of business) attributed to them, out of a collection of 100+ fields. I need to report on multiple clients (in the same report) and only return data from the fields relevant to each specific client. Can anyone suggest a suitable way to approach this problem?
Thanks in advance,
Richard
Quote from: NiceCube on 13 Apr 2017 03:39:15 AM
Hi,
I have a reporting requirement based on the following:
Each client has between 1 and 10 fields (specific to an area of business) attributed to them, out of a collection of 100+ fields. I need to report on multiple clients (in the same report) and only return data from the fields relevant to each specific client. Can anyone suggest a suitable way to approach this problem?
Thanks in advance,
Richard
Hi,
This sounds tricky. How can you determine which fields relate to which clients? Are you expecting to see all clients in the same reporting container (eg list/crosstab) or is each in a different container?
MF.
Hi,
Our data guy has built a table (SQL Server) that has the client name and the field name (one row for each field name). I'm hoping to show multiple clients in one container. Yes, tricky indeed.
Thanks,
Rich
Quote from: NiceCube on 13 Apr 2017 04:12:42 AM
Hi,
Our data guy has built a table (SQL Server) that has the client name and the field name (one row for each field name). I'm hoping to show multiple clients in one container. Yes, tricky indeed.
Thanks,
Rich
I think you can already appreciate what a bad design decision your data guys took here. Whoever designed that approach mustn't know anything about the way SQL or reporting works.
Some questions. Do all clients have the same number of fields? I'm assuming not? How can you know how many fields to accommodate in your reporting container? What would your titles be if the fields are not consistent between clients? How would a person consuming the report know what they were looking at if the contents of the report change for every client? I'm struggling to see how this could make any sense for someone reading this report, notwithstanding the technical challenges. Is this really what your users are asking for?
MF.
Yes, all good points. Needs a rethink. I hadn't (stupidly) considered the column headers.
I guess the way forward would be to have one client per container (list/crosstab). But even then the challenge remains as the clients don't all have the same number of fields (though there's a max of ten).
Thanks,
Rich