New Topic
CommunityTopicProblemDashboards
Following
photo
Chris McCarthy
Yellowfin Support Team
Assigned
photo
Deepak Chaganti
Yellowfin Support Team
Properties
Category Dashboards
Product Version 9.0

How to update a report which gets data from another report?

Tiago shared this problem 23 days ago
Defect Logged

Hello.

I build a report with a query like this: 


select
UniqueIdentifier,
Name,
Product,
Brand,
Cost,
Source
from tableA
where DestinationId = {?}

I published this report with the setting Use as View On, in Report Settings.

I build more reports using the first one. For each one, I use UniqueIdentifier as a Filter to get a single row. For example, for one report, I set the filter for UniqueIdentifier = 1. By doing this, I can create several Text Charts, one with Name, another with Product,...


My problem starts at the dashboard level. After adding the first report and setting the filter to it, I add the remaining reports, the ones dependent of the first report. When running the dashboard, i input a value to the filter and the first report shows the data, which match the value of filter, but the other reports don't show any data.

Can you identify the problem to this issue?

Note: If I define a value to the filter of the first report while creating or editing it and then publishing it, I can get data for the dependent reports. Nevertheless, if I change the value of the filter, at the dashboard level, those reports will not update their values, remaining static.

1 The same problem 0  

Replies (8)

photo
1
Deepak Chaganti ● 23 days ago

Hello Tiago,

My name is Deepak Chaganti from the Yellowfin Technical Support Team. We have received your support request, and I will be your primary contact on the following ticket:

Ticket Number: #31586
Case Title: How to update a report which gets data from another report?


Request for Additional Information:

To better understand and expedite the resolution of your case, please provide me following details:

  • The info.jsp file for this instance in HTML format. In order to get this file please follow the instructions in the following link:

https://community.yellowfinbi.com/topic/how-to-get-the-info-jsp

  • A screenshot/screen recording to illustrate the problem that you are facing
  • Could you please let me know the actual requirement? Are we looking to have a only one filter (from teh first report) and rest of the report won't be filtered (yet they display the default values?)
  • Are you linking the filters of the reports on dashboard ?

Kindly provide more details on the actual expectation of the dashboard so that I can try and implement the same on my local machine to see if there is any way to achieve it.


Sincerely,

Deepak Chaganti

Yellowfin Technical Support Engineer

Reply
photo
1
Tiago ● 23 days ago

Hello Deepak.

Thank you for your reply.

I uploaded the files to https://ftp.yellowfin.bi/files/.

They were compacted to a zip file with the name UpdateReportsData_tiago28Nov2024.7z

So, the idea is:

  1. When the user opens the dashboard, only the NHS: text, the input control, next to the NHS: text, and the button Open are visible (check either Dashboard1.png or Dashboard2.png),
  2. User inputs the NHS number of a patient, and if valid (background colour changes to green), Open button is enabled and user can click on this button to search for the patient in the database (this value is copied to the filter using Code Mode). In Dashboard1.png, everything is visible just to show to you, Deepack.
  3. If the table isn't empty for that filter value (table from the first report - FirstReport.png), the table changes status and it is visible, now. By comparing Dashboard1.png and Dashboard2.png, we can see that the report was updated, because it changes the number of rows from 2 to 1,
  4. The other reports, the ones that depend of the first report, don't update their values. I can't show you data, so you can't see that they have the same value in Dashboard1.png and in Dashboard2.png. In this case, both reports, the one with yellow colour background and the one with blue colour blackground, are showing the same data, the NHS value. The report with yellow colour background shows data for the first row, while the report with blue colour background shows data for the second row,
  5. The first report, FirstReport.png, has one filter, which copy the filter value to the query, to replace the {?} string in the query,
  6. Some fields were hide in this report, like TypeOrder, Last DNA encounter, and Last Encounter,which will be used in the other reports,
  7. An example of a report, dependent of the first report, is visible in ExampleSecondReport1.png and ExampleSecondReport2.png,
  8. TypeOrder is the UniqueIdentifier and it is filter for a single value in each report. For example, set TypeOrder=1,
  9. After applying the filter, I can go to the Chart section, see ExampleSecondReport2.png, and build different Text Charts. One for NHS, another for Patient's name, another for the Practice Code, another to Last DNA Encounter, and another for Last Encounter.

photo
1
Deepak Chaganti ● 22 days ago

Hi Tiago,

Thanks for sharing the business requirement. Usually building a particular type of report/dashboard according to the business requirement is handled by our consulting team. Please let me know if you would like to get in touch with CSM to arrange for consulting hours.

