[solved]master detail type report

[BIsrik]

Hi
I have a report which is almost similar to the master detail report. The report requirement goes like this..

The report is to be developed for the SLA analysis of a department. The SLA of the department per month is displayed and then the details of that month is shown just below that...An eg..

Department - XYZ

Ã,  Jan-2004

Ã,  Problem TypeÃ,  Ã,  Ã,  Ã,  Total CountsÃ,  Ã,  Ã, Total SolvedÃ,  Ã,  Total Unsolved

Ã,  Ã,  Ã,  Ã,  WebÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  39Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 1
Ã,  Ã,  Ã,  Ã,  ManualÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  50Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 10

Operator NameÃ,  Ã,  Ã,  Ã,  Total CountsÃ,  Ã,  Ã, Total SolvedÃ,  Ã,  Total Unsolved

Ã,  Ã,  Ã,  Ã,  Ã, ABCÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  35Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 35Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 0
Ã,  Ã,  Ã,  Ã,  Ã, XYZÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 15Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 10Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 5
Ã,  Ã,  Ã,  Ã,  Ã, DEFÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  34Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  6

This pattern will continue for the rest of the months.

Note: In the above eg: Operator are the persons who is giving the support.Ã,  If u check the total count of the problem it would be same in both the part. the department and date range are selected through the prompts.

I tried with the master detail relationship but it is not coming up...It firsts lists the count of problem for all the months and then lists the detail of the each month in one go itself. It acts as two independent queries and not as related ones..

Plz help me out...

SrikÃ, 

[BIsrik]

One quick clarification...can we have master detail relationship established through a calculation. I mean i have a data item created with some if else condition and use this for the master detail relation column.

When i use that it gives me a error:

UDA-EE-0094 The operation "assign" is invalid for the following combination of data types: "null" and "null"

UDA-EE-0094 The operation "assign" is invalid for the following combination of data types: "null" and "null"

UDA-SQL-0460 A general exception has occurred during local processing.

RSV-SRV-0025 Unable to execute this request.

Srik.

[bdybldr]

Hi Srik,
I would use a tabular set to accomplish this....

Query1:

Select dept, Month, problem_type, total_count, total_solved, total_unsolved

UNION

Query2:

select dept, month, operator_name, total_count, total_solved, total_unsolved

NOTE: This assumes that Opererator Name and Problem Type are the same data type.

Hope this helps.

[BIsrik]

but how will does help me out...i have to first show the summary list for the month first and then show the detail part. If i use tabular set it would show all the data once ie..both summary and detail in one list itself...

Srik

[BIsrik]

The problem got solved....

I had an to_char function to get the month and year part of the date fields. I replaced this with extract functions and the master detail relationship worked.

I feel the to_char function is not supported in master detail function...Not very sure...

Srik

[bdybldr]

It's just a matter layout.

Create a new tabular set query just like the first.  Then add a filter to the first one for type = "problem".  Then add a filter to the second query for type = 'operator'.

Add a table to the page and drop the fields from the first tab set into the top cell.  Drop the fields from the second tab set into the botton cell.  That's it.  It will work...I tried it.

[BIsrik]

bdybldr, i am really not able to understand ur point...i tried with Tset but it didn't satisfy my reqd..this is because the user might have given support for both problem in web and manual type. In the query i could only select the problem type, user name, and measures field. As u point i cannot add any filters stating as problem or operator.

Well plz let us know how did u do with the Tset..

Srik

[bdybldr]

Srik,
within your tab set, you have a filter on the "left" tab model where type='problem'.  You also have a filter on the "right" tab model where type = 'operator'.

Name this query problem (or whatever).  Copy the entire query and paste it as Operator.

Open the problem query and add a filter to the top tab model: type = 'problem'.  Open the operator query and add the filter to the top tab model: type ='operator'.

Then add a table or block to your report and add the fields from the problem query to the top cell.  Add the fileds to the operator query to the bottom cell.  That's it.  The rest is just layout and padding, ect.

I should mention that you need to have the same column name for the problem type and operaor name fields.  I used "type" in the above instructions.

I hope this helps.  Let me know how it goes.

[bdybldr]

OK Srik,
I may have over-simplified your reqs a little bit.  You need to add a data item to the left and right tab sets Called "Category" or something.  Define the category for the left (problem) tab model as 'Problem Type'.  Define the right (operator) tab model as 'Operator Name'.  Then you should be able to use Master-Detail relationship by creating a section on category.  Let me know. 

I know this is possible.  Can you send an example in GoSales?

[BIsrik]

Firstly problem and operators come from different fields, and secondly is there only one query or more than one???

Really confused....Though the problem is solved i wanted to know how did u do it with Tset...

can u create a sample report on gosales and share it us..

Srik

[bdybldr]

it doesn't matter that they come from different fields because the tabset treats them as the same field.  I don't have access to ReportNet at the moment but I will try to reproduce the scenario in GOSales.

Although I can access the GOSales database and design a report, I get an error when trying to render the report.  I'll have to get someone to fix that issue.  So I can design it but can't test the results.

[BIsrik]

well can u send me the designed xml itself...

Srik