If I understood correctly:
Initially only 'NHS: text box' is visible to the user, once a valid NHS text is entered in the text box, the dashboard data would then get filtered based on this (Code Mode passes the value to dashboard filter NHS)

This way we can see the correct data from the Report1 (Filtered correct based on the NHS text input)

Unfrotuantely, I didn't understand how report1 is related to other two reports (yellow and blue colour backgrounds). If you are passing the same filter value (NHS text) to other reports as well, then you have to link them on a dashboard:
a04dd9cd8015286c275489844f6c9e0c

Have you already tried linking the filters?
We are basically trying to link the filter of report 1 with report 2 and report3 such that if a value is entered in report1 filter, the other reports get filtered accordingly. Please let me know if I understood the requirement correctly, if not please let me know if you would like to discuss this over a call. I am usually available between 9-5 AEDT(Melbourne time).

Thanks,

Deepak


photo
1
Tiago ● 22 days ago

Hi Deepack.

Let just wait a little bit, before we call CSM to this topic.

Not only the NHS: text box, but also the Open button is visible. Only when user clicks on Open button, the value is copied to the dashboard filter NHS, via Code Mode, which led to the update of the report 1.

Regarding this comment: 

Unfrotuantely, I didn't understand how report1 is related to other two reports (yellow and blue colour backgrounds).

When report 1 is created, it is saved with the option Use as View on. This allows to create other reports based on report 1. And it was what I did. The reports dependent on report 1 only have 1 filter, TypeOrder, and that filter only exist to select a single row for that report. For example, set TypeOrder=1 in a report set, that report, to show a single row where TypeOrder is 1.

Regarding filtering at the dashboard level.

I have only one filter available, the one in report 1:

9342849030e74b227b320a41071f8c35


Previously, I didn't configure link filters:

b87925ee024c681bdf6d5135ba314753


I tried and didn't work (it appears a message saying No results returned.). Next image is before running the dashboard and before No results returned message:

aad463291a684ce8c6c3941cc20a05e4


We can schedule a meeting. My timezone is GMT.

Best wishes,

Tiago Marçal

photo
1
Deepak Chaganti ● 19 days ago

Hi Tiago,

Sure, I think it's better to have a look and understand the report + filter/field setup in your environment.

Could you please let me know your availability over the week so that we can schedule a call. If your availability falls way outside my hours, I can transfer this to someone from your timezone to have a look and understand the funxtioning in the background.

Thanks,

Deepak

photo
1
Tiago ● 19 days ago

Hi Deepak.

I am available from Monday to Friday, 9am to 6pm. My timezone is GMT.

Best wishes,

Tiago Marçal

photo
1
Deepak Chaganti ● 18 days ago

Hi Tiago,

Thanks for letting us know. I will transfer the case to someone from your timezone to join the call.

Regards,

Deepak

photo
1
Tiago ● 18 days ago

Hi Deepak.

Thank you.

Best wishes,

Tiago

photo
photo
1
Chris McCarthy ● 18 days ago

Hi Tiago,

My name is Chris McCarthy and I'm taking over from my colleague Deepak on this ticket as I'm based in the UK. How are you?

Shall we set up a call for this week to take a look at your report and I'll try to assist where I can.

Does Thursday at 11am GMT work for you?

Kind regards,

Chris

Reply
photo
1
Tiago ● 17 days ago

Hi Chris.

I am fine. Thanks. And you?

I am available Thursday. It is schedule.

Thanks.

Best wishes,

Tiago

Reply
photo
1
Chris McCarthy ● 17 days ago

Hi Tiago,

All good, thanks. Here's the meeting invite for Thursday:


________________________________________________________________________________


Microsoft Teams Need help?

Join the meeting now

Meeting ID: 462 111 755 44

Passcode: 5Pd6mE9F


For organisers: Meeting options

________________________________________________________________________________

See you then.

Kind regards,

Chris

Reply
photo
1
Tiago ● 17 days ago

Hi Chris.

See you tomorrow.

Best wishes,

Tiago

Reply
photo
1
Chris McCarthy ● 16 days ago

Hi Tiago,

Thanks for your time and patience on our call just now.

We took a look at this problem and attempted to link the filters for these reports in the dashboard but that didn't work as already decribed earlier in the ticket.

Your setup is that you've got a freehand SQL report that is being used as a view. It has a mandatory filter, which is also a declared parameter, for an NHS number and another filter so only one result is shown at a time.

The data source is SQL Server.

We realised that the report based off this view didn't have the NHS number filter added so we opened up that report and added the filter. However when running the chart report, we ran into a null pointer error, which I've copied below: 

YF:2024-12-05 11:19:23.707:ERROR (ReportRunner) - [13974] [8DDA2DE6] [REPORT_RUN] [214648] Error running report: java.lang.NullPointerException: Cannot invoke "com.hof.report.result.ReportResultSet.getFieldIds()" because "" is null
java.lang.NullPointerException: Cannot invoke "com.hof.report.result.ReportResultSet.getFieldIds()" because "" is null
at com.hof.mi.process.ReportFromReportProcess.populateDatabase(ReportFromReportProcess.java:631) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.process.ReportFromReportProcess.createReportBasedDBAction(ReportFromReportProcess.java:612) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.process.ReportFromReportProcess.createReportBasedDBAction(ReportFromReportProcess.java:564) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.process.ReportRunner.J(ReportRunner.java:1153) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.process.ReportRunner.runReport(ReportRunner.java:294) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.util.background.ReportQueueItem.process(ReportQueueItem.java:93) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.util.background.ReportQueueItem.process(ReportQueueItem.java:41) ~[i4-mi.9.13 (20240906)]
at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:488) ~[i4-mi.9.13 (20240906)]
at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
at java.lang.Thread.run(Thread.java:1583) [?:?]
We spoke about how you are using the report as a view feature for efficiency reasons - you stated that by using a parameter with a required filter value, it's faster than polling all of the data.

You've sent me the SQL used in this report-as-a-view.

I'm doing to do a bit of research into this error message and ask the developers to see if we've discovered a bug here or need to come up with an alternative solution.

I'll get back to you in a few days with an answer.

Kind regards,

Chris



Reply
photo
1
Tiago ● 15 days ago

Hi Chris.

Just one correction: The report used as a view just have one filter, the NHS filter. The second report, that gets data from the first report, has another filter to force the report to have only one row of data. Later, we added a second filter, NHS, to this second report without results.

Best wishes,

Tiago

photo
photo
1
Chris McCarthy ● 16 days ago

Hi Tiago,

Quick question to clarify a couple things about this:

If you return the report you're using as a view to show a value, does the error go away in the report that's used to show a chart? Does the NHS filter then work in that report?

I believe we looked at this one the call, but if you have the parameter unset in the report/view and then use the NHS field as a filter, it does bring through results correctly, is that right?

I think this error we're seeing might be because the report that's throwing it might be expecting a value from the NHS parameter but as it's just a parameter it ends up being an empty string.

Kind regards,

Chris

Reply
photo
1
Tiago ● 15 days ago

Hi Chris.

With a value defined in the filter for the report that is used as a view, the error goes away when running the dashboard. In this case, something different appears on the logs: 

YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] Creation location:
YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] DBAction was not disconnected! Calling disconnect() from finalize(). Connection id = 0
YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] Creation location:
YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] DBAction was not disconnected! Calling disconnect() from finalize(). Connection id = 0
YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] Creation location:
YF:2024-12-05 17:50:50.416: WARN (DBAction) - [10] [Unknown] [Unknown] DBAction was not disconnected! Calling disconnect() from finalize(). Connection id = 0

In both scenarios, NHS filter with and without a default value, the report used as a view returns data when a new filter value is set at the dashboard level.

Best wishes,

Tiago

photo
photo
2
Chris McCarthy ● 15 days ago

Hi Tiago,

On some further experimentation, I was able to replicate this against a freehand SQL view on my own SQL Server connection using a simplified version of your SQL.

e5d5cc7007684b5f6f636a6cb035f666

7c66c3571ef2a1481ae4625b3bf57091


I'll submit it as a bug for review but it may be that this is impossible as you either need to define a default value for a parameter and reports built off that will always use the default parameter value, or you put the freehand SQL into a view and avoid using the declared parameter.

Kind regards,

Chris

Reply
photo
1
Tiago ● 15 days ago

Hi Chris.

Good to know you were able to replicate the issue. I hope there is a solution.

Either way, I can workaround this issue, creating independent reports, by splitting the SQL query to get one row for each report and using the {?} placeholder in the SQL query. But, I think, the current solution is better, since it needs a single SQL query, and everything is related with a single report, used as a view.

Awaiting for more news.

Thanks.

Best wishes,

Tiago

photo
1
Chris McCarthy ● 15 days ago

Hi Tiago,

No worries. I'll let you know when I have more news.

Have a good weekend!

Kind regards,

Chris

photo
Leave a Comment
 
Attach a